Implementing a 3-Tier architecture with C# – Part 2

Hi again,

Let’s keep on going and work on the Data Access Layer. The DAL layer we are going to design can be used with, theoretically, any sort of RDBMS because it will take advantage of ADO.NET generic classes. For this reason, I have written a base class for DAL classes and have placed in into a seperate .dll. The reason is that the base DAL class is used for any database and is not being changed if the underlaying RDBMS is changed. But the DAL class must be replaced in order to change the target database. This will reduce coupling and will let you inherit your own DAL classes and make minor customizations only.

The base DAL class will do nearly everything and the derived DAL classes are used to customize CRUD commands only.

The following class diagram shows the relation between the base and derived DAL classes:

The DalBase class is using System.Data.Common namespace and it’s factory methods in order to create generic objects for different kind of databases. ADO.NET 2 introduces a class named DbProviderFactory, which is an implementation of Factory pattern, to produce a variety kind of database objects, like connection, command, adapter and so on. So far, it can create the following sort of objects:

  1. using System.Data.SqlClient
  2. using System.Data.Odbc
  3. using System.Data.OleDb

Most of the common ADO.NET classes are inherited from their bases classes defined in System.Data.Common namespace. For example. SqlConnection is derived from DbConenction, SqlDataAdapter is derived fom DbDataAdapter and so on.

To create a specific series of objects, we must get an instance of DbProviderFactory class, using DbProviderFactories.GetFactory method. The following code does this to obtain a factory for System.Data.SqlClient provider:

DbProviderFactory _ProviderFactory = DbProviderFactories.GetFactory(“System.Data.SqlClient”);

To make it more flexible, I will put the name of the desired provider in Web.Config file :

<add key=”Provider” value=”System.Data.SqlClient”/>


Access to Web.Config file will be through a helper class, called DalHelper. This class will provide us the connection string, provider name and so on :

public static class DALHelper
public static string ProviderName
return WebConfigurationManager.AppSettings[“Provider”];

Let’s take a look at DalBase class. DalBase has three protected properties:

  1. innerAdapter : a protected and read only DbDataAdapter that the derived classes will use to customize it’s Insert/Udpate/Select/Delete commands.
  2. innerConnection: a protected and read only DbConnection that will be in available to the derived classes and will be used in case a derived class needs to perform a particular CRUD operation.
  3. ProviderFactory: a protected and read only DbProviderFactory that will help derived classes create a specific DbConnection, DbCommand, DbDataAdapter, …

DalBase also has a parameterless constructor which will be discussed shortly. It also provides the following methods:

  1. Fill : it fills a DataSet or DataTable using innerAdapter.
  2. Update : it reflects the updates made to a DataSet/DataTable to the underlaying database using innerAdapter.
  3. Initialize : This method is protected and is used to customze the Select/Update/Insert/Delete command of innerAdapter.
  4. GetTotalRecordCount : for the time being, forget about this method. We will come back and discuss about it later.

The DALBase() parameterless constructor creates Command, Adapter and Connection objects according to the provider name, specified in Web.Config file and finally calls Initialize method:

public DALBase()
ProviderName = DALHelper.ProviderName;
_ProviderFactory = DbProviderFactories.GetFactory(ProviderName);

_innerConnection = _ProviderFactory.CreateConnection();

_innerAdapter = _ProviderFactory.CreateDataAdapter();

_innerAdapter.SelectCommand = _ProviderFactory.CreateCommand();
_innerAdapter.UpdateCommand = _ProviderFactory.CreateCommand();
_innerAdapter.InsertCommand = _ProviderFactory.CreateCommand();
_innerAdapter.DeleteCommand = _ProviderFactory.CreateCommand();

_innerConnection.ConnectionString = DALHelper.ConnectionString;
_innerAdapter.SelectCommand.Connection = _innerConnection;
_innerAdapter.UpdateCommand.Connection = _innerConnection;
_innerAdapter.InsertCommand.Connection = _innerConnection;
_innerAdapter.DeleteCommand.Connection = _innerConnection;


Therefore, by specifying a different provider name in Web.Config file, you will have a different set of ADO.NET objects.

The fill method has three parameters:

  1. a DataTable (or DataSet) to be filled with innerDataAdapter.
  2. an int parameter called startRowIndex. This is used for paging.
  3. ab int parameter called RowCount. This will be used for paging too

If one or two of the two int parameters is -1, the paging will not be performed.

if (startRowIndex == -1 && RowCount == -1)
_innerAdapter.Fill(startRowIndex, RowCount, table);