Data Access Layer based on DataSets

Introduction This article aims to introduce the reader to several conceptualproblems encountered in the development of a generic Data Access Layer(from now on referred to as DAL). It addresses itself both to beginnersand advanced practitioners of the art and it is hoped that everybodywill find something useful in the text that follows.

Context

The approach used in this writing is based onDataSets. In other words, the structures of data exchange is based onsuch sets which, since they constitute a generic representation ofdata, offer considerable flexibility when one is faced with changes ofdata structure. Data sets also make modifications to the entire systemeasy.

It is known that the most critical componentin the development of an enterprise system is the DAL, and that thesuccess of the whole system depends on it: the ability to adapt tochanges, as well as the performances, depend, again, on the DAL. Noticealso that the DAL intrinsically possesses the property of preventingerrors, or at least of reducing their impact on the system. Also,security depends in large part on the DAL – see for instance theproblem of SQL tampering. The underlying concept of this project isthat activities such as transaction management, caching, connectionmanagement, tracing, execution plan monitoring, should be managedautomatically by the DAL classes. Hence, developer should focus only onthe solution of functional problems. In such a context, developmenttime will be drastically reduced, and the quality of the entire systemis expected to be high.

What We Expect

When creating a new data access class (forinstance, the one that deals with Contacts) the type of code we wouldlike 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 oneresponsible for reading the data, only parameters for a storedprocedure have to be set, while class DBClass takes care of caching, ofthe management of connections and of the transactions for the readingoperations.

The class for data saving, DBContactsSave,should be as simple as the one for reading. Method Save calls a basemethod, SaveBase, and passes to it only the DataSet to be processed andthe name of the table in the data base, and nothing else. The best wayto ?force? a developer to think of possible data transformations, or ofthe necessary data integrity checks, is to define two mandatorymethods, defined as abstract: method PrepareDataSet and methodCheckData.

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 andReadBase. GetCommand manages the caching of the command parameters. Thenext 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 acommand based on the name of the stored procedure, which has beenpassed as parameter if it is not already present in the cache. Notethat, at the time of the creation of the command, methodDeriveParameters is called, which takes care of obtaining the parameterlist of the stored procedure, by querying the data base. At this point,it is merely necessary to save the parameters into the cache ? it isabsolutely necessary to copy them by calling the CopyTo method ? byusing the name of the stored procedure as key.

In the case that the stored procedure werestill in cache, it would only be necessary to rebuild the parameterslist for the command for it to become ready to be used.

Caching the parameters of the storedprocedures reduces the network traffic between this system and the database, 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 theExecute 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 methodFill is called to fill the DataSet. Of interest is the section forerror management, which could be further developed so that itautomatically writes all error details into the EventLog, as needed fordebugging, which has not been done for the code described here. Noticethat, for all errors, method RollBackTransaction is called. Later inthis writing, its work will be shown in more detail, while at thepresent 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 creationof an adapter for the command passed to it as parameter. If, at themoment of its construction, a transaction were set as parameter(m_trans), it would be assigned to the command in such a way that allreading operations would occur within the same transaction context.This makes it possible to read uncommitted records and perform allnecessary locks.

Method GetAdapter takes care of the creationof an adapter for the command passed to it as parameter. If, at themoment of its construction, a transaction were set as parameter(m_trans), it would be assigned to the command in such a way that allreading operations would occur within the same transaction context.This makes it possible to read uncommitted records and perform allnecessary locks.

Managing Transactions and Connections

This section explains how connections andtransactions 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 ispossible 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 beobtained by the transaction, however if this one has been set to null,then it is necessary to create a new one by getting the connectionstring from the Web.Config file (AppSettings).

In reference to the operation of closing theconnection, it is important to consider that the actual object couldlie in a call-chain – the current class could have been instantiated byanother one that updated some data on other tables. Thus, only thefirst object, the one that initiated the call-chain, can perform theclosing operation, and the same applies to the transaction. To thisend, the constructor sets a member variable, m_TransactionReceived,which indicates if the transaction, and also the connection, has beenpassed 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 beconsidered again, and in particular their closing within savingoperations 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 ofthe kind that needs to be re-implemented in all derived classes. Theyare used to manipulate the DataSet (for instance, by setting some fieldin an automatic way, by calculating some value or any other datamanipulation), or eventually to call other classes (call-chain) thatmanage the business logic. On the other hand, method CheckData is usedto perform all the checks necessary for the saving operations (forinstance, verification of the integrity of the amounts in theregistration of an account or any other checks). MethodGetAdapterForUpdate takes care of the creation of the adapter to savethe DataSet ? details will be shown later. Then method Update is calledand, if there is no error, it is necessary to commit the transaction.This operation can be performed only if the transaction has been openedby the current instance of the class, and not received at constructiontime (m_TransactionReceived). If instead an error has occurred, it isnecessary 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 calledfrom within SaveBase. Before looking into its details, the underlyingconcept is worth mentioning. For each table managed by this framework,it is necessary to create a set of stored procedures, not only thosefor reading operations, but also for writing such as insert, update anddelete. In the following example about management of cont

acts, suchstored 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 composedby the name of the table, followed by an underscore and by the name ofthe operation to be performed (insert, update or delete). To theSaveBase 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 creationof 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 updateoperation, it is necessary to assign the parameters, starting from theDataSet, to all stored procedures.

How To Use the DAL

The flow of operations is quite simple:

1. Read a DataSet (if it is needed to add new record, read an empty one)
2. Do all the modifications needed
3. 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 DALanalyzed in this writing, it would be desirable, in addition toliberally use stored procedures, to integrate a dynamic and automaticdata caching system.

In that is done, in addition to caching theparameters 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 backthem from it, it is necessary to modify method Execute, where all datareading operation are performed. Method GetDsFromCache takes care ofgetting 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 savingoperation, what is needed is a structure containing all DataSets. Thismeans that a HashTable has to be stored that contains all the DataSetsrelated 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 entireparameter list. Therefore, all commands with the same parameters willnot access the DB additionally, but the results will be retrieved fromthe cache.

GetDsFromCache has the purpose of getting aDataSet from the cache. To put one in the cache, there is methodCacheDs, the details of which are shown here:

protected void CacheDs(SqlCommand objCommand, DataSet objDataSet)
{
string strTableNam

e = "", 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 toclear the cached DataSets in order to keep the data into the cachesynchronized with those of the data base. Method ClearCache takes careof this operation.

Conclusions

The fulcrum around which any management systemrotates is the DAL, and therefore it is absolutely important to focuson performance and security when designing it. The example proposedhere shows a solution to this problem.

Download DAL-Article.zip

About the Author:

Salvatore Capuano is a R&D engineer at theiCIMSI Institute, the R&D department of the University of theApplied Science of Southern Switzerland. Salvatore and collaboratorSergio Aquilini have worked on .NET technology from its early versionsand developed a generic framework to easily support their partners inresearch projects. He can be reached at scapuano@freesurf.ch

The author acknowledges the suggestions of Dr.Denis L. Baggi and the support of the institute CIMSI of the Universityof Applied Science of Southern Switzerland (www.icimsi.ch ) for thisproject.