Click here to Skip to main content
13,348,514 members (81,695 online)
Click here to Skip to main content
Add your own
alternative version


86 bookmarked
Posted 22 Oct 2001

Declarative Transactions using ADO.NET and without Enterprise Services.

, 26 Oct 2001
Rate this:
Please Sign up or sign in to vote.
Sometimes it is nice to prototype up a simple database application. This code may help, by providing the automatic transactional model of COM+ in a non-COM+ environment. This example uses "Interception" to provide automatic transactioning support for non-COM+ classes.
<!-- Download Links -->

Summary and Motivation

I am developing a simple in-house database application that doesn’t have the requirements of a full-fledged enterprise-wide, high security, multi-threaded, Object pooled, JIT Activated, heterogeneous distributed transactional application (phew). It uses SQL Server only, as the backend. It doesn’t have to scale to a gajillion users. It is a rather typical in-house simple database development project. I wanted some of the goodies of COM+ such as declarative transactions, in other words I wanted to write simple readable code like:

sealed public class Employees : ContextBoundObject
    public EmployeeDataSet GetEmployeeById(intnID) // Returns an EmployeeDataSet
        return (EmployeeDataSet)Sql.RunSP("Employee_GetById", 
                new EmployeeDataSet(), 
                Sql.CreateParameterWithValue("@EmployeeID", nID));

But I didn’t want the “hassles” of generating Strong Names, COM+ Registration, Configuration, all the “Extra Steps” that go along with the COM+ enterprise model. I know the COM+ (enterprise) support under the .NET environment is incredibly easy to use and configure… But it still seemed like a bit of extra deployment work for such a simple project. I also was interested in learning more about the .NET environment and in this light, my motivation makes more sense as clearly I did not save any time. *laughing*. So enough babbling, what I came up with was “Automatic Transactional” support and addition “Connection” support for the SQL server managed provider (though the source provided can easily be tweaked to provide support for the other managed providers).

