Click here to Skip to main content
Click here to Skip to main content

Data Access and Transaction Handling Framework

, 13 Jan 2005
Rate this:
Please Sign up or sign in to vote.
Generic data access component for different datasources, sprocs/SQL, implicitly propagated transactions, explicitly managed transaction contexts etc.

Introduction

This is another go at the problem of data access and transaction handling in a multi-tier business application (for example web application). The suggested approach should help in building a database independent data layer. The database can be changed later by simply translating the XML CONFIG files to the required database dialect. Both the SQL and stored procedure commands can be used and easily switched between, without changing the data layer code. The transactions are managed implicitly, but the code explicitly deals with transaction contexts. Several TransactionHandlers are supplied.

Background

The data layer in a multi-tier application depends on the principal application architecture. The architectural design, on which the rest of the article is based, consists of a Facade layer, Business Logic Layer, Data Layer, and of course additionally a UI in front and a database back behind. The middle tier as a whole is stateless (state kept in the DB) and the data should pass through it in two directions - from UI to the database and from the database to the UI. The architecture is a long story and I will not go into its details. The transactions can be controlled from different layers, usually either from the Facade layer or the Business Logic Layer, while the data access code is usually placed in the data layer.

Objectives

  1. Database independent data layer - This means that the database can be changed simply by rewriting the XML files containing the command definitions without rewriting the data layer code (similar to localization).
  2. Support for different data providers and multiple data sources - A certain data source can be retrieved from the factory by specifying its name.
  3. Implicit support for stored procedures as well as SQL statements and SQL batches - In the beginning an SQL batch can be specified and saved in the CONFIG file, later on it can be changed to a stored proc in the data source without affecting the data layer code.
  4. Command parameters are cached and only the values are set in the code (no need for defining the parameters in the code).
  5. DataSet/DataAdapter support, defining DataAdapters in XML or in the code using commands defined in the CONFIG files.
  6. Implicit handling of transactions/connections, the code explicitly enters/commits/rollbacks/exits transaction contexts. A transaction context may span several methods. A method may enter several nested transaction contexts, across several data sources as well.

Design ideas

  1. CONFIG XML file with available data providers and data sources.
  2. CONFIG XML file(s) (one or more in a specified directory with a specified file mask) per data source, containing the definitions of the commands. A command definition consists of commandtext, commandtype and parameters. A command's name may/may not coincide with a sproc name.
  3. It is possible to directly derive stored procedures by name from the database, if a corresponding command is not specified in the CONFIG files.
  4. Current transaction/connections are stored in the Thread Local Storage (TLS). Once a transaction (+connection) is opened it is used implicitly by the data layer.

Using the Framework.DataAccess code

The IDataSource is used as a factory for IDataCommands. It represents a certain database and a .NET data provider, and caches all the commands for this database. Data sources are defined like this:

<dataAccessSettings xmlns="Framework.DataAccess">
    <dataProviders>
        <dataProvider name="SqlClient" 
           connectionType="System.Data.SqlClient.SqlConnection, 
              System.Data, Version=1.0.3300.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089"
           commandType="System.Data.SqlClient.SqlCommand, 
              System.Data, Version=1.0.3300.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089"
           parameterType="System.Data.SqlClient.SqlParameter, 
              System.Data, Version=1.0.3300.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089"
           parameterDbType="System.Data.SqlDbType, System.Data, 
              Version=1.0.3300.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089"
           parameterDbTypeProperty="SqlDbType"
           dataAdapterType="System.Data.SqlClient.SqlDataAdapter, 
              System.Data, Version=1.0.3300.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089"
           commandBuilderType="System.Data.SqlClient.SqlCommandBuilder, 
              System.Data, Version=1.0.3300.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089"
           parameterNamePrefix="@"/>
                   ...
    </dataProviders>
    <dataSources>
        <dataSource name="DataSource1" isDefault="true"
           provider="SqlClient"
           connectionString="Server=XXXXX;Database=XXXXXXXX;User 
              ID=XXXX;Password=XXXXXXXX"
           dataOperationsPath=
              "..\..\config\SqlClient.DataSource1.Commands*.config"/>
            .....
    </dataSources>
