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.
No comments:
Post a Comment