Sqlclient: How to use Microsoft.Data.SqlClient with DbProviderFactories?

[tonihele]

How to use Microsoft.Data.SqlClient with DbProviderFactories? Is there a way to register this SQL Client to DBProviderFactories? It doesn't seem to appear there automatically? In .NET Framework 4.6.1.

I'm trying to switch over from the System.Data.SqlClient.

[cheenamalhotra]

Hi @tonihele

Microsoft.Data.SqlClient provides SqlClientFactory as implementation of DBProviderFactory, which is applicable for both .NET Framework and .NET Core.

For registering, you should use below Snippet:

<system.data>  
  <DbProviderFactories>  
    <add name="SqlClient Data Provider"  
     invariant="Microsoft.Data.SqlClient"   
     description=".Net Framework Data Provider for SqlServer"   
     type="Microsoft.Data.SqlClient.SqlClientFactory, Microsoft.Data.SqlClient,   
     Version=1.0.19269.1, Culture=neutral, PublicKeyToken=****"  
    />  
  </DbProviderFactories>  
</system.data>

Alternatively, you can also use the code below:

using (SqlConnection con = new SqlConnection())
{
    PropertyInfo dbProviderFactoryProperty = con.GetType().GetProperty("DbProviderFactory", BindingFlags.NonPublic | BindingFlags.Instance);
    DbProviderFactory factory = dbProviderFactoryProperty.GetValue(con) as DbProviderFactory;
    // 'factory' is an instance of `SqlClientFactory'
    // Add code here.
}

_Edit_: https://github.com/dotnet/SqlClient/issues/239#issuecomment-537770942 is the correct solution for this issue.

[tonihele]

Thank you for the reply. I have few concerns over the registration (the Snippet you gave). It looks like this needs to be manually updated each time we update the Nuget package to point to the correct assembly. Is this true?
Is there some more automatic way to register this? We have several application and this process would be needed to all of them.

[cheenamalhotra]

You should be able to register without version details as well, as below block in App.config:

  <system.data>
    <DbProviderFactories>
      <add name="SqlClient Data Provider"
       invariant="Microsoft.Data.SqlClient"
       description=".Net Framework Data Provider for SqlServer"
       type="Microsoft.Data.SqlClient.SqlClientFactory, Microsoft.Data.SqlClient" />
    </DbProviderFactories>
  </system.data>

And then in your Code:

DbProviderFactory f = DbProviderFactories.GetFactory("Microsoft.Data.SqlClient");
using (DbConnection con = f.CreateConnection())
{
     con.ConnectionString = "<your connection string>";
     con.Open();
}

[tonihele]

Great! It seems to indeed work just fine without the version and public key. Thanks you!

[sherland]

@cheenamalhotra - about the code above where you use reflection to get the factory.
I had a look at the code -

/master/src/Microsoft.Data.SqlClient/netfx/src/Microsoft/Data/SqlClient/SqlConnection.cs
& 
/master/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnection.cs

The property DbProviderFactory simply returns SqlClientFactory.Instance in both cases similar to:
c# protected override DbProviderFactory DbProviderFactory { get => SqlClientFactory.Instance; }
Would it not be better to just use SqlClientFactory.Instance instead of the reflection-code, or is there something I'm missing?

[cheenamalhotra]

@sherland The above code makes use of DbProviderFactory which is generic to any provider, whereas SqlClientInstance needs import of using Microsoft.Data.SqlClient.

Ofcourse you can create instance directly, that code is for users who do not want to import using and be able to work with System.Data.Common.DbProviderFactory and other System.Data.Common classes.

[sherland]

@cheenamalhotra Just to be sure, I'm refering to this code:

using (SqlConnection con = new SqlConnection())
{
    PropertyInfo dbProviderFactoryProperty = con.GetType().GetProperty("DbProviderFactory", BindingFlags.NonPublic | BindingFlags.Instance);
    DbProviderFactory factory = dbProviderFactoryProperty.GetValue(con) as DbProviderFactory;
    // 'factory' is an instance of `SqlClientFactory'
    // Add code here.
}

I was assuming that your code refered to Microsoft.Data.SqlClient.SqlConnection - Which means that Microsoft.Data.SqlClient must be imported anyway, and then there would not be any advantage of the reflection? - Or does new System.Data.SqlClient.SqlConnection().DbProviderFactory return a Microsoft.Data.SqlClient.SqlClientFactory instance? - (That would be confusing?)

[cheenamalhotra]

Sorry I was referring to this one: https://github.com/dotnet/SqlClient/issues/239#issuecomment-537770942

For the one you mentioned, that's also going to return the Microsoft.Data.SqlClient.SqlClientFactory instance , it was part of a test that verifies this behavior, that's where I grabbed it from. And yes in that case reflection is not needed, it just validates DbProviderFactory behavior.

[ericsampson]

@cheenamalhotra , is that code that you show in #239 in any of the normal developer docs?

[cheenamalhotra]

@ericsampson

Are you talking about https://github.com/dotnet/SqlClient/issues/239#issuecomment-537770942 ?
You can find this documented on Microsoft Docs - Obtaining a DB Provider Factory for System.Data.SqlClient, but also applies to other DB Providers.

[ericsampson]

@cheenamalhotra thanks, in that document I was looking for an example similar to this section, except using the .NET Core configuration provider code (like appsettings.json) instead of web/app.config. I haven't seen that type of example anywhere.

[schmidlkofer]

Referring to this code sample:

using (SqlConnection con = new SqlConnection())
{
    PropertyInfo dbProviderFactoryProperty = con.GetType().GetProperty("DbProviderFactory", BindingFlags.NonPublic | BindingFlags.Instance);
    DbProviderFactory factory = dbProviderFactoryProperty.GetValue(con) as DbProviderFactory;
    // 'factory' is an instance of SqlClientFactory'
    // Add code here.
}

Outside of tests, what would the purpose of this code be? If I was using the factory only to get a new connection, I would already have had it with the SqlConnection.

[roji]

@schmidlkofer the main purpose of DbProviderFactory/DbProviderFactories is to get a DbConnection without coding directly against SqlClient, i.e. to write libraries or applications that are database-agnostic. If your application is meant to work only with SqlClient, you probably don't need to use DbProviderFactory/DbProviderFactories.