</dataAccessSettings>

This is how all the provider-specific stuff is externalized from the code and put in the CONFIG file.

Instantiation of the default data source would require the following:

IDataSource ds = DataSourceFactory.GetDataSource();

for a named data source:

IDataSource ds = DataSourceFactory.GetDataSource("DataSource1");

IDataCommand is the object representing a certain database operation resulting in no output, output parameters or output IDataReader.

The command definitions are externalized in an XML file like this:

<?xml version="1.0" encoding="utf-8" ?> 
<dataOperations dataSource="DataSource1" 
     xmlns="Framework.DataAccess">
<dataCommands>
    <dataCommand name="Command1" type="Text">
        <commandText>INSERT INTO Users(Username) 
            VALUES('user')</commandText>
    </dataCommand>
</dataCommands>
....
</dataOperations>

A command with two parameters returning nothing can be executed this way:

IDataSource ds = DataSourceFactory.GetDataSource();
IDataCommand cmd = ds.GetCommand("UpdateUserAmount");
cmd.Parameters["UserID"].Value = userID;
cmd.Parameters["Amount"].Value = amount;
cmd.ExecuteNonQuery();

The execution of a command returning IDataReader looks like this:

IDataSource ds = DataSourceFactory.GetDataSource();
IDataCommand cmd = ds.GetCommand("ListUsers");
IDataReader dr = cmd.ExecuteReader();
ArrayList userInfos = new ArrayList();
while(dr.Read())
{
   userInfos.Add(new SampleState.UserInfo(dr.GetInt32(0)));
}
dr.Close();

Output parameters are retrieved using IDataCommand.Parameters[...].Value:

IDataSource ds = DataSourceFactory.GetDataSource();
IDataCommand cmd = ds.GetCommand("GetUserDetails");
cmd.Parameters["UserID"].Value = userID;
cmd.ExecuteNonQuery();
byte userAge = (byte)cmd.Parameters["UserAge"].Value);

Apart from the IDataSource.GetCommand method there is also the possibility to create commands ad hoc, which will not be cached, like this:

IDataSource ds = DataSourceFactory.GetDataSource();    

IDataCommand cmd = ds.CreateCommand("InsertUser", 
    "InsertUser", CommandType.StoredProcedure);    
cmd.Parameters.Add("Username", "@Username", 
    SqlDbType.NVarChar, 50, ParameterDirection.Input, 
    "user1");    
cmd.Parameters.Add("UserID", "@UserID", DbType.Int32, 
    ParameterDirection.Output);    

int recordsAffected = cmd.ExecuteNonQuery();

int userID = (int)cmd.Parameters["UserID"].Value;

This code however introduces dependencies to a certain data provider if the data provider XXXDbType enum is used (this is supported as well as the generic DbType) and to a certain command, you cannot change after that the CommandType/CommandText for example without modifying the code.

Additionally commands can be derived directly from the database. In this case the command name is used to find the stored procedure. All parameter names are accessible by skipping the prefix (for example "@") defined in the Framework.DataAccess.dll.config XML file. The search order for getting a command by name is first the CONFIG files (if any), then the database. An exception is thrown if the data command is not found in either of these.

Another option is to use DataSets as DTOs and DataAdapters for filling and updating the DataSets. There are two ways to handle DataAdapters in DAC2 - either you define them in the XML CONFIG files or you construct them programmatically in the code, both ways being similar to the DataCommands handling. There is a thin wrapper, called IDataSetAdapter, around the IDbDataAdapter with Fill and Update methods taking into consideration the DAC2 framework. The XML definition of the IDataSetAdapter is the following:

<dataOperations dataSource="DataSource1" 
           xmlns="Framework.DataAccess">
    <dataSetAdapters>    
      <dataSetAdapter name="Adapter1" populateCommands="true">
        <selectCommand>gt;
          <dataCommand name="Adapter1_SelectCommand" type="Text">
            <commandText>SELECT * FROM Users WHERE Username 
                         = @Username</commandText>
              <parameters>
                <param key="Username" name="@Username" 
                    type="NVarChar" size="50" 
                    direction="Input" />
              </parameters>
          </dataCommand>
        </selectCommand>
        <tableMappings>
          <tableMapping sourceTable="Table" 
                     dataSetTable="Users"/>
        </tableMappings>
      </dataSetAdapter>   
    </dataSetAdapters>    
