Developing generic data access layer using ADO.NET 2.0

Introduction

Let's accept the real world fact that you need to write applications targeting not only SQL Server but also many other databases including MS-Access, Oracle and other ODBC databases. Classic ADO was a generic object model. You used to use the same Connection class with SQL Server, Access and Oracle. In ADO.NET, however, you need to use different classes depending on the provider you are using. That means before starting the coding phase you need to be aware of target database. If you develop an application using SQL Server data provider and later on decide to use Oracle you must change your code. At first thought using OLEDB data provider or ODBC data provider might come into your mind. However, considering the performance penalty that is not always a good idea. Won't it be nice to have a generic data access layer which will allow you to write provider independent code? That's exactly what this article will talk about.

Writing generic code with .NET 1.x

In .NET framework 1.x, people wrote generic data access code using interface based approach. You might be aware that all the data provider classes implement certain common interfaces. You can download one such implementation here. You can use the same approach in ADO.NET 2.0 also. However, ADO.NET 2.0 simplifies the job for you. Microsoft has opted for inheritance based approach rather than interface based one. This means all the data provider classes inherit from certain common base classes. For the sake of backward compatibility they still implement the interfaces as before. The following table lists these generic classes:

 That means SqlConnection and OracleConnection classes inherit from a common base class called DbConnection.

Taking advantage of polymorphism via inheritance we can now write generic code capable of dealing with multiple database in neutral manner. You may feel - "previously there were interfaces now there are common base classes. How does that make your life easy?". But wait. ADO.NET 2.0 has more to offer. In addition to the classes mentioned in the above table, ADO.NET 2.0 also provides a set of classes called "Factory" classes. These factory classes help you to create instances of these base classes dynamically. This ability makes it possible to store the choice of your data provider in a configuration file and then at run time create instances of corresponding data provider classes. For example, SQL server dta provider has a factory class called SqlClientFactory that allows you to create instances of SqlConnection, SqlCommand and so on. Similar classes exists for other data providers also. The SqlClientFactory class has various methods as listed in the following table:

Example of writing generic data access code

Let's see with a code snippet how the above classes can be used.

public void ExecuteQuery(string sql,string provider)
{
	DbConnection cnn=null;
	DbCommand cmd=null;
	DbProviderFactory factory = null;
	switch(provider)
	{
	   case "sqlclient":
		   factory = SqlClientFactory.Instance;
		   break;
	   case "oracleclient":
		   factory = OracleClientFactory.Instance;
		   break;
	}
	cnn = factory.CreateConnection();
	cmd = factory.CreateCommand();

	//now use cnn and cmd as usual to execute a query
}

Note, how we used Instance property of SqlClientFactory and OracleClientFactory classes to get an instace of corresponding factory class.

BinaryIntellect DatabaseHelper Open Source Component

BinaryIntellect DatabaseHelper is an open source component that utilizes above features of ADO.NET 2.0 to give you a generic data access layer. You can download the complete source code in C# and VB.NET at http://www.binaryintellect.com/products/. The download includes a set of samples along with documentation. Here are some core features of the component:

• Source code available in C# as well as VB.NET
• Generic data access for all the four data providers of .NET
• Developed using new Factory classes of ADO.NET 2.0
• Very small amount of code needed from your side
• Flexible and easy in use
• Support for parameterized queries and stored procedures
• Support for error handling
• Support for error logging
• Support for transactions
• Samples included

Comparing BinaryIntellect DatabaseHelper with MSDAAB

It is possible that you are already using Microsoft Data Access Application Block as a data access layer and might be wondering how BinaryIntellect DatabaseHelper differs from MSDAAB. Though we will not discuss which one is better than the other we will see some main differences between the two.

Difference 1: Target database

MSDAAB is exclusively fro SQL Server databases. However, using BinaryIntellect DatabaseHelper you can write code for all the four data providers of .NET 2.0.

Difference 2: Overall Class Design

MSDAAB extensively uses static methods. There are around 9 overloads per method. As against that BinaryIntellect DatabaseHelper needs to be instantiated and there are four overloads per method.

Difference 3: Working with parameters

using MSDAAB you typically write code as shown below:

SqlParameter[] p=new SqlParameter[2];
p[0]=new SqlParameter("@fname","Bipin");
p[1]=new SqlParameter("@lname","Joshi");
SqlHelper.ExecuteNonQuery("your query here",p);

The problem with this code is that you have provider specific classes such as SqlParameter embedded in your calling code. Even if you decide to use interface based approach you will have parameter specific code in the caller. Let's see the equivalent code in BinaryIntellect DatabaseHelper:

DatabaseHelper db = new DatabaseHelper();
db.AddParameter("@fname", "Bipin");
db.AddParameter("@lname", "Joshi");
object obj=db.ExecuteScalar("your query here");

If you notice the above code it doesn't use any data provider classes in the caller. This means your calling code (such as business logic layer) is database independent.

Difference 4: Executing multiple queries

Many times you need to fire multiple queries at one go. For better performance you would like to held the connection open, fire required queries and then close the connection. MSDAAB allows you to do that but you need to supply the SqlConnection instance from the calling code. This again means that you have data provider classes in the business logic layer. As against this BinaryIntellect DatabaseHelper provides a simple approach. have at look at the following code:

DatabaseHelper db = new DatabaseHelper();
object o = db.ExecuteNonQuery
("query here",ConnectionState.KeepOpen);
object o = db.ExecuteNonQuery("query here");

You do not need to create any connection object in your code. Simply indicate whether you would like to keep the connection open via ConnectionState enumeration. If you do not specify this option the underlying connection is closed.

Difference 5: Working with transactions

MSDAAB expects you to supply the transaction object from your code. This again gives rise to the problem mentioned above. On the other hand BinaryIntellect DatabaseHelper allows much easier way to execute queries under a transaction.

DatabaseHelper db = new DatabaseHelper();
db.BeginTransaction();
int i = db.ExecuteNonQuery
("first query",ConnectionState.KeepOpen);
int j = db.ExecuteNonQuery
("second query", ConnectionState.KeepOpen);
db.CommitTransaction();

Simple. Isn't it?

Difference 6: Error handling

MSDAAB does not provide any error handling facility as such. BinaryIntellect DatabaseHelper on the other hand allows you to handle errors in the component itself. This can simplify your code.

DatabaseHelper db = new DatabaseHelper();
db.HandleErrors = true;
object obj = db.ExecuteScalar("query here");
Console.WriteLine(db.LastError);

Two properties - HandleErrors and LastError does the job for you.

Difference 7: Error logging

MSDAAB does not provide any facility to log database errors (which is a common requirement in many projects). BinaryIntellect allows you to log errors to a text file at a specified location.

DatabaseHelper db = new DatabaseHelper();
db.LogErrors = true;
db.LogFile=@"D:\Bipin\Errorlog.txt";
object obj = db.ExecuteScalar("query here");

Difference 8: Performance

Since MSDAAB uses classes specific to SQL Server, it will give you better performance as compared to generic classes used in BinaryIntellect DatabaseHelper. However, many times the performance difference will be very small.

Summary

ADO.NET 2.0 simplifies writing generic data access code with the help of common base classes and factory classes. If the application requirement calls for provider independence then these classes can significantly help you generalize your code.