Begin Tran in VB.NET
I have created a windows form in VB.NET and in the click event of "Save", i am calling 5 procedures to perform different tasks.开发者_JAVA百科 All these are in a a single TRY - CATCH block. The problem which i am facing is that from the 5 procedures being called, if there is an error while executing the 4th procedure, the data stored from the 3 before procedures will exist in tables.
Can anyone help me as to how I can use begin tran, rollback tran and commit tran in VB.NET.
Regards,
GeorgeYou can setup ADO.Net to use a transaction:
Dim conn As SqlConnection = New SqlConnection("connString")
Dim transaction As SqlTransaction = conn.BeginTransaction
conn.Open()
Try
'do all your work....
transaction.Commit()
Catch ex As Exception
transaction.Rollback()
Finally
'clean up....
End Try
If all 5 procedures are in the same database and there are no linked databases involved:-
You could create one procedure that calls all 5 procedures in the correct sequence. In this procedure you could do a BEGIN TRAN and a COMMIT / ROLLBACK TRAN at the beginning and end respectively
If you want to do it using ADO.net and as long as all procedures are being called on the same Connection, you could associate the transaction with your command obect and use it as per the example given here
However In case you are talking across multiple databases,
- you will need to use a distributed transaction and TransactionScope. Refer this link for more details and sample on how to make that work
I'd use a TransactionScope object and implicit transactions. That way even if you cross databases they'll all work together transparently. Well, you need the DTC running if you do that... but anyway, something like this
Imports System.Transactions
Using scope as new TransactionScope()
' do all your work, here, then ...
' Commit everything
scope.Complete()
End Using
The nice thing here is that the code that does the actual work doesn't have to know there's a transaction running, and if there's an exception in there it'll automatically roll back thanks to the Using block.
精彩评论