Implementing a Generic Data Access Layer in ADO.NET Part 2
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.