Declarative Transactions using ADO.NET and without Enterprise Services.

• Download source files - 13 Kb

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:

[DbConnection]
[Transaction(TransactionOption.Required)]
sealed public class Employees : ContextBoundObject
{
    [AutoComplete]
    public EmployeeDataSet GetEmployeeById(intnID) // Returns an EmployeeDataSet

    {
        return (EmployeeDataSet)Sql.RunSP("Employee_GetById", 
                "Employees", 
                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"]);
        Connection.Open();
        try
        {
            IDataParameter[] parameters =
            {
                SQL.CreateParameterWithValue("@EmployeeID", nID)
            };
            
            return (EmployeeDataSet)SQL.RunSP("Employee_GetById", parameters, 
                                              "Employees", new EmployeeDataSet(), 
                                              Connection);
        }
        finally
        {
              Connection.Dispose();
              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 [Transaction(TransactionOption.Required)] 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,
    NotSupported,
    Required,
    RequiresNew,
    Supported
}
 
[AttributeUsage(AttributeTargets.Class)]
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 “Session 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:

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

public override void GetPropertiesForNewContext(IConstructionCallMessage ctor)
{
    ctor.ContextProperties.Add(this);
}

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):

Connection.OpenConnection()
Transaction.Begin()
MethodCallGoesHere()
Transaction.Commit()
Connection.CloseConnection()

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))
            returnm_Next.SyncProcessMessage(imCall);
 
        IMethodMessage imm = imCall as IMethodMessage;
        bool bAutoComplete = (Attribute.GetCustomAttribute(imm.MethodBase, 
                    typeof(AutoCompleteAttribute)) != null);
        m_TransactionAttribute.DisableCommit();
 
        SqlConnection Connection = (SqlConnection)DbConnectionAttribute.Connection;
        if(Connection == null)
            return m_Next.SyncProcessMessage(imCall);
 
#if DEBUGGING_TRXS
        Console.WriteLine("[" + Thread.CurrentContext.ContextID + "]" + " Beginning Transaction...");
#endif
        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)
            {
                m_TransactionAttribute.SetAbort();
            }
            else
            {
                if(bAutoComplete)
                      m_TransactionAttribute.SetComplete();
            }
 
            if(!m_TransactionAttribute.Done)
                      m_TransactionAttribute.SetAbort();
 
            if(m_TransactionAttribute.ContextConsistent)
            {
#if DEBUGGING_TRXS
                  Console.WriteLine("[" + Thread.CurrentContext.ContextID + "]" + 
                                    " Commiting Transaction...");
#endif
                  dbTransaction.Commit();
            }
            else
            {
#if DEBUGGING_TRXS
                  Console.WriteLine("[" + Thread.CurrentContext.ContextID + "]" + 
                                    " Aborting Transaction...");
#endif
                  dbTransaction.Rollback();
            }
 
            dbTransaction.Dispose();
            dbTransaction = null;
        }
 
        returnimReturn;
    } 
 
    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:

[DbConnection]
[Transaction(TransactionOption.Required)]
sealed public class Employees : ContextBoundObject
{
    [AutoComplete]
    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.