1. Introduction
ADO.NET class libraries are used for accessing data from a wide range of data sources. Its extensible nature allows the programmer to write their own implementation for accessing data from different data source i.e. programmer can write their own data provider API for accessing and manipulating data form custom data sources. ADO.Net class libraries in .Net Framework 1.1 allowed programmers to create their own data providers by implementing certain set of interfaces i.e. IDbConnection, IDbCommand, IDataReader, IDbDataAdapter etc.
If we closely look at some of the classes used to access data from a data source (lets say SQL Server) in framework 1.1, we can verify the interfaces implemented by these classes.
| ADO.Net classes for accessing data from SQL Server |
Interface implemented |
| SqlConnection |
IDbConnection |
| SqlCommand |
IDbCommand |
| SqlDataReader |
IDataReader |
| SqlDataAdapter |
IDbDataAdapter |
In Framework 1.1, if programmers want to implement a generic data access API, they have to use the interfaces instead of using provider specific classes. The provider information was mostly configured in the Config file based on which instances of the provider specific classes were created.
ADO.NET 2.0 provides newly defined factory classes which standardize the way programmer will create a provider-specific Connection. This simplifies the way programmers create provider specific instances in a generic data access API.
2. Inheriting Base Classes instead of Implementing Interfaces
In Framework 2.0, new base classes are introduced and each provider specific implementation derives from these base classes instead of implementing generic interfaces as in framework 1.1. This feature has a definite disadvantage as programmers cannot derive form multiple base classes, but this certainly gives much more flexibility to accommodate changes to the base class.
If we closely look at some of the classes used to access data from a data source (lets say SQL Server) in framework 2.0, we can verify the base class inherited by these classes
| ADO.Net classes for accessing data from SQL Server |
Base class Inherited |
| SqlConnection |
DbConnection |
| SqlCommand |
DbCommand |
| SqlDataReader |
DbDataReader |
| SqlDataAdapter |
DbDataAdapter |
With these changes the class hierarchy can be summarized in following diagram
Note: These base classes added in Framework 2.0 are abstract; hence they cannot be instantiated directly.
3. Looking at Provider Factory
Framework 2.0 exposes a class for instantiating the provider specific class in a generic data access API via something called Provider Factory. We have factory classes for each provider i.e. SqlClientFactory, OracleClientFactory etc. These classes are derived from a base class called DbProviderFactory and contain following methods:
-
public
virtual DbConnection CreateConnection();
-
public
virtual DbCommand CreateCommand();
-
public
virtual DbCommandBuilder CreateCommandBuilder();
-
public
virtual DbConnectionStringBuilder CreateConnectionStringBuilder();
-
public
virtual DbDataAdapter CreateDataAdapter();
-
public
virtual DbDataSourceEnumerator CreateDataSourceEnumerator();
-
public
virtual DbParameter CreateParameter();
-
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.
-
public
static DataTable GetFactoryClasses();
-
public
static DbProviderFactory GetFactory(DataRow providerRow);
-
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.