Data Access Component - declarative transactions at the method level without EnterpriseServices, different data sources supported, Part 1

• Download demo project - 92 Kb

I assume the readers are acquainted with multi-tier application design, databases, XML, COM+, C# and .NET Framework.

Thanks to Sandy Place for his wonderful article on Declarative Transactions using ADO.NET and without Enterprise Services which inspired me for this data access component (DAC) in the first place, as well as to microsoft.public.dotnet newsgroup contributors!

Because the functionality of the component was extended quite a bit I shall split the article into two parts - the first one dealing with the methods of the common interface, implemented by MsSqlData and OleDbData, transaction support etc. and the second one dealing with the XmlData class and XQuery support.

Introduction

Unless I use COM+ when writing a transactional Business Logic Layer I can't effectively write encapsulated code. That means that I can't have 2 methods (belonging to different classes in different components), participating in the same DECLARATIVE transaction without needing to be very much aware of the transaction (either by sharing the same connection explicitly or by merging their functionality in a common stored procedure etc.). That means, that I can't write a simple Customer class and let's say Order class with the logic completely encapsulated in them and reusable in different situations/applications without modification, logic merging etc. etc. ...

COM+ is a solution to the problem in the VS6 world but has the following disadvantages:

• The overhead of context interception and two phase commit is too big and it is mainly oriented to a scenario in which more than one database is used transaction spanning multiple computers are coordinated.
• COM+ allows for marking WHOLE classes with a certain transaction attribute (for example RequiresNew), which does not allow to have 1 method in the class which does not run under a transaction and another one, which requires a transaction. This implies, that in order to separate the methods the developer should create 2 classes - for example EmployeeReader and EmployeeWriter, which is a pain 
• By default the transaction isolation level is Serializable in COM+ and if you want to override this setting you have to start all your stored procedures with SET TRANSACTION ISOLATION LEVEL ... The latest version of COM+ allows for administrative setting of the isolation level, but again at the class level. What if I want to mark my methods in 1 class with different isolation settings?
• In the .NET managed world using COM+ is absolutely possible, but taking into consideration the additional overhead of COM Interop etc. I don't find it's really applicable ;). Many people are expecting some kind of managed MTS ...

