Transaction Management With Component-Based Object Extender

• Download source code - 354 KB

Introduction

Component-Based Object Extender (CBO Extender) is an object extensibility framework. It works with interfaces and extends objects directly. CBO Extender can be used to extend any interface methods including interfaces from third party, from .NET Framework, or interfaces defined by you. It is a general-purpose tool for adding functionality to objects by attaching behaviors to interface methods of the objects.

In this article, I discuss how the CBO Extender is used to do transaction management. First, a set of operations (commands) are defined in several components. Then, aspects for transaction management are defined. Last, the aspects are attached to objects of the components to form a transaction in an application.

Background

A transaction can be defined as an indivisible unit of work comprised of several operations, all or none of which must be performed in order to preserve data integrity. An operation itself is not aware what other operations are involved in a transaction. A transaction is defined after individual operations have been designed. Based on business requirements, the individual operations may or may not be put in a transaction. When an operation is not in an explicitly specified transaction, the operation behaves like it is in its own transaction. When an operation is put in an explicitly specified transaction, all or none of the operations in the transaction are performed.

CBO Extender has been used to add behaviors, like logging, security checking, sorting, etc., to objects. You can find how it is done in the article Application Development With Component-Based Object Extender. In this article, I discuss how the CBO Extender is used to do transaction management. Here, we try to write an application to insert a record into the [Sales].[SalesOrderHeader] and [Sales].[SalesOrderDetail] tables of the AdventureWorks database shipped with Microsoft SQL Server. Two operations are defined: one for inserting some data into [Sales].[SalesOrderHeader], the other for inserting additional data into [Sales].[SalesOrderDetail]. The data for the second operation depends on the data for the first operation. To preserve data integrity, the two operations are put in one transaction.

We start to design the application to insert the two tables without considering the transaction. Then, we add the transaction capabilities by defining some aspects and modifying the application. Two different approaches are used to add transaction capabilities to the application: using CreateProxy and using AOPContainer.