</dataOperations>

You can use the above defined IDataSetAdapter in the following way:

IDataSource ds = DataSourceFactory.GetDataSource();
IDataSetAdapter ad = ds.GetDataSetAdapter("Adapter1");

ad.SelectCommand.Parameters["Username"].Value = "user1";

DataSet dataSet = new DataSet();
ad.Fill(dataSet);

Respectively you can update using an IDataSetAdapter:

IDataSource ds = DataSourceFactory.GetDataSource();
IDataSetAdapter ad = ds.GetDataSetAdapter("Adapter1");

ad.SelectCommand.Parameters["Username"].Value = "user1";

DataSet dataSet = new DataSet();
ad.Fill(dataSet);
dataSet.Tables[0].Rows[0][1] = "user1MODIFIED";
recordsAffected = ad.Update(dataSet);

The generation of the INSERT/UPDATE/DELETE commands by a CommandBuilder is invoked by setting the property populateCommands=true in the XML CONFIG or after manually calling IDataSetAdapter.PopulateCommands().

As I already mentioned, IDataSetAdapter can be programmatically created in the code without using the XML CONFIG files. Only the IDataCommands can be defined in the "localizable" XML files, while the data provider agnostic IDataSetAdapter can be instantiated in the code and its Select/Insert/Update/DeleteCommand properties can be set to the IDataCommands retrieved from the CONFIGs:

IDataSource ds = DataSourceFactory.GetDataSource();
IDataCommand cmd = ds.GetCommand("Command5");
cmd.Parameters["Username"].Value = "user1";

IDataSetAdapter ad = ds.CreateDataSetAdapter();
ad.SelectCommand = cmd;
ITableMapping dtm = ad.TableMappings.Add("st","dt");

ad.PopulateCommands();

DataSet dataSet = new DataSet();
int recordsAffected = ad.Fill(dataSet);

dataSet.Tables[0].Rows[0][1] = "user1MODIFIED";
recordsAffected = ad.Update(dataSet);

Defining IDataSetAdapters in the CONFIG files may add flexibility (if needed), although their place may not be there in general, as they are data provider agnostic (this is the difference between them and the IDataCommands).

One last point concerning the IDataSetAdapters is that they fill/update DataSets. Other adapters may fill/update Hashtables or even custom business objects (look at SQLMaps in the IBatis db layer). It is questionable, however, whether the definition of the retrieval/modification of custom business objects in CONFIG files (and the added complexity) really buys something ...

The Framework.DataAccess component should be used in the following way. The IDataSource should be retrieved in the data layer classes (a good place is in the constructor and stored in a private variable) and an IDataCommand should be retrieved from the IDataSource using IDataSource.GetCommand method. This way, all the database/provider/command specific code is external to the app and resides in CONFIG files; hence it can be easily replaced. Upon each modification of the CONFIG files an event is triggered and the internal cache of the DataSourceFactory is internally used by a certain IDataSource. DataCommandFactory is refilled with the fresh info without stopping the application.

Using the Framework.Transactions code

Framework.Transactions.dll is the other component in the package, together with the Framework.DataAccess.dll. All the credits for the idea about transaction contexts goes to David Goldstein, whom I would like to thank personally for sharing it with me!

TransactionContextFactory is the object servicing the requests for new TransactionContexts. A parameter of type TransactionAffinity is passed to it. The values of the TransactionAffinity enum are the following:

    public enum TransactionAffinity
    {
        //creates new transaction
        RequiresNew,
        //creates new transaction if no current transaction
        Required,
        //uses current transaction if present
        Supported,
        //does not use a transaction
        NotSupported
    }

TransactionAffinity values closely match the COM+ Transaction types (also in EnterpriseServices). So a TransactionContext is requested in the following way:

TransactionContext ctx = 
   TransactionContextFactory.GetContext(
          TransactionAffinity.RequiresNew);

