Implementing a Data Access Layer in C#

A Data Access Layer is an important layer in the architecture of any software. This layer is responsible for communicating with the underlying database. Making this layer provider independent can ensure multi database support with ease. This article discusses implementation of a provider independent Data Access Layer in C#.

ADO.NET Data Providers

The following are the major ADO.NET data providers.

• SQL Server Data Provider
• Oracle Data Provider
• Odbc Data Provider
• OleDB Data Provider

ADO.NET Classes

The data providers stated above consist of these major ADO.NET classes.

• Connection
• Command
• Data Reader
• Data Adapter

These data provider classes implement the following interfaces.

• IDbConnection
• IDataReader
• IDbCommand
• IDbDataAdapter

In order to ensure that our DAL layer is provider independent, we make use of the above interfaces in our Data Access Layer.

Designing the Data Access Layer

The following enum is declared and ensures that we have a loose coupling between the UI layer and the Data Access Layer.

Listing 1: The Data Provider enum

public enum DataProvider  {    Oracle,SqlServer,OleDb,Odbc  }

The DBManager class implements the IDBManager interface that contains the signature of the methods that the DBManager class implements. The following code shows IDBManager interface:

Listing 2: The IDBManager interface

using System;  using System.Data;  using System.Data.Odbc;  using System.Data.SqlClient;  using System.Data.OleDb;  using System.Data.OracleClient;     namespace DataAccessLayer  {    public interface IDBManager    {      DataProvider ProviderType      {        get;        set;      }         string ConnectionString      {        get;        set;      }         IDbConnection Connection      {        get;      }      IDbTransaction Transaction      {        get;      }         IDataReader DataReader      {        get;      }      IDbCommand Command      {        get;      }         IDbDataParameter[]Parameters      {        get;      }         void Open();      void BeginTransaction();      void CommitTransaction();      void CreateParameters(int paramsCount);      void AddParameters(int index, stringparamName, object objValue);      IDataReader ExecuteReader(CommandTypecommandType, string      commandText);      DataSet ExecuteDataSet(CommandTypecommandType, string      commandText);      object ExecuteScalar(CommandTypecommandType, string commandText);      int ExecuteNonQuery(CommandType commandType,string commandText);      void CloseReader();      void Close();      void Dispose();    }  }

Listing 3: The DBManagerFactory class

using System;  using System.Data;  using System.Data.Odbc;  using System.Data.SqlClient;  using System.Data.OleDb;  using System.Data.OracleClient;     namespace DataAccessLayer  {    public sealed class DBManagerFactory    {      private DBManagerFactory(){}      public static IDbConnectionGetConnection(DataProvider       providerType)      {        IDbConnection iDbConnection = null;        switch (providerType)        {          case DataProvider.SqlServer:            iDbConnection = new SqlConnection();            break;          case DataProvider.OleDb:            iDbConnection = new OleDbConnection();            break;          case DataProvider.Odbc:            iDbConnection = new OdbcConnection();            break;          case DataProvider.Oracle:            iDbConnection = new OracleConnection();            break;          default:            return null;        }        return iDbConnection;      }         public static IDbCommandGetCommand(DataProvider providerType)      {        switch (providerType)        {          case DataProvider.SqlServer:            return new SqlCommand();          case DataProvider.OleDb:            return new OleDbCommand();          case DataProvider.Odbc:            return new OdbcCommand();          case DataProvider.Oracle:            return new OracleCommand();          default:            return null;        }      }         public static IDbDataAdapterGetDataAdapter(DataProvider      providerType)      {        switch (providerType)        {          case DataProvider.SqlServer:            return new SqlDataAdapter();          case DataProvider.OleDb:            return new OleDbDataAdapter();          case DataProvider.Odbc:            return new OdbcDataAdapter();          case DataProvider.Oracle:            return new OracleDataAdapter();          default:            return null;        }      }         public static IDbTransactionGetTransaction(DataProvider       providerType)      {        IDbConnection iDbConnection =GetConnection(providerType);        IDbTransaction iDbTransaction =iDbConnection.BeginTransaction();        return iDbTransaction;      }         public static IDataParameterGetParameter(DataProvider       providerType)      {        IDataParameter iDataParameter = null;        switch (providerType)        {          case DataProvider.SqlServer:            iDataParameter = new SqlParameter();            break;          case DataProvider.OleDb:            iDataParameter = new OleDbParameter();            break;          case DataProvider.Odbc:            iDataParameter = new OdbcParameter();            break;          case DataProvider.Oracle:            iDataParameter = newOracleParameter();            break;           }        return iDataParameter;      }         public staticIDbDataParameter[]GetParameters(DataProvider       providerType,        int paramsCount)      {        IDbDataParameter[]idbParams = newIDbDataParameter[paramsCount];           switch (providerType)        {          case DataProvider.SqlServer:            for (int i = 0; i < paramsCount;++i)            {              idbParams[i] = new SqlParameter();            }            break;          case DataProvider.OleDb:            for (int i = 0; i < paramsCount;++i)            {              idbParams[i] = new OleDbParameter();            }            break;          case DataProvider.Odbc:            for (int i = 0; i < paramsCount;++i)            {              idbParams[i] = new OdbcParameter();            }            break;          case DataProvider.Oracle:            for (int i = 0; i = newOracleParameter();            }            break;          default:            idbParams = null;            break;        }        return idbParams;      }    }  }

