Click here to Skip to main content
Click here to Skip to main content

Writing a Generic Data Access Layer Using .NET 2.0

, 6 Oct 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
This is an article that will help people who develop applications in .NET 2.0 or higher as a template for DAL (data acess layer) which would be generic. By generic I mean that it would respond to any of the Data Providers that the .NET Framework supports.

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.

Screenshot - untitled1.gif

Screenshot - untitled2.gif

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.

    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.

    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.

    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.

Screenshot - src1.gif

Screenshot - src2.gif

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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sachin Mangesh Naik
Web Developer
India India
Hi Friends,
Let me introduce myself to the world.
Iam Sachin Naik a programmer working for an Indian Software company.
I have been programming in asp.net/c# for the past three years and still learning the technology and the language as much as I can.
I would love to use this place to share my findings with you guys and would also be pleased to know your opinions and suggestions.
Thanks...

Comments and Discussions

 
GeneralExample code PinmemberRakhi0071-May-11 23:21 
GeneralAn object reference is required for the non-static field, method, or property 'HPR.DAL.ConnectionClass.GetConnection()' PinmemberMember 56468678-Nov-08 4:02 
GeneralRe: An object reference is required for the non-static field, method, or property 'HPR.DAL.ConnectionClass.GetConnection()' Pinmemberdhilbmann18-Sep-09 7:45 
QuestionWhy re-invent the wheel? Pinmemberfreddie20001-Dec-07 0:45 
AnswerRe: Why re-invent the wheel? PinmemberSachin Mangesh Naik4-Dec-07 4:34 
QuestionInstanceCounter PinmemberSubrata Ghosh15-Oct-07 0:53 
AnswerRe: InstanceCounter PinmemberSachin Mangesh Naik16-Oct-07 2:45 
GeneralUnfinished PinmemberPawel Gielmuda1-Oct-07 5:00 
GeneralRe: Unfinished PinmemberSachin Mangesh Naik1-Oct-07 5:27 
GeneralRe: Unfinished PinmemberPawel Gielmuda2-Oct-07 0:12 
GeneralRe: Unfinished PinmemberSachin Mangesh Naik9-Oct-07 22:34 
Hi dear friend,
The article is now complete by god's grace.
Would appreciate if you can go through it..
 
Thanks..
Sachin

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411023.1 | Last Updated 6 Oct 2007
Article Copyright 2007 by Sachin Mangesh Naik
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid