Click here to Skip to main content
15,861,168 members
Articles / Programming Languages / C#

Declarative Transactions using ADO.NET and without Enterprise Services

Rate me:
Please Sign up or sign in to vote.
4.95/5 (20 votes)
26 Oct 20018 min read 215.8K   1.5K   87   24
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.

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

C#
[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:

C#
 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:

C#
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.

C#
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:

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

C#
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 GetObjectSink is as follows:

C#
...
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 some way 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):

C#
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. Anyhow, 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:

C#
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 + "]" + 
                                    " Committing 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 part 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 let's revisit the example at the top of the article and go over what we get for free:

C#
[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 which 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. Let's look at the Sql.RunSP() method to see how the Connection and Transaction are accessed from within managed SQL Server.

C#
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.

History

  • 21st October, 2001: Initial version

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.


Written By
Canada Canada
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey4-Apr-12 22:35
professionalManoj Kumar Choubey4-Apr-12 22:35 
GeneralMessage sink not being called Pin
Richard Bamford11-Oct-04 4:38
Richard Bamford11-Oct-04 4:38 
GeneralRe: Message sink not being called Pin
Lucas Ottoni13-Sep-06 8:22
Lucas Ottoni13-Sep-06 8:22 
GeneralThread-Safety Pin
Toro from SO7-Jun-04 6:43
professionalToro from SO7-Jun-04 6:43 
Generaltransactions in asp.net (calling 2 functions) Pin
chandler sue21-May-04 7:27
chandler sue21-May-04 7:27 
GeneralDistributed Transaction Pin
Karthikeyan Ganesan3-Feb-04 1:29
Karthikeyan Ganesan3-Feb-04 1:29 
GeneralRe: Distributed Transaction Pin
Roberto7813-Feb-04 9:07
Roberto7813-Feb-04 9:07 
I think it is possible. But is very difficult. I'm studying about a remoting solution (channel or interceptor) to support two-phase commit.
If you know Java can see the JTA interfaces (Java Transaction Api) thet implement the X/Open DTP (The same standard used by MS DTC).
I'm trying to build a Transactional Framework without using Net class on EnterpriseServices framework (COM+) and without SWC technology of Win2k3.
If you have find something else please write to me!
GeneralHelp : How to create a datareader/dataadapter that doesnt open a transaction. Pin
Ddl_Smurf29-Aug-03 2:19
Ddl_Smurf29-Aug-03 2:19 
GeneralContextBoundObject can not work for this purpose Pin
cklein15-Jul-03 5:42
cklein15-Jul-03 5:42 
GeneralRe: ContextBoundObject can not work for this purpose Pin
Sandy Place15-Jul-03 7:18
Sandy Place15-Jul-03 7:18 
GeneralRe: ContextBoundObject can not work for this purpose Pin
cklein15-Jul-03 8:29
cklein15-Jul-03 8:29 
GeneralPossible Bug Pin
cklein14-Jul-03 5:36
cklein14-Jul-03 5:36 
GeneralNice but should be use with care Pin
MQA10-Jun-03 6:36
MQA10-Jun-03 6:36 
GeneralRe: Nice but should be use with care Pin
Roberto7813-Feb-04 8:57
Roberto7813-Feb-04 8:57 
GeneralNice piece of code! Pin
Ramon Smits13-Aug-02 1:26
Ramon Smits13-Aug-02 1:26 
GeneralGreat!!!! Thanks, man! I will try to introduce method-level transactions... Pin
3-Dec-01 8:02
suss3-Dec-01 8:02 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
3-Dec-01 12:14
suss3-Dec-01 12:14 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
10-Dec-01 5:12
suss10-Dec-01 5:12 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
CliveM16-Dec-02 8:42
CliveM16-Dec-02 8:42 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
Anonymous18-Dec-02 7:24
Anonymous18-Dec-02 7:24 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
Yazid12-Jan-03 9:19
Yazid12-Jan-03 9:19 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
Deyan Petrov19-Dec-01 10:51
Deyan Petrov19-Dec-01 10:51 
GeneralRe: Great!!!! Thanks, man! I will try to introduce method-level transactions... Pin
3-Jan-02 11:56
suss3-Jan-02 11:56 
GeneralOmitted spaces and dots make this very difficult to follow Pin
John Crim30-Oct-01 19:45
John Crim30-Oct-01 19: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.