My eyes are getting bigger and bigger as I’m digging into transactions in ADO.NET 2.0, or I should really just say in .NET 2.0.

First, I like that it solves a problem I currently have with a .NET 1.1 application.

I have to insert data into two tables. The first gets one new record, the second gets many that are related to the first.. If the first insert works, I go the second. If one of the updates to the 2nd table update fails, I have to roll back all that were done before it and then I have to delete the new record out of the first table. There may be a better way in 1.x, but that’s the best I could figure out.

Now with MARS, I can use one connection and therefore one transaction on all of these executions. A failure in the 2nd set will also rollback the first. That is super sweet.

In fact, Angel Saenz-Badillos recommends this as one of the two reasons *he* would use MARS.

Now I find this great 15 seconds article by Bill Ryan who has also found a wonderful muse in the aptly named Angel . Though I was experimenting with transactions using the same old way (using the SqlTransaction class), Bill shows a much simpler implementation using the new System.Transactions.TransactionScope class. (The namespace is new and I cannot believe I don’t have this (yet) in my Whidbey BCL presentation!!!) Oh boy! First of all, if you can remember the first time you tried to implement a transaction in ADO.NET, it was really confusing (and remains so). This implementation, wrapping the whole thing inside of a transaction (very new stuff for me) is really simple!

Think about what’s happening here, I’m using System.Transactions.TransactionScope – nothing to do with SQL, but it’s rolling back or committing my SQL transactions!  I have coded this up with two commands on one connections. Here’s some VB:

    Public Sub MARSUpdateTestTrans()
        Dim sqlbuilder As New SqlConnectionStringBuilder
        With sqlbuilder
            .ConnectionString = GetSqlString()
            .MultipleActiveResultSets = True
        End With
        Using ts As New System.Transactions.TransactionScope
            Dim IsConsistent As Boolean = False
            Dim conn As New SqlConnection(sqlbuilder.ToString)
            Dim cmd1 As New SqlCommand(“update employee set fname=’Julie’ where emp_id=’KJJ92907F'”, conn)
            Dim cmd2 As New SqlCommand(“update employee set fname=’Annabelle’ where emp_id=’AMD15433F'”, conn)
            conn.Open()
            Try
                cmd1.ExecuteReaderExecuteNonQuery ‘oops this had started out as a query…forgot to change the method
                cmd2.ExecuteNonQuery
                Throw New Exception
                IsConsistent = True
            Catch ex As Exception
                 ‘whatever else you want to do
            Finally
                ts.Consistent = IsConsistent
                conn.Close()
            End Try
        End Using
    End Sub

If that’s not enough, Bill explains that you can wrap calls to totally different  databases inside of this one transaction and it handles all of the goo for you.

As if that isn’t enough, Bill goes on (with some encouragement from Angel) to explain something that is beyond the scope of work I have done, but this is what thrills him even more. Not only can the transactionscope handle the different databases, but it also knows how to deal with local and distributed, SQL Server, Oracle, MSMQ.

Do not ignore this class. No, it won’t solve world hunger. But maybe you can go do some community service work with all of your free time it just gave you!

So remember, the SqlTransaction class is still there and there may be lots of cases where it’s better to use (one case is for more control about how the commits and rollbacks work). There is still more to explore!

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!