Understanding ADO.Net Provider Factory in .Net 2.0

1. public virtual DbConnection CreateConnection();

2. public virtual DbCommand CreateCommand();

3. public virtual DbCommandBuilder CreateCommandBuilder();

4. public virtual DbConnectionStringBuilder CreateConnectionStringBuilder();

5. public virtual DbDataAdapter CreateDataAdapter();

6. public virtual DbDataSourceEnumerator CreateDataSourceEnumerator();

7. public virtual DbParameter CreateParameter();

8. public virtual CodeAccessPermission CreatePermission(PermissionState state);

Method names are self explanatory about their usage; we will discuss some of these methods subsequently in this article.
DbProviderFactory for each available data provider is registered in the machine.config file as shown below.

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Providerfor 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 Providerfor 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 Providerfor 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 Providerfor SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</DbProviderFactories>
</system.data>

Programmer can as well create a new entry in the machine.config file for a new data provider to expose the corresponding DbProviderFactory.

Another interesting point in above configuration file is the attribute invariant. This attribute represent the string, which can be passed to the GetFactory method of DBProviderFactories class to obtain a provider specific DBProviderfactory.

DBProviderFactories class is used to create provider specific DBProviderfactory. Various static method exposed by this class are described below.

1. public static DataTable GetFactoryClasses();

2. public static DbProviderFactory GetFactory(DataRow providerRow);

3. public static DbProviderFactory GetFactory(string providerInvariantName);

Method names are self explanatory about their usage; Just notice that GetFactory method also accepts a string parameter, here we can pass the invariant name (as specified in the machine.config file) to obtain the provider specific DbProviderFactory. Also notice the first method which returns a DataTable, this can easily bind to a DataGridView control to display the list of available Data Providers to the users as described below

private System.Windows.Forms.DataGridView dataGridView1;
private void InitializeComponent()
{
this.dataGridView1 = new System.Windows.Forms.DataGridView();
// Set Various properties of DataGridView
}
private void Form1_Load(object sender, EventArgs e)
{
dataGridView1.DataSource = DbProviderFactories.GetFactoryClasses();
}

Note: With the introduction of Partial class, all the designer generated code will be a part of different file in VS 2005, so in above code the declaration of dataGridView1 and the initialization will be the part of designer generated code where as programmer has to write the Form1_Load code himself.

When we run the application we will see the following result.

When we run the application we will the following result.
In above application we can ask the user to select a specific row and then create the provider specific factory based on that as shown below

DbProviderFactory dbf = DbProviderFactories.GetFactory(dataGridView1.SelectedRows[0].Cells[2].Value.ToString());

Instead of above we can also pass the DataRow to the GetFactory() method as shown below

// Assuming that I want to create a factory for SQL Client
DbProviderFactory dbf = DbProviderFactories.GetFactory((DbProviderFactories.GetFactoryClasses()).Rows[3]);

Based on the exact requirement and usage scenario, programmer can use various options to create provider specific factory form DBProviderfactories class.

After obtaining the provider specific DBProviderFactory, the same can be used to create provider specific connections etc using the methods explained in section 3.

//Create connection from the DBProviderFactory
DbConnection con = dbf.CreateConnection();

Programmer can store the Connection string in the config file in following format

<connectionStrings>
<add name="LocalSqlServer" connectionString="data source=...;Integrated Security= true;Initial Catalog=...;" providerName="System.Data.SqlClient" />
</connectionStrings>

DBProviderFactory also exposes a method CreateDataSourceEnumerator which can be used to obtain all data source instances as described below.

dataGridView2.DataSource = dbf.CreateDataSourceEnumerator().GetDataSources();

CreateDataSourceEnumerator().GetDataSources() returns a DataTable which contains all the server names on the network along with instance name. It also provides some other information like IsClustered and Version etc.

Using this method, it's possible to configure the data source at runtime dynamically.

4. On an additional note

Framework 2.0 also introduces a new class called DBConnectionStringBuilder for building Connection Strings. This class implements interfaces like IDictionary and ICustomTypeDescriptor etc. DbConnectionStringBuilder is a base class for provider-specific connection string builders i.e. SQLConnectionStringBuilder, OleDbConnectionStringBuilder etc. DBConnectionStringBuilder class allows to specify specific properties for specific attributes in the connection string for example ShouldSerialize (Programmer can specify which all attributes in the connection string can be serialized.

5. How much generic code can be written?

Irrespective of using the provider factories, it's impossible to write complete generic code which will be provider independent. Programmers can make use of casting to expose various provider specific properties and methods i.e. by using code as shown below

if (con is SqlConnection)
//Do SQL Specific coding

Even though provider factories can be used by programmers to write code that might access different data sources, for example those writing data access tools or development environments; it's still an Option and not the way for all data access code to be written. Other issues must be considered, for example connection string formats and content vary for different data sources. The new classes use reflection to obtain an instance of the factory object, so this is a performance hit. However, this only has to be done once and not every time an object instance is required.