The standard structure for using a TransactionContext is the following:

    ctx.Enter();
    //call bll/dal methods(db operations)
    ctx.VoteCommit() or ctx.VoteRollback()
    ctx.Exit();

If we add exception handling it turns to the following:

    TransactionContext ctx = 
        TransactionContextFactory.GetContext(
               TransactionAffinity.RequiresNew);

    try 
    {
        ctx.Enter();

        //call bll/dal methods(db operations)

        ctx.VoteCommit();
    }
    catch(Exception e) 
    {
        ctx.VoteRollback();
    }
    finally 
    {
        ctx.Exit();
    }

There is also a short way for managing Enter and Exit:

using(TransactionContext ctx = 
     TransactionContextFactory.EnterContext(
               TransactionAffinity.Supported)) 
    {
        //already entered

        ctx.VoteCommit() or exception for example
        //automatic exit upon leaving the scope ...
    }

The nice thing here is that a TransactionContext can be created and entered in any logical application layer, without any knowledge of the caller. This is an explicit way of declaring the needs of the code at any level. Several contexts may be used in the scope of a method as well as a context may span several methods. There are no limitations as the existing ones in COM+ Wink | ;)

The TransactionContext has also an IsolationLevel property of type TransactionIsolationLevel, which determines the isolation level of the opened transaction. Remember that only contexts which control transactions (i.e. Controlling Contexts - RequiresNew, Required) consider this property, others don't care about its value.

    public enum TransactionIsolationLevel
    {
        ReadUncommitted,
        ReadCommitted,
        RepeatableRead,
        Serializable
    }

The Framework.DataAcccess.dll contains several transaction handling implementations:

  • HomeGrownTransactionHandler (internal handling of connection and transactions),
  • SWCTransactionHandler (uses services without components in COM+ 1.5) and
  • ESTransactionHandler (uses ServicedComponents without requiring that the business classes inherit from ServicedComponent!).

The following section in Framework.DataAccess.dll determines which one should be used (a switch):

[Editor Note : Line Breaks added to avoid scrolling]

<transactionHandlingSettings xmlns="Framework.Transactions">
  <transactionHandler name="HomeGrown" 
    handlerType="Framework.DataAccess.TransactionHandling.
                                HomeGrownTransactionHandler, 
             Framework.DataAccess.TransactionHandling"
            />

</transactionHandlingSettings>

The Framework.Transactions component should be used in the following way. The Facade and BLL layers should reference it and use the TransactionContextFactory for instantiating TransactionContexts and should use the above described skeleton code to Enter/VoteCommit/VoteRollback/Exit the context. Framework.DataAccess.dll internally references Framework.Transactions.dll and subscribes to the events so that it can manage transactions and connections corresponding to the currently available transaction contexts. That's how transactions are controlled from the upper layers and the data layer picks them up and uses them in the data access.

CommandText runtime modification

You have certainly encountered the following problem - there is a requirement to query data (usually for reporting purposes) with a lot of optional filters. In this case if you use stored procedures, you have to add the filters as optional sproc parameters, which results in a very bad execution plan (at least in SQL Server). Of course the answer to this is dynamic SQL, which omits the optional parameters for which no value has been supplied. But how should this dynamic SQL be built? Inside the code? One possible way of dealing with this is the replaceByParamValues tag which can be used in the dataCommand definition. In this way it can be specified which parts of the CommandText can be omitted in case an input parameter has a certain value, or what additional text should be added (by replacing some placeholder). Here is an example:

<dataCommand name="SelectUsersCommand2" type="Text">
   <commandText>
     <![CDATA[
      SELECT 
      COUNT(*)
      FROM Users AS U1
      WHERE 1=1
      AND U1.UserID < @UserID]]>
   </commandText>
   <replaceByParamValues>
     <replaceByParamValue paramName="@UserID" 
                      paramValue="DBNull.Value">
       <oldString><![CDATA[AND U1.UserID < @UserID]]>
       </oldString>
       <newString></newString>
     </replaceByParamValue>
   </replaceByParamValues>
   <parameters>
     <param name="@UserID" type="Int" direction="Input" />
   </parameters>   
