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

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

By , 29 Oct 2002
Rate this:
Please Sign up or sign in to vote.

Sample Image - transactions.gif

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 Wink | ;) . 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. Wink | ;) 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.

Sample Image

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.

Sample Image

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 Wink | ;) .
  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.

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

About the Author

Deyan Petrov
Web Developer
Austria Austria
No Biography provided

Comments and Discussions

 
GeneralTransaction Help Me PinmemberAnhTin7-Mar-06 19:55 
Generalconnection data access Pinmembertrungvinh22-Jan-06 20:11 
Questionmore than one database ? Pinmemberibrahim yetkin11-Jul-05 22:30 
GeneralReturnDataParam Pinsussaspnet1005-Jun-04 15:57 
GeneralRe: ReturnDataParam Pinsussaspnet1005-Jun-04 17:18 
GeneralRe: ReturnDataParam Pinsussaspnet1005-Jun-04 17:23 
GeneralDistributed Transaction Pinsusskarthikeyan ganesan3-Feb-04 1:35 
GeneralRe: Distributed Transaction PinmemberDeyan Petrov3-Feb-04 1:51 
GeneralRe: Distributed Transaction Pinmemberkarthikeyan ganesan3-Feb-04 22:23 
GeneralNew article PinmemberDeyan Petrov17-Apr-03 3:08 
GeneralRe: New article Pinmemberkorasa24-Jun-04 7:20 
Generalnested transactions PinmemberBrynjar14-Apr-03 7:38 
GeneralRe: nested transactions PinmemberDeyan Petrov14-Apr-03 21:02 
GeneralRe: nested transactions PinmemberBrynjar14-Apr-03 23:00 
GeneralRe: nested transactions PinmemberBrynjar16-Apr-03 7:05 
GeneralRe: nested transactions PinmemberDeyan Petrov17-Apr-03 3:05 
GeneralRe: nested transactions PinmemberBrynjar17-Apr-03 3:18 
GeneralRe: nested transactions PinmemberDeyan Petrov17-Apr-03 3:20 
GeneralRe: nested transactions PinmemberBrynjar17-Apr-03 3:24 
GeneralRe: nested transactions PinmemberDeyan Petrov17-Apr-03 3:31 
GeneralRe: nested transactions Pinmemberersan30-May-03 4:07 
GeneralRe: nested transactions Pinmembertaoqx7-Aug-03 3:42 
GeneralRe: nested transactions Pinmemberersan17-Sep-03 4:36 
QuestionCan I not using stored procedure ?? PinmemberJack Hui4-Dec-02 1:39 
GeneralAdding LDAP as a datasource PinmemberAmadrias2-Nov-02 10:30 
GeneralRe: Adding LDAP as a datasource PinmemberDeyan Petrov2-Nov-02 23:28 
GeneralRe: Adding LDAP as a datasource PinmemberAmadrias3-Nov-02 2:41 
GeneralSetOnHold()/SetHoldComplete() PinsussAnonymous21-Oct-02 5:38 
GeneralRe: SetOnHold()/SetHoldComplete() PinmemberDeyan Petrov21-Oct-02 6:31 
GeneralRe: SetOnHold()/SetHoldComplete() PinmemberAnonymous21-Oct-02 14:43 
GeneralRe: SetOnHold()/SetHoldComplete() PinmemberDeyan Petrov22-Oct-02 0:13 
QuestionWhat about the performance PinsussCalvin Guo26-Sep-02 5:07 
AnswerRe: What about the performance PinmemberDeyan Petrov26-Sep-02 5:36 
GeneralRe: What about the performance PinsussCalvin Guo26-Sep-02 5:57 
GeneralRe: What about the performance PinmemberDeyan Petrov26-Sep-02 21:23 
Hi Calvin,
 
Well, the facade might appear just a pass-through. However it has it's important functions:
1) and most important - it decouples the UI and the business domain classes. When the business classes are used directly in the UI, then a high coupling is present and the design becomes fragile. You have to worry then about all the UI pages, which access the business object, that you want to change. There is this contradiction also - usually the UI is interested only in displaying and collecting some data, for which full-blown business objects are not needed. Another contradiction is between the Use-case orientation of the UI and the domain classes, which should be written for reusability ...
2) centralized security,auditing etc.
3) transaction handling per Use Case - at this level you know exactly when you should open and close a transaction
4) imagine a distributed scenario - the business tier is on a different machine, or you don't want to burden the client with deployment of business logic etc. Then some methods in the business objects (persisting data, accessing external service providers etc.) are allowed only in the context of the facade, not at the client. You can make your facade classes MarshalByRefObject and use them with Remoting (Duwamish). You can make even your facade classes ContextBoundObjects and use interception message sink etc.
 
Of course, if your project is small, then don't overengineer Wink | ;)
 
Best regards,
Deyan
GeneralRe: What about the performance PinsussCalvin Guo27-Sep-02 5:07 
GeneralRe: What about the performance PinsussAnonymous15-May-03 9:51 
QuestionWhere is the second part of this article? PinmemberAnonymous14-Jun-02 13:26 
AnswerRe: Where is the second part of this article? Pinmemberchadb18-Jun-02 9:55 
GeneralRe: Where is the second part of this article? PinmemberDeyan Petrov18-Jun-02 21:54 
GeneralRe: Where is the second part of this article? PinmemberAnonymous28-Jun-02 12:58 
GeneralFinally the article is updated PinmemberDeyan Petrov5-Apr-02 3:34 
GeneralRe: Finally the article is updated PinsussKoen24-Sep-02 21:21 
GeneralRe: Finally the article is updated PinmemberDeyan Petrov24-Sep-02 22:20 
GeneralDAL - Declarative Transactions PinmemberScott Smith3-Jan-02 9:27 
GeneralRe: DAL - Declarative Transactions PinmemberDeyan Petrov6-Jan-02 23:44 
GeneralRe: DAL - Declarative Transactions PinmemberAnonymous7-Feb-02 5:58 
GeneralRe: DAL - Declarative Transactions PinmemberDeyan Petrov10-Feb-02 21:31 
GeneralRe: DAL - Declarative Transactions PinmemberLairton Ballin5-Apr-02 3:23 
GeneralRe: DAL - Declarative Transactions PinmemberDeyan Petrov5-Apr-02 3:33 

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 | Mobile
Web01 | 2.8.140415.2 | Last Updated 30 Oct 2002
Article Copyright 2001 by Deyan Petrov
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid