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

}

Ian Boyd · Accepted Answer · 2009-06-27 16:22:19Z

17

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

edited Jun 27, 2009 at 16:22

Ian Boyd

259k272 gold badges920 silver badges1.3k bronze badges

answered Jun 25, 2009 at 20:39

tvanfosson

534k102 gold badges703 silver badges801 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ian Boyd Over a year ago

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.

blu · Accepted Answer · 2009-06-25 20:29:58Z

0

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.

answered Jun 25, 2009 at 20:29

blu

13.2k22 gold badges73 silver badges107 bronze badges

2 Comments

Ian Boyd Over a year ago

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

blu Over a year ago

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

Marc · Accepted Answer · 2009-06-25 20:46:37Z

0

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?

answered Jun 25, 2009 at 20:46

Marc

1,1781 gold badge18 silver badges31 bronze badges

3 Comments

Ian Boyd Over a year ago

Because that's not this question.

Yuliy Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related