Implementing a Data Access Layer in C#


In this article, Joydip demosntrates the working of a provider independent DAL layer in C# with relevant source code. Note: This article was originally published on AspAlliance.
By: Joydip Kanjilal Spacer Date: October 27, 2007Spacer

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

publicenum�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
 {
 ��publicinterface�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
 {
 ��publicsealedclass�DBManagerFactory
 ��{
 ����private�DBManagerFactory(){}
 ����publicstatic�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:
 ����������returnnull;
 ������}
 ������return�iDbConnection;
 ����}
 �
 ����publicstatic�IDbCommandGetCommand(DataProvider�providerType)
 ����{
 ������switch�(providerType)
 ������{
 ��������case�DataProvider.SqlServer:
 ����������returnnew�SqlCommand();
 ��������case�DataProvider.OleDb:
 ����������returnnew�OleDbCommand();
 ��������case�DataProvider.Odbc:
 ����������returnnew�OdbcCommand();
 ��������case�DataProvider.Oracle:
 ����������returnnew�OracleCommand();
 ��������default:
 ����������returnnull;
 ������}
 ����}
 �
 ����publicstatic�IDbDataAdapterGetDataAdapter(DataProvider
 ����providerType)
 ����{
 ������switch�(providerType)
 ������{
 ��������case�DataProvider.SqlServer:
 ����������returnnew�SqlDataAdapter();
 ��������case�DataProvider.OleDb:
 ����������returnnew�OleDbDataAdapter();
 ��������case�DataProvider.Odbc:
 ����������returnnew�OdbcDataAdapter();
 ��������case�DataProvider.Oracle:
 ����������returnnew�OracleDataAdapter();
 ��������default:
 ����������returnnull;
 ������}
 ����}
 �
 ����publicstatic�IDbTransactionGetTransaction(DataProvider
 �����providerType)
 ����{
 ������IDbConnection�iDbConnection�=GetConnection(providerType);
 ������IDbTransaction�iDbTransaction�=iDbConnection.BeginTransaction();
 ������return�iDbTransaction;
 ����}
 �
 ����publicstatic�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
 {
 ��publicsealedclass�DBManager:�IDBManager,IDisposable
 ��{
 ����private�IDbConnection�idbConnection;
 ����private�IDataReader�idataReader;
 ����private�IDbCommand�idbCommand;
 ����private�DataProvider�providerType;
 ����private�IDbTransaction�idbTransaction�=null;
 ����private�IDbDataParameter[]idbParameters�=null;
 ����privatestring�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;
 ������}
 ����}
 �
 ����publicstring�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;
 ������}
 ����}
 �
 ����publicvoid�Open()
 ����{
 ������idbConnection�=
 ������DBManagerFactory.GetConnection(this.providerType);
 ������idbConnection.ConnectionString�=this.ConnectionString;
 ������if�(idbConnection.State�!=ConnectionState.Open)
 ��������idbConnection.Open();
 ������this.idbCommand�=DBManagerFactory.GetCommand(this.ProviderType);
 ����}
 �
 ����publicvoid�Close()
 ����{
 ������if�(idbConnection.State�!=ConnectionState.Closed)
 ��������idbConnection.Close();
 ����}
 �
 ����publicvoid�Dispose()
 ����{
 ������GC.SupressFinalize(this);
 ������this.Close();
 ������this.idbCommand�=null;
 ������this.idbTransaction�=null;
 ������this.idbConnection�=null;
 ����}
 �
 ����publicvoid�CreateParameters(intparamsCount)
 ����{
 ������idbParameters�=�newIDbDataParameter[paramsCount];
 ������idbParameters�=DBManagerFactory.GetParameters(this.ProviderType,
 ��������paramsCount);
 ����}
 �
 ����publicvoid�AddParameters(int�index,�stringparamName,�object
 �����objValue)
 ����{
 ������if�(index�<�idbParameters.Length)
 ������{
 ��������idbParameters[index].ParameterName�=paramName;
 ��������idbParameters[index].Value�=�objValue;
 ������}
 ����}
 �
 ����publicvoid�BeginTransaction()
 ����{
 ������if�(this.idbTransaction�==�null)
 ��������idbTransaction�=
 ��������DBManagerFactory.GetTransaction(this.ProviderType);
 ������this.idbCommand.Transaction�=idbTransaction;
 ����}
 �
 ����publicvoid�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();
 ������returnthis.DataReader;
 ����}
 �
 ����publicvoid�CloseReader()
 ����{
 ������if�(this.DataReader�!=null)
 ��������this.DataReader.Close();
 ����}
 �
 ����privatevoid�AttachParameters(IDbCommandcommand,
 ������IDbDataParameter[]commandParameters)
 ����{
 ������foreach�(IDbDataParameter�idbParameter�incommandParameters)
 ������{
 ��������if�((idbParameter.Direction�==�ParameterDirection.InputOutput)
 ��������&&
 ����������(idbParameter.Value�==�null))
 ��������{
 ����������idbParameter.Value�=�DBNull.Value;
 ��������}
 ��������command.Parameters.Add(idbParameter);
 ������}
 ����}
 �
 ����privatevoid�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);
 ������}
 ����}
 �
 ����publicint�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;
 ����}
 �
 ����publicobject�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

privatevoid�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.