List available DB providers

Question

I have some problems with building connection strings. I used to generate "Provider=SQLOLEDB" or "Provider=SQLNCLI". Now I see that some users have SQLNCLI10 while SQLNCLI is missing. Is it possible to enumerate available providers so I could pick a valid one?

System.Data.Common.DbProviderFactories.GetFactoryClasses() lists .NET data providers but I still don't know which connection string parameters are valid.

Answer 1

If you're using System.Data.SqlClient.SqlConnection (either directly or indirectly) then there is no need to specify a Provider element in the connection string. I imagine this value is simply ignored if provided.

See the documentation for the comprehensive list of all supported elements in SqlConnection connection strings.

However, if you're using OleDb, this does what you want: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbenumerator.aspx

For example:

internal static class Program
{
    private static void Main(string[] args)
    {
        using (OleDbDataReader dataReader = OleDbEnumerator.GetRootEnumerator())
        {
            while (dataReader.Read())
            {
                Console.WriteLine("{0}, {1}", dataReader[0], dataReader[2]);
            }
        }
    }
}

Answer 2

For more details on connection string, see connectionstrings.com.