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!

Sahil (who is the author of this book on ADO.NET) and I chatted about transactions in MARS after my DotNetRocks show on Friday. I think his curiousity lay in the potential of multiple transactions within one connection in MARS. Rather than theorize, I thought I would just try it in code and my answer is “can’t be done”. You will get a System.InvalidOperation Exception that says “SqlConnection does not support parallel transactions.”

In theory this makes sense. In his MARS FAQ post, Angel (a tester on the ADO.NET team) explains when he likes to leverage MARS:

Q: So if MARS is not for performance what is it good for?

A: I like to use MARS in two core scenarios, 1) When using MARS results in cleaner looking code and 2) when I am using Transactions and need to execute in the same isolation level scope.

Q: When does using MARS result in cleaner looking code?

A: The quintessential MARS example involves getting a datareader from the server and issuing insert/delete/update statements to the database as you process the reader.

 Also, it’s good to note that MARS just is, it’s just there. You don’t really turn it on or off (though you can change the SQLConnection setting – but definitely read the FAQ on that point). It just kicks in when needed. It lets us code in a way that is logical. MARS was one of the most requested features for ADO.NET 2.0. It was really hard to get in there, but people were tired of getting an error message when they did what came naturally, try to use one connection for multiple commands.

But MARS won’t prevent you from writing code that will reduce your performance. In many cases, you will actually have much better performance leveraging connection pooling over forcing multiple queries (or executes) on one connection. So you should definitely consider how you take advantage of this feature.