DbProviderFactories and You

One of the great new features of ADO.NET 2.0 is the use of reflection as a means of instantiating database providers programmatically. The information .NET uses to enumerate the available data providers in the system is relatively simple. It merely looks in the machine.config and in your own app.config file for some XML data to tell it what providers are installed and what assemblies those providers are in.

Scenario 1:  Version Independent (does not use the Global Assembly Cache)

This method allows you to drop any new version of the System.Data.SQLite.DLL into your application's folder and use it without any code modifications or recompiling.  Add the following code to your app.config file:

<configuration>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite"/>
      <add name="SQLite Data Provider" invariant="System.Data.SQLite"
           description=".Net Framework Data Provider for SQLite"
type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" /> </DbProviderFactories> </system.data> </configuration>

Scenario 2:  Version Dependent, using either the DLL located in the same folder as the application or the Global Assembly Cache

This method expands on the above XML to provide the version number and key token of the SQLite DLL so it can be found either in the same folder as the application or looked up in the GAC.  The downside to this method is that DbProviderFactories will use this version information to only load the version specified.  This means if you update the DLL, you must also update this XML.

<configuration>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite"/>
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" 
           description=".Net Framework Data Provider for SQLite"
           type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite,
                 Version=1.0.80.0, Culture=neutral,
                 PublicKeyToken=db937bc2d44ff139"/>
    </DbProviderFactories>
  </system.data>
</configuration>

The following C# code demonstrates instantiating SQLite through DbProviderFactories:

      DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SQLite");
      using (DbConnection cnn = fact.CreateConnection())
      {
        cnn.ConnectionString = "Data Source=test.db3";
        cnn.Open();
      }