A Data Access Layer (DAL) is an integral part in the design of any application. There are plenty of articles that discuss how we an implement a DAL using ADO.NET. Most of these have constraints in the sense that they are not generic in nature. In other words, they are not provider independent. This series of articles will discuss the implementation of a generic, i.e., a provider independent Data Access Layer in ADO.NET. The basic prerequisite to learning this article is a proper understanding of ADO.NET and good coding skills in C#. I will present the code examples in this article in C#. However with little effort, you can twist it over to VB.NET as well.
First, you need to open the connection using a database provider. Fine, but what is a provider anyway? A provider is responsible for connecting to a specific database. Why specific? The reason is that a provider for an Oracle database cannot be used to connect to a SQL Server database and vice-versa. Next, you need a command object that can be used to execute the database commands of your choice. This is followed by the usage of a DataReader or a DataSet or a DataTable instance to retrieve data (if you are performing a Read operation) from the database table. When you use a DataSet, you need a DataAdapter as a bridge between the actual database and the DataSet instance.
Let us first understand the ADO.NET Library. The major classes that constitute the ADO.NET library are:
The next class in our discussion is the DBFactory class, designed on the factory design pattern. Before we discuss the DBFactory class and its intent, let us understand what a factory design pattern is. What is a factory design pattern? The Factory pattern is responsible for providing an interface for the creation of objects, but allows the inherited classes to decide on the appropriate time of these instantiations.
The following is the source code for our DBFactory class. It contains two static methods called GetProvider and GetDataAdapter both of which accept an instance of the database provider type enum, i.e., ProviderType.
Refer to the code example shown above. Both the methods in the code example shown above check the value of the enum reference instance and accordingly return an appropriate DbDataProvider or DbProviderFactory instance respectively. Such methods are actually called factory methods.
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.
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.
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.
Now we will come to the DBManager class; the wrapper class that encapsulates the calls to another class called DBHelper that actually performs the CRUD operations on the underlying database. The DBManager class extends the DBManagerBase abstract class. The DBManagerBase class contains the definition for the Open () and the Close () methods and some other public properties that are generic and can be used by any class that acts as a wrapper. We will have a look at the DBManagerBase class first.
The following code listing shows the DBManagerBase class.
The DBManager class that extends the DBManagerBase abstract class contains a list of methods that can be used to execute stored procedures, queries and return DataSet instance or DataReader instances as well. You can opt for keeping your connection open after the ExecuteReader method is called so that you can use the live connection in the subsequent operations that you need to perform on your database. The methods names in the DBManager class relate to the operations that they are meant to perform. I feel not you will have any problems understanding what each of these methods are supposed to do.
Then, you have the AddParameter method that can be used to add parameters to your stored procedure so that at the time of invoking the procedure, you can pass the parameters along. The connection string that we need to use to connect to our database can be set using the ConnectionString public property. The connection string can typically be stored in your configuration file and the DBManager class can read the configuration file to retrieve the connection string.
The provider type can be set using the ProviderType enum. Fine, but, where will these values be set, i.e., how can we call the DBManager and from where? Confused? Hang on. Let us have a look at the DBManager class followed by how we can use this class to perform CRUD operations.
The following code listing depicts the DBManager class.
Similarly, you can use the DBManager class to insert data as shown in the code snippet below.
The Strategies Involved in Creating a Data Access Layer
Let us first understand what the necessities are for building such a layer. I would rather start by discussing how an application designed using ADO.NET actually connects to the database and performs the CRUD (Create, Read, Update and Delete) operations.First, you need to open the connection using a database provider. Fine, but what is a provider anyway? A provider is responsible for connecting to a specific database. Why specific? The reason is that a provider for an Oracle database cannot be used to connect to a SQL Server database and vice-versa. Next, you need a command object that can be used to execute the database commands of your choice. This is followed by the usage of a DataReader or a DataSet or a DataTable instance to retrieve data (if you are performing a Read operation) from the database table. When you use a DataSet, you need a DataAdapter as a bridge between the actual database and the DataSet instance.
Implementing the DAL Framework
With this in mind, let us design a provider independent Data Access Layer.Let us first understand the ADO.NET Library. The major classes that constitute the ADO.NET library are:
- Connection
- Command
- Data Reader
- Data Adapter
- IDBConnection
- IDataReader
- IDBCommand
- IDBDataAdapter
- SQL Server Data Provider
- Oracle Data Provider
- ODBC Data Provider
- OleDB Data Provider
- ProviderType (Enum)
- DatabaseConnectionState (Enum)
- StoredProcedureParameterDirection (Enum)
- DBManager (Class)
- DBHelper (Class)
public enum ProviderType
{
SqlServer, OleDb, Oracle, ODBC, ConfigDefined
}Now, there may be situations where you might need to either keep the database connection state open or close after a database operation is over. As an example, after you read data into a DataReader instance from the underlying database, you might need to keep the connection state open for subsequent operations. You may also need to close it if it is no longer used. Keeping this in mind, let us have an enum data type that houses two values that correspond to the database connection states that we just discussed about. The following is the code for the enum called DatabaseConnectionState.public enum DatabaseConnectionState
{
KeepOpen, CloseOnExit
}When you are executing the stored procedures, you might want to send data to the database or retrieve the same from the database. Accordingly, we have another enum called StoredProcedureParameterDirection that contains values that correspond to the parameter directions for the stored procedures that we would execute with the help of our DAL. The following is the code for this enum.public enum StoredProcedureParameterDirection
{
Input, InputOutput, Output, ReturnValue
}We need a factory class that would return a DbProviderFactory type instance or a DbDataAdapter type instance depending on the data provider that we are using. This class contains factory methods that typically are static methods. What is a static method, anyway? This is an often misunderstood concept but a very important one. Well, a static method, often called a shared method (it is shared by all instances of the class that it belongs to) belongs to the class and a non-static method belongs to an object of a class. That is, a non-static method can only be called on an object of a class that it belongs to. A static method can however be called both on the class as well as an object of the class. Further, a static method can access the static members of a class only unlike a non-static method that can access both static and non-static members. These static methods in the DBFactory class accept a reference to the ProviderType enum that denotes the data provider type in use.The next class in our discussion is the DBFactory class, designed on the factory design pattern. Before we discuss the DBFactory class and its intent, let us understand what a factory design pattern is. What is a factory design pattern? The Factory pattern is responsible for providing an interface for the creation of objects, but allows the inherited classes to decide on the appropriate time of these instantiations.
The following is the source code for our DBFactory class. It contains two static methods called GetProvider and GetDataAdapter both of which accept an instance of the database provider type enum, i.e., ProviderType.
using System.Data.Common; using System.Data.SqlClient; using System.Data.OleDb; using System.Data.Odbc; using System.Data.OracleClient; using System.Collections.Generic; using System.Text; namespace DataAccessLayer { internal class DBFactory { private static DbProviderFactory objFactory = null; public static DbProviderFactory GetProvider(ProviderType provider) { switch (provider) { case ProviderType.SqlServer: objFactory = SqlClientFactory.Instance; break; case ProviderType.OleDb: objFactory = OleDbFactory.Instance; break; case ProviderType.Oracle: objFactory = OracleClientFactory.Instance; break; case ProviderType.ODBC: objFactory = OdbcFactory.Instance; break; } return objFactory; } public static DbDataAdapter GetDataAdapter(ProviderType providerType) { switch (providerType) { case ProviderType.SqlServer: return new SqlDataAdapter(); case ProviderType.OleDb: return new OleDbDataAdapter(); case ProviderType.ODBC: return new OdbcDataAdapter(); case ProviderType.Oracle: return new OracleDataAdapter(); default: return null; } } } }Note that you have different providers for different databases, i.e., the database providers are all database specific. A DataAdapter as we may recall, is a bridge between the database and the DataSet – a set of disconnected data. Though you have various data readers depending on the type of the data provider you are using, you have only one type of data set. Why? This is because a data set is a disconnected in-memory set of data. The schema of the database defines the schema of the data set.
Refer to the code example shown above. Both the methods in the code example shown above check the value of the enum reference instance and accordingly return an appropriate DbDataProvider or DbProviderFactory instance respectively. Such methods are actually called factory methods.
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
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 “Dinesh Sabat”.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;
}
}
} Note that most of the methods of the DatabaseHelper class have been marked as “internal” to prevent them from being called outside of the “ApplicationFramework.DataAccessLayer” namespace. Now we will come to the DBManager class; the wrapper class that encapsulates the calls to another class called DBHelper that actually performs the CRUD operations on the underlying database. The DBManager class extends the DBManagerBase abstract class. The DBManagerBase class contains the definition for the Open () and the Close () methods and some other public properties that are generic and can be used by any class that acts as a wrapper. We will have a look at the DBManagerBase class first.
The following code listing shows the DBManagerBase 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;
namespace ApplicationFramework.DataAccessLayer
{
public abstract class DBManagerBase
{
protected DatabaseHelper databaseHelper = null;
protected DbDataReader dbDataReader = null;
protected DataSet dataSet = null;
protected ProviderType providerType;
protected String connectionString = String.Empty;
protected bool isOpen = false;
public bool IsOpen
{
get
{
return isOpen;
}
set
{
isOpen = value;
}
}
public string ConnectionString
{
get
{
return connectionString;
}
set
{
connectionString = value;
}
}
public DbConnection Connection
{
get
{
return databaseHelper.Connection;
}
}
public DbCommand Command
{
get
{
return databaseHelper.Command;
}
}
public ProviderType DBProvider
{
set
{
providerType = value;
}
get
{
return providerType;
}
}
public DataSet DBSet
{
get
{
return dataSet;
}
}
public DbDataReader DBReader
{
get
{
return dbDataReader;
}
}
protected void Open(string connectionString)
{
databaseHelper = new DatabaseHelper(connectionString, DBProvider);
}
protected void Close()
{
if (dbDataReader != null)
if (!dbDataReader.IsClosed)
dbDataReader.Close();
databaseHelper.Dispose();
}
public void BeginTransaction()
{
databaseHelper.BeginTransaction();
}
public void CommitTransaction()
{
databaseHelper.CommitTransaction();
}
public void RollbackTransaction()
{
databaseHelper.RollbackTransaction();
}
}
}Note that the DBManagerBase class contains the most common methods that are required. You can Open or Close a connection, Begin, Commit or Rollback transactions, etc. These methods would remain the same and are mandatory in this context even if you decide to have another version of the DBManager class with some more methods implemented it.The DBManager class that extends the DBManagerBase abstract class contains a list of methods that can be used to execute stored procedures, queries and return DataSet instance or DataReader instances as well. You can opt for keeping your connection open after the ExecuteReader method is called so that you can use the live connection in the subsequent operations that you need to perform on your database. The methods names in the DBManager class relate to the operations that they are meant to perform. I feel not you will have any problems understanding what each of these methods are supposed to do.
Then, you have the AddParameter method that can be used to add parameters to your stored procedure so that at the time of invoking the procedure, you can pass the parameters along. The connection string that we need to use to connect to our database can be set using the ConnectionString public property. The connection string can typically be stored in your configuration file and the DBManager class can read the configuration file to retrieve the connection string.
The provider type can be set using the ProviderType enum. Fine, but, where will these values be set, i.e., how can we call the DBManager and from where? Confused? Hang on. Let us have a look at the DBManager class followed by how we can use this class to perform CRUD operations.
The following code listing depicts the DBManager 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;
namespace ApplicationFramework.DataAccessLayer
{
public sealed class DBManager : DBManagerBase
{
public void OpenConnection()
{
connectionString = ConfigurationSettings.AppSettings["ConnectionString"].ToString();
base.Open(connectionString);
}
public void OpenConnection(String connectionString)
{
base.Open(connectionString);
base.IsOpen = true;
}
public void CloseConnection()
{
if (base.isOpen)
base.Close();
base.IsOpen = false;
}
public int AddParameter(string name, object value)
{
return databaseHelper.AddParameter(name, value);
}
public int AddParameter(string name, StoredProcedureParameterDirection parameterDirection)
{
return databaseHelper.AddParameter(name, parameterDirection);
}
public int AddParameter(string name, object value, StoredProcedureParameterDirection parameterDirection)
{
return databaseHelper.AddParameter(name, value, parameterDirection);
}
public int AddParameter(string name, StoredProcedureParameterDirection parameterDirection, int size, DbType dbType)
{
return databaseHelper.AddParameter(name, parameterDirection, size, dbType);
}
public int AddParameter(string name, object value, StoredProcedureParameterDirection parameterDirection, int size, DbType dbType)
{
return databaseHelper.AddParameter(name, value, parameterDirection, size, dbType);
}
public object GetParameter(string name)
{
return databaseHelper.GetParameter(name);
}
public DbDataReader ExecuteReader(string query)
{
this.dbDataReader = databaseHelper.ExecuteReader(query);
return this.dbDataReader;
}
public DbDataReader ExecuteReader(string query, CommandType commandtype)
{
this.dbDataReader = databaseHelper.ExecuteReader(query, commandtype, DatabaseConnectionState.CloseOnExit);
return this.dbDataReader;
}
public IDataReader ExecuteReader(string storedProcedureName, params object[] parameters)
{
this.dbDataReader = (DbDataReader)databaseHelper.ExecuteReader(storedProcedureName, parameters);
return this.dbDataReader;
}
public DbDataReader ExecuteReader(string query, CommandType commandtype, DatabaseConnectionState connectionstate)
{
this.dbDataReader = databaseHelper.ExecuteReader(query, commandtype, connectionstate);
return this.dbDataReader;
}
public DbDataReader ExecuteReader(string query, DatabaseConnectionState connectionstate)
{
this.dbDataReader = databaseHelper.ExecuteReader(query, connectionstate);
return this.dbDataReader;
}
public object ExecuteScalar(string query)
{
return databaseHelper.ExecuteScalar(query);
}
public object ExecuteScalar(string query, CommandType commandtype)
{
return databaseHelper.ExecuteScalar(query, commandtype);
}
public object ExecuteScalar(string query, DatabaseConnectionState connectionstate)
{
return databaseHelper.ExecuteScalar(query, connectionstate);
}
public object ExecuteScalar(string query, CommandType commandtype, DatabaseConnectionState connectionstate)
{
return databaseHelper.ExecuteScalar(query, commandtype, connectionstate);
}
public DataSet ExecuteDataSet(string query)
{
this.dataSet = databaseHelper.ExecuteDataSet(query);
return this.dataSet;
}
public DataSet ExecuteDataSet(string query, CommandType commandtype)
{
this.dataSet = databaseHelper.ExecuteDataSet(query, commandtype);
return this.dataSet;
}
public int ExecuteNonQuery(string query, CommandType commandtype)
{
return databaseHelper.ExecuteNonQuery(query, commandtype);
}
public int ExecuteNonQuery(string query, CommandType commandtype, DatabaseConnectionState databaseConnectionState)
{
return databaseHelper.ExecuteNonQuery(query, commandtype, databaseConnectionState);
}
}
}Using the DBManager class
You can make use of the DBManager class as shown in the code snippet below.DBManager dbManager = new DBManager();
dbManager.OpenConnection();
dbManager.ExecuteReader("Select * from employee");
while (dbManager.DBReader.Read())
Response.Write(dbManager.DBReader[“EmpName”].ToString());
dbManager.CloseConnection();Note that the OpenConnection and the CloseConnection methods of the DBManager class invoke the Open and the Close methods of the DBManagerBase class internally.Similarly, you can use the DBManager class to insert data as shown in the code snippet below.
DBManager dbManager = new DBManager();
String sql = "insert into employee (EmpCode, EmpName) values ('E001''Joydip')";
try
{
dbManager.OpenConnection();
dbManager.ExecuteNonQuery(sql,CommandType.Text);
}
catch(Exception e)
{
HttpContext.Current.Response.Write(e);
}
finally
{
dbManager.CloseConnection();
HttpContext.Current.Response.Write("<BR>"+"1 record added...");
}
Very well documented thanks...can you please share the definition of ParameterCache.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHello, Though it may appear a bit late but I need your assistance on how to use Parameters to perform CRUD with this code. Please, could you make for us a WinForm Sample Application for this code? This could be helpful to many of your blog followers.
ReplyDeleteThanks