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!
Posted from BLInk!