So you can write code as above instead of something like:

 sealed public class Employees
    public EmployeeDataSet GetEmployeeById(intnID) // Returns an EmployeeDataSet
        SqlConnection Connection
            = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["dsn"]);
            IDataParameter[] parameters =
                SQL.CreateParameterWithValue("@EmployeeID", nID)
            return (EmployeeDataSet)SQL.RunSP("Employee_GetById", parameters, 
                                              "Employees", new EmployeeDataSet(), 
              Connection = null;

This code is a little longer, a little harder to read, and is still missing the manual transactional support. The real benefits to declarative automatic transactions are the causal effects. I.e. A calls B which calls C and if C fails you want to abort the entire transaction. Or in the case where you don’t know in advance how your objects are going to be composed into transactional units.

How does it work?

The first step was to somehow partition the objects into transactions based upon declared attributes. So we could write the following annotation

to a class and at runtime an appropriate transactional “Context” would be created. This custom attribute would be a “Class” attribute that uses “Interception” to hook the methods of the class so that we can inject the transactional support into the class. I decided to also support the [AutoComplete] “Method” attribute as well. In addition I created a [DbConnection] attribute to help automate database connections. So let’s dive in:

Contexts and Class Attributes

I guess before we get to deep here, we must have a short interlude and talk about .NET “Contexts”. The .NET framework documentation defines a context as follows: A context is an ordered sequence of properties that define an environment for the objects resident inside it. Contexts get created during the activation process for objects that are configured to require certain automatic services such synchronization, transactions, just-in-time activation, security, and so on. Multiple objects can live inside a context. EEEEK! It sounds scary but in actual fact it is a real cool and an incredibly useful concept. Basically it is a way of grouping objects that share certain run-time properties together (not sure if that is any better *laughing*). Anyway, Contexts allow us to say all of these objects are going to share this transaction, or this class of objects always require a separate transaction. Contexts allow us to differentiate and therefore partition objects.

It is beyond the scope of this article to go into all the details about how to write custom attributes but essentially it involves deriving from one or the System.Attribute classes and providing your own functionality. The key to getting our [Transacation] attribute to work is deriving from the System.Runtime.Remoting.Contexts.ContextAttribute class as follows:

public enum TransactionOption // The transaction options.
    Disabled = 0,
public class TransactionAttribute : ContextAttribute
    private TransactionOption transactionOption;
    public TransactionAttribute(TransactionOption transactionOption)
        : base("Transaction")
        this.transactionOption = transactionOption; // Store the TransactionOption for later.

and implement the method bool IsContextOK(Context ctx, IConstructionCallMessage ctor). This method is called by the runtime to check to see whether the context for this object is compatible with the context passed to the method. So… this allows us to differentiate our objects based on our declared attribute.

public override boolIsContextOK(Context ctx, IConstructionCallMessage ctor)
    if(transactionOption == TransactionOption.RequiresNew) // This class always requires a new Context
        return false;

    TransactionProperty transactionProperty
            = ctx.GetProperty(Transaction.PropertyName) as TransactionProperty;
    if(transactionOption == TransactionOption.Required)
        // If there is no existing transaction context then create a new one
        if(transactionProperty == null)
            return false;
    return true; // The current context is fine!!

Ok… So far so good. We can differentiate and create contexts based upon our declared transactions. We must cover one more topic before we move on to hooking the methods of our class in order to inject the transactional support.

Context Properties

Contexts, like most other objects, have properties which define and hold their state. And the good news is that these properties can be user defined. For example I want to store a property on the context so that I can tell whether there is already a transaction associated with this context. In many ways it is equivalent to “<st1:place><st1:placename>Session <st1:placetype>State” in ASP.NET or any other kind of “Name-Value Dictionary” like lookup. The .NET framework provides a way for you to store your custom properties on a context and it does this by calling the method:


 virtual void
GetPropertiesForNewContext(IConstructionCallMessage ctorMsg)
of the System.ContextAttribute class. So our implementation is as follows:

public override void GetPropertiesForNewContext(IConstructionCallMessage ctor)

The only requirement is that the class implements theIContextProperty interface which fortunately System.ContextAttribute does.

Interception (Finally)

Well... there is a small part I left out. In order to make your classes participate in this “Context” business, you must mark your class as being a context bound class (which means it runs in a context). To do this you must derive your class from System.ContextBound. Ok. I think we got all the pieces now… The way it goes down when you create a new instance of your class (from the best that I can glean) is as follows:

  1. ContextAttribute.IsContextOK() method is called. If that returns true then the class is created in the context that is passed to it.
  2. If it returns false a new context is created.
  3. The system then calls GetPropertiesForNewContext() on the newly created context. This is where we can attach any new “Properties” to the context.
  4. For each of these properties it tests to see if you implemented the System.Runtime.Remoting.Contexts.IContributeObjectSink interface.
  5. This interface contains the method IMessageSink GetObjectSink(MarshalByRefObjectobj, IMessageSink nextSink) which allows you to chain in a custom IMessageSink interface which allows you to intercept method calls on the object.

Some code for an example, the implementation of GetObjectSinkis as follows:

public IMessageSink GetObjectSink(MarshalByRefObject o, IMessageSinkm_Next)
    TransactionAttribute transactionProperty = Transaction.ContextProperty;
    if(transactionProperty != null)
        return new DbConnectionMessageSink(this, 
            new TransactionMessageSink(transactionProperty, m_Next));

    return new DbConnectionMessageSink(this, m_Next);

One thing of interest is that the implementation always chains the Connection attribute first so that its method hook gets called before the transaction hook. There is no guarantee by putting the Connection attribute first before the Transaction attribute that it will get called first when you create a new instance. We need someway of controlling the order of things, otherwise we would try to create a transaction without a corresponding open connection. You always want (with blue being the interjected code):


To complete the Interception coup de gras is the actual implementation of the IMessageSink interface. When a method is called on an object in a context the method is redirected through the IMessageSink interface. This, as we have discovered, is actually a chain of IMessageSink implementations of which our hook is one of them. Smells kinda like Proxy/Stub for all you COM/Remoting addicts. Anywho, The main method on the IMessageSink interface we are interested in is

public IMessageSync ProcessMessage(IMessageimCall).
By implementing this method it allows to hook “Synchronous” method calls. The actual implementation of the TransactionAttribute message sink is as follows:

public class TransactionMessageSink : IMessageSink
    private IMessageSink          m_Next;
    private TransactionAttribute      m_TransactionAttribute;
    internal TransactionMessageSink(TransactionAttribute transactionProperty, IMessageSinkims)
        m_Next = ims;
        m_TransactionAttribute = transactionProperty;
    public IMessageSink NextSink
        get { returnm_Next; }
    public IMessageSync ProcessMessage(IMessageimCall)
        // Perform whatever preprocessing is needed on the message
        if (!(imCallisIMethodMessage))
        IMethodMessage imm = imCall as IMethodMessage;
        bool bAutoComplete = (Attribute.GetCustomAttribute(imm.MethodBase, 
                    typeof(AutoCompleteAttribute)) != null);
        SqlConnection Connection = (SqlConnection)DbConnectionAttribute.Connection;
        if(Connection == null)
            return m_Next.SyncProcessMessage(imCall);
        Console.WriteLine("[" + Thread.CurrentContext.ContextID + "]" + " Beginning Transaction...");
        SqlTransaction dbTransaction = m_TransactionAttribute.DbTransaction
             = Connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
        // Dispatch the call on the object
        IMessage imReturn = m_Next.SyncProcessMessage(imCall);
        if(dbTransaction != null)
            IMethodReturnMessage methodReturn = imReturn as IMethodReturnMessage;
            Exception exc = methodReturn.Exception;
            if (exc != null)
                  Console.WriteLine("[" + Thread.CurrentContext.ContextID + "]" + 
                                    " Commiting Transaction...");
                  Console.WriteLine("[" + Thread.CurrentContext.ContextID + "]" + 
                                    " Aborting Transaction...");
            dbTransaction = null;
    public IMessageCtrlAsync ProcessMessage(IMessage im, IMessageSink ims)
        // TODO: Find some way to also allow AsyncMessages to work (and ideally, be tracked)
        return m_Next.AsyncProcessMessage(im, ims);

Jeez louise that is a lot of code. A key part is the IMessage imReturn = m_Next.SyncProcessMessage(imCall); which is the call that forwards the method call to the object. The other parts is just wrapping the transaction around the call.

Putting it all Together

The title of the article said simple and we are yet to see anything that resembles simplicity. This is because the simple refers to the “Client” side (as it should be). So lets revisit the example at the top of the article and go over what we get for free:

sealed public class Employees : ContextBoundObject
    public EmployeeDataSet GetEmployeeById(intnID) // Returns an EmployeeDataSet
        return (EmployeeDataSet)Sql.RunSP("Employee_GetById", 
            "Employees", new EmployeeDataSet(), Sql.CreateParameterWithValue("@EmployeeID", nID));

We have a [DbConnection] attribute this provides our connection to and from the database. We have automatic transaction support with the [Transaction] attribute. We have the [AutoComplete] attribute that if all goes well during the method call, I.E, no exceptions, the transaction can be automatically committed. Lets look at the Sql.RunSP() method to see how the Connection and Transaction are accessed from within managed Sql Server.

static public DataSetRunSP(string procName, string tableName, DataSet dataSet, 
                           params IDataParameter[] parameters)
    SqlConnection dbConnection     = (SqlConnection)DbConnectionUtil.Connection;
        SqlTransaction dbTransaction   = (SqlTransaction)ContextUtil.DbTransaction;
        if((dbConnection == null) || (dbTransaction == null))
            throw new System.Exception("No Connection!");
        SqlDataAdapter DSCommand = new SqlDataAdapter();
        DSCommand.SelectCommand    = newSqlCommand(procName, dbConnection, dbTransaction);
        DSCommand.SelectCommand.CommandType   = CommandType.StoredProcedure;
        if(parameters != null)
            foreach ( SqlParameter parameter in parameters )
                      DSCommand.SelectCommand.Parameters.Add( parameter );
        DSCommand.Fill(dataSet, tableName);
        return dataSet;

As you can see there are two helper classes for accessing the current connection and the current transaction, DbConnectionUtil and ContextUtil respectively.

Implementation Notes

  • Currently supports Sql Server managed provider.
  • Supports only synchronous methods.
  • There is overhead associated with interception, contexts, …, However, the overhead and cost of connections and transactions probably outweigh this.
  • Didn’t have time to provide full documentation and sample.
  • Not fully debugged. This code should be used as a learning tool and not in production without full testing. (re-stating the obvious)

I hope this helps somebody out there, I would be interested in receiving any bug fixes, enhancements etc.


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

Sandy Place
Canada Canada
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralMy vote of 5 Pin
manoj kumar choubey4-Apr-12 23:35
membermanoj kumar choubey4-Apr-12 23:35 
GeneralMessage sink not being called Pin
Richard Bamford11-Oct-04 5:38
memberRichard Bamford11-Oct-04 5:38 
GeneralRe: Message sink not being called Pin
Lucas Ottoni13-Sep-06 9:22
memberLucas Ottoni13-Sep-06 9:22 
GeneralThread-Safety Pin
MDPConnect7-Jun-04 7:43
memberMDPConnect7-Jun-04 7:43 
Generaltransactions in (calling 2 functions) Pin
chandler sue21-May-04 8:27
memberchandler sue21-May-04 8:27 
GeneralDistributed Transaction Pin
karthikeyan ganesan3-Feb-04 2:29
susskarthikeyan ganesan3-Feb-04 2:29 
GeneralRe: Distributed Transaction Pin
Roberto7813-Feb-04 10:07
memberRoberto7813-Feb-04 10:07 
GeneralHelp : How to create a datareader/dataadapter that doesnt open a transaction. Pin
Ddl_Smurf29-Aug-03 3:19
memberDdl_Smurf29-Aug-03 3:19 
GeneralContextBoundObject can not work for this purpose Pin
cklein15-Jul-03 6:42
membercklein15-Jul-03 6:42 
GeneralRe: ContextBoundObject can not work for this purpose Pin
Sandy Place15-Jul-03 8:18
sussSandy Place15-Jul-03 8:18 
GeneralRe: ContextBoundObject can not work for this purpose Pin
cklein15-Jul-03 9:29
membercklein15-Jul-03 9:29 
GeneralPossible Bug Pin
cklein14-Jul-03 6:36
membercklein14-Jul-03 6:36 
I have seen the following code
<br />
public class Transaction<br />
{<br />
	internal static TransactionAttribute ContextProperty <br />
	{<br />
		get { return Thread.CurrentContext.GetProperty(Transaction.PropertyName) as TransactionAttribute; }<br />
	} <br />
}<br />

I think there could be a problem with this line of code:
<br />
return Thread.CurrentContext.GetProperty(Transaction.PropertyName)<br />

In ASP.NET, they are using the threadpool. The same thread could be picked up by another request. That means the TransactionAttribute could be picked up from the previous request, which could possiblly cause problems. How serious this? I didn't look into it deeply.

In my opinion, in ASP.NET, we should never depend on user set thread related properties, since they could be set by previous request.

GeneralNice but should be use with care Pin
MQA10-Jun-03 7:36
memberMQA10-Jun-03 7:36 
GeneralRe: Nice but should be use with care Pin
Roberto7813-Feb-04 9:57
memberRoberto7813-Feb-04 9:57 
GeneralNice piece of code! Pin
Ramon Smits13-Aug-02 2:26
memberRamon Smits13-Aug-02 2:26 
GeneralGreat!!!! Thanks, man! I will try to introduce method-level transactions... Pin
Deyan Petrov3-Dec-01 9:02
memberDeyan Petrov3-Dec-01 9:02 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
Sandy Place3-Dec-01 13:14
memberSandy Place3-Dec-01 13:14 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
dJoker10-Dec-01 6:12
memberdJoker10-Dec-01 6:12 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
CliveM16-Dec-02 9:42
memberCliveM16-Dec-02 9:42 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
Anonymous18-Dec-02 8:24
sussAnonymous18-Dec-02 8:24 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
Yazid12-Jan-03 10:19
memberYazid12-Jan-03 10:19 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
Deyan Petrov19-Dec-01 11:51
memberDeyan Petrov19-Dec-01 11:51 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
Sandy Place3-Jan-02 12:56
memberSandy Place3-Jan-02 12:56 
GeneralOmitted spaces and dots make this very difficult to follow Pin
John Crim30-Oct-01 20:45
memberJohn Crim30-Oct-01 20:45 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180111.1 | Last Updated 27 Oct 2001
Article Copyright 2001 by Sandy Place
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid