How do i detect the provider from a user-specified connectionstring?

Question

My application needs to do a certain query against a user-specified connectionstring. The query will need some parameters, so I was thinking of using the DbProviderFactory , in combination with myConnection.GetSchema("DataSourceInformation").Rows[0]["ParameterMarkerFormat"] to find out if I need to use :(Oracle), @(Sql) or ?(OleDb) to send in the parameters to the database.

To use the DbProviderFactory I need to find out what Provider is needed for a connectionstring. Is there a good way to do this, or do I need to use some sort of if(conStr.indexOf("oledb") != -1) { type = DbTypes.OleDB; } logic? (Or is there a better way to connect to an "unknown" database type?)

Note: The DbProviderFactory expects the provider to be in the form of System.Data.SqlClient, not the SQLNCLI.1 that is in the actual connectionstring.

Answer 1

You need to specify this in your app.config file. From MSDN,

<configuration>
  <connectionStrings>
    <clear/>
    <add name="NorthwindSQL" 
     providerName="System.Data.SqlClient" 
     connectionString=
     "Data Source=MSSQL1;Initial Catalog=Northwind;Integrated Security=true"
    />

    <add name="NorthwindAccess" 
     providerName="System.Data.OleDb" 
     connectionString=
     "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Northwind.mdb;"
    />
  </connectionStrings>
</configuration>

Answer 2

The thing is that a connection string has meaning only in the context of the provider and not the other way around. In other words there's no standard for identifying the provider based on the string. That said, you can generally infer the provider from the string as you've demonstrated with using IndexOf.

You can get a list of installed providers using the static method DbProviderFactories.GetFactoryClasses(). This will return a DataTable with a row for each provider. The row will have the column "InvariantName" that will give you the correct value to pass to DbProviderFactories.GetFactory. If your application design allows, you can expose the list of available providers at the same time you get the connection string so your users can specify which provider they intend to make the connection. The "Name" column is intended for that very purpose.