ASP.NET 2.0 and SQLServer 2000 Transactions

27 Jun 2007 | net | comments | edit

The new ADO.NET 2.0 and it's typed datasets and table adapters are very useful tools for creating a DAL in you application. Still when working with SQLServer 2000 implementing transactions is a bit tricky.

You can use TransactionScope but is seems to be designed for SQLServer 2005 as it will promote all transactions to distributed on SQLServer 2000 witch is a performance problem but also a bigger problem if your hosting provider does not provide distributed transactions. I've found a few solutions on the net some using reflection some using partial classes to add a method to the table adapter. Since i don't like using reflection unless i absolutely have to I've come up with a solution using partial classes but trying to minimize the code you have to write for each table adapter and also trying to make is less error prone.

First we define an interface witch the table adapters will implement:

1 public interface ITransactionable
2 {
3   SqlConnection SqlConnection {get;set;}
4   SqlCommand[] Commands {get;}
5 }

Now define a helper class that will manage all transaction operations.

 1 public class TransactionHelper
 2 {
 3   public static SqlTransaction BeginTransaction(ITransactionable adapter)
 4   {
 5     return BeginTransaction(adapter,IsolationLevel.ReadUncommitted);
 6   }
 7 
 8   public static SqlTransaction BeginTransaction(ITransactionable adapter, IsolationLevel lvl)
 9   {
10     SqlConnection con = adapter.SqlConnection;
11     if (con.State == ConnectionState.Closed)
12     con.Open();
13     SqlTransaction trans = con.BeginTransaction(lvl);
14     SetTransaction(adapter, trans);
15     return trans;
16   }
17   
18   public static void SetTransaction(ITransactionable adapter, SqlTransaction trans)
19   {
20     foreach (SqlCommand com in adapter.Commands)
21     com.Transaction = trans;
22     adapter.SqlConnection = trans.Connection;
23   }
24 }

Now for each table adapter that we need to use transactions:

1 public partial class DemoTableAdapter : ITransactionable
2 {
3   public SqlConnection SqlConnection { get { return Connection; } set { Connection = value; } }
4   public SqlCommand[] Commands { get { return CommandCollection; } }
5 }

As you can see the code needed for each table adapter is minimal and only requires copy-pasting and changing the name of the class. If c# would support c-style macros this could be reduced to a single line of code. To use the TransactionHelper class:

 1 ....
 2 SqlTransaction transaction=null;
 3 try{
 4   using(DemoTableAdapter adapter = new DemoTableAdapter())
 5   {
 6     transaction = TransactionHelper.BeginTransaction(adapter);
 7   .... operations with adapter ....
 8   }
 9   using(OtherTableAdapter adapter = new OtherTableAdapter ())
10   {
11     TransactionHelper.SetTransaction(adapter,transaction);
12   .... operations with adapter ....
13   }
14   transaction.Commit();
15 }
16 catch(Exception x)
17 {
18   transaction.Rollback();
19   ...handle error....
20 }
21 finally 
22 {
23   transaction.Dispose();
24 }

Hope it helps.

Erata.NET

ASP.NET 2.0 and SQLServer 2000 Transactions

Comments