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.



The following is the complete code for the DatabaseHelper class.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.IO;

using ApplicationFramework.Configuration;

namespace ApplicationFramework.DataAccessLayer
{
public class DatabaseHelper : IDisposable
{
private string strConnectionString;
private DbConnection objConnection;
private DbCommand objCommand;
private DbProviderFactory objFactory = null;
private ParameterCache parameterCache = ParameterCache.GetParameterCache();

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;
}

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

internal int AddParameter(DbParameter parameter)
{
return objCommand.Parameters.Add(parameter);
}

internal int AddParameter(string name, StoredProcedureParameterDirection parameterDirection)
{
DbParameter parameter = objFactory.CreateParameter();

parameter.ParameterName = name;
parameter.Value = String.Empty;
parameter.DbType = DbType.String;
parameter.Size = 50;

switch (parameterDirection)
{
case StoredProcedureParameterDirection.Input:
parameter.Direction = System.Data.ParameterDirection.Input;
break;
case StoredProcedureParameterDirection.Output:
parameter.Direction = System.Data.ParameterDirection.Output;
break;
case StoredProcedureParameterDirection.InputOutput:
parameter.Direction = System.Data.ParameterDirection.InputOutput;
break;
case StoredProcedureParameterDirection.ReturnValue:
parameter.Direction = System.Data.ParameterDirection.ReturnValue;
break;
}

return objCommand.Parameters.Add(parameter);
}

internal int AddParameter(string name, object value, StoredProcedureParameterDirection parameterDirection)
{
DbParameter parameter = objFactory.CreateParameter();

parameter.ParameterName = name;
parameter.Value = value;
parameter.DbType = DbType.String;
parameter.Size = 50;

switch (parameterDirection)
{
case StoredProcedureParameterDirection.Input:
parameter.Direction = System.Data.ParameterDirection.Input;
break;
case StoredProcedureParameterDirection.Output:
parameter.Direction = System.Data.ParameterDirection.Output;
break;
case StoredProcedureParameterDirection.InputOutput:
parameter.Direction = System.Data.ParameterDirection.InputOutput;
break;
case StoredProcedureParameterDirection.ReturnValue:
parameter.Direction = System.Data.ParameterDirection.ReturnValue;
break;
}

return objCommand.Parameters.Add(parameter);
}

internal int AddParameter(string name, StoredProcedureParameterDirection parameterDirection, int size, DbType dbType)
{
DbParameter parameter = objFactory.CreateParameter();

parameter.ParameterName = name;
parameter.DbType = dbType;
parameter.Size = size;

switch (parameterDirection)
{
case StoredProcedureParameterDirection.Input:
parameter.Direction = System.Data.ParameterDirection.Input;
break;
case StoredProcedureParameterDirection.Output:
parameter.Direction = System.Data.ParameterDirection.Output;
break;
case StoredProcedureParameterDirection.InputOutput:
parameter.Direction = System.Data.ParameterDirection.InputOutput;
break;
case StoredProcedureParameterDirection.ReturnValue:
parameter.Direction = System.Data.ParameterDirection.ReturnValue;
break;
}

return objCommand.Parameters.Add(parameter);
}

internal int AddParameter(string name, object value, StoredProcedureParameterDirection parameterDirection, int size, DbType dbType)
{
DbParameter parameter = objFactory.CreateParameter();

parameter.ParameterName = name;
parameter.Value = value;
parameter.DbType = dbType;
parameter.Size = size;

switch (parameterDirection)
{
case StoredProcedureParameterDirection.Input:
parameter.Direction = System.Data.ParameterDirection.Input;
break;
case StoredProcedureParameterDirection.Output:
parameter.Direction = System.Data.ParameterDirection.Output;
break;
case StoredProcedureParameterDirection.InputOutput:
parameter.Direction = System.Data.ParameterDirection.InputOutput;
break;
case StoredProcedureParameterDirection.ReturnValue:
parameter.Direction = System.Data.ParameterDirection.ReturnValue;
break;
}

return objCommand.Parameters.Add(parameter);
}

internal DbCommand Command
{
get
{
return objCommand;
}
}

internal DbConnection Connection
{
get
{
return objConnection;
}
}

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();
}

internal int ExecuteNonQuery(string query)
{
return ExecuteNonQuery(query, CommandType.Text, DatabaseConnectionState.CloseOnExit);
}

internal int ExecuteNonQuery(string query, CommandType commandtype)
{
return ExecuteNonQuery(query, commandtype, DatabaseConnectionState.CloseOnExit);
}

internal int ExecuteNonQuery(string query, DatabaseConnectionState connectionstate)
{
return ExecuteNonQuery(query, CommandType.Text, connectionstate);
}

internal int ExecuteNonQuery(string query, CommandType commandtype, DatabaseConnectionState connectionstate)
{
objCommand.CommandText = query;
objCommand.CommandType = commandtype;

int i = -1;

try
{
if (objConnection.State == System.Data.ConnectionState.Closed)
{
objConnection.Open();
}

i = objCommand.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
if (connectionstate == DatabaseConnectionState.CloseOnExit)
{
objConnection.Close();
}
}

return i;
}

