Using transactions in ADO.NET

Introduction

Information is critical in today's information age, but we need to keep the information as consistent with the reality as possible. Database systems holds data and ADO.NET enables to access this data in the backend system, and in order to keep the data consistent while we access the data using ADO.NET, we need to use transactions. A transaction is a set of operations (enabling data interchange between the business entities) where all of them must be successful or fail to ensure consistency on the behavior of the system. A transaction is said to perform a "unit of work" because it does all the work required to update the database to reflect the real-world changes. In this article, I will cover the main principles and techniques concerning transactions using ADO.NET as the data access technology and SQL Server as the Database system in order to build robust enterprise information systems.

ACID Properties of Transactions

Transactions are characterized by four properties called ACID properties. To pass this ACID test, a transaction must be Atomic, Consistent, Isolated, and Durable.

• Atomic. All steps in the transaction should succeed or fail together. If a transaction successfully completes and the system agrees to preserve its effects, we say that the transaction has committed, otherwise the transaction has aborted, and the underlying changes made to the database are undone, or rolled back.
   
• Consistency. The transaction takes the database from a stable state into a new stable state. The database must satisfy the business rules of the real-world enterprise it models, thus the execution of a transaction must maintain all these consistency constraints.
   
• Isolated. Every transaction is an independent entity. The execution of one transaction does not affect the execution of other transactions running at the same time.
   
• Durability. The results of committed transactions are permanent.

Transactions in enterprise systems

Modern relational database management systems support transactions such as Oracle database and Microsoft SQL Server. Data access API such as ODBC, JDBC. OleDB and ADO.NET enable developer use transactions in their applications. If the developer is executing transactions against several distributed data sources, then the Microsoft Distributed Transaction Coordinator (MSDTC) is used. MSDTC along with COM+ are middleware which enables the execution of distributed transactions. There are a lot of software packages to assist developers to write and execute distributed transactions ensuring the ACID properties across all the underlying data sources, using mechanisms such as two-phase commit and rollback.

Transactions in ADO.NET

ADO.NET supports single-database transactions as well as distributed transactions. Single-database transaction model is implemented using the underlying .NET managed providers for Transaction and Connection classes from the System.Data namespace. Distributed transaction model is implemented using classes in the namespace System.Transactions.

The following code snippet illustrates how to implement single-database transaction in ADO.NET (see Listing 1).

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace TransactionExampleCons
{
    class Program
    {
        static void Main(string[] args)
        {
            string strConnString = "myconnectionstring";
            SqlTransaction objTrans = null;
            using (SqlConnection objConn = new SqlConnection(strConnString))
            {
                objConn.Open();
                objTrans = objConn.BeginTransaction();

                SqlCommand objCmd1 = new SqlCommand("insert into tbExample values(1)", objConn);
                SqlCommand objCmd2 = new SqlCommand("insert into tbExample values(2)", objConn);

                try
                {
                    objCmd1.ExecuteNonQuery();
                    objCmd2.ExecuteNonQuery();

                    objTrans.Commit();
                }
                catch (Exception)
                {
                    objTrans.Rollback();
                }
                finally
                {
                    objConn.Close();
                }
            }
        }
    }
}

Listing 1

You can also use transactions along with DataSet and DataAdapter objects. The main idea is to set the created transactions to every command of the DataAdapter (see Listing 2).

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Reflection;

namespace OLA.Framework.Data.SqlClient.Transactions
{
    public class SQLDataAdapter_TransactionalManagement
    {
        public SqlTransaction BeginTransaction(object objTableAdapter)
        {
            return this.BeginTransaction(objTableAdapter, IsolationLevel.ReadCommitted);
        }

        public SqlTransaction BeginTransaction(object objTableAdapter, IsolationLevel isLevel)
        {
            Type taType = objTableAdapter.GetType();
            SqlConnection objConn = this.prvGetConnection(objTableAdapter);

            if (objConn.State == ConnectionState.Closed)
            {
                objConn.Open();
            }

            SqlTransaction stTrans = objConn.BeginTransaction(isLevel);
 
            this.prvSetTransaction(objTableAdapter, stTrans);

            return stTrans;
        }

        public void EnlistInTransaction(object objTableAdapter, SqlTransaction stTrans)
        {
            this.prvSetTransaction(objTableAdapter, stTrans);
        }

        private SqlConnection prvGetConnection(object objTableAdapter)
        {
            SqlConnection scResult = null;

            Type taType = objTableAdapter.GetType();
            PropertyInfo prtConnection = taType.GetProperty("Connection",BindingFlags.NonPublic | BindingFlags.Instance);
            scResult = (SqlConnection)prtConnection.GetValue(objTableAdapter,null);

            return scResult;
        }