</dataCommand>

History

  • 13.01.2005
    • Code update. Added command timeout, possibility for creating DB objects instead of cloning them if the provider does not support cloning, as well as the new replaceByParamValues tags which allow for customizing the CommandText at runtime.
  • 23.09.2004
    • Reorganized CONFIG files (moved to App.config). Added ESTransactionHandler (uses COM+ and ServicedComponent with no need to inherit the business/service classes from ServicedComponent). Added ColumnMappings to DataSetAdapter definition.
  • 08.07.2004
    • Added the possibility to derive sprocs directly from the datasource (if there is no cmd found in the CONFIG file (or there is no CONFIG file) then the database will be queried (using DeriveCommand).
  • 14.04.2004
    • bugfixes (DataSetAdapter), no new features.
  • 20.10.2003
    • download update (HomeGrown Transaction implementation bugfix, added "parameterNamePrefix" and "key" CONFIG XML attributes so that the parameter names are also isolated from the concrete DB used, Framework.Configuration separated etc.).
  • 17.09.2003
    • download update (SWC bugfix, installinstructions.htm re-included).
  • 11.09.2003
    • Services Without Components transaction handling implementation (to be tested on Windows Server 2003). XML CONFIG schema changes (have a look at the code download). Commit and Rollback changed to VoteCommit and VoteRollback, the Exit() method now completes the transaction. ITransactionContext interface removed etc. impl. changes and fixes.
  • 16.08.2003
    • Restructuring in two components (Framework.DataAccess.dll and Framework.Transactions.dll). TransactionContexts implementation.
  • 27.06.2003
    • Fixes.
  • 10.06.2003
    • Added DataSet/Adapter support; changed API for manipulating Parameters( cmd.Parameters[...].Value instead of cmd.Set/GetParameterValue); DataCommandFactory -> DataOperationFactory, respectively CONFIG attribute definitions commandDir -> dataOperationsDir, commandFileMask -> dataOperationsFileMask; added SourceColumn and SourceVersion attributes to parameter definition; projects now converted to VS.NET 2003, .NET Framework 1.1.
  • 10.04.2003
    • Initial functional prototype version without proper exceptions and arguments checking/error handling.

Sample installation instructions

Read the InstallInstructions.htm in the root of the demo project.

Request

I would appreciate your remarks/corrections/suggestions.

Questions

  • Can someone test the SWCTransactionHandler implementation on Windows Server 2003 and tell me if it works? Wink | ;)

Thanks

I want to thank Dan Fox for his Data Factory example which gave me some of the ideas as well as David Goldstein who suggested the design of the explicit transaction contexts.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Deyan Petrov
Web Developer
Austria Austria
No Biography provided

Comments and Discussions

 
QuestionLicense of this article Pinmembertomailvenky23-Sep-13 23:25 
AnswerRe: License of this article PinmemberDeyan Petrov24-Sep-13 8:00 
QuestionLicense? PinmemberVeener15-Jun-12 7:10 
QuestionCould you give an configuation XML file example to visit MS-Access db? Pinmemberzllq28-Oct-07 22:04 
Generalfirebird Pinmembergruncic25-Apr-07 11:25 
GeneralWould this concept work with VS 2005 PinmemberMayureshSawardekar9-Mar-07 10:49 
QuestionRead App.config file error Pinmemberpenda806-Feb-07 1:37 
Generalusing this framework with existing DAL based on DAO pattern PinmemberTic123-Jan-07 21:01 
QuestionCan you tell me what the ER-DesignTool you use? PinmemberAdvanced27-Nov-06 4:51 
QuestiondbCommand = (IDbCommand)getCommandMethod.Invoke(_commandBuilder, null) Problem for Sybase Table. Pinmemberzhuomiao.ma@bankofamerica.com10-Oct-06 11:24 
Generalnunit.framework.dll is missed Pinmemberkiran.k22-Jun-06 10:29 
AnswerRe: nunit.framework.dll is missed Pinmemberzhuomiao.ma@bankofamerica.com13-Oct-06 5:51 
Generalcan't find the refrence of nunit PinmemberKENTDEAN17-Jun-06 1:05 
open the src file by VS.Net,but can't find the refrence of nunit.framework,where is the nunit?
 
