Av rating:
Total votes: 52
Total comments: 17

 send to a friend
 printer friendly version

This article explains how to use .NET 2.0’s data provider factory classes to develop a pluggable data layer that is independent of database type and ADO.NET data provider.

If you want to develop a data layer that supports many types of database products, it should not be tightly coupled with a particular database product or ADO.NET data provider. The fact that ADO.NET has data providers that are enhanced for specific databases makes that independence more difficult and cumbersome to achieve.

You should have a good understanding of the .NET framework and familiarity with the ADO.NET library before using .NET 2.0’s data provider factory classes to create a pluggable data layer.

If you plan to market your application to many potential clients, it should support more than one database product. Since some clients may have already invested in a particular database, the ability to easily configure your application to work with different products is a strong feature.

When developing a data-centric application, I generally use a particular ADO.NET data provider and develop the data layer targeting a particular database product. One benefit of isolating the data layer is that it makes it easy to change the database product without affecting the application too much.

If the business and user interface layers in your application use the data layer for database-related operations and do not directly access the database, then you can have multiple data layers for the database products you want to support. Although this approach sounds reasonable, maintaining multiple data layers for every database product you intend to support is not feasible. The classes in System.Data.Common namespace enable you to build a data layer independent of the database product, and easily change the database product on which it works.

Take the following ADO.NET code that connects to a SQL Server database and execute an arbitrary SQL statement:

System.Data.SqlClient.SqlConnection con = new 
System.Data.SqlClient.SqlConnection();
con.ConnectionString = "Data Source=.;initial 
catalog=Northwind;Integrated security=true";
System.Data.SqlClient.SqlCommand cmd = new 
System.Data.SqlClient.SqlCommand();
cmd.CommandText = "Update Products set UnitsInStock=UnitsInStock+10";
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();

System.Configuration.AppSettingsReader appReader = new 
System.Configuration.AppSettingsReader();
string provider = appReader.GetValue("provider", typeof(string)).ToString();
string connectionString = appReader.GetValue("connectionString", 
  typeof(string)).ToString();
System.Data.Common.DbProviderFactory factory = 
System.Data.Common.DbProviderFactories.GetFactory(provider);
System.Data.Common.DbConnection con = factory.CreateConnection();
con.ConnectionString = connectionString;
System.Data.Common.DbCommand cmd = factory.CreateCommand();
cmd.CommandText = "Update Products set UnitsInStock=UnitsInStock+10″;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();

<configuration> 
  <appsettings> 
    <add key = "provider" value ="System.Data.SqlClient"/> 
    <add key = "connectionString" value ="Data Source=.;
        initial catalog=Northwind;Integrated security=true" />  
  </appsettings> 
</configuration>  

In the code above, other than isolating the connection string, we have used the common ADO.NET data provider in the System.Data.Common namespace. This is a simple implementation of the abstract factory pattern. Each ADO.NET data provider has a factory class that enables us to create ADO.NET objects of its provider type.

The SQL Client ADO.NET data provider, for example, has a SqlClientFactory that can be used to create SqlConnection, SqlCommand, and other SQL Client ADO.NET data provider-specific objects. Based on the string value that is passed to the GetFactory method of the DbProviderFactories class, a concrete instance of a particular ADO.NET data provider factory will be created. Instead of creating the connection and the command objects directly, we use this factory instance to create the necessary ADO.NET objects for us.

The code above shows that we are passing the string value System.Data.SqlClient from the application configuration file, indicating that we want a SqlClientFactory object to be created and assigned to the factory variable. From that point on, all the create methods of the DbProviderFactory object will create ADO.NET objects of the SQL Client ADO.NET data provider.

The classes in ADO.NET have been altered from .NET 1.1 to inherit common base classes from the SystemData.Common namespace. ADO.NET connection classes such as SqlConnection and OleDbConnection inherit from the DbConnection class, for example. The following diagram shows the inheritance hierarchy of the factory classes and the ADO.NET classes:

Provided we have used standard SQL statements, we can easily make our product work with a different ADO.NET data provider by changing the provider in the application configuration file. If we set it to System.Data.OleDb, an OleDbFactory class will be created, which will create OleDb data provider-specific ADO.NET objects such as OleDbConnection and so on.

You might also want to list all of the available ADO.NET data providers. You can do so using the GetFactoryClasses method of the DbProviderFactories class:

 DataTable tbl = 
System.Data.CommonDbProviderFactories.GetFactoryClasses();
dataGridView1.DataSource = tbl;
foreach (DataRow row in tbl.Rows)
{ 
  Console.WriteLine(row["InvariantName"].ToString()); 
} 

The GetFactoryClasses method returns a data table containing information about the available ADO.NET data providers. The InvariantName column provides the necessary string value needed to pass to the GetFactory method in order to create a factory for a particular ADO.NET data provider.

One disadvantage of using the factory classes and developing a common data layer is that it limits us to standard SQL statements. This means we cannot take advantage of the full functionality of a particular database product.

One way to overcome this is to make a check on the type of ADO.NET object created by a factory and execute some statements based on it. Though it’s not an elegant approach, it is useful when we need to execute database product-specific SQL statements. For example:

DbProviderFactory factory = 
DbProviderFactories.GetFactory("System.Data.SqlClient");
DbCommand cmd = factory.CreateCommand();
if(cmd is System.Data.SqlClient.SqlCommand)
{ 
  //set command text to SQL Server specific statement 
} 
else if (cmd is System.Data.OleDb.OleDbCommand) 
{ 
  //set command text to OleDb specific statement 
}