Listing 4: The DBManager Class

using System;  using System.Data;  using System.Data.Odbc;  using System.Data.SqlClient;  using System.Data.OleDb;  using System.Data.OracleClient;     namespace DataAccessLayer  {    public sealed class DBManager: IDBManager,IDisposable    {      private IDbConnection idbConnection;      private IDataReader idataReader;      private IDbCommand idbCommand;      private DataProvider providerType;      private IDbTransaction idbTransaction =null;      private IDbDataParameter[]idbParameters =null;      private string strConnection;         public DBManager(){         }         public DBManager(DataProvider providerType)      {        this.providerType = providerType;      }         public DBManager(DataProvider providerType,string       connectionString)      {        this.providerType = providerType;        this.strConnection = connectionString;      }         public IDbConnection Connection      {        get        {          return idbConnection;        }      }         public IDataReader DataReader      {        get        {          return idataReader;        }        set        {          idataReader = value;        }      }         public DataProvider ProviderType      {        get        {          return providerType;        }        set        {          providerType = value;        }      }         public string ConnectionString      {        get        {          return strConnection;        }        set        {          strConnection = value;        }      }         public IDbCommand Command      {        get        {          return idbCommand;        }      }         public IDbTransaction Transaction      {        get        {          return idbTransaction;        }      }         public IDbDataParameter[]Parameters      {        get        {          return idbParameters;        }      }         public void Open()      {        idbConnection =        DBManagerFactory.GetConnection(this.providerType);        idbConnection.ConnectionString =this.ConnectionString;        if (idbConnection.State !=ConnectionState.Open)          idbConnection.Open();        this.idbCommand =DBManagerFactory.GetCommand(this.ProviderType);      }         public void Close()      {        if (idbConnection.State !=ConnectionState.Closed)          idbConnection.Close();      }         public void Dispose()      {        GC.SupressFinalize(this);        this.Close();        this.idbCommand = null;        this.idbTransaction = null;        this.idbConnection = null;      }         public void CreateParameters(intparamsCount)      {        idbParameters = newIDbDataParameter[paramsCount];        idbParameters =DBManagerFactory.GetParameters(this.ProviderType,          paramsCount);      }         public void AddParameters(int index, stringparamName, object       objValue)      {        if (index < idbParameters.Length)        {          idbParameters[index].ParameterName =paramName;          idbParameters[index].Value = objValue;        }      }         public void BeginTransaction()      {        if (this.idbTransaction == null)          idbTransaction =          DBManagerFactory.GetTransaction(this.ProviderType);        this.idbCommand.Transaction =idbTransaction;      }         public void CommitTransaction()      {        if (this.idbTransaction != null)          this.idbTransaction.Commit();        idbTransaction = null;      }         public IDataReader ExecuteReader(CommandTypecommandType, string        commandText)      {        this.idbCommand =DBManagerFactory.GetCommand(this.ProviderType);        idbCommand.Connection = this.Connection;        PrepareCommand(idbCommand,this.Connection, this.Transaction,         commandType,          commandText, this.Parameters);        this.DataReader =idbCommand.ExecuteReader();        idbCommand.Parameters.Clear();        return this.DataReader;      }         public void CloseReader()      {        if (this.DataReader != null)          this.DataReader.Close();      }         private void AttachParameters(IDbCommandcommand,        IDbDataParameter[]commandParameters)      {        foreach (IDbDataParameter idbParameter incommandParameters)        {          if ((idbParameter.Direction == ParameterDirection.InputOutput)          &&            (idbParameter.Value == null))          {            idbParameter.Value = DBNull.Value;          }          command.Parameters.Add(idbParameter);        }      }         private void PrepareCommand(IDbCommandcommand, IDbConnection        connection,        IDbTransaction transaction, CommandTypecommandType, string        commandText,        IDbDataParameter[]commandParameters)      {        command.Connection = connection;        command.CommandText = commandText;        command.CommandType = commandType;           if (transaction != null)        {          command.Transaction = transaction;        }           if (commandParameters != null)        {          AttachParameters(command, commandParameters);        }      }         public int ExecuteNonQuery(CommandTypecommandType, string      commandText)      {        this.idbCommand =DBManagerFactory.GetCommand(this.ProviderType);        PrepareCommand(idbCommand,this.Connection, this.Transaction,        commandType, commandText,this.Parameters);        int returnValue =idbCommand.ExecuteNonQuery();        idbCommand.Parameters.Clear();        return returnValue;      }         public object ExecuteScalar(CommandTypecommandType, string        commandText)      {        this.idbCommand =DBManagerFactory.GetCommand(this.ProviderType);        PrepareCommand(idbCommand,this.Connection, this.Transaction,        commandType,          commandText, this.Parameters);        object returnValue = idbCommand.ExecuteScalar();        idbCommand.Parameters.Clear();        return returnValue;      }         public DataSet ExecuteDataSet(CommandTypecommandType, string       commandText)      {        this.idbCommand =DBManagerFactory.GetCommand(this.ProviderType);        PrepareCommand(idbCommand,this.Connection, this.Transaction,       commandType,          commandText, this.Parameters);        IDbDataAdapter dataAdapter =DBManagerFactory.GetDataAdapter          (this.ProviderType);        dataAdapter.SelectCommand = idbCommand;        DataSet dataSet = new DataSet();        dataAdapter.Fill(dataSet);        idbCommand.Parameters.Clear();        return dataSet;      }    }  }

