How can I control which SQL provider is used for a database connection?

Question

In a recent comment, I was told:

the default SQL Server client used to be System.Data.SqlClient, but more recently there's also Microsoft.Data.SqlClient - same thing conceptually, but packaged slightly differently and more up to date. It is largely up to you whether to use System.Data.SqlClient or Microsoft.Data.SqlClient

Similarly, I recently tried to use a DSN connection string "DSN=MyDSNName" and this failed because I need to use an ODBC driver, not SqlServer - DSN isn't a property on SqlConnectionStringBuilder.

I was under the impression that .NET automatically chose the right driver based on my connection string but seemingly not.

How do I control this sort of stuff? Can .Net auto-detect the right provider to use from my connection-string, or must I hard-code in C# whether I want to use Odbc Vs SqlServer for instance?

Marc Gravell · Accepted Answer · 2020-07-21 10:57:52Z

3

When using direct connections (not DSN strings), then it all depends on how you are constructing the connection; if you are using new SqlConnection(...), then it all comes down to your code; changing the implementation is as simple as adding the package reference and changing the using directive from

using System.Data.SqlClient;

to

using Microsoft.Data.SqlClient;

This means that your new SqlConnection(...) is now talking about a completely different type, that just happens to be called SqlConnection in both places.

If you're using the automatic provider model via application config, then you need to change the provider there, i.e.

<add name="Name" providerName="System.Data.SqlClient" connectionString="..." />

becomes

<add name="Name" providerName="Microsoft.Data.SqlClient" connectionString="..." />

This would typically be used with something like:

internal static DbConnection CreateConnection(string name)
{
    var config = ConfigurationManager.ConnectionStrings[name];
    if (config is null)
    {
        // throw some specific exception, or return null; whatever you want to do
    }
    var factory = DbProviderFactories.GetFactory(config.ProviderName);
    var connection = factory.CreateConnection(); // note: not "using" - caller is owner
    connection.ConnectionString = connection.ConnectionString;
    return connection;
}

This topic is covered in more detail here, along with example <configuration> scenarios.

edited Jul 21, 2020 at 10:57

answered Jul 21, 2020 at 10:40

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

1 Comment

Marc Gravell Over a year ago

@Mr.Boy sure, example added

Viknesh · Accepted Answer · 2020-07-21 10:28:22Z

0

Even if you have updated the connection string for both Odbc and Sql Server in webconfig or appsettings, if you need to utilize it in the code, you will need to call the connection string in the C# code to do any db activities. You can call it in the code as below:

var connectionString = Environment.GetEnvironmentVariable("DefaultConnection");
or
var connectionString = Configuration.GetConnectionString("DefaultConnection");
or 
string connectionString = ConfigurationSettings.AppSettings("DefaultConnection");

answered Jul 21, 2020 at 10:28

Viknesh

2031 silver badge10 bronze badges

Collectives™ on Stack Overflow

How can I control which SQL provider is used for a database connection?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related