Achieve database independence by developing a pluggable data layer

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.

Introduction

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.

Supporting many database products

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.

System.Data.Common namespace

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

C# Code

There are quite a few problems in the code above that would make it difficult to modify to work with a different database product. One obvious change that is needed is to move the hard-coded connection string information out to a configuration file. Another problem is that we are tying our code to a particular ADO.NET data provider, in this case the SQL Client data provider. This increases the changes that are needed if we want to support another database product.

Now let us see how the code is changed after we move the connection string out to the application configuration file and use the classes in the System.Data.Common namespace instead of the SQL Client ADO.NET data provider:

C# Code

App.config file

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:

C# Code

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:

C# Code

Conclusion

The ADO.NET data providers in .NET 2.0 provide factory and common ADO.NET classes that make it easy to keep your code independent from a particular ADO.NET data provider or database product.