internal object ExecuteScalar(string query)
{
return ExecuteScalar(query, CommandType.Text, DatabaseConnectionState.CloseOnExit);
}

internal object ExecuteScalar(string query, CommandType commandtype)
{
return ExecuteScalar(query, commandtype, DatabaseConnectionState.CloseOnExit);
}

internal object ExecuteScalar(string query, DatabaseConnectionState connectionstate)
{
return ExecuteScalar(query, CommandType.Text, connectionstate);
}

internal object ExecuteScalar(string query, CommandType commandtype, DatabaseConnectionState connectionstate)
{
objCommand.CommandText = query;
objCommand.CommandType = commandtype;
object o = null;
try
{
if (objConnection.State == System.Data.ConnectionState.Closed)
{
objConnection.Open();
}

o = objCommand.ExecuteScalar();
}
catch
{
throw;
}
finally
{
objCommand.Parameters.Clear();
if (connectionstate == DatabaseConnectionState.CloseOnExit)
{
objConnection.Close();
}
}

return o;
}

internal DbDataReader ExecuteReader(string query)
{
return ExecuteReader(query, CommandType.Text, DatabaseConnectionState.CloseOnExit);
}

internal DbDataReader ExecuteReader(string query, CommandType commandtype)
{
return ExecuteReader(query, commandtype, DatabaseConnectionState.CloseOnExit);
}

internal DbDataReader ExecuteReader(string query, DatabaseConnectionState connectionstate)
{
return ExecuteReader(query, CommandType.Text, connectionstate);
}

internal DbDataReader ExecuteReader(string query, CommandType commandtype, DatabaseConnectionState connectionstate)
{
objCommand.CommandText = query;
objCommand.CommandType = commandtype;
DbDataReader reader = null;
try
{
if (objConnection.State == System.Data.ConnectionState.Closed)
{
objConnection.Open();
}
if (connectionstate == DatabaseConnectionState.CloseOnExit)
{
reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
else
{
reader = objCommand.ExecuteReader();
}

}
catch
{

}
finally
{
objCommand.Parameters.Clear();
}

return reader;
}

internal DataSet ExecuteDataSet(string query)
{
return ExecuteDataSet(query, CommandType.Text, DatabaseConnectionState.CloseOnExit);
}

internal DataSet ExecuteDataSet(string query, CommandType commandtype)
{
return ExecuteDataSet(query, commandtype, DatabaseConnectionState.CloseOnExit);
}

internal DataSet ExecuteDataSet(string query, DatabaseConnectionState connectionstate)
{
return ExecuteDataSet(query, CommandType.Text, connectionstate);
}

internal DataSet ExecuteDataSet(string query, CommandType commandtype, DatabaseConnectionState connectionstate)
{
DbDataAdapter adapter = objFactory.CreateDataAdapter();
objCommand.CommandText = query;
objCommand.CommandType = commandtype;
adapter.SelectCommand = objCommand;
DataSet ds = new DataSet();
try
{
adapter.Fill(ds);
}
catch
{
throw;
}
finally
{
objCommand.Parameters.Clear();
if (connectionstate == DatabaseConnectionState.CloseOnExit)
{
if (objConnection.State == System.Data.ConnectionState.Open)
{
objConnection.Close();
}
}
}
return ds;
}

public void Dispose()
{
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
objConnection.Dispose();
}

objCommand.Dispose();
}

internal IDataReader ExecuteReader(string storedProcedureName, params object[] parameters)
{
objCommand.CommandText = storedProcedureName;
objCommand.CommandType = CommandType.StoredProcedure;
DbDataReader reader = null;
try
{
RetrieveParameters(objCommand);
SetParameterValues(objCommand, parameters);

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

reader = objCommand.ExecuteReader();
}
catch
{
throw;
}
finally
{
objCommand.Parameters.Clear();
}
return reader;
}

internal void SetParameterValues(DbCommand objCommand, object[] parameters)
{
int index = 0;
for (int i = 0; i < parameters.Length; i++)
{
DbParameter parameter = objCommand.Parameters[i + index];
SetParameterValue(objCommand, parameter.ParameterName, parameters[i]);
}
}

internal virtual void SetParameterValue(DbCommand dbCommand, string parameterName, object value)
{
dbCommand.Parameters[parameterName].Value = (value == null) ? DBNull.Value : value;
}

internal void RetrieveParameters(DbCommand dbCommand)
{
if (parameterCache.ContainsParameters(Connection.ConnectionString, dbCommand.CommandText))
{
DbParameter[] parameters = parameterCache.GetParameters(Connection.ConnectionString, dbCommand.CommandText);
dbCommand.Parameters.AddRange(parameters);
}
else
{
string connectionString = Connection.ConnectionString;
dbCommand.Connection = Connection;
Connection.Open();
SqlCommandBuilder.DeriveParameters(dbCommand as SqlCommand);
parameterCache.AddParameters(connectionString, dbCommand.CommandText, dbCommand.Parameters);
}
}

internal object GetParameter(string name)
{
return objCommand.Parameters[name].Value;
}
}
}

Conclusion

In the concluding part of this series we will discuss how we can create a wrapper class that encapsulates the DBHelper class and how we can use that class to perform the various CRUD operations in our database.