DbProviderFactory

Since .Net 2.0, DbProviderFactory helped building application working on database without knowing the type of the database. Give us a query string, a query and we can give you back the data returned by the query.

In the old days, the app.config file contained and connectionStrings section with the actual connection string and the provider name.

<?xml version="1.0" encoding="utf-8"?>  
<configuration>  
  <connectionStrings>  
    <add name="DefaultConnection" connectionString="Server=YOURSERVERNAME; Database=YOURDATABASENAME; Trusted_Connection=True;"  
      providerName="System.Data.SqlClient" />  
  </connectionStrings>  
</configuration>  

Nowadays, the appsettings.json only contains the connection string, so the application have to know the type of the database to be able to create the DbConnection of the expected type.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=YOURSERVERNAME; Database=YOURDATABASENAME; Trusted_Connection=True"
  } 
}

Yet, it is possible to overcome this by using the fact that a connection is a collection of key/value pair, so we can include in it a ProviderName key.

{
  "ConnectionStrings": {
    "DefaultConnection": "ProviderName=System.Data.SqlClient; Server=YOURSERVERNAME; Database=YOURDATABASENAME; Trusted_Connection=True"
  } 
}

Then we simply have to extract the provider name from the connection string before invoking the factory:

var builder = new DbConnectionStringBuilder();
builder.ConnectionString = configuration.GetConnectionString("DefaultConnection");
var providerName = (string)builder["ProviderName"];
builder.Remove("ProviderName");

var factory = DbProviderFactories.GetFactory(providerName);
var connection = factory.CreateConnection();
connection.ConnectionString = builder.ConnectionString;

Finally, to make it work, you may have to declare the factories somewhere during the initialization of the application.

DbProviderFactories.RegisterFactory("System.Data.Odbc", typeof(System.Data.Odbc.OdbcFactory));
DbProviderFactories.RegisterFactory("System.Data.OleDb", typeof(System.Data.OleDb.OleDbFactory));
DbProviderFactories.RegisterFactory("System.Data.SqlClient", typeof(System.Data.SqlClient.SqlClientFactory));