Obtaining the DbProviderFactory 

The DbProviderFactory provides a strongly-typed object based on information provided at runtime about the provider along with a connection string. Code written against the DbProviderFactory is not dependent on any particular provider. To simplify code references, include the following statements in your classes:

using System.Data.Common;

Imports System.Data.Common

Supplying the Provider Name

The provider name supplied at runtime determines the actual provider that the DBProviderFactory creates. You can elect to store the provider name offline and retrieve it at runtime to dynamically connect to different providers. Some of the places you might choose to store the information are in configuration files, the registry, or in structured storage. The advantage of storing information in a configuration file is that it is not compiled with your application code, and can therefore be modified at any time without having to modify and rebuild the application. When a configuration file has been changed, a new instance of the application will use the saved settings when it loads.

In this example, the provider name is saved in the Web.config file for an ASP.NET application, where it can be retrieved at runtime. If you are working with a Windows application, you can also store it in the application's configuration file. The appSettings section of a configuration file lets you store custom key/value pairs that can be retrieved at runtime.

The following configuration file fragment defines key names of "provider" and "connectionString". The value part of each definition points to the actual provider and the connection string, respectively. In this case, provider is the key, and the value is a string representing the System.Data.SqlClient provider. The connection string that is required to connect to the data source is supplied as the value for the connectionString key. If you modify the provider or connectionString values and save the configuration file, the new settings will take effect when new instances of the application load.

<configuration>
  <appSettings>
     <!-- Specify the provider  -->  
     <add key="provider" value="System.Data.SqlClient" />
     <!-- Specify the connection string -->  
    <add key="connectionString" value="Data Source=localhost;
     Integrated Security=SSPI;Initial Catalog=AdventureWorks"/>
  </appSettings>
</configuration>

Creating the Factory

When the provider information has been retrieved from a configuration file, a factory object can be created and cached in a local variable so that it is available to the code in the application. Using a variable that is static (shared in Visual Basic) causes the factory to be maintained across multiple calls to a page in an ASP.NET application, avoiding the need to create a new instance each time a page is rendered. A static or shared variable can also be created in the Global.asax page so that the factory object is available to the entire application.

The following code fragment retrieves the "provider" value from the configuration file to use as the basis for the GetFactory method. The DbProviderFactory object provides access to provider-specific methods for retrieving and manipulating data, and the DbProviderFactory object is cached in the dataFactory variable.

static DbProviderFactory dataFactory = 
    DbProviderFactories.GetFactory(
    ConfigurationSettings.AppSettings["provider"]);

Shared dataFactory As DbProviderFactory = _
    DbProviderFactories.GetFactory( _
    ConfigurationSettings.AppSettings("provider"))

See Also

Reference

appSettings Element (General Settings Schema)

Concepts

Writing Provider Independent Code in ADO.NET
Provider Independent Model Overview
Creating Commands and Retrieving Data
Retrieving Data with a DbDataAdapter