Using System.Transactions

At work we are updating our web apps from .NET 1.1 to .NET 2.0. For reasons I won't go into here, we needed to update how we handled our business transactions. So using .NET 2.0 and SQL 2005 we went with the obvious System.Transactions class. There are tons of articles out there on using the TransactionScope class, which is suggested by Microsoft, but we needed more explicit control. We went with the CommittableTransaction and it works well. Now putting this in place was not difficult but as there is not much information out there on it I thought I'd give you some tid bits. Please note that this is not supported on Win98/ME. My intent here is to give an example of CommittableTransactions and the explicit control you have, I'll refer to other links for more detail on tangent topics.

First you must (of course) reference the System.Transactions namespace in your project. In my scenario I'm connecting to multiple databases, but only one at a time. If you need to use multiple databases in a single transaction please see this article for more information on Distributed Transactions.

First I created an object to hold my transaction and connection information as properties to easily pass between my app tiers if needed

Public Class ExtendedTxn
Private m_objCommittableTxn As CommittableTransaction
Private m_objSqlConnection As SqlConnection

Public Sub New()
MyBase.New()
m_objSqlConnection = Nothing
m_objCommittableTxn = New CommittableTransaction
End Sub

'----------------------------------------------------------------------------
' Holds SQL Connection object to help prevent transaction promotion.
'----------------------------------------------------------------------------

Public Property SqlConnection() As SqlConnection
Get
Return m_objSqlConnection
End Get
Set(ByVal objSqlConnection As SqlConnection)
m_objSqlConnection = objSqlConnection
End Set
End Property

'----------------------------------------------------------------------------
' Holds Transaction object to pass between business objects.
'----------------------------------------------------------------------------

Public Property CommittableTxn() As CommittableTransaction
Get
Return m_objCommittableTxn
End Get
Set(ByVal objCommittableTxn As CommittableTransaction)
m_objCommittableTxn = objCommittableTxn
End Set
End Property
End Class

So next, I created my connection to the database

Private Function GetConnection(ByVal SqlConn As String) As SqlConnection
Dim objConnection As SqlConnection
objConnection = New SqlConnection(SqlConn)
Return objConnection
End Function

Next I put in place my begin, commit, and rollback methods to handle those events when necessary

Public Function BeginTxn(ByVal SqlConn As String, Optional ByVal objExtTxn As ExtendedTxn = Nothing) As ExtendedTxn
Dim objNewTxn As ExtendedTxn

If objExtTxn Is Nothing Then
objNewTxn = New ExtendedTxn
objNewTxn.SqlConnection = GetConnection(SqlConn)
Else
objNewTxn = objExtTxn
objNewTxn.SqlConnection = GetConnection(SqlConn)
End If

Return objNewTxn
End Function

Public Sub CommitTxn(ByVal objExtTxn As ExtendedTxn)

If Not objExtTxn Is Nothing AndAlso Not objExtTxn.CommittableTxn Is Nothing Then
objExtTxn.CommittableTxn.Commit()
objExtTxn.CommittableTxn.Dispose()
objExtTxn.SqlConnection.Close()
End If
objExtTxn = Nothing
End Sub

Public Sub RollbackTxn(ByVal objExtTxn As ExtendedTxn)
If Not objExtTxn Is Nothing AndAlso Not objExtTxn.CommittableTxn Is Nothing Then
objExtTxn.CommittableTxn.Rollback()
objExtTxn.CommittableTxn.Dispose()
objExtTxn.SqlConnection.Close()
End If
objExtTxn = Nothing
End Sub

For this demo, I'm executing a simple stored procedure on my button's click event, of course wrapped in a transaction. Once I open the connection, I maintain the same connection to eliminate the promotion of the transaction to a distributed transaction. You'll want to do this with connection pooling as well. After executing the first stored procedure, I paused for three seconds and executed the second to show that the transaction will process both T-SQL statements at the same time. Then clean up my connection objects and commit my transaction.

Private Sub btnCommittableTxn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCommittableTxn.Click
Dim objCommandOne As New SqlCommand
Dim objCommandTwo As New SqlCommand
Dim objExtLogic As ExtendedTxnLogic
Dim objExtTxn As ExtendedTxn

' --- Set UI lables
Call ResetLabels()

objExtLogic = New ExtendedTxnLogic
objExtTxn = objExtLogic.BeginTxn(My.Settings.strConnection)
objExtTxn.SqlConnection.Open()

' --- Setup the Data Access to run the Stored Procedures
objCommandOne.Connection = objExtTxn.SqlConnection
objCommandOne.CommandType = CommandType.StoredProcedure
objCommandOne.CommandText = "dbo.CommittableTxn"
objCommandOne.Parameters.AddWithValue("@TxnName", SqlDbType.NVarChar).Value = "First Txn " & DateTime.Now.ToString
objCommandOne.Connection.EnlistTransaction(objExtTxn.CommittableTxn)

' --- The second txn will use the same connection
objCommandTwo.Connection = objExtTxn.SqlConnection
objCommandTwo.CommandType = CommandType.StoredProcedure
objCommandTwo.CommandText = "dbo.CommittableTxn"
objCommandTwo.Parameters.AddWithValue("@TxnName", SqlDbType.NVarChar).Value = "Second Txn " & DateTime.Now.ToString
objCommandTwo.Connection.EnlistTransaction(objExtTxn.CommittableTxn)

Try
objCommandOne.ExecuteNonQuery()
objCommandTwo.ExecuteNonQuery()

Call objExtLogic.CommitTxn(objExtTxn)
Me.lblCommittable.Visible = True

Catch ex As Exception
' --- If any exceptions rollback
Call objExtLogic.RollbackTxn(objExtTxn)

Finally
' --- Close local connection objects
objCommandOne.Connection.Close()
objCommandTwo.Connection.Close()
End Try
End Sub

My stored procedures simply write one value to a one column table; you could make this anything you'd like to define. As noted earlier we store the CommittableTransaction as a property of an object that we pass between other classes, this works well for us when the transaction begins in a method of the UI, but passes through the business tier and back. Microsoft provides an example I suggest you take a look at as well. The sample code provided does not include the stored proc, if you are looking at using System.Transactions in your app I feel confident you can create your own stored procedures.

Download the sample code - TransactionDemo1.zip (21.97 KB)