ASP.NET: How to create a connection from a web.config ConnectionString?

Question

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();

}

Solution

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;
}

Comments

[mistertodd]

It's not different builds. One system has to connect to various databases based on the circumstances.

[blu]

ahh, I misunderstood the example. I wouldn't think an application would connect to Development, Production, and Testing at the same time.

[mistertodd]

Because that's not this question.

[mistertodd]

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.

[Yuliy]

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.

[Marc]

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.