Implementing a Generic Data Access Layer in ADO.NET Part 2

In the first part of this series of articles on Data Access Layer, we have had a look at what the strategies are, for designing and implementing a Generic Data Access Layer. We have had a look at the enumerators and the factory classes that we will be using. In this part of this article of three part series, we will discuss how we can implement the DatabaseHelper class, one that would be responsible for performing the actual database operations.

The DatabaseHelper class encapsulates the various calls to the database to perform the CRUD operations. The DBManager class that we will discuss later acts as a wrapper on top of this class. You have various methods in the DatabaseHelper class to add parameters, to execute queries, stored procedures, etc.

Here is the code that illustrates how the connection to the database is established based on the provider type chosen and the command object created. 

        public DatabaseHelper(string connectionstring, ProviderType provider)
        {
            this.strConnectionString = connectionstring;
            objFactory = DBFactory.GetProvider(provider);
            objConnection = objFactory.CreateConnection();
            objCommand = objFactory.CreateCommand();
            objConnection.ConnectionString = this.strConnectionString;
            objCommand.Connection = objConnection;        
        }

In ADO.NET, you have the following data providers.

• SQL Server Data Provider
• Oracle Data Provider
• Odbc Data Provider
• OleDB Data Provider

Note: Depending on the data provider used, you need to use the command object that is specific to that provider. Your data reader should also be specific to the data provider used. The use of the DBFactory class as shown in the code snippet above. Note that you use the command objects to execute the database commands that contain the SQL statements. Added to this, we will have overloaded versions of AddParameter method to add parameters to the command objects so that we can pass parameters to the database stored procedures or SQL statements. Here is the simplest version of the AddParameter method.

        internal int AddParameter(string name, object value)
        {
            DbParameter dbParameter = objFactory.CreateParameter();
            dbParameter.ParameterName = name;
            dbParameter.Value = value;
            return objCommand.Parameters.Add(dbParameter);
        }

While the ParameterName identifies the unique name of the parameter to be passed, the Value implies the value of the parameter passed. Hence, if the ParameterName comprises of “@EmpName”, the Parameter’s value might be “Joydip Kanjilal”.

In order to ensure that our DataAccessLayer supports transactions, we have three methods that enable support for transactions. Fine, but what is a transaction? A transaction is an unit of work that is guaranteed to be executed in its entirety or not executed at all. Here are those methods.

        internal void BeginTransaction()
        {
            if (objConnection.State == System.Data.ConnectionState.Closed)
            {
                objConnection.Open();
            }

            objCommand.Transaction = objConnection.BeginTransaction();
        }

        internal void CommitTransaction()
        {
            objCommand.Transaction.Commit();
            objConnection.Close();
        }

        internal void RollbackTransaction()
        {
            objCommand.Transaction.Rollback();
            objConnection.Close();
        }

Note that we have methods that correspond to beginning, commiting or rolling a transaction back to revert the changes.

We will have the following four methods for performing the CRUD (Create, Update, Read and Delete) operations in the database. These methods are:
ExecuteScalar()
ExecuteReader()
ExecuteNonQuery()
ExecuteDataSet()

The ExecuteScalar() method is used to read one value from the database. The ExecuteReader() method returns a DataReader instance from the database populated with rows of data. The ExecuteNonQuery() method is used to insert, update or delete data either using SQL statements or using stored procedures.