articles >> asp.net / ado.net >> Implementing a Generic Data Access Layer in ...

Implementing a Generic Data Access Layer in ADO.NET Part 1

By : Joydip Kanjilal Feb 27, 2008

Page 2 / 2


When you are executing the stored procedures, you might want to send data to the database or retrieve the same from the database. Accordingly, we have another enum called StoredProcedureParameterDirection that contains values that correspond to the parameter directions for the stored procedures that we would execute with the help of our DAL. The following is the code for this enum.

    public enum StoredProcedureParameterDirection
    {
        Input, InputOutput, Output, ReturnValue
    }

We need a factory class that would return a DbProviderFactory type instance or a DbDataAdapter type instance depending on the data provider that we are using. This class contains factory methods that typically are static methods. What is a static method, anyway? This is an often misunderstood concept but a very important one. Well, a static method, often called a shared method (it is shared by all instances of the class that it belongs to) belongs to the class and a non-static method belongs to an object of a class. That is, a non-static method can only be called on an object of a class that it belongs to. A static method can however be called both on the class as well as an object of the class. Further, a static method can access the static members of a class only unlike a non-static method that can access both static and non-static members. These static methods in the DBFactory class accept a reference to the ProviderType enum that denotes the data provider type in use.

The next class in our discussion is the DBFactory class, designed on the factory design pattern. Before we discuss the DBFactory class and its intent, let us understand what a factory design pattern is. What is a factory design pattern? The Factory pattern is responsible for providing an interface for the creation of objects, but allows the inherited classes to decide on the appropriate time of these instantiations. 

The following is the source code for our DBFactory class. It contains two static methods called GetProvider and GetDataAdapter both of which accept an instance of the database provider type enum, i.e., ProviderType.

using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.OracleClient;
using System.Collections.Generic;
using System.Text;

namespace DataAccessLayer
{
    internal class DBFactory
    {
        private static DbProviderFactory objFactory = null;

        public static DbProviderFactory GetProvider(ProviderType provider)
        {
            switch (provider)
            {
                case ProviderType.SqlServer:
                    objFactory = SqlClientFactory.Instance;
                    break;
                case ProviderType.OleDb:
                    objFactory = OleDbFactory.Instance;
                    break;
                case ProviderType.Oracle:
                    objFactory = OracleClientFactory.Instance;
                    break;
                case ProviderType.ODBC:
                    objFactory = OdbcFactory.Instance;
                    break;               
            }
            return objFactory;
        }

        public static DbDataAdapter GetDataAdapter(ProviderType providerType)
        {
            switch (providerType)
            {
                case ProviderType.SqlServer:
                    return new SqlDataAdapter();
                case ProviderType.OleDb:
                    return new OleDbDataAdapter();
                case ProviderType.ODBC:
                    return new OdbcDataAdapter();
                case ProviderType.Oracle:
                    return new OracleDataAdapter();
                default:
                    return null;
            }
        }
    }
}

Note that you have different providers for different databases, i.e., the database providers are all database specific. A DataAdapter as we may recall, is a bridge between the database and the DataSet – a set of disconnected data. Though you have various data readers depending on the type of the data provider you are using, you have only one type of data set. Why? This is because a data set is a disconnected in-memory set of data. The schema of the database defines the schema of the data set.

Refer to the code example shown above. Both the methods in the code example shown above check the value of the enum reference instance and accordingly return an appropriate DbDataProvider or DbProviderFactory instance respectively. Such methods are actually called factory methods.

Conclusion
In this article we have discussed how we can design and implement and generic data access layer, i.e., one that can be used to perform database operations irrespective of the database being used. In the next article in this series, we will discuss the Databasehelper class, i.e., the class that actually performs the database operations.