Many of us developers have used SqlTransaction to ensure data integrity. But, SqlTransaction works on only one database. You can get it to work across multiple databases buy using the Save() method, but it gets ugly. There is a much better solution, TransactionScope. TransactionScope uses DTC (aka MSDTC) to manage the Sql Transactions and promoting transactions from Lightweight Transaction Manager (LTM) to full DTC.
Let’s see what we need to do to get SqlTransaction to work across multiple databases:
public void DoWorkSqlTransactions()
{
using (SqlConnection con1 = new SqlConnection("my connection string"))
using (SqlConnection con2 = new SqlConnection("my second connection string"))
{
try
{
con1.Open();
con2.Open();
SqlTransaction tran1 = con1.BeginTransaction();
SqlTransaction tran2 = con2.BeginTransaction();
try
{
SqlCommand cmd1 = new SqlCommand("update ...", con1, tran1);
cmd1.ExecuteNonQuery();
// Don't want select in transaction
cmd1 = new SqlCommand("select ...", con1);
SqlCommand cmd2 = new SqlCommand("insert ...", con2, tran2);
cmd2.ExecuteNonQuery();
tran1.Save("savepoint");
tran2.Save("savepoint");
tran1.Commit();
tran2.Commit();
}
catch (Exception)
{
tran1.Rollback();
tran2.Rollback();
}
}
catch (Exception)
{
// error handling for connection failure
}
finally
{
con1.Close();
con2.Close();
}
}
}
Now, try to do that with 5 or more databases at the same time! And try passing those SqlTransaction objects around! Really, really ugly code. Let’s look at the same code, but with TransactionScope:
public void DoWorkSqlTransactionScope()
{
try
{
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew))
using (SqlConnection con1 = new SqlConnection("my connection string"))
using (SqlConnection con2 = new SqlConnection("my second connection string"))
{
con1.Open();
con2.Open();
SqlCommand cmd1 = new SqlCommand("update ...", con1);
cmd1.ExecuteNonQuery();
// Suppress the select statement from the transaction:
using (TransactionScope ts2 = new TransactionScope(TransactionScopeOption.Suppress))
{
cmd1 = new SqlCommand("select ...", con1);
cmd1.ExecuteReader(); // and so on...
}
SqlCommand cmd2 = new SqlCommand("insert ...", con2);
cmd2.ExecuteNonQuery();
ts.Complete();
}
}
catch (Exception)
{
// whole transaction was rolled back automatically
}
}
As you can see, any call to the db made within the TransactionScope is automatically enrolled in a transaction.
This is true even if the call is made in another class, or external dll!
Think of what that can do for you….
If you don’t want to include something, suppress it like I did in the above example.
To use TransactionScope, you need to configure the machine you are developing on and the machine the application will be deployed on for DTC.
Configuring DTC is pretty simple to get working, and a little more work to secure. Follow this guide to configure it for Vista and Server 2008: http://technet.microsoft.com/en-us/library/cc753510(WS.10).aspx
For most other Windows Operating Systems, the short of it is run dcomcnfg for Component Services. Expand Component Services, Computers. Right click My Computer in the left pane, select Properties. Open the MSDTC tab. Select Security Configuration button. Check all the check boxes and select the “No Authentication Required” radio button. It may suggest to restart the DTC service, which is fine, do it. DTC may need to be configured on your Sql Server, too.
Now that the OS is configured for DTC, now we have to setup our project. Just add a reference for System.Transactions to the project, and a using clause for System.Transactions to the file where the class wrapping all the sql calls is, even if it is the ui or business layer. At the end of the using clause, call Complete() on the TransactionScope variable to commit the all the transactions.
