C# Retrieving correct DbConnection object by connection string

Question

I have a connection string being passed to a function, and I need to create a DbConnection based object (i.e. SQLConnection, OracleConnection, OLEDbConnection etc) based on this string.

Is there any inbuilt functionality to do this, or any 3rd party libraries to assist. We are not necessarily building this connection string, so we cannot rely on a format the string is written in to determine its type, and I would prefer not to have to code up all combinations and permutations of possible connection strings

Tyler2P · Accepted Answer · 2022-02-28 16:15:24Z

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

edited Feb 28, 2022 at 16:15

Tyler2P

2,37030 gold badges26 silver badges34 bronze badges

answered Oct 9, 2008 at 1:13

Mark Cidade

100k33 gold badges231 silver badges238 bronze badges

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

3 Comments

Philipp Munin Over a year ago

I also don't think it's gonna work for OleDb connection strings like: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=9cb8a4c4-9661-4c10-a21c-fb4a85ce2471.xlsx;Mode=ReadWrite;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=0;"

Mark Cidade Over a year ago

No, it will only return an OLEDB connection if the provider is specified as "System.Data.OleDb" or if the connection string is specified in the configuration.

Mr. Boy Over a year ago

I find this really surprising, that you can't make your code DB-independent without this kind of hassle.

Eric Tuttleman · Accepted Answer · 2008-10-09 00:29:26Z

19

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

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

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

answered Oct 9, 2008 at 0:29

Eric Tuttleman

1,3201 gold badge9 silver badges16 bronze badges

Comments

DamienG · Accepted Answer · 2008-10-09 00:28:22Z

1

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

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

answered Oct 9, 2008 at 0:28

DamienG

6,6841 gold badge30 silver badges43 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:34:31Z

1

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

providerName="System.Data.SqlClient"

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

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Oct 9, 2008 at 0:31

Bryant

8,6701 gold badge37 silver badges54 bronze badges

Collectives™ on Stack Overflow

C# Retrieving correct DbConnection object by connection string

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related