Implementing a Data Access Layer in C#: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
Implementing a Data Access Layer in C#

page 1 of 1

Published: 08 May 2006

Unedited - Community Contributed

Abstract
In this article, Joydip demosntrates the working of a provider independent DAL layer in C# with relevant source code.

by Joydip Kanjilal
Feedback

Average Rating:

Views (Total / Last 10 Days): 47666/ 47

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 <intParamsLength; ++i)
          {
            idbParams[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

In this article we have designed and implemented a provider independent Data Access Layer that can be loosely coupled with other layers. I invite the readers to post their comments and suggestions regarding this article.

User Comments

Title: Error
Name: Mr erro
Date: 2006-12-22 4:08:05 PM
Comment:
I am getting the following error when I try to compile the project:

Error 1 Class, struct, or interface method must have a return type

Its complaining about the DBManagerFactory class. Can someone help?

Title: Error in DataProvider
Name: Beena
Date: 2006-12-06 7:09:56 AM
Comment:
Its fine ,but When i compile the solution,it showing the following Error Message
"The type or namespace name 'DataProvider' could not be found (are you missing a using directive or an assembly reference?)"

Title: Implementing a Data Access Layer in C#
Name: Nisha
Date: 2006-12-06 5:02:12 AM
Comment:
superb article

Title: Where's "getschema"?
Name: Kamila
Date: 2006-11-30 11:34:44 AM
Comment:
Where's that getschema you're talking about??

Title: Getschema()
Name: ashokmohanty
Date: 2006-11-28 7:19:50 AM
Comment:
How can i call Getschema method through DBManager

Title: Best
Name: Bansh Patel
Date: 2006-11-27 5:18:30 AM
Comment:
Thanks,
This is one of the best article. This is too usefull for us.

Title: Anyone here to answer questions?
Name: Kamila
Date: 2006-11-14 10:21:28 AM
Comment:
I'm getting an error but seems like this site is deserted...

I've even emailed the author with no response!

Title: DAL article
Name: ramakishroe reddy
Date: 2006-11-14 2:08:47 AM
Comment:
this is very good and so helpfull to me.

Title: To:Muhammad Yousaf
Name: Kamila
Date: 2006-10-31 3:23:02 PM
Comment:
I think he's used Factory Design Pattern.

I agree, this is the best generic code I've come across. I downloaded other sample code but this is the easiest to understand and it actually works.

Title: Design Patter
Name: Muhammad Yousaf
Date: 2006-10-31 6:50:31 AM
Comment:
This article is really very awesome and helpful. I was just wondering that have you used any design patterns while writing the implementation of Data Access Layer ???

Title: To :David Lester
Name: Kamila
Date: 2006-10-24 2:34:34 PM
Comment:
David - why do you say performance is better with provider specific DAL? I'm coding against IBM DB2 and SQL. Wondering if performance is gonna take a hit doing it as generic DAL.

Title: Multiple databases
Name: David Lester
Date: 2006-10-23 7:09:53 AM
Comment:
Like many others here I need to develop applications that target many databases and have found no matter how hard I try I cannot make my code generic to work on all databases. Plus if I right provider specific code performance also improves considerable.

Is there any pros/cons in writing a specific data layer using the Providers plug in, as the SqlMembershipProvider, SqlRoleProvider use so we could create a different plugin for wach database we wish to user. What are peoples thoughts on doing this.

Title: Add Types to add output parameter
Name: Robert
Date: 2006-09-11 2:19:47 PM
Comment:
Good job on the article and comments. In the 'add output parameter' section you left out how to add the parameter types. Thats the only issue I was having. Thanks.

Title: Very Nice
Name: SAM
Date: 2006-09-06 2:43:45 AM
Comment:
Article is very nice. But what is the core concept behind this article.

Title: Mr
Name: Srini Rao
Date: 2006-08-17 6:08:15 AM
Comment:
I am trying to understand why we need the IDBManager interface as we can simply write these as methods in DBManager class.

Title: Excellent work!
Name: Mandy
Date: 2006-08-03 3:33:41 PM
Comment:
Really good work Joydip! Thanks!

Title: Wonderful
Name: Sahar
Date: 2006-07-21 8:02:09 AM
Comment:
You've done a good job

Title: To add output parameter
Name: Tesi
Date: 2006-07-20 2:28:00 AM
Comment:
to add output parameter add this:
to interface IDBManager.cs add:
void AddParameters(int index, string paramName, object objValue, ParameterDirection paramDirection, int size);

Then in your calling code when adding parameters:
mapper.AddParameters(0, "@PageName", this.PageName, ParameterDirection.Input);
mapper.AddParameters(1, "@PageID", this.PageID, ParameterDirection.Output, 4);

after executing the above you retrieve the value as:
mapper.Parameters[1].Value.

Cheers,
Tesi

Title: Structure works for me, I'm using parts of this
Name: y-rock
Date: 2006-07-19 6:56:34 PM
Comment:
OldSchool-
To add output parameters I doubled-up the parameter array, creating one for 'in' one for 'out'.

inParms[] , outParms[]

CreateInParameters(int paramsCount)...

CreateOutParameters(int paramsCount)...

AddInParameters(int index, string paramName, object objValue)...

AddOutParameters(int index, string paramName, int typ, int size)
if (index < outParms.Length)
{
outParms[index].ParameterName = paramName;
outParms[index].Direction = ParameterDirection.Output;
switch(typ)
{....add types...}
if (size > 0)
{
outParms[index].Size = size;
}...

In prepareParameters() attach both arrays, one after the other.

After ExecuteNonQuery() retreive out parms like this:

public string getStrOutParameter(string parmOut)
{
return cmd.Parameters[parmOut].Value.ToString();
}

HTH

Title: RollBackTransaction()
Name: Rehan
Date: 2006-07-17 2:12:34 AM
Comment:
you can use the following code forRollBack Transaction

public void RollBackTransaction()
{
if (this.idbTransaction != null)
this.idbTransaction.Rollback();
idbTransaction = null;
}

Title: Thank you
Name: OldSchool
Date: 2006-07-13 1:03:15 AM
Comment:
Thanks for a nice piece of starter code - have you implemented a stored procedure with an output parameter? I'm having problems setting that bit up.

Title: Nice Artical
Name: Gangaprasad
Date: 2006-07-01 9:06:06 AM
Comment:
Hi,
This artical was very nice . I was impressed with that. But still i am having some queries . I am working on an ERP based application .in C#.NET and ASP.NET . I want to write a same DAL as u but this haven't covered the XML Serialization and DeSerialization part.

One point of transaction has already being raised. Plz help me in working it out . If u go through factory classes of ADO.NET 2.0 they have introduced lots of new features like properties etc. You can mail me on gangaprasad_s@rediffmail.com

I am working as .NET Developer with 1 year experience.
I am also having queries about BLL and will ask latter about that.

Regards,
Gangaprasad

Title: RollBack Transaction?
Name: Tom
Date: 2006-06-13 4:31:50 AM
Comment:
Hello, did anyone implement the rollback transaction? Could you share it perhaps?

Thank you,
Tom

Title: A very nice contribution by Mr Kanjilal
Name: Darryl Pentz
Date: 2006-06-09 12:27:43 PM
Comment:
To add my agreement to Mr Deeds, my company is also adding a product to our product suite, that will need to work with numerous databases depending on what is at the customers site.

This code gives a well designed, intuitive layout to a generic data access layer. It doesn't have all the bells and whistles but it's a very good start. I imagine that it can be improved wherever needed - one of the benefits of 'open source' coming to the .NET world, as already witnessed by Carl's observation that it was missing a RollbackTransaction().

Thanks for a fine contribution, Joydip.

Title: very good
Name: Raptor
Date: 2006-05-27 1:42:06 PM
Comment:
your article gived me many ideals about my DAL

Title: Getting there, but one major flaw
Name: Tim
Date: 2006-05-21 12:35:11 PM
Comment:
Problem is that you assume the SQL that's being executed is also provider-independent.
If it's not (IDENTITY vs Oracle sequences, for example), then your other application layers still suffer from the problem that switching the data access layer incurs extra development at best, or errors everywhere at worst.

Title: More Use Full
Name: Prabakar
Date: 2006-05-20 7:23:59 AM
Comment:
Its a very use fill artical.Helps me lot Thanks

Title: Good overview
Name: Mr Deeds
Date: 2006-05-18 7:41:00 PM
Comment:
In response to Dale's comments about not needing to change the provider very often I would like to point out that not all applications are designed to be run in house only. I work at a company that develops software for resale, not all of our customers have a SQL Server license, instead some use Oracle, and others (no matter how much we try to convince them not to) use access databases. In this type of scenario you cannot limit your customer base to only those who have a SQL Server license. You must make your DAL be able to adjust.

Title: A good work
Name: Joydeep Adhikary
Date: 2006-05-16 6:12:59 AM
Comment:
Mr. Kanjilal
This is a good article.Your all article is able to focus on your deepth in study.

Title: Lowest Common Denominator
Name: Dale
Date: 2006-05-12 4:47:46 PM
Comment:
This is a good article but it suffers from the same basic shortcoming that Microsoft's Data Access Application Block suffers from: By supporting all common data providers, it must lower itself to the lowest common denominator of all the providers. You can't take advantage of any specific enhancements by any provider.

How often, in real world situations, do you change providers in the life time of an application? Very seldom, I think. When you do change, it is easier then to just do a global search and replace in your code for SqlConnection and change to OracleConnection, and so on. That will prepare 90% of your code for the change and you only have to manually deal with the provider specific features that you used. But you will go through this so seldomly that changing the code when a data layer change actually occurs will utilize much less developer time than writing a one-size-fits-all solution from the start.

Generally, in code as in life, when you try to please all the people all the time, you end up pleasing no one. When you try to make your code handle anything, it generally handles nothing optimally.

Title: Outstanding
Name: Amit
Date: 2006-05-08 10:07:01 AM
Comment:
This is simply an outstanding article. thanks.

Title: Author Response
Name: Joydip
Date: 2006-05-08 7:16:45 AM
Comment:
Thanks for the feedbacks. Carl , you r very right, the RollbackTransaction() method is missing. It should be there. Thanks...

Title: Good
Name: Carl
Date: 2006-05-08 4:37:38 AM
Comment:
Good article, but obviously the BeginTransaction and CommitTransaction are pretty much unusable without a RollbackTransaction method....

But hey, there's a lot less source code than in the Enterprise Library, if this is the only sort of functionality that you need.

Yes, the Enterprise Library comes with full source code, but I challenge anyone to untangle just this functionality for standalone use...

Title: Nice
Name: Simone
Date: 2006-05-08 4:24:04 AM
Comment:
You did a nice work, but there's plenty of stuff like this out there, first at all the Data Access Application Block of MS Enterprise Library. Guess this is mostly for educational purpose.