Designing Data Access Layer

Designing the Data Access Layer

Step 1: Define enum for different Data Providers to be used.

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

Step 2: 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();
  }
}

Step 3: Defining DBMangerFactory 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 <intParamsLength; ++i)
          {
            idbParams[i] = newOracleParameter();
          }
          break;
        default:
          idbParams = null;
          break;
      }
      return idbParams;
    }
  }
}

Step 4: Defining DBManger 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;
    }
  }
}

Step 5: Using Data 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();
}

Data Layer: Reading 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();
}

Data Layer: Insert values

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();
  }
}