Transactions in .net

What are the best practices to do transactions in C# .Net 2.0. What are the classes that should be used? What are the pitfalls to look out for etc. All that commit and rollback stuff. I'm just starting a project where I might need to do some transactions while inserting data into the DB. Any responses or links for even basic stuff about transactions are welcome.

There are 2 main kinds of transactions; connection transactions and ambient transactions. A connection transaction (such as SqlTransaction) is tied directly to the db connection (such as SqlConnection), which means that you have to keep passing the connection around - OK in some cases, but doesn't allow "create/use/release" usage, and doesn't allow cross-db work. An example (formatted for space):

using (IDbTransaction tran = conn.BeginTransaction()) {
    try {
        // your code
        tran.Commit();
    }  catch {
        tran.Rollback();
        throw;
    }
}

Not too messy, but limited to our connection "conn". If we want to call out to different methods, we now need to pass "conn" around.

The alternative is an ambient transaction; new in .NET 2.0, the TransactionScope object (System.Transactions.dll) allows use over a range of operations (suitable providers will automatically enlist in the ambient transaction). This makes it easy to retro-fit into existing (non-transactional) code, and to talk to multiple providers (although DTC will get involved if you talk to more than one).

For example:

using(TransactionScope tran = new TransactionScope()) {
    CallAMethodThatDoesSomeWork();
    CallAMethodThatDoesSomeMoreWork();
    tran.Complete();
}

Note here that the two methods can handle their own connections (open/use/close/dispose), yet they will silently become part of the ambient transaction without us having to pass anything in.

If your code errors, Dispose() will be called without Complete(), so it will be rolled back. The expected nesting etc is supported, although you can't roll-back an inner transaction yet complete the outer transaction: if anybody is unhappy, the transaction is aborted.

The other advantage of TransactionScope is that it isn't tied just to databases; any transaction-aware provider can use it. WCF, for example. Or there are even some TransactionScope-compatible object models around (i.e. .NET classes with rollback capability - perhaps easier than a memento, although I've never used this approach myself).

All in all, a very, very useful object.

Some caveats:

• On SQL Server 2000, a TransactionScope will go to DTC immediately; this is fixed in SQL Server 2005 and above, it can use the LTM (much less overhead) until you talk to 2 sources etc, when it is elevated to DTC.

• There is a glitch that means you might need to tweak your connection string

protected void Button1_Click(object sender, EventArgs e)
   {


       using (SqlConnection connection1 = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.mdf;Integrated Security=True;User Instance=True"))
       {
           connection1.Open();

           // Start a local transaction.
           SqlTransaction sqlTran = connection1.BeginTransaction();

           // Enlist a command in the current transaction.
           SqlCommand command = connection1.CreateCommand();
           command.Transaction = sqlTran;

           try
           {
               // Execute two separate commands.
               command.CommandText =
                "insert into [doctor](drname,drspecialization,drday) values ('a','b','c')";
               command.ExecuteNonQuery();
               command.CommandText =
                "insert into [doctor](drname,drspecialization,drday) values ('x','y','z')";
               command.ExecuteNonQuery();

               // Commit the transaction.
               sqlTran.Commit();
               Label3.Text = "Both records were written to database.";
           }
           catch (Exception ex)
           {
               // Handle the exception if the transaction fails to commit.
               Label4.Text = ex.Message;
               

               try
               {
                   // Attempt to roll back the transaction.
                   sqlTran.Rollback();
               }
               catch (Exception exRollback)
               {
                   // Throws an InvalidOperationException if the connection 
                   // is closed or the transaction has already been rolled 
                   // back on the server.
                   Label5.Text = exRollback.Message;
                   
               }
           }
       }
       

   }

You could also wrap the transaction up into it's own stored procedure and handle it that way instead of doing transactions in C# itself.

if you just need it for db-related stuff, some OR Mappers (e.g. NHibernate) support transactinos out of the box per default.

It also depends on what you need. For basic SQL transactions you could try doing TSQL transactions by using BEGIN TRANS and COMMIT TRANS in your code. That is the easiest way but it does have complexity and you have to be careful to commit properly (and rollback).

I would use something like

SQLTransaction trans = null;
using(trans = new SqlTransaction)
{
    ...
    Do SQL stuff here passing my trans into my various SQL executers
    ...
    trans.Commit  // May not be quite right
}

Any failure will pop you right out of the using and the transaction will always commit or rollback (depending on what you tell it to do). The biggest problem we faced was making sure it always committed. The using ensures the scope of the transaction is limited.