Distributed Transaction Coordinator/Control In ASP.NET (DTC)

Introduction

The scope of the transaction includes activities such as retrieving data from SQLServer, reading messages from the message queue & writing to the database. Microsoft DTC, which implements two phases, commits protocols that ensure that the transaction outcome is consistent across all data resources involved in a transaction.

What is a Database transaction?

Invoking a stored procedure that wraps the required operation with BEGIN TRANSACTION COMMIT/ROLLBACK TRANSACTION allows you to run transactions in a single round trip to the database server.

Types of Transaction

There are two types of transaction that you will deal with Local Implicit and Distributed Implicit A local implicit transaction is a transaction that simply talks to only one database. A distributed transaction talks to multiple databases at the same time.

The transaction namespace is smart and will automatically use the appropriate transaction broker based on what type of database you are using; if you are talking to SQL Server 2000 databases, the transactions will be handled through the DTC on the client machine. When SQL Server 2005 is used, the transactions are handled directly by the SQL server.

Creating a transaction is easy, simply create a TransactionScope with a statement and include your code to execute your SQL statements. When the transaction is complete, tell the TransactionScope, that the commit is ready by setting the Consistent property to true

The managed functionality of COM+ is exposed in the .net via an assembly name called 'System.EnterpriseServices', which is part of the framework class library, but still, you have to add a reference from the project properties.

What is the TransactionScope class?

The TransactionScope class in the System. Transactions namespace enables the developer to programmatically wrap a series of statements within the scope of a transaction and includes support for complex transactions that involve multiple sources, such as two different databases or even heterogeneous types of data stores such as Microsoft SQL server and Oracle database & a web services. TransactionScope class uses Microsoft Distributed Transaction Coordinator (MSDTC). The configuration and implementation make it a rather advanced topic and beyond the scope of this tutorial.

Steps for Transaction Scope

• Reference to the system.Transactions assembly.
• Import the system. Transactions namespace in your application.
• If you want to specify nesting behavior for the transaction, declare the TransactionScopeOption variable and assign it a suitable value.
• If you want to specify the isolation level or timeout for the transaction, create a TransactionOptions instance and set its isolation level and TimeOut properties.
• Create a new TransactionScope Object.
• Pass a TransactionScope object variable, and TransactionOptions object into the constructor, if appropriate.
• Open connections to each database that you need to update and perform update operations as required by the application; if all updates succeed, call the Complete method on the TransactionScope object and close the using block.

  using (TransactionScope transScope = new TransactionScope())
  {
      string conString1 = WebConfigurationManager.ConnectionStrings["DB1"].ConnectionString;
      string conString2 = WebConfigurationManager.ConnectionStrings["DB2"].ConnectionString;
      using (SqlConnection SqlCon = new SqlConnection(conString1))
      {
          SqlCommand SqlCmd = SqlCon.CreateCommand();
          SqlCmd.CommandText = "INSERT INTO Tbl_Categories(catId, catName) VALUES(1, 'Electronics')";
          SqlCon.Open();
          SqlCmd.ExecuteNonQuery();
          SqlCon.Close();
      }
      using (SqlConnection SqlCon2 = new SqlConnection(conString2))
      {
          SqlCommand SqlCmd2 = SqlCon2.CreateCommand();
          SqlCmd2.CommandText = "INSERT INTO Tbl_Products(ProdId, catId, ProdName, Qty) VALUES(1, 1, 'Samsung Monitor', 1)";
          
          SqlCon2.Open();
          SqlCmd2.ExecuteNonQuery();
          SqlCon2.Close();
      }
      transScope.Complete();
  }

If all the operations in the transaction succeed, then we should call the complete method on the TransactionScope object to indicate that the transaction was completed successfully, and thus, the transaction manager commits the transaction.