Begin Tran in VB.NET

Question

Dear All,

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. 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,
George

Answer 1

You 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

Answer 2

If all 5 procedures are in the same database and there are no linked databases involved:-

1. 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

2. 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,

1. you will need to use a distributed transaction and TransactionScope. Refer this link for more details and sample on how to make that work

Answer 3

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.