Introduction
This article aims to introduce the reader to several conceptual problems encountered in the development of a generic Data Access Layer (from now on referred to as DAL). It addresses itself both to beginners and advanced practitioners of the art and it is hoped that everybody will find something useful in the text that follows.
Context
The approach used in this writing is based on DataSets. In other words, the structures of data exchange is based on such sets which, since they constitute a generic representation of data, offer considerable flexibility when one is faced with changes of data structure. Data sets also make modifications to the entire system easy.
It is known that the most critical component in the development of an enterprise system is the DAL, and that the success of the whole system depends on it: the ability to adapt to changes, as well as the performances, depend, again, on the DAL. Notice also that the DAL intrinsically possesses the property of preventing errors, or at least of reducing their impact on the system. Also, security depends in large part on the DAL - see for instance the problem of SQL tampering. The underlying concept of this project is that activities such as transaction management, caching, connection management, tracing, execution plan monitoring, should be managed automatically by the DAL classes. Hence, developer should focus only on the solution of functional problems. In such a context, development time will be drastically reduced, and the quality of the entire system is expected to be high.
What We Expect
When creating a new data access class (for instance, the one that deals with Contacts) the type of code we would like to write is as follows:
public class DBContacts : DBClass
{
public DBContacts(SqlTransaction trans) : base(trans)
{
}
public DataSet Read(int intID)
{
SqlCommand objCommand = GetCommand("Contacts_Read");
objCommand.Parameters["@id"].Value = intID;
return base.ReadBase(objCommand);
}
public DataSet List()
{
SqlCommand objCommand = GetCommand("Contacts_List");
return base.ListBase(objCommand);
}
}
public class DBContactsSave : DBClassSave
{
public DBContactsSave(SqlTransaction trans) : base(trans)
{
}
public void Save(DataSet objDataSet)
{
base.SaveBase(objDataSet, "Contacts");
}
protected override DataSet PrepareDataSet (DataSet objDataSet)
{
base.BeginTransaction();
//all needed operation to prepare the dataset for the saving operation
return objDataSet;
}
protected override void CheckData(DataSet objDataSet)
{
//all needed check
}
}
In the first class, DBContacts, the one responsible for reading the data, only parameters for a stored procedure have to be set, while class DBClass takes care of caching, of the management of connections and of the transactions for the reading operations.
The class for data saving, DBContactsSave, should be as simple as the one for reading. Method Save calls a base method, SaveBase, and passes to it only the DataSet to be processed and the name of the table in the data base, and nothing else. The best way to force a developer to think of possible data transformations, or of the necessary data integrity checks, is to define two mandatory methods, defined as abstract: method PrepareDataSet and method CheckData.
This is the aim of this work. What follows will show how this aim is achieved.
Reading Data
What follows is a review of method Read:
public DataSet Read(int intID)
{
SqlCommand objCommand = GetCommand("Contacts_Read");
objCommand.Parameters["@id"].Value = intID;
return base.ReadBase(objCommand);
}
There are two basic methods: GetCommand and ReadBase. GetCommand manages the caching of the command parameters. The next paragraph describes its function.
Caching Stored Procedure Parameters
Here is the listing of the code for GetCommand:
protected SqlCommand GetCommand(string strStoredName)
{
strStoredName = strStoredName.ToUpper();
SqlCommand objCommand = new SqlCommand();
objCommand.CommandText = strStoredName;
objCommand.CommandType = CommandType.StoredProcedure;
if (HttpContext.Current.Cache[strStoredName] == null)
{
Utilities.Trace("Deriving Command Parameters from DB (" + strStoredName + ")");
SqlConnection objConn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnStr"]);
objCommand.Connection = objConn;
objCommand.Connection.Open();
SqlCommandBuilder.DeriveParameters(objCommand);
objCommand.Connection.Close();
SqlParameter[] arrParam = new SqlParameter[objCommand.Parameters.Count];
objCommand.Parameters.CopyTo(arrParam, 0);
HttpContext.Current.Cache[strStoredName] = arrParam;
}
else
{
Utilities.Trace("Retriving Command Parameters from Cache (" + strStoredName + ")");
SqlParameter[] param = (SqlParameter[]) HttpContext.Current.Cache[strStoredName];
for (int i = 0; i < param.Length; i++)
{
objCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(
param[i].ParameterName, param[i].SqlDbType, param[i].Size, param[i].Direction, param[i].IsNullable, param[i].Precision, param[i].Scale, param[i].SourceColumn, param[i].SourceVersion, null));
}
}
return objCommand;
}
This method takes care of the creation of a command based on the name of the stored procedure, which has been passed as parameter if it is not already present in the cache. Note that, at the time of the creation of the command, method DeriveParameters is called, which takes care of obtaining the parameter list of the stored procedure, by querying the data base. At this point, it is merely necessary to save the parameters into the cache it is absolutely necessary to copy them by calling the CopyTo method by using the name of the stored procedure as key.
In the case that the stored procedure were still in cache, it would only be necessary to rebuild the parameters list for the command for it to become ready to be used.
Caching the parameters of the stored procedures reduces the network traffic between this system and the data base, which consequently improves the performance.
Executing and Reading Commands
The ReadBase method is examined as follows:
protected virtual DataSet ReadBase(SqlCommand objCommand)
{
return Execute(objCommand);
}
This method, like ListBase, is based on the Execute method. To better understand it, and because of its complexity, it will be analyzed step by step.
The simplest version, the one that merely reads data, will be considered first:
private DataSet Execute(SqlCommand objCommand)
{
try
{
DataSet objDataSet = null;
using(SqlDataAdapter objAdapter = GetAdapter(objCommand))
{
objDataSet = new DataSet();
objAdapter.Fill(objDataSet);
objDataSet.EnforceConstraints = false;
return objDataSet;
}
}
catch (Exception e)
{
RollBackTransaction();
throw e;
}
finally
{
CloseConnection();
}
}
The flow is very simple: GetAdapter is called (more details will be considered later in this writing), then method Fill is called to fill the DataSet. Of interest is the section for error management, which could be further developed so that it automatically writes all error details into the EventLog, as needed for debugging, which has not been done for the code described here. Notice that, for all errors, method RollBackTransaction is called. Later in this writing, its work will be shown in more detail, while at the present time method GetAdapter will be considered:
private SqlDataAdapter GetAdapter (SqlCommand objSqlCommand)
{
SqlDataAdapter objAdapter;
objSqlCommand.Connection = GetConnection();
if (m_trans != null)
objSqlCommand.Transaction = m_trans;
objAdapter = new SqlDataAdapter();
objAdapter.SelectCommand = objSqlCommand;
return objAdapter;
}
Method GetAdapter takes care of the creation of an adapter for the command passed to it as parameter. If, at the moment of its construction, a transaction were set as parameter (m_trans), it would be assigned to the command in such a way that all reading operations would occur within the same transaction context. This makes it possible to read uncommitted records and perform all necessary locks.
Method GetAdapter takes care of the creation of an adapter for the command passed to it as parameter. If, at the moment of its construction, a transaction were set as parameter (m_trans), it would be assigned to the command in such a way that all reading operations would occur within the same transaction context. This makes it possible to read uncommitted records and perform all necessary locks.
Managing Transactions and Connections
This section explains how connections and transactions are managed. The base class for data access, DBGenericClass, requires a transaction as parameter for the constructor (the transaction can also be null). From the transaction object, it is possible to obtain a connection, which may have been already opened. Here is the code:
public DBGenericClass(SqlTransaction trans)
{
if (trans != null)
{
m_conn = trans.Connection;
m_trans = trans;
}
else
{
m_conn = null;
m_trans = null;
}
m_TransactionReceived = (m_trans != null);
}
And here follows the code of the GetConnection method:
protected SqlConnection GetConnection()
{
if (m_conn == null)
{
if (m_trans != null)
{
m_conn = m_trans.Connection;
}
else
{
string strConn = ConfigurationSettings.AppSettings["ConnStr"];
m_conn = new SqlConnection(strConn);
}
}
return m_conn;
}
If the connection has not been set, it can be obtained by the transaction, however if this one has been set to null, then it is necessary to create a new one by getting the connection string from the Web.Config file (AppSettings).
In reference to the operation of closing the connection, it is important to consider that the actual object could lie in a call-chain - the current class could have been instantiated by another one that updated some data on other tables. Thus, only the first object, the one that initiated the call-chain, can perform the closing operation, and the same applies to the transaction. To this end, the constructor sets a member variable, m_TransactionReceived, which indicates if the transaction, and also the connection, has been passed by another class or not.
protected void CloseConnection()
{
if (!m_TransactionReceived)
{
if (m_conn != null)
{
m_conn.Dispose();
m_conn = null;
}
}
}
Next, in this writing, transactions will be considered again, and in particular their closing within saving operations RollBack and Commit.
Saving Data
Here is the listing of method DBClassSave.SaveBase:
protected virtual void SaveBase(DataSet objDataSet, string strPrefixStored)
{
m_BeginTransAllowed = true;
SqlDataAdapter objDataAdapter = null;
try
{
objDataSet = PrepareDataSet(objDataSet);
CheckData(objDataSet);
objDataAdapter = GetAdapterForUpdate(strPrefixStored, objDataSet);
if (objDataSet.HasChanges ())
if (objDataAdapter.Update(objDataSet) == 0)
throw new CustomException ("Exception_NoRecordUpdated");
if (!this.m_TransactionReceived)
{
if (this.m_trans != null)
{
this.m_trans.Commit();
}
}
else
{
if (this.m_trans.Connection == null)
{
throw new ApplicationException("The transaction is closed when it has to be still open");
}
}
}
catch (DBConcurrencyException)
{
RollBackTransaction();
throw new CustomException("Exception_ConcurrencyViolation");
}
catch (Exception e)
{
RollBackTransaction();
throw e;
}
finally
{
CloseConnection();
if (objDataAdapter != null)
objDataAdapter.Dispose();
}
}
Methods PrepareDataSet and CheckData are of the kind that needs to be re-implemented in all derived classes. They are used to manipulate the DataSet (for instance, by setting some field in an automatic way, by calculating some value or any other data manipulation), or eventually to call other classes (call-chain) that manage the business logic. On the other hand, method CheckData is used to perform all the checks necessary for the saving operations (for instance, verification of the integrity of the amounts in the registration of an account or any other checks). Method GetAdapterForUpdate takes care of the creation of the adapter to save the DataSet details will be shown later. Then method Update is called and, if there is no error, it is necessary to commit the transaction. This operation can be performed only if the transaction has been opened by the current instance of the class, and not received at construction time (m_TransactionReceived). If instead an error has occurred, it is necessary to perform a RollBack of all changes made in the transaction. Here is the code of the RollBackTransaction method:
protected void RollBackTransaction()
{
try
{
if (m_trans != null)
if (m_trans.Connection != null)
m_trans.Rollback();
}
catch (Exception e)
{
Logging.Log(e.Message);
throw;
}
finally
{
if (m_conn != null)
{
m_conn.Dispose();
m_conn = null;
}
}
}
GetAdapter is the most important method called from within SaveBase. Before looking into its details, the underlying concept is worth mentioning. For each table managed by this framework, it is necessary to create a set of stored procedures, not only those for reading operations, but also for writing such as insert, update and delete. In the following example about management of contacts, such stored procedures are:
Contacts_Delete
CREATE PROCEDURE dbo.Contacts_Delete
(
@Original_ID int,
@Original_time_st timestamp
)
AS
SET NOCOUNT OFF;
DELETE FROM CONTACTS WHERE (ID = @Original_ID) AND (time_st = @Original_time_st)
GO
Contacts_Insert
CREATE PROCEDURE dbo.Contacts_Insert
(
@GUID uniqueidentifier,
@FIRST_NAME varchar(50),
@LAST_NAME varchar(50),
@MIDDLE_NAME varchar(50)
)
AS
SET NOCOUNT OFF;
INSERT INTO CONTACTS(GUID, TITLE, FIRST_NAME, LAST_NAME, MIDDLE_NAME) VALUES (@GUID, @FIRST_NAME, @LAST_NAME, @MIDDLE_NAME)
GO
Contacts_Update
CREATE PROCEDURE dbo.Contacts_Update
(
@GUID uniqueidentifier,
@FIRST_NAME varchar(50),
@LAST_NAME varchar(50),
@MIDDLE_NAME varchar(50)
@Original_ID int,
@Original_time_st timestamp
)
AS SET NOCOUNT OFF;
UPDATE CONTACTS SET GUID = @GUID, FIRST_NAME = @FIRST_NAME, LAST_NAME = @LAST_NAME, MIDDLE_NAME = @MIDDLE_NAME WHERE (time_st = @Original_time_st) AND (ID = @Original_ID)
GO
The name of the stored procedures is composed by the name of the table, followed by an underscore and by the name of the operation to be performed (insert, update or delete). To the SaveBase method, the prefix of the stored procedure name is passed, i.e., the table name as for strPrefixStored.
To manage concurrent accesses for all writing operations, each table has a field named time_st which represents a timestamp.
After these explanations, it is now possible to consider in greater detail GetAdapterForUpdate:
public SqlDataAdapter GetAdapterForUpdate (string strPrefixStored, DataSet ds)
{
SqlDataAdapter objAdapter;
SqlCommand objDeleteCommand = GetCommand(strPrefixStored + "_Delete");
SqlCommand objUpdateCommand = GetCommand(strPrefixStored + "_Update");
SqlCommand objInsertCommand = GetCommand(strPrefixStored + "_Insert");
if (m_trans != null)
{
objDeleteCommand.Transaction = m_trans;
objUpdateCommand.Transaction = m_trans;
objInsertCommand.Transaction = m_trans;
}
objDeleteCommand.Connection = GetConnection();
objUpdateCommand.Connection = GetConnection();
objInsertCommand.Connection = GetConnection();
///////////////////////////////////////////////////////
/// Adapter Creation
///////////////////////////////////////////////////////
objAdapter = new SqlDataAdapter();
objAdapter.DeleteCommand = objDeleteCommand;
objAdapter.UpdateCommand = objUpdateCommand;
objAdapter.InsertCommand = objInsertCommand;
try
{
foreach (SqlParameter p in objDeleteCommand.Parameters)
{
if (p.ParameterName.ToUpper() != "@RETURN_VALUE")
{
p.SourceColumn = ds.Tables[0].Columns[p.ParameterName.ToUpper().Replace
("@ORIGINAL_","")].ToString();
p.SourceVersion = DataRowVersion.Original;
}
foreach (SqlParameter p in objInsertCommand.Parameters)
{
if (p.ParameterName.ToUpper() != "@RETURN_VALUE")
{
p.SourceColumn = ds.Tables[0].Columns[p.ParameterName.Replace("@","")
].ToString();
}
}
foreach (SqlParameter p in objUpdateCommand.Parameters)
{
if (p.ParameterName.ToUpper() != "@RETURN_VALUE")
{
if (!p.ParameterName.ToUpper().StartsWith("@ORIGINAL_"))
{
p.SourceColumn = ds.Tables[0].Columns[p.ParameterName.Replace
("@","")].ToString();
}
else
{
p.SourceColumn = ds.Tables[0].Columns[p.ParameterName.ToUpper
().Replace("@ORIGINAL_","")].ToString();
p.SourceVersion = DataRowVersion.Original;
}
}
}
}
catch(Exception e)
{
}
return objAdapter;
}
The first task to be performed is the creation of the command. If member variable m_trans, the transaction received, is set, then it has to be assigned to all the commands. At this point, the adapter is created and the three commands objDeleteCommand, objInsertCommand, objUpdateCommand are assigned to it.
In order to use the adapter for the update operation, it is necessary to assign the parameters, starting from the DataSet, to all stored procedures.
How To Use the DAL
The flow of operations is quite simple:
- Read a DataSet (if it is needed to add new record, read an empty one)
- Do all the modifications needed
- Call method Save
That is all there is to it.
Consider this simple example:
DataSet ds = (new DBContacts(null)).Read(0);
DataRow dr = ds.Tables[0].NewRow();
dr["First_Name"] = "FirstName";
dr["Last_Name"] = "LastName";
dr["Middle_name"] = "MiddleName";
ds.Tables[0].Rows.Add(dr);
new DBContactsSave(null).Save(ds);
Caching
To improve the performances of the DAL analyzed in this writing, it would be desirable, in addition to liberally use stored procedures, to integrate a dynamic and automatic data caching system.
In that is done, in addition to caching the parameters of the stored procedures, also the DataSets would be cached, which would decrease the number of accesses to the Data Base.
To put all DataSets in the cache and get back them from it, it is necessary to modify method Execute, where all data reading operation are performed. Method GetDsFromCache takes care of getting the DataSets from the cache. Here is the code:
protected DataSet GetDsFromCache(SqlCommand objCommand)
{
string strTableName = GetTableName(objCommand).ToUpper(), strCacheElementKey = "";
strCacheElementKey = objCommand.CommandText.ToUpper();
foreach (SqlParameter par in objCommand.Parameters)
{
if (par.Direction != ParameterDirection.ReturnValue)
strCacheElementKey += par.ParameterName.ToUpper() + par.Value;
}
if (HttpContext.Current.Cache[strTableName] != null)
{
if (((Hashtable) HttpContext.Current.Cache[strTableName])
[strCacheElementKey] != null)
{
return ((DataSet)(((Hashtable)
HttpContext.Current.Cache[strTableName])[strCacheElementKey])).Copy();
}
}
return null;
}
To clear the cache correctly at each saving operation, what is needed is a structure containing all DataSets. This means that a HashTable has to be stored that contains all the DataSets related to a table, the name of which is used as key for the caching. The DataSets in the HashTable are stored using as key the entire parameter list. Therefore, all commands with the same parameters will not access the DB additionally, but the results will be retrieved from the cache.
GetDsFromCache has the purpose of getting a DataSet from the cache. To put one in the cache, there is method CacheDs, the details of which are shown here:
protected void CacheDs(SqlCommand objCommand, DataSet objDataSet)
{
string strTableName = "", strCacheElementKey = "";
strTableName = GetTableName(objCommand);
strCacheElementKey = objCommand.CommandText.ToUpper();
foreach (SqlParameter par in objCommand.Parameters)
{
if (par.Direction != ParameterDirection.ReturnValue)
strCacheElementKey += par.ParameterName.ToUpper() + par.Value;
}
if (HttpContext.Current.Cache[strTableName] == null)
{
HttpContext.Current.Cache[strTableName] = new Hashtable();
}
try
{
((Hashtable) HttpContext.Current.Cache[strTableName]).
Add(strCacheElementKey, objDataSet.Copy());
}
catch
{
}
}
Notice that it is absolutely necessary to copy the original DataSet.
In the SaveBase method it is necessary to clear the cached DataSets in order to keep the data into the cache synchronized with those of the data base. Method ClearCache takes care of this operation.
Conclusions
The fulcrum around which any management system rotates is the DAL, and therefore it is absolutely important to focus on performance and security when designing it. The example proposed here shows a solution to this problem.