        private void prvSetConnection(object objTableAdapter, SqlConnection objConn)
        {
            Type taType = objTableAdapter.GetType();
            PropertyInfo prtConnection = taType.GetProperty("Connection", BindingFlags.NonPublic | BindingFlags.Instance);
            prtConnection.SetValue(objTableAdapter, objConn, null);
        }

        private void prvSetTransaction(object objTableAdapter, SqlTransaction stTrans)
        {
            Type taType = objTableAdapter.GetType();

            PropertyInfo adapterProperty = taType.GetProperty("Adapter", BindingFlags.NonPublic | BindingFlags.Instance);
            SqlDataAdapter sdaAdapter = (SqlDataAdapter)adapterProperty.GetValue(objTableAdapter, null);
            sdaAdapter.UpdateCommand.Transaction = stTrans;
            sdaAdapter.InsertCommand.Transaction = stTrans;
            sdaAdapter.DeleteCommand.Transaction = stTrans;
            sdaAdapter.AcceptChangesDuringUpdate = false;

            PropertyInfo prtCommandCollection = taType.GetProperty("CommandCollection", BindingFlags.NonPublic | BindingFlags.Instance);
            SqlCommand[] arrCommands = (SqlCommand[])prtCommandCollection.GetValue(objTableAdapter, null);
            foreach (SqlCommand objCmd in arrCommands)
            {
                objCmd.Transaction = stTrans;
            }
            this.prvSetConnection(objTableAdapter, stTrans.Connection);
        }
    }
}

Listing 2