Another issue is that I want to centralise my data access code(don't like typing lots of identical data access code) and to be able afterwards to easily change the datasource (choose a different database or even XML files as a datasource) without rewriting a significant percentage of the application.

The Data Access Component (DAC), which I have written, supports the following:

• Declarative transaction at the method level - in 1 class certain methods are marked as transactional and execute in a certain transactional context, while the others execute without transaction. For example if you have a Employee class with method GetInfo() and Insert(), the first will not participate in a transaction (as it just selects data) while the second can be marked as transactional.
• Transactions can span several datasources. If the operation in 1 datasource fails, the other are rolled back automatically.
• Support for XML files as datasource and the XQuery language for querying.
• Uniform design of all data classes, common interfaces allowing for plugging new classes, abstraction of the underlying implementation.
• Transactionless Data Access - if no transaction attribute is specified for the method, then the DAC will short circuit and skip the transactional internal code => there will be almost no additional overhead (in comparison with directly using ADO.NET specific classes).

Using the DAC I am able to accomplish the following (otherwise impossible) taks:

• To use EXACTLY the same interface for different data sources, databases etc. Switch from one to another later, if needed.
• Write LESS code when accessing the data store.
• Specify transactions at the Method level with a similar functionality as in COM+.
• Span transactions across 2 or more different data sources.
• When I don't want to use transactions, there is almost no additional overhead, when I use transactions the additional overhead is the least possible.

In conclusion the DAC offers extended functionality compared with the transactional support offered by COM+ with much smaller overhead. The drawback is that it keeps the connection open for a little bit longer but only when the methods in the executing thread are marked transactional. That's life, nothing is for free. ;) If used appropriately, I think the DAC is the best tradeoff so far.

The client perspective

Let's start by a simple example of a transactional method of a Business Logic Layer class, using the DAC:

[TransactionContext(Transaction.Required, Isolation = Isolation.ReadUncommitted)]
public void Method1() {
    try {
        IData data = (IData)(new MsSqlData(_connectionString));
        DataParamCol dataParams = new DataParamCol(
            new InputDataParam("@Description", "TestTransactions.Method1")
            );
        data.Modify("Table4_Insert", dataParams);
        data = null;

        TransactionManager.SetComplete();
    }
    catch(Exception e) {
        TransactionManager.SetAbort();
        Console.WriteLine(e.Message);
    }
}

Method1() simply executes a stored procedure, which inserts a value in Table4, passing the value as an input parameter.

The first thing to note is the TransactionContext attribute. Two enumerations - Transaction and Isolation are used in it.

public enum Transaction { 
    Disabled = 0,// no transaction context will be created
    NotSupported,// transaction context will be created 
            // managing internally a connection, no transaction is opened though
    Required,// transaction context will be created if not present 
            // managing internally a connection and a transaction
    RequiresNew,// a new transaction context will be created 
            // managing internally a connection and a transaction
    Supported// an existing appropriate transaction context will be joined if present
}
    
public enum Isolation {
    Chaos = 0,        // not really used, copied from Data.IsolationLevel
    ReadCommitted,    
    ReadUncommitted,
    RepeatableRead,
    Serializable,
    Unspecified        // default, meaning not set
}

The TransactionContextAttribute is looked up when the data operation is executed. A transaction context is created behind the scenes and it manages internally a connection and in certain cases a transaction.

In case Transaction.Disabled or Transaction.NotSupported is used, then there is no need to specify the Isolation property as no Transaction will be opened on the connection. Transaction.Disabled is equivalent to skipping the TransactionContext attribute. If no TransactionContext attribute is specified (or Transaction.Disabled is specified) then it is not required that at the end of the current method TransactionManager.SetComplete or TransactionManager.SetAbort is called. In this case the Connection object is opened just before the data operation and closed immediately after its execution. If Transaction.Supported, Transaction.Required or Transaction.RequresNew is used, then the Isolation property is taken into consideration, the Connection and Transaction ADO.NET objects are managed internally and at the end of the method TransactionManager.SetComplete or TransactionManager.SetAbort MUST be called,  otherwise an open Connection will be left.

Next comes the instantiation of a specific data source class. The classes MsSqlData(connection to MS SQL Server), OleDbData(connection to other datasources) and XmlData(xml files as a datasource) are instantiated and upcasted to the common interface IData:

public interface IData {
    //---------------------------select, output parameters----------------------------
    #region Description
    /// <summary>
    /// Gets data ONLY in OUTPUT parameters. 
    /// Should be the fastest way.
    /// </summary>
    /// <param name="commandText">stored procedure for SELECT</param>
    /// <param name="dataParams">stored procedure parameters</param>
    /// <returns>RecordsAffected</returns>
    #endregion
    int Retrieve(string commandText, DataParamCol dataParams);

    //-----------------------------select, Untyped DataSet-----------------------------
    #region Description
    /// <summary>
    /// Creates and populates a DataSet with data. 
    /// The stored procedure does not accept parameters.
    /// </summary>
    /// <param name="commandText">stored procedure for SELECT</param>
    /// <param name="ds">Reference to a DataSet. Should not be instantiated.</param>
    /// <returns>RecordsAffected</returns>
    #endregion
    int Retrieve(string commandText, out DataSet ds);
    #region Description
    /// <summary>
    /// Creates and populates a DataSet with data. 
    /// The stored procedure does not accept parameters.
    /// The tables names in the DataSet are specified.
    /// </summary>
    /// <param name="commandText">stored procedure for SELECT</param>
    /// <param name="ds">Reference to a DataSet. Should not be instantiated.</param>
    /// <param name="tableNames">Array of table names to be created inside the 
    /// DataSet</param>
    /// <returns>RecordsAffected</returns>
    #endregion
    int Retrieve(string commandText, out DataSet ds, string[] tableNames);
    #region Description
    /// <summary>
    /// Creates and populates a DataSet with data. 
    /// The stored procedure accepts parameters.
    /// Default table names in the DataSet (Table, Table1, Table2....)
    /// </summary>
    /// <param name="commandText">stored procedure for SELECT</param>
    /// <param name="dataParams">stored procedure parameters</param>
    /// <param name="ds">Reference to a DataSet. Should not be instantiated.</param>
    /// <returns>RecordsAffected</returns>
    #endregion
    int Retrieve(string commandText, DataParamCol dataParams, out DataSet ds);
    #region Description
    /// <summary>
    /// Creates and populates a DataSet with data. 
    /// The stored procedure accepts parameters.
    /// The tables names in the DataSet are specified.
    /// </summary>
    /// <param name="commandText">stored procedure for SELECT</param>
    /// <param name="dataParams">stored procedure parameters</param>
    /// <param name="ds">Reference to a DataSet. Should not be instantiated.</param>
    /// <param name="tableNames">Array of table names to be created inside the 
    /// DataSet</param>
    /// <returns>RecordsAffected</returns>
    #endregion
    int Retrieve(string commandText, DataParamCol dataParams, out DataSet ds, 
                 string[] tableNames);

    //--------------------------select, Typed DataSet--------------------------
    #region Description
    /// <summary>
    /// Populates a Strongly Typed DataSet with data.
    /// The stored procedure does not accept parameters.
    /// </summary>
    /// <param name="commandText">stored procedure for SELECT</param>
    /// <param name="ds">Already instantiated Strongly Typed DataSet</param>
    /// <returns>RecordsAffected</returns>
    #endregion
    int Retrieve(string commandText, DataSet ds);
    #region Description
    /// <summary>
    /// Populates a Strongly Typed DataSet with data.
    /// The stored procedure accepts parameters.
    /// </summary>
    /// <param name="commandText">stored procedure for SELECT</param>
    /// <param name="dataParams">stored procedure parameters</param>
    /// <param name="ds">Already instantiated Strongly Typed DataSet</param>
    /// <returns>RecordsAffected</returns>
    #endregion
    int Retrieve(string commandText, DataParamCol dataParams, DataSet ds);

    //--------------------------select, IDataReader---------------------------
    #region Description
    /// <summary>
    /// Creates and Loads an IDataReader with data.
    /// The stored procedure does not accept parameters.
    /// </summary>
    /// <param name="commandText">stored procedure for SELECT</param>
    /// <param name="dr">Reference to an IDataReader. Should not be 
    /// instantiated.</param>
    /// <returns>RecordsAffected</returns>
    #endregion
    int Retrieve(string commandText, out IDataReader dr);
    #region Description
    /// <summary>
    /// Creates and Loads an IDataReader with data.
    /// The stored procedure accepts parameters.
    /// </summary>
    /// <param name="commandText">stored procedure for SELECT</param>
    /// <param name="dataParams">stored procedure parameters</param>
    /// <param name="dr">Reference to an IDataReader. Should not be 
    /// instantiated.</param>
    /// <returns>RecordsAffected</returns>
    #endregion
    int Retrieve(string commandText, DataParamCol dataParams, 
                 out IDataReader dr);  //input parameters

    //-----------------------------insert/update/delete---------------------------
    #region Description
    /// <summary>
    /// Modifies the Data Store 
    /// by simply executing an INSERT || UPDATE || DELETE stored procedure
    /// </summary>
    /// <param name="commandText">stored procedure for INSERT || UPDATE || 
    /// DELETE</param>
    /// <returns>RecordsAffected</returns>
    #endregion
    int Modify(string commandText);
    #region Description
    /// <summary>
    /// Modifies the Data Store 
    /// by executing an INSERT || UPDATE || DELETE stored procedure with 
    /// parameters
    /// </summary>
    /// <param name="commandText">stored procedure for INSERT || UPDATE || 
    /// DELETE</param>
    /// <param name="dataParams">stored procedure parameters</param>
    /// <returns>RecordsAffected</returns>
    #endregion
    int Modify(string commandText, DataParamCol dataParams);

    //-----------insert/update/delete DataSet using a DataAdapter---------------
    #region Description
    /// <summary>
    /// Modifies the Data Store 
    /// by using a DataAdapter on the DataSet
    /// Single DataAdapterAction allowed
    /// </summary>
    /// <param name="dataAdapterCommand">the custom DataAdapterCommand</param>
    /// <param name="ds">DataSet, containing Data Store changes</param>
    /// <param name="tableName">specific Table in the DataSet</param>
    #endregion
    void Modify(
        DataAdapterCommand dataAdapterCommand, 
        DataSet ds, 
        string tableName);
    #region Description
    /// <summary>
    /// Modifies the Data Store 
    /// by using a DataAdapter on the DataSet
    /// Multiple DataActions allowed
    /// </summary>
    /// <param name="dataAdapterCommands">Array of custom 
    /// DataAdapterCommands</param>
    /// <param name="ds">DataSet, containing Data Store changes</param>
    /// <param name="tableName">specific Table in the DataSet</param>
    #endregion
    void Modify(
        DataAdapterCommand[] dataAdapterCommands, 
        DataSet ds, 
        string tableName);
}

So the following instantiations are possible:

IData data = (IData)(new MsSqlData("Server=WIETEC29;Database=Test;" + 
                                   "User ID=sa;Password= ")); 
IData data = (IData)(new OleDbData(@"Provider= Microsoft.Jet.OLEDB.4.0;" + 
    @"Data Source= C:\TryProjects\DAL3\DB\db1.mdb;" + 
    @"User Id=admin;Password=;")); 

etc.

After that a DataParamCol is instantiated (if needed), which is a collection of DataParam objects. The latter can be instantiated and added to the collection in the constructor or later:

DataParamCol dataParams = new DataParamCol(
            new InputDataParam("@Description", "SomeStringValue"),
            new OutputDataParam("@Param2", SqlDbType.Int),
            new ReturnDataParam("@RETURN_STATUS")
                                              );

or

DataParamCol dataParams = new DataParamCol();
dataParams.Add(new InputDataParam("@Description", "SomeStringValue"));

The next step is to execute a command against the data source, for examlpe:

data.Modify("Table4_Insert", dataParams);

The data operation can be generally either Retrieve (with lots of overloads - returing output params, DataSets, IDataReaders) or Modify(simple INSERT/UPDATE/DELETE command or DataSet/DataAdapter INSERT/UPDATE/DELETE).

The last step is to vote for the transaction:

TransactionManager.SetComplete();

or

TransactionManager.SetAbort();

On particular occasions when you don't want to close the connection (when returing a IDataReader for example) the TransactionManager.SetOnHold(); and TransactionManager.SetHoldComplete(); are used.

The server implementation

Let's get to the server implementation details now.

There are three major client classes - MsSqlData, OleDbData and XmlData, all of them implementing the IData interface. First two inherit from the common abstract class DbData, which implements IData . Other classes like an OraData and so on can be elegantly added to the framework, when a managed provider for Oracle databases is available. The design allows for treating different data sources in the same way (by upcasting them upon creation to IData) and eventually easy data source substitution. The abstract DbData class contains common implementation for MsSqlData and OleDbData. In fact the puprose of the last two classes is to "produce" data provider specific ADO.NET classes.

All the database operations are meant to execute ONLY Stored Procedures. The advantages of using Stored procedures compared to inline Sql statements are numerous and won't be discussed in this artible.

The DataParam class is the base class for the different parameter classes - InputDataParam, BoundInputDataparam, OutputDataParam and ReturnDataParam. The constuctor of the DataParamCol class accepts variable number of base DataParam objects. Each specific DataParam class has several overloaded constructors.

The DataParamCol class represents a collection of data parameters. Allows for accessing of parameters by index and by key. Additionally the DataParamCol class features an Add() method for individually adding DataParam objects and a Clear() method.

The automatic transactions are supported through the TransactionManager class, which has only static methods. Besides the public SetComplete(), SetAbort(), SetOnHold() and SetHoldComplete() it has internal methods for locating the current transaction context, creating a new transaction context etc. For each separate transaction a new TransactionContext is created, which contains the connection and transaction objects and manages their lifetime internally.

At the heart of the automatic transactions is the CallContext .NET Framework class, which is something analogous to Thread Local Storage and contains data specific to the currently running thread and available to all methods in the call stack of the thread. TransactionContexts are created in the CallContext; they are used by the methods and destroyed after the last method in the called stack has finished its database operations. The TransactionContextCol class is a thin wrapper around CallContext and deals with the collection of TransactionContext objects stored in the CallContext:

internal class TransactionContextCol {
    public static void AddContext(TransactionContext TC) {
        CallContext.SetData(TC.Name, TC);
    }
    public static TransactionContext GetContext(string tcName) {
        return(CallContext.GetData(tcName) as TransactionContext);
    }
    public static void RemoveContext(string tcName) {
        CallContext.FreeNamedDataSlot(tcName);
    }
}

When a method with a unique combination of transaction/isolation attributes is executed, a new TransactionContext is created in the thread's CallContext. If this method executes another method with the same attributes, the second method executes in the same transaction context, otherwise another transaction context is created. When the the execution comes to a SetComplete() or SetAbort(), it checks the depth of the call stack and if it is equal to the depth of the method, which created the transaction context it checks further the Happy flag and commits or rollbacks the transaction accordingly. If the method is not marked with a transaction attribute then defaults apply (TransactionEnum.Disabled, IsolationEnum.Unspecified). Also the method should issue TrCtx.SetComplete() or TrCtx.SetAbort() before its return. This is similar to COM+ where you should call GetObjectContext.SetComplete() or GetObjectContext.SetAbort(). These two static methods of the TrCtx class trigger the process of transaction context handling - first checks if this is the ROOT method in the transaction context and if it is then determines whether to commit or rollback the whole transaction.

Running the sample project

In order to successfully compile and run the sample project you should provide the following:

1. Ensure you have .NET RTM installed ;).
2. Change the database connection strings and execute the SQL script in the project archive or make your own stored procedures/tables.
3. Compile the project and Run DACClient example console application...

The second part of this article will focus on the XmlData class.

Not being an experienced C# programmer I will appreciate comments and suggestions.

History

30 Oct 2002 - updated demo.