Kent
GeneralPrecision and Scale PinmemberJuan Manuel Bastidas Bonilla11-May-06 10:35 
Generalis it work fine on .net 2.0 with sqlserver 2000 Pinmembernarendra bhabha9-May-06 22:34 
GeneralDeadlock problems PinmemberThiago Rafael25-Apr-06 11:52 
GeneralRe: Deadlock problems PinmemberDeyan Petrov25-Apr-06 22:13 
GeneralTimeout PinmemberThiago Rafael20-Mar-06 8:13 
GeneralRe: Timeout PinmemberDeyan Petrov24-Mar-06 6:52 
QuestionRe: Timeout PinmemberJuan Manuel Bastidas Bonilla31-Jul-07 15:11 
GeneralSQL Batches Pinmemberrhaps0dy_14-Mar-06 5:36 
GeneralRe: SQL Batches PinmemberDeyan Petrov24-Mar-06 6:48 
GeneralRe: SQL Batches Pinmemberversteijn9-May-06 4:31 
GeneralExcellent works on ASP.NET 2.0 & Oracle Pinmembercrabo27-Feb-06 0:06 
QuestionProblem with Clone and DB2 Provider Pinmemberpefr13-Jan-06 6:23 
AnswerRe: Problem with Clone and DB2 Provider PinmemberDeyan Petrov16-Jan-06 13:33 
GeneralRe: Problem with Clone and DB2 Provider Pinmemberpefr25-Jan-06 6:22 
General.Net2.0 Error PinmemberXp4ever6-Jan-06 5:29 
GeneralRe: .Net2.0 Error PinmemberXp4ever6-Jan-06 5:30 
GeneralRe: .Net2.0 Error Pinmembermaxotido24-May-07 5:58 
QuestionTransaction behavior with multiple Win32 Threads Pinmemberpefr21-Oct-05 12:07 
AnswerRe: Transaction behavior with multiple Win32 Threads PinmemberDeyan Petrov22-Oct-05 4:49 
QuestionWebApplication PinsussJoey Chömpff12-Oct-05 4:06 
AnswerRe: WebApplication PinmemberDeyan Petrov12-Oct-05 7:12 
GeneralConnection pool PinmemberThiago Rafael27-Sep-05 15:52 
GeneralRe: Connection pool PinmemberDeyan Petrov27-Sep-05 22:11 
GeneralParameter Problems Pinmembersion@igltd.co.uk4-Aug-05 3:14 
GeneralRe: Parameter Problems PinmemberDeyan Petrov4-Aug-05 3:37 
GeneralRe: Parameter Problems Pinmembersion@igltd.co.uk4-Aug-05 3:47 
GeneralRe: Parameter Problems PinmemberDeyan Petrov4-Aug-05 4:42 
GeneralRe: Parameter Problems Pinmembersion@igltd.co.uk4-Aug-05 5:03 
GeneralRe: Parameter Problems Pinmembersion@igltd.co.uk4-Aug-05 6:52 
GeneralRe: Parameter Problems PinmemberFZelle16-Aug-05 1:37 
GeneralRe: Parameter Problems Pinmembersion@igltd.co.uk16-Aug-05 3:25 
QuestionHow do I resolve &quot;Error loading transactionHandler from config&quot;? PinsussneuralSea821-Jul-05 1:06 
AnswerRe: How do I resolve &quot;Error loading transactionHandler from config&quot;? PinmemberDeyan Petrov27-Jul-05 22:47 
GeneralIDataSource interface issue PinmemberMariano Nicolas20-Jul-05 5:43 
GeneralRe: IDataSource interface issue PinmemberDeyan Petrov27-Jul-05 22:50 
GeneralUnhandled Exception PinsussneuralSea819-Jul-05 0:31 
Questioncan't use replayparams in odbc connection? Pinmembermargiex26-Jun-05 23:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141220.1 | Last Updated 13 Jan 2005
Article Copyright 2003 by Deyan Petrov
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid