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
By: Joydip Kanjilal
Date: October 27, 2007
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.