Using the DAL Layer

Compile the above project to create DALLayer.dll. This section shows how we can use the DAL layer for database operations in our projects. Create a new project and add the reference to the DALLayer.dll in this project. The following code shows how we can read data from a database table called "emp" using the DAL Layer.

Listing 5: Read data using the DAL Layer

IDBManager dbManager = newDBManager(DataProvider.SqlServer);  dbManager.ConnectionString =ConfigurationSettings.AppSettings[    "ConnectionString"].ToString();  try  {    dbManager.Open();    dbManager.ExecuteReader("Select * fromemp ",CommandType.Text);    while(dbManager.DataReader.Read())Response.Write(dbManager.    DataReader["name"].ToString());  }     catch (Exception ex)  {  //Usual Code  }     finally  {    dbManager.Dispose();  }

Note that we can read the connection string from the web.config file or we can hard code the same directly using the ConnectionString property. It is always recommended to store the connection string in the web.config file and not hard code it in our code.

The following code shows how we can use the Execute Scalar method of the DBManager class to obtain a count of the records in the "emp" table.

Listing 6: Reading one value using Execute Scalar

IDBManager dbManager = newDBManager(DataProvider.OleDb);  dbManager.ConnectionString =ConfigurationSettings.AppSettings[    "ConnectionString"].ToString();  try  {    dbManager.Open();    object recordCount =dbManager.ExecuteScalar("Select count(*) from    emp ", CommandType.Text);    Response.Write(recordCount.ToString());  }     catch (Exception ce)  {  //Usual Code  }     finally  {    dbManager.Dispose();  }

The following code shows how we can invoke a stored procedure called "Customer_Insert" to insert data in the database using our DAL layer.

Listing 7: Inserting data using stored procedure

private void InsertData()  {    IDBManager dbManager = new DBManager(DataProvider.SqlServer);    dbManager.ConnectionString =ConfigurationSettings.AppSettings[      "ConnectionString "].ToString();    try    {      dbManager.Open();      dbManager.CreateParameters(2);      dbManager.AddParameters(0, "@id",17);      dbManager.AddParameters(1,"@name", "Joydip Kanjilal");     dbManager.ExecuteNonQuery(CommandType.StoredProcedure,      "Customer_Insert");    }    catch (Exception ce)    {      //Usual code                  }    finally    {      dbManager.Dispose();    }  }

Conclusion