DbProviderFactories Demystified

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

In this BLOG I will describe how to register a .NET Data Provider with the ADO.NET   DbProviderFactories Class.

System.Data.Common Namespace defines a set of abstract classes that are implemented by all .NET Data Providers. The intent is to provide a Data Source/Provider agnostic way of accessing data. Provider agnostic applications do not have direct references to ADO.NET Data Provider assemblies and it is the job of DbProviderFactories class to load the requested .NET Data Provider and return a concrete class known as DbProviderFactory. DbProviderFactory serves as the Factory for DbConnection, DbCommand and other ADO.NET  objects. For example the sample code shown below will load .NET Data Provider for Teradata assembly using the invariant name Teradata.Client.Provider:

class TestConsole
{
    static void Main(string[] args)
    {
        // DbProviderFactories loads "Teradata.Client.Provider.dll" assembly using
        DbProviderFactory factory = DbProviderFactories.GetFactory("Teradata.Client.Provider");

        // DbProviderFactory creates a TdConnection class.
        using (DbConnection cn = factory.CreateConnection())
        {
            // DbProviderFactory creates a TdConnectionStringBuilder class.
            DbConnectionStringBuilder conStrBuilder = factory.CreateConnectionStringBuilder();
            conStrBuilder["Data Source"] = @"System1.myCorp.com";
            conStrBuilder["User Id"] = @"Alex";
            conStrBuilder["Password"] = @"myPassword";

            cn.ConnectionString = conStrBuilder.ConnectionString;
            cn.Open();

            // DbProviderFactory creates a TdCommand class.
            DbCommand cmd = factory.CreateCommand();
            cmd.Connection = cn;
            cmd.CommandText = @"SELECT DATE";

            Console.WriteLine("System Date: {0}", cmd.ExecuteScalar());
        }
    }
}

The mystery is how DbProviderFactories correlates an Invariant Name with a Provider Factory class (derived from DbProviderFactory class) and the corresponding assembly. DbProviderFactories simply reads the information from the configuration context/files (Machine.Config, App.Config ...).

NOTE: The overall concept of .NET Framework Configuration framework/files is outside of the scope of this blog. For additional information refer to Cracking the Mysteries of .NET 2.0 Configuration article by Jon Rista.

DbProviderFactories will read the System.Data section of the configuration context. For example .NET Framework 2.0 Machine.Config (located in %SystemRoot%\Microsoft.NET\Framework\v2.0.50727\CONFIG and %SystemRoot%\Microsoft.NET\Framework64\v2.0.50727\CONFIG ) usually has the following System.Data section:

<system.data>
  <DbProviderFactories>
     <add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc"
         type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    <add name="OleDb Data Provider" invariant="System.Data.OleDb"
         description=".Net Framework Data Provider for OleDb" 
type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    <add name="OracleClient Data Provider" invariant="System.Data.OracleClient"
         description=".Net Framework Data Provider for Oracle" 
type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    <add name="SqlClient Data Provider" invariant="System.Data.SqlClient"
         description=".Net Framework Data Provider for SqlServer" 
type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
  </DbProviderFactories>
</system.data>

The invariant attribute is a version independent name (e.g. System.Data.Odbc) and type attribute is a fully qualified Type name for the associated DbProviderFactory class.  Type name has two parts namely class name (System.Data.Odbc.OdbcFactory) and the Strong Assembly Name (System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"). At this point DbProviderFactories simply invokes Type.GetType   to load Assembly and create an instance of the Type.

This means that you can simply add your favorite .NET Data Provider to the configuration context. For example add the following XML element for .NET Data Provider for Teradata version 13.1.0.4:

<add name=".NET Data Provider for Teradata" invariant="Teradata.Client.Provider"
    description=".NET Framework Data Provider for Teradata" 
type="Teradata.Client.Provider.TdFactory, Teradata.Client.Provider, Version=13.1.0.4, Culture=neutral, PublicKeyToken=76b417ee2e04956c" />

.NET Data Provider for Teradata installation automatically modifies the .NET Framework 2.0 Machine.Config files (32-bit and 64-bit). Note the emphasizes on .NET Framework 2.0; .NET Framework 4.0 has a separate set of Machine.Config files which you can manually update or alternatively you can invoke InstallUtil.exe version 4.0 as described below to update .NET Framework 4.0 Machine.config file:

  1. Open a command with elevated permissions (Run as Administrator).
  2. Execute 32-bit InstallUtil version 4.0 to update the 32-bit Machine.config  with a reference to .NET Data Provider for Teradata: %SystemRoot%\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe "C:\Program Files (x86)\Teradata\NET Data Provider for Teradata\13.01.00\bin\Teradata.Client.Provider.dll"
  3. Execute 64-bit InstallUtil version 4.0 to update the 64-bit machine.config file. 

There is yet another alternative; App.Config file can simply have a "System.Data" section which is merged with the System.Data section of the Machine.config file as shown below:

<?xml version="1.0"?>
<configuration>

  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>

  <system.data>
    <DbProviderFactories>
      <add name=".NET Data Provider for Teradata" invariant="Teradata.Client.Provider.Unique"
        description=".NET Framework Data Provider for Teradata" 
        type="Teradata.Client.Provider.TdFactory, Teradata.Client.Provider, Version=13.1.0.4, Culture=neutral, PublicKeyToken=76b417ee2e04956c" />
    </DbProviderFactories>
  </system.data>

</configuration>

Note that the Data Provider Invariant name must be unique after the content of machine.config and app.config are merged into the configuration context.

2 Comments

I am running on target framework as ".NET framework 4 client Profile". and As per code provided I updated my app.config file with <dbProviderFactories> code with set invariant as "Teradata.Client.Provider.Unique".

Now my Form1.cs file has code as follow:

DbProviderFactory factory = DbProviderFactories.GetFactory("Terada.Client.Provider"); 

And that thows an error on runtime "Unable to find the requested .Net framework Data Provider. It may not be installed."

I have installed reference to "Teradata.Client.Provider.dll" version 14.10.0.0

Can you please help me here to resolve my issue.

Thanks,

Teradata Employee

You must use the Invariant name in the App.Config file. That is GetFactory("Teradata.Client.Provider.Unique") if the entry in App.config file has the "Teradata.Client.Provider.Unique" invariant name.