views:

2415

answers:

3

How do you construct a DbConnection based on a provider name?

Sample provider names

  • System.Data.SqlClient
  • System.Data.OleDb
  • System.Data.Odbc
  • FirebirdSql.Data.FirebirdClient

i have connection strings stored in my IIS server's web.config file:

<connectionStrings>
  <add name="development"
        connectionString="Provider = IBMDA400; Data Source = MY_SYSTEM_NAME; User Id = myUsername; Password = myPassword;" 
        providerName="System.Data.OleDb" />
  <add name="live" 
        connectionString="usd=sa;pwd=password;server=deathstar;" 
        providerName="System.Data.Odbc" />
  <add name="testing" 
        connectionString="usd=sa;pwd=password;server=deathstar;" 
        providerName="System.Data.SqlClient" />
  <add name="offline"
        connectionString="Server=localhost;User=SYSDBA;Password=masterkey;Charser=NONE;Database=c:\data\mydb.fdb"
        providerName="FirebirdSql.Data.FirebirdClient"/>

You can see they all use different providers. When it comes time for me to create a connection, i have to know what kind of DbConnection to create, e.g.:

  • SqlConnection
  • OleDbConnection
  • OdbcConnection
  • FbConnection

The connectionStrings entries contains a providerName, but these aren't the names of DbConnection descendant classes, but appear to be a namespace

How do i turn construct a DbConnection based on a string providerName?


public DbConnection GetConnection(String connectionName)
{
    //Get the connectionString infomation
    ConnectionStringSettings cs = 
          ConfigurationManager.ConnectionStrings[connectionName];
    if (cs == null)
       throw new ConfigurationException("Invalid connection name \""+connectionName+"\");

    //Create a connection based on the provider
    DbConnection conn = new DbConnection();

}
A: 

Check out this Hanselman blog on adding custom build types for different connection strings names, it looks like it may fit what you want to accomplish in a different way than using the provider types.

blu
It's not different builds. One system has to connect to various databases based on the circumstances.
Ian Boyd
ahh, I misunderstood the example. I wouldn't think an application would connect to Development, Production, and Testing at the same time.
blu
+5  A: 

If you go this route, I think you'll want to use the DbProviderFactories class to get a DbProviderFactory that you can use to construct the connection. I haven't tried this code out, but I think it will work. It's possible that you may need to look up the provider name using the GetFactoryClasses method on the DbProviderFactories class and use the InvariantName.

public DbConnection GetConnection(String connectionName)
{
   //Get the connection string info from web.config
   ConnectionStringSettings cs= 
         ConfigurationManager.ConnectionStrings[connectionName];

   //documented to return null if it couldn't be found
   if (cs == null)
      throw new ConfigurationErrorsException("Invalid connection name \""+connectionName+"\"");

   //Get the factory for the given provider (e.g. "System.Data.SqlClient")
   DbProviderFactory factory = 
         DbProviderFactories.GetFactory(cs.ProviderName);

   //Undefined behaviour if GetFactory couldn't find a provider.
   //Defensive test for null factory anyway
   if (factory == null)
      throw new Exception("Could not obtain factory for provider \""+cs.ProviderName+"\"");

   //Have the factory give us the right connection object      
   DbConnection conn = factory.CreateConnection();

   //Undefined behaviour if CreateConnection failed
   //Defensive test for null connection anyway
   if (conn == null)
      throw new Exception("Could not obtain connection from factory");

   //Knowing the connection string, open the connection
   conn.ConnectionString = cs.ConnectionString;
   conn.Open()

   return conn;
}
tvanfosson
This is exactly what's needed. The secret ingredient is the "provider name" is used somewhere in the system, and that is the collection DbProviderFactories. Each provider registers itself with the DbProviderFactories; that's how you find it.
Ian Boyd
A: 

If the providerName for the particular connection name (dev, test, prod) never changes why cant you do a switch on the connectionName param for your method and set the providerName instance that way?

Marc
Because that's not this question.
Ian Boyd
Because that way lies madness. Five years later, a new dev gets assigne the task of moving the test database from SQL Server to Oracle. They update the connection string, and it fails miserably. Hidden assumptions like this (i.e. dev database uses X database provider) are a very good way of causing tears down the road.
Yuliy
To be honest, its not a good solution to begin with. To have your production code be dependent on dynamically changing DB providers brings tears long before trying to solve this question here.
Marc

related questions