Writing a Generic Data Access Layer Using .NET 2.0

• Download source (ConnectionClass) - 1.26 KB
• Download source (DbClass) - 1.66 KB

Introduction

This article explains how to write a quick and lightweight data access layer for short projects (Windows as well as Web). It is useful as it is static. It depends on config files to get the Data Provider name and other connection properties. So you can use this code snippet for all kinds of data providers that the .NET Framework supports.

This article can solve the problem of rewriting the data access layer separately for each project that uses a different database. Also, it has all the generally necessary actions that are taken whenever we query a database.

This article is written in .NET Framework 2.0 and uses its namespaces and classes. So .NET 1.1 Framework users may need to upgrade to 2.0 to use it.

Background

I would like to discuss something about abstract factory pattern before we go through our article as it can help us to have a grasp of what exactly happens behind the scenes.

Those who are familiar may not need this, but for the rest an abstract factory is generally an abstract class that creates objects of similar sorts. By 'objects of similar sorts', I mean a family of related objects. Like in this case (DbConnection, DbCommand, DbParameter, etc).

In our code we would also be using a similar abstract factory class (System.Data.Common.DBProviderFactory). This abstract factory class will return to us the desired objects. Let's see the below section for a more detailed explanation.

Using the Code

We would be exploring the System.Data.Common namespace in ADO.NET 2.0.

This namespace has been introduced in ADO.NET 2.0 with classes that implement an abstract factory pattern. In earlier versions, it used an Interface pattern.

Let's have a closer look at the System.data.Common namespace classes first. The most important one is the DbProviderFactory Class. This abstract factory class accepts a provider name and in return provides us with objects of all necessary ADO.NET classes.

We start by creating a new Class Library project. Add two classes to the library. I have created two classes as ConnectionClass.cs and DbClass.cs. You can download the source code for both the class files from the uploaded zip files on this article.

Let's first have a closer look at the ConnectionClass.cs file.

Below are the screen shots of the ConnectionClass.cs and its defined set of methods.

As you can see, the ConnectionClass.cs contains three methods:

1. GetDbFactory(): Returns a DbProviderFactory object.This method makes uses of the static method GetFactory(string ProviderName) of the DbProviderFactories class in the System.Data.Common namespace. It returns a DbProviderFactory object.

   C#

   internal static DbProviderFactory GetDbFactory()
   {
       try
       {
           string ProviderName = ConfigurationManager.AppSettings["ProviderName"];
           DbProviderFactory Dbfactory = DbProviderFactories.GetFactory(
               ProviderName);
           return Dbfactory;
       }
       catch(DbException)
       {
           throw new Exception("An exception has occurred while creating the" +
           "database provider factory. Please check the ProviderName" +
           "specified in the web.config file.");
       }
   }

2. GetDbFactory(string ProviderName): Returns a DbProviderFactory object. This is nothing but just an overload in case you wish to programmatically provide your ProviderName.

   C#

   internal static DbProviderFactory GetDbFactory(string ProviderName)
   {
       DataTable dtProviders = DbProviderFactories.GetFactoryClasses();
       if (dtProviders.Rows.Count == 0)
       {
           throw new Exception("No Data Providers are installed in the" +
           ".Net FrameWork that implement the abstract DbProviderFactory" +
           "Classes. ");
       }
   
       bool errorFlag = false;
       foreach (DataRow dr in dtProviders.Rows)
       {
           if (dr[2] != null)
           {
               string ExistingProviderName = dr[2].ToString();
               if (ProviderName.ToLower() ==
                   ExistingProviderName.Trim().ToLower())
               {
                   errorFlag = false;
                   break;
               }
               else
               {
                   errorFlag = true;
               }
           }
       }
       if (errorFlag)
       {
           throw new Exception("The ProviderName string supplied is not a" +
           "valid Provider Name or it does not implement the abstract" +
           "DbProviderFactory Classes. The string ProviderName is" +
           "case-sensitive. Also please check it for proper spelling. ");
       }
       DbProviderFactory Dbfactory = DbProviderFactories.GetFactory(ProviderName);
       return Dbfactory;
   }

3. GetConnection(): Returns a database connection object. Here we use the GetDbFactory() method explained above.

   C#

   internal static DbConnection GetConnection()
   {
       try
       {
           string ConnectionString = Convert.ToString(
               ConfigurationManager.ConnectionStrings["HPRConnectionString"]);
           DbProviderFactory Dbfactory = ConnectionClass.GetDbFactory();
           DbConnection conn = Dbfactory.CreateConnection();
           conn.ConnectionString = ConnectionString;
           return conn;
       }
       catch (DbException)
       {
           throw new ApplicationException("An exception has occurred while" +
           "creating the connection. Please check Connection String settings" +
           "in the web.config file.");
       }
   }

Look carefully, I have kept all methods as internal, so that they cannot be accessed from outside the namespace or the data access layer.

Let's now have a look at the DbClass.cs file and its methods.
This class provides us with a set of Utility functions that we would require in our project while performing any database transaction.

I have put the screen shots below.

The GetCommand, GetParameter, GetParameters methods all return corresponding ADO objects. The methods use the Connection class' GetDbFactory() method to create an object of DbProviderFctory. Then this object provides us with the corresponding ADO objects like command, parameter, datareader etc.

ConnectionClass.GetDbFactory().CreateCommand()

The Business layer can make use of those objects and add their properties, like parameter values for Parameter object and commandtype and commandText for Command object.

There are also methods like GetTable and GetDataSet that accept a command object from the Business layer and execute those against the database. The idea is that the calling application (or function) needs to first Parameter and Command objects by calling the appropriate methods. Then add the values to the parameters, add the parameters to the command object and than call the getTable or GetDataSet as per the requirement.

The ExecuteNonQuery and ExecuteScalar methods also work in the same way except that they do updating or insertion transactions.

All these methods in the DbClass.cs file use the ConnectionClass.cs methods for making connections etc.

I have also used Transaction objects in my DbClass methods to execute the commands within an transaction. You can download the zip files uploaded in this article which contains the source code of both DbClass.cs and ConnectionClass.cs.

Conclusion

The aim of my article was to help users to avoid writing the same code for Data access again and again for different projects.

It is much helpful to users who actually write applications but are not sure about the type of database that would be existing on the Live-Server or imagine a situation when the client decides to migrate his existing database from SQL to Oracle or maybe Microsoft Access.

These are some of the problems that I have faced in my life as a programmer and through this article, I would hope to help people who face a similar problem.

Feedback

Thanks to all readers for reading my article and I would seriously look forward to your comment, feedback, suggestions, anything that you can possibly provide me to improve this article.

Happy programming!