[ACCEPTED]-C# Retrieving correct DbConnection object by connection string-dbconnection

Accepted answer

Score: 32

   DbConnection GetConnection(string connStr)
    { string providerName = null;
      var    csb = new DbConnectionStringBuilder{ConnectionString=connStr};

      if (csb.ContainsKey("provider")) 
       { providerName = csb["provider"].ToString();
       }          
      else
       { var css = ConfigurationManager
                           .ConnectionStrings
                           .Cast<ConnectionStringSettings>()
                           .FirstOrDefault(x=>x.ConnectionString==connStr);
         if (css != null) providerName = css.ProviderName;
       }

      if (providerName != null) 
       { var providerExists =  DbProviderFactories
                                     .GetFactoryClasses()
                                     .Rows.Cast<DataRow>()
                                     .Any(r=>r[2].Equals(providerName));
         if (providerExists) 
          { var factory = DbProviderFactories.GetFactory(providerName);
            var dbConnection = factory.CreateConnection();

            dbConnection.ConnectionString = connStr;
            return dbConnection;
          }
       }

      return null;
   }

Score: 19

if you're using framework 2.0 or above, and 4 you can get them to pass in a second string 3 with the driver class, you can use the dbProviderFactory 2 class to load the driver for you.

DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory(myDriverClass);
DbConnection dbConnection = dbProviderFactory.CreateConnection();
dbConnection.ConnectionString = myConnectionString;

Here's 1 an MSDN link to the Factory class: http://msdn.microsoft.com/en-us/library/wda6c36e.aspx

Score: 1

You should be able to parse out the Provider 6 section and pass it into DbProviderFactories.GetFactory 5 which will return a OdbcFactory, OleDbFactory 4 or SqlClientFactory and let you then perform 3 CreateConnection etc.

I'm not sure how this 2 would work with Oracle unless they provide 1 an OracleDbFactory.

Score: 1

Most connection strings (at least in .NET 6 2.0) also have a providerName property that 5 goes with them. So a SQL connection string 4 will have a provider Name like:

providerName="System.Data.SqlClient"

So your method 3 would need to accept both the connection 2 string and the provider name and then you 1 could use the DbProviderFactory as mentioned by damieng.

Source: stackoverflow.com

[ACCEPTED]-C# Retrieving correct DbConnection object by connection string-dbconnection

Is there any need to close a DbConnection if a using clause is used?

How to check is connection string valid?

DBReader has rows but Read() returns false

SQLite EF6 programmatically set connection string at runtime

Entity Framework: "ProviderName" in connection string required- all of the sudden?

With VBA, find the version of the MySQL ODBC driver installed in Windows

Specifying Port With SqlConnectionStringBuilder?

Output Azure SQL Database ConnectionString after template deployment

How to hide connection string, user name, pw when using source control?

Edit zip file content in subfolder with Powershell