Note: CBO Extender has been uploaded as a NuGet package. You can download and add it to your project from Visual Studio 2010. You can also download the source code, latest updates, and more examples here (http://centurytechs.com/products/CBOExtender.zip).

To install CBOExtender to your project from Visual Studio 2010, click Tools->Library Package Manager->Manage NuGet Packages... to open the Manage NuGet Packages dialog. Type in CBOExtender as shown.

You probably need to install NuGet for Visual Studio 2010 before you can download the package.

Using the Code

For ADO.NET with SQL Server, an operation is a method of SqlCommand. An interface ISqlOperation, which has a single property to present SQL Server operations, is defined as follows.

public interface ISqlOperation
{
    SqlCommand Command { get; set; }
}

The components Order and OrderDetail correspond to a record in the table [Sales].[SalesOrderHeader] and a record in the table [Sales].[SalesOrderDetail], respectively, and are defined as follows:

public interface IOrder
{
    int OrderID { get; set; }
    int CustomerID { get; set; }
    DateTime DueDate { get; set; }
    string AccountNumber { get; set; }
    int ContactID { get; set; }
    int BillToAddressID { get; set; }
    int ShipToAddressID { get; set; }
    int ShipMethodID { get; set; }
    double SubTotal { get; set; }
    double TaxAmt { get; set; }

    int InsertOrder();
}

public class Order : IOrder, ISqlOperation
{
    public int OrderID { get; set; }
    public int CustomerID { get; set; }
    public DateTime DueDate { get; set; }
    public string AccountNumber { get; set; }
    public int ContactID { get; set; }
    public int BillToAddressID { get; set; }
    public int ShipToAddressID { get; set; }
    public int ShipMethodID { get; set; }
    public double SubTotal { get; set; }
    public double TaxAmt { get; set; }

    private SqlCommand commd;
    public SqlCommand Command 
    {
        get { return commd;}
        set { commd = value; }
    }

    public int InsertOrder()
    {
        string sqlStr = @"INSERT [Sales].[SalesOrderHeader] ([CustomerID], " + 
           @"[DueDate], [AccountNumber], [ContactID], [BillToAddressID], " + 
           @"[ShipToAddressID], [ShipMethodID], [SubTotal], [TaxAmt]) values " + 
           @"(@CustomerID, @DueDate, @AccountNumber, @ContactID, @BillToAddressID, " + 
           @"@ShipToAddressID, @ShipMethodID, @SubTotal, @TaxAmt); " + 
           @"SET @scopeId = SCOPE_IDENTITY()";

        commd.CommandText = sqlStr;
        commd.CommandType = CommandType.Text;

        SqlParameter CustomerIDParameter = new SqlParameter("@CustomerID", SqlDbType.Int);
        CustomerIDParameter.Direction = ParameterDirection.Input;
        CustomerIDParameter.Value = CustomerID;
        commd.Parameters.Add(CustomerIDParameter);

        SqlParameter DueDateParameter = new SqlParameter("@DueDate", SqlDbType.DateTime);
        DueDateParameter.Direction = ParameterDirection.Input;
        DueDateParameter.Value = DueDate;
        commd.Parameters.Add(DueDateParameter);

        SqlParameter AccountNumberParameter = new SqlParameter("@AccountNumber", SqlDbType.Text);
        AccountNumberParameter.Direction = ParameterDirection.Input;
        AccountNumberParameter.Value = AccountNumber;
        commd.Parameters.Add(AccountNumberParameter);

        SqlParameter ContactIDParameter = new SqlParameter("@ContactID", SqlDbType.Int);
        ContactIDParameter.Direction = ParameterDirection.Input;
        ContactIDParameter.Value = ContactID;
        commd.Parameters.Add(ContactIDParameter);

        SqlParameter BillToAddressIDParameter = new SqlParameter("@BillToAddressID", SqlDbType.Int);
        BillToAddressIDParameter.Direction = ParameterDirection.Input;
        BillToAddressIDParameter.Value = BillToAddressID;
        commd.Parameters.Add(BillToAddressIDParameter);

        SqlParameter ShipToAddressIDParameter = new SqlParameter("@ShipToAddressID", SqlDbType.Int);
        ShipToAddressIDParameter.Direction = ParameterDirection.Input;
        ShipToAddressIDParameter.Value = ShipToAddressID;
        commd.Parameters.Add(ShipToAddressIDParameter);

        SqlParameter ShipMethodIDParameter = new SqlParameter("@ShipMethodID", SqlDbType.Int);
        ShipMethodIDParameter.Direction = ParameterDirection.Input;
        ShipMethodIDParameter.Value = ShipMethodID;
        commd.Parameters.Add(ShipMethodIDParameter);

        SqlParameter SubTotalParameter = new SqlParameter("@SubTotal", SqlDbType.Float);
        SubTotalParameter.Direction = ParameterDirection.Input;
        SubTotalParameter.Value = SubTotal;
        commd.Parameters.Add(SubTotalParameter);

        SqlParameter TaxAmtParameter = new SqlParameter("@TaxAmt", SqlDbType.Int);
        TaxAmtParameter.Direction = ParameterDirection.Input;
        TaxAmtParameter.Value = TaxAmt;
        commd.Parameters.Add(TaxAmtParameter);

        SqlParameter scopeIDParameter = new SqlParameter("@scopeId", SqlDbType.Int);
        scopeIDParameter.Direction = ParameterDirection.Output;
        commd.Parameters.Add(scopeIDParameter);

        int i = commd.ExecuteNonQuery();

        OrderID = (int)scopeIDParameter.Value;

        return i;
    }
}

The IOrder interface defines a set of properties corresponding to the fields in the table [Sales].[SalesOrderHeader] and a method InsertOrder. Order implements both the IOrder interface and the ISqlOperation interface. Inside InsertOrder of Order, a record is inserted into the table [Sales].[SalesOrderHeader].

public interface IOrderDetail
{
    int SalesOrderID { get; set; }
    int OrderQty { get; set; }
    int ProductID { get; set; }
    int SpecialOfferID { get; set; }
    double UnitPrice { get; set; }

    int InsertOrderDetail();
}
    
public class OrderDetail : IOrderDetail, ISqlOperation
{
    public int SalesOrderID { get; set; }
    public int OrderQty { get; set; }
    public int ProductID { get; set; }
    public int SpecialOfferID { get; set; }
    public double UnitPrice { get; set; }

    private SqlCommand commd;
    public SqlCommand Command
    {
        get { return commd; }
        set { commd = value; }
    }

    public int InsertOrderDetail()
    {
        string sqlStr = @"INSERT INTO [Sales].[SalesOrderDetail] ([SalesOrderID], " + 
          @"[OrderQty], [ProductID], [SpecialOfferID], [UnitPrice]) " + 
          @"values (@orderID, @OrderQty, @ProductID, @SpecialOfferID, @UnitPrice)";

        commd.CommandText = sqlStr;
        commd.CommandType = CommandType.Text;

        SqlParameter orderIDParameter = new SqlParameter("@orderID", SqlDbType.Int);
        orderIDParameter.Direction = ParameterDirection.Input;
        orderIDParameter.Value = SalesOrderID;
        commd.Parameters.Add(orderIDParameter);

        SqlParameter OrderQtyParameter = new SqlParameter("@OrderQty", SqlDbType.Int);
        OrderQtyParameter.Direction = ParameterDirection.Input;
        OrderQtyParameter.Value = OrderQty;
        commd.Parameters.Add(OrderQtyParameter);

        SqlParameter ProductIDParameter = new SqlParameter("@ProductID", SqlDbType.Int);
        ProductIDParameter.Direction = ParameterDirection.Input;
        ProductIDParameter.Value = ProductID;
        commd.Parameters.Add(ProductIDParameter);

        SqlParameter SpecialOfferIDParameter = new SqlParameter("@SpecialOfferID", SqlDbType.Int);
        SpecialOfferIDParameter.Direction = ParameterDirection.Input;
        SpecialOfferIDParameter.Value = SpecialOfferID;
        commd.Parameters.Add(SpecialOfferIDParameter);

        SqlParameter UnitPriceParameter = new SqlParameter("@UnitPrice", SqlDbType.Float);
        UnitPriceParameter.Direction = ParameterDirection.Input;
        UnitPriceParameter.Value = UnitPrice;
        commd.Parameters.Add(UnitPriceParameter);

        return commd.ExecuteNonQuery();
    }
}

The IOrderDetail interface defines a set of properties corresponding to the fields in the table [Sales].[SalesOrderDetail] and a method InsertOrderDetail. OrderDetail implements both IOrderDetail interface and ISqlOperation interface. Inside InsertOrderDetail of OrderDetail, a record is inserted into the table [Sales].[SalesOrderDetail].

With the Order and the OrderDetail defined, we write a console application to use them for inserting some data into both tables. The application code is listed as follows.

static void Main(string[] args)
{
    string connStr = "Integrated Security=true;" + 
       "Data Source=(local);Initial Catalog=AdventureWorks";
    using(IDbConnection conn = new SqlConnection(connStr))
    {
        IDbTransaction transaction = null;

        try
        {
            conn.Open();

            IOrder o = new Order();
            o.CustomerID = 18759;
            o.DueDate = DateTime.Now.AddDays(1);
            o.AccountNumber = "10-4030-018759";
            o.ContactID = 4189;
            o.BillToAddressID = 14024;
            o.ShipToAddressID = 14024;
            o.ShipMethodID = 1;
            o.SubTotal = 174.20;
            o.TaxAmt = 10;
            ((ISqlOperation)o).Command = new SqlCommand();
            ((ISqlOperation)o).Command.Connection = (SqlConnection)conn;

            int iStatus;
            iStatus = o.InsertOrder();

            //throw new Exception();

            IOrderDetail od = new OrderDetail();

            od.SalesOrderID = o.OrderID;
            od.OrderQty = 5;
            od.ProductID = 708;
            od.SpecialOfferID = 1;
            od.UnitPrice = 28.84;
            ((ISqlOperation)od).Command = new SqlCommand();
            ((ISqlOperation)od).Command.Connection = (SqlConnection)conn;
            iStatus = od.InsertOrderDetail();
        }
        catch (Exception ex)
        {
            if (transaction != null)
                transaction.Rollback();
        }
        finally
        {
            conn.Close();
        }
    }
}

When executing, in each of the tables [Sales].[SalesOrderHeader] and [Sales].[SalesOrderDetail] is inserted a record. If we uncomment the line //throw new Exception(); and execute the code again, in the first table is inserted a record but in the second table not. Not good, we have a data integrity issue with only partial data in the database!

So, we need to put the both operations in a transaction so that in case either of them fails, the database will rollback to the original state.

In the following sections, we define some aspects for transaction management first. Then, two different approaches are used to add transaction capabilities to the application. They are: use CreateProxy and use AOPContainer, respectively.

Define Transaction Aspects

Before continuing, we need to install CBO Extender as a NuGet package to the project by following the instructions described in the Background section. Now, let's define some aspects for transaction management. We need an aspect for objects to join a transaction. We also need an aspect for an object to commit the transaction. These aspects are defined as follows.

class AppConcerns
{
    public static void JoinSqlTransaction(AspectContext ctx, object[] parameters)
    {
        Exception exInner = null;

        try
        {
            if (parameters != null && parameters[0] is IDbTransaction)
            {
                (ctx.Target as ISqlOperation).Command.Transaction = parameters[0] as SqlTransaction;
                return;
            }
        }
        catch (Exception ex)
        {
            exInner = ex;
        }
    }

    public static void CommitSqlTransaction(AspectContext ctx, object[] parameters)
    {
        Exception exInner = null;

        try
        {
            (ctx.Target as ISqlOperation).Command.Transaction.Commit();
            return;
        }
        catch (Exception ex)
        {
            exInner = ex;
        }

        throw new Exception("Failed to commit!", exInner);
    }
}

The JoinSqlTransaction aspect registers a command to a transaction that is passed in as the first element of the parameters argument. It can only be attached to targets that implement the ISqlOperation interface. This aspect can be applied to multiple objects to form one transaction by using it as a preprocessing aspect for each of the objects.

The CommitSqlTransaction aspect commits a transaction. It can only be attached to a target that implements the ISqlOperation interface. Only the last object in a transaction should use it as its postprocessing aspect.

Use CreateProxy

With the aspect defined, the CreateProxy<T> method is used to attach them to the objects. The console application now looks like:

static void Main(string[] args)
{
    string connStr = "Integrated Security=true;" + 
           "Data Source=(local);Initial Catalog=AdventureWorks";
    using (IDbConnection conn = new SqlConnection(connStr))
    {
        IDbTransaction transaction = null;

        try
        {
            conn.Open();
            transaction = conn.BeginTransaction();

            IOrder o = new Order();
            o.CustomerID = 18759;
            o.DueDate = DateTime.Now.AddDays(1);
            o.AccountNumber = "10-4030-018759";
            o.ContactID = 4189;
            o.BillToAddressID = 14024;
            o.ShipToAddressID = 14024;
            o.ShipMethodID = 1;
            o.SubTotal = 174.20;
            o.TaxAmt = 10;
            ((ISqlOperation)o).Command = new SqlCommand();
            ((ISqlOperation)o).Command.Connection = (SqlConnection)conn;

            int iStatus;
            iStatus = ObjectProxyFactory.CreateProxy<IOrder>(
                o,
                new string[] { "InsertOrder" },
                new Decoration(AppConcerns.JoinSqlTransaction, new object[] { transaction }),
                null
            ).InsertOrder();

            //throw new Exception();

            IOrderDetail od = new OrderDetail();

            od.SalesOrderID = o.OrderID;
            od.OrderQty = 5;
            od.ProductID = 708;
            od.SpecialOfferID = 1;
            od.UnitPrice = 28.84;
            ((ISqlOperation)od).Command = new SqlCommand();
            ((ISqlOperation)od).Command.Connection = (SqlConnection)conn;
            iStatus = ObjectProxyFactory.CreateProxy<IOrderDetail>(
                od,
                new string[] { "InsertOrderDetail" },
                new Decoration(AppConcerns.JoinSqlTransaction, new object[] { transaction }),
                new Decoration(AppConcerns.CommitSqlTransaction, null)
            ).InsertOrderDetail();
        }
        catch (Exception ex)
        {
            if (transaction != null)
                transaction.Rollback();
        }
        finally
        {
            conn.Close();
        }
    }
}

There are a few changes in the application. First, a transaction starts by calling the BeginTransaction method of SqlConnection. Second, before calling the InsertOrder method of the Order component, CreateProxy<IOrder> is called to attach JoinSqlTransaction to the object o of Order as a preprocessing aspect with the transaction object as a parameter. Third, before calling the InsertOrderDetail method of the OrderDetail component, CreateProxy<IOrderDetail> is called to attach JoinSqlTransaction to the object od of OrderDetail as a preprocessing aspect with the transaction object as a parameter. And also, CommitSqlTransaction is attached to the same object as a postprocessing aspect.

When executing, a record is inserted to each of the [Sales].[SalesOrderHeader] and [Sales].[SalesOrderDetail] tables. Uncomment the line //throw new Exception(); and execute again, and no record is inserted to either of the tables. The transaction rollbacks and data integrity is preserved.

Note that the transaction capabilities are added to the application by attaching the aspects to objects. No change is made in the components Order and OrderDetail.

Use AOPContainer

The application is rewritten using AOPContainer with Windsor Container and Unity Container. The code is listed as follows.

static void Main(string[] args)
{
    AOPContainer aopcontainer = null;
    switch (args.Length == 0 ? "" : args[0])
    {
        case "0": //Register types for Windsor Container and create AOPWindsorContainer
            {
                Console.WriteLine("Use Windsor Container");

                IWindsorContainer windsorContainer = new WindsorContainer();
                windsorContainer.Register(AllTypes
                    .FromAssembly(Assembly.LoadFrom("DataModel.dll"))
                    .Where(t => (t.Name.Equals("Order") || t.Name.Equals("OrderDetail")))
                    .Configure(c => c.LifeStyle.Transient)
                );

                aopcontainer = new AOPWindsorContainer(windsorContainer);
            }
            break;

        case "1": //Register types for Unity Container and create AOPUnityContainer
            {
                Console.WriteLine("Use Unity Container");

                IUnityContainer unityContainer = new UnityContainer();
                unityContainer.RegisterType<IOrder, Order>(new InjectionConstructor()
                ).RegisterType<IOrderDetail, OrderDetail>(new InjectionConstructor()
                );

                aopcontainer = new AOPUnityContainer(unityContainer);
            }
            break;

        default:
            {
                Console.WriteLine("Usage: ConsoleUtil i");
                Console.WriteLine("where i: 0 (Windsor Container)");
                Console.WriteLine("         1 (Unity Container)");
                Console.ReadLine();
            }
            return;
    }

    Console.WriteLine();
    string connStr = "Integrated Security=true;" + 
           "Data Source=(local);Initial Catalog=AdventureWorks";
    using (IDbConnection conn = new SqlConnection(connStr))
    {
        IDbTransaction transaction = null;

        try
        {
            conn.Open();
            transaction = conn.BeginTransaction();

            IOrder o = aopcontainer.Resolve<Order, IOrder>();
            o.CustomerID = 18759;
            o.DueDate = DateTime.Now.AddDays(1);
            o.AccountNumber = "10-4030-018759";
            o.ContactID = 4189;
            o.BillToAddressID = 14024;
            o.ShipToAddressID = 14024;
            o.ShipMethodID = 1;
            o.SubTotal = 174.20;
            o.TaxAmt = 10;
            ((ISqlOperation)o).Command = new SqlCommand();
            ((ISqlOperation)o).Command.Connection = (SqlConnection)conn;

            int iStatus;
            iStatus = AOPContainer.ChainAspect<IOrder, IOrder>(
                o,
                "InsertOrder",
                new Decoration(AppConcerns.JoinSqlTransaction, new object[] { transaction }),
                null
            ).InsertOrder();

            //throw new Exception();

            IOrderDetail od = aopcontainer.Resolve<OrderDetail, IOrderDetail>();

            od.SalesOrderID = o.OrderID;
            od.OrderQty = 5;
            od.ProductID = 708;
            od.SpecialOfferID = 1;
            od.UnitPrice = 28.84;
            ((ISqlOperation)od).Command = new SqlCommand();
            ((ISqlOperation)od).Command.Connection = (SqlConnection)conn;
            iStatus = AOPContainer.ChainAspect<IOrderDetail, IOrderDetail>(
                od,
                "InsertOrderDetail",
                new Decoration(AppConcerns.JoinSqlTransaction, new object[] { transaction }),
                new Decoration(AppConcerns.CommitSqlTransaction, null)
            ).InsertOrderDetail();
        }
        catch (Exception ex)
        {
            if (transaction != null)
                transaction.Rollback();
        }
        finally
        {
            conn.Close();
        }
    }
}

Since AOPContainer provides a common application programming interface for IoC containers, transaction management is the same no matter what IoC container you use. And the same components and aspects are used. AOPContainer.ChainAspect is used to attach aspects to objects this time, although we can still use ObjectProxyFactory.CreateProxy.

Points of Interest

• Transaction management is made easier by extending objects instead of extending components.
• The transaction capabilities are presented in a couple of aspect methods.
• By designing components transaction-oblivious, the system is more flexible and needs less maintenance.
• The same components and aspects can be used for any IoC container for transaction management with AOP Container.