Data Access and Transaction Handling Framework

• Download source files and demo project - 134 Kb (VS.NET 2003/.NET 1.1.)

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+ ;)

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? ;)

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.