Distributed Transactions in .NET 2.0

Introduction

Transactions are commonly used to update data as a batch. If any one operation from the batch is failed the entire batch of operations must be cancelled. Thus either all operations in the batch succeed or all of them are cancelled. ADO.NET 1.x provided SqlTransaction class that in conjunction with SqlConnection class provided to commit or rollback transactions. In .NET 2.0 an additional technique can be used and that is what is the topic of this article.

Types of Transactions

Transactions can be broadly classified as Single Transactions and Distributed Transactions. Single transactions involve only one database whereas distributed transactions involve multiple databases.

Transactional support provided by ADO.NET 1.x was single. Developers used COM+ features (System.ServiceModel namespace) to create distributed transactions. In .NET 2.0 you can work with distributed transactions without any direct interference with COM+.

The System.Transactions Namespace

.NET 2.0 provides System.Transactions namespace that provides classes for creating distributed transactions. The System.Transactions namespace resides in System.Transactions assembly. The TransactionScope class from System.Transactions namespace does the job of providing and completing a transaction. The TransactionScope works in such a way that you need not set any specific properties of SqlConnection or SqlCommand objects. It handles the job of creating, committing and rolling back a transaction in independent fashion. This way you can implement transactions on objects that are not originally written with transactional support. Note that the new architecture still depends in Microsoft Distributed Transaction Coordinator (MSDTC) for achieving the functionality.

Distributed Transactions - Good and Bad

Spanning a transaction across databases is not a simple job. The underlying resource manager (such as DTC) needs to do a lot of work internally. Typically resource managers use Two Phase Commit protocol to implement distributed transactions. The good part is that developers need not know every minute detail about this process. The new design simplifies that job considerably. However, distributed transactions come with their own price - performance penalty. Considering that a distributed transaction involves multiple database that are possibly heterogeneous there is bound to be performance overhead in the process. Luckily the new design takes care of this difficult as well. When you use TransactionScope class to create transactions by default they behave as a single transaction. The moment you open multiple connections the same single transaction gets promoted as a distributed transaction. That means if you use TransactionScope class for single transactions you won't get performance penalty. This way you can use consistent programming model for single as well as distributed transactions.

Example - Money Transfer

Let's understand the new distributed transaction model with the help of classic money transfer example. Assume that you want to transfer money from bank A to bank B. Thus money from one account must be withdrawn and deposited to another account. Both of these operations must be under one transaction so that either both of them succeed or both of them are cancelled. We demonstrate this scenario using a Windows Forms application.

First of create a new Windows Application using C# as the language.

Then right click on the project in the Solution Explorer and choose Add New Item menu option. Add a new SQL Server database called Database1.mdf in the project.

Open Server Explorer and create a new table called Bank in the newly added database. The schema of the table is shown below:

The Bank table consists of only two columns - AccountID (varchar(50), primary key) and Balance (money).

Now add a reference to System.Transactions asembly.

Also, add a new class called BankHelper and code it as shown below:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;

namespace DistTxnDemo
{
class BankHelper
{
private static string strConn;
private static int MinBalance = 500;

static BankHelper()
{
strConn = @"Data Source=.\SQLEXPRESS;
AttachDbFilename=C:\Bipin\DistTxnDemo\
Database1.mdf;Integrated Security=True;
User Instance=True";
}

public static bool ValidateBalance
(string accountno, decimal amount)
{
SqlConnection cnn = new SqlConnection(strConn);
cnn.Open();
SqlCommand cmd = new SqlCommand("select 
balance from BankA where accountid='" + accountno + 
"'", cnn);
object obj = cmd.ExecuteScalar();
decimal balance=Convert.ToDecimal(obj);
if (balance > MinBalance && (Convert.ToDecimal(obj) 
- amount) > MinBalance)
{
return true;
}
else
{
return false;
}
}

public static bool Transfer(string sourceAcc,
string destinationAcc,decimal amount)
{
try
{
if (ValidateBalance(sourceAcc, amount))
{
using (TransactionScope scope = 
new TransactionScope())
{
SqlConnection cnn1 = new SqlConnection(strConn);
SqlConnection cnn2 = new SqlConnection(strConn);
cnn1.Open();
cnn2.Open();

SqlCommand cmd1 = new SqlCommand
("update BankA set balance=balance-" + amount + 
" where accountid='" + sourceAcc + "'", cnn1);
SqlCommand cmd2 = new SqlCommand("update BankA set 
balance=balance+" + amount + " where accountid='" + 
destinationAcc + "'", cnn2);

cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
cnn1.Close();
cnn2.Close();

scope.Complete();
return true;
}
}
else
{
return false;
}
}
catch
{
return false;
}
}
}
}

The BankHelper class consists of a single public method called Transfer. The Transfer() method accepts source account ID, destination account ID and amount to be transferred and returns true if transfer is successful. The helper method ValidateBalance() checks if the amount can be transferred by validating the available balance.

The ADO.NET code consisting of SqlConnection and SqlCommand objects is the same code that you would have used in ADO.NET 1.x. Notice the lines marked in bold letters. First we import System.Transactions namespace. Then we put a "using" block that creates an instance of TransactionScope class. All the database operations are enclosed within this "using" block. This way whenever we fire the first query a single transaction is created for us. When we fire the second query that uses the second SqlConnection the same single transaction now gets promoted as distributed one. If the code throws any exception the transaction is automatically rolled back. If the control reaches to the call of Complete() method the transaction is committed.

You might be wondering as to how to check if the transaction is really distributed. To prove that open Control Panel > Administrative Tools > Component Services and expand Distributed Transaction Coordinator folder. After you run the above application you should see some activity there (see below).

If your transaction contains only one SqlConnection object then you will not see any activity because it will participate in a single transaction.

That's it! You just developed an application that uses the new distributed transaction features of .NET 2.0.