The AcceptChangesDuringUpdate property on the DataAdapter object when it's set to false, it specifies that the DataAdapter object should not change the state of data rows as long as it's executing the commands on several rows, thus the developer have to explicitly call the AcceptChanges method on the underlying DataSet object at the end of the transaction. If you set the AcceptChangesDuringUpdate property to true, and one row of all the rows to be updated (let's suppose this row is the last one) is not consistent with the rules of the database; when the transaction is executed and then it's rolled back, all the rows but the last one (the row with invalid values) has their state changed to UnModified. In .NET 1.1, a partial solution to this new problem is to extract the rows to be updated into a smaller DataSet using the GetChanges method of DataSet class. If the transaction commits, you could refresh the data and merge the new and fresh data from the database into the DataSet. One drawback with this approach is that the GetChanges and Merge methods are very expensive.

Let's see how to use the class in the Listing 2 in a real-world example (see Listing 3).

SqlTransaction stTrans = null;
try
{
    this.Validate();
    this.m_bsEmployee.EndEdit();//Accept changes through this BindingSource //instance

    SQLDataAdapter_TransactionalManagement sdatmInstance = new SQLDataAdapter_TransactionalManagement();
    stTrans = sdatmInstance.BeginTransaction(this.m_taEmployee); //Set //transaction for the table adapter in order to update the Employee table. //You can extend this transaction scope to several table adapters
    this.m_taEmployee.Update(this.m_hR_DataSet.Employee); //Update the //changes in the this.m_hr_DataSet.Employee object into the Employee table //through the table adapter

    stTrans.Commit();//Commit changes to the database system, if everything //is OK
    this.m_hR_DataSet.Employee.AcceptChanges();//Commit the changes to the //dataset object
}
catch (SqlException ex)
{
    stTrans.Rollback();//Cancel the changes to the database system, if an //error occurs
    System.Windows.Forms.MessageBox.Show(ex.Message, "SQL Exception", MessageBoxButtons.OK, MessageBoxIcon.Information); //Show the error message
}
catch (Exception ex)
{
    System.Windows.Forms.MessageBox.Show(ex.Message, "Error Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
    stTrans.Dispose();//Release the resources associated to the transaction //object
}

Listing 3

Distributed Transactions

Developers can also deal with database systems in which a transaction can access a heterogeneous set of transaction processing systems at multiple sites (perhaps scattered throughout the world) also known as resource managers. This sort of transactions is known as distributed transactions. These systems process some operations within the current transactions (a subtransaction) and then report a success or a failure. In addition to resource managers, we need a middleware package that listens to and coordinates the final result between resource managers. This middleware is known as transaction manager.

The transaction manager that ships with Windows is the Microsoft Distributed Transaction Coordinator (MSDTC). You can consume the services provided by MSDTC by using MTS/COM+, System.EnterpriseServices and the new System.Transactions namespace in .NET 2.0.

In order to implement an atomic commit, we need a protocol for the communication of resource managers and transaction managers. A lot of atomic commit protocols have been proposed, but the one that is in common use is called two-phase commit protocol. This protocol is initiated by the transaction manager or coordinator when the underlying transaction requests to commit. To perform correctly this protocol, the coordinator needs to know the identities of all the resource managers involved in the transaction. Thus, each time a resource manager joins the transaction; its identification is sent to the coordinator.

The two-phase commit protocol comprises two phases. The first phase involves preparing the changes required for the commit. The purpose is to determine whether resource managers are willing and ready to commit, but not actually committed yet. Once all the other resource managers notify to the transaction manager or coordinator to agree to commit, then the coordinator lets the resource managers to go ahead and commit their changes.

In a distributed transaction, anything, which has the capability to enlist itself in an MSDTC transaction, can participate as resource managers.
In .NET 1.1, you need to create a component hosted within a class library. This library must be in the GAC, thus you need to strongly name it using the sn.exe tool. This component can be implemented by inheriting from the class ServicedComponent in the System.EnterpriseServices namespace which provides the programming interface to access MSDTC functionality. You need to specify the TransactionAttribute on top of the component class and declare the transactional behavior using the TransactionOption enumeration (see Table 1). Finally, this component will be able to enlist itself in an MSDTC transaction.
 

Enumeration	Description
Disable	This component does not participate in a transaction. This is the default value.
NotSupported	This component runs outside the context of a transaction.
Supported	This component participates in a transaction if this exists. But it does not require a transaction or create a new one.
Required	This component must have a transaction. If a transaction does not exist, then it creates a new one. If a transaction exists, then it participates in this one.
RequiresNew	This component must have a transaction, and always creates a new transaction.

Table 1

The implementation of the component is shown in Listing 4.

using System;
using System.EnterpriseServices;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Collections.Generic;
using System.Text;

namespace TransactionExampleCons
{
    [Transaction(TransactionOption.RequiresNew)]
    public class COMPlusDistributedTransaction : ServicedComponent
    {
        public static void FinancialAccount(int nDebitAccount, float fDebitAmount, int nCreditAccount, float fCreditAmount)
        {
            OracleConnection objOracleConn = null;
            SqlConnection objSqlConn = null;
            OracleCommand cmdDebit = null;
            SqlCommand cmdCredit = null;

            try
            {
                objOracleConn = new OracleConnection("oracleconnstring");
                objSqlConn = new SqlConnection("oracleconnstring");

                string strDebitCmd = String.Format("UPDATE tbAccount SET amount={0} WHERE accountid={1}", fDebitAmount, nDebitAccount);
                string strCreditCmd = String.Format("UPDATE tbAccount SET amount={0} WHERE accountid={1}", fCreditAmount, nCreditAccount);

                cmdDebit = new OracleCommand(strDebitCmd, objOracleConn);
                cmdCredit = new SqlCommand(strCreditCmd, objSqlConn);

                cmdCredit.ExecuteNonQuery();
                cmdDebit.ExecuteNonQuery();
            }
            finally
            {
                cmdDebit.Dispose();
                cmdCredit.Dispose();

                objOracleConn.Close();
                objSqlConn.Close();
            }
        }
    }
}

Listing 4

If the method finishes successfully, then the component notifies to the coordinator that it's ready and willing with the changes, pending others. If anyone else in the distributed transaction aborts, then all the bets are off and no changes are committed.

Now let's illustrate how to implement distributed transaction in .NET 2.0 using the class in the System.Transactions namespace. In this case, we don't need an instance of a ServicedComponent-inherited class. We just create an instance of a .NET managed class (see Listing 5).

using System;
using System.Transactions;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Collections.Generic;
using System.Text;

namespace TransactionExampleCons
{
    public class ManagedDistributedTransaction
    {
        public void FinancialAccount(int nDebitAccount, float fDebitAmount, int nCreditAccount, float fCreditAmount)
        {
            using(TransactionScope tsInstance = new TransactionScope())
            {
                OracleConnection objOracleConn = new OracleConnection("oracleconnstring");
                SqlConnection objSqlConn = new SqlConnection("oracleconnstring");

                string strDebitCmd = String.Format("UPDATE tbAccount SET amount={0} WHERE accountid={1}", fDebitAmount, nDebitAccount);
                string strCreditCmd = String.Format("UPDATE tbAccount SET amount={0} WHERE accountid={1}", fCreditAmount,
nCreditAccount);

                OracleCommand cmdDebit = new OracleCommand(strDebitCmd, objOracleConn);
                SqlCommand cmdCredit = new SqlCommand(strCreditCmd, objSqlConn);

                cmdCredit.ExecuteNonQuery();
                cmdDebit.ExecuteNonQuery();

                tsInstance.Complete();
            }
        }
    }
}

Listing 5

You can check the status of the distributed transaction when it's running by going to the Control Panel | Administrative Tools | Component Services and navigate through the tree on the left side to view the transaction list (see Figure 1).



Figure 1

Conclusion

In this article, I've illustrated the main principles of transactions in ADO.NET through real-world example. Now, you can adapt these examples in your own business scenario.