ADO.NET Asynchronous Transactions

Searching on the web and on the MSDN forums for Asynchronous Transactions didn’t give me what I was looking for.  I knew a bit about both, so I decided trying to combine the two to get the result I wanted.  This was the general outline of what I came up with.

Public Sub DoLongSQLOperation()
    Dim ConnectionStringBuilder As New SqlClient.SqlConnectionStringBuilder

    ConnectionStringBuilder.IntegratedSecurity = True
    ConnectionStringBuilder.DataSource = "SQLSERVER"
    ConnectionStringBuilder.InitialCatalog = "DATABASE"
    ConnectionStringBuilder.AsynchronousProcessing = True

    Dim MySQLConnection As New SqlConnection(ConnectionStringBuilder.ToString)
    MySQLConnection.Open()

    Dim LongSQLCommand As New SqlCommand("sp_LongOperation", MySQLConnection)

    Dim MySQLTrans As SqlTransaction = MySQLConnection.BeginTransaction

    LongSQLCommand.Transaction = MySQLTrans
    LongSQLCommand.CommandType = CommandType.StoredProcedure

    Dim Callback As New AsyncCallback(AddressOf CallbackMethod)

    Dim Result As IAsyncResult = LongSQLCommand.BeginExecuteNonQuery(Callback, LongSQLCommand)

    While Not Result.IsCompleted
      'do something if needed
    End While

  End Sub

  Private Sub CallbackMethod(ByVal result As IAsyncResult)
    Dim LongSQLCommand As SqlCommand

    LongSQLCommand = DirectCast(result.AsyncState, SqlCommand)
    LongSQLCommand.EndExecuteNonQuery(result)

    Dim MyTransaction As SqlTransaction = LongSQLCommand.Transaction

    Try
      MyTransaction.Commit()
    Catch ex As Exception
      'Try to rollback on a commit exception
      Try
        MyTransaction.Rollback()
      Catch exRollback As Exception
        'Rollback failed
      End Try
    End Try

    'Dispose of your objects
    MyTransaction.Dispose()
    LongSQLCommand.Dispose()
    LongSQLCommand.Connection.Dispose()

  End Sub

I used the Asynchronous Callback method of doing what I needed.  CallbackMethod gets called when the asynchronous operation completes.  Within my callback method is where I commit my transaction and dispose of any data resources.  I’ve kept the error handling to a minimum in my example for brevity, but you’ll definitely want to add them where they’re needed.