posted on Saturday, June 12, 2004 3:22 PM by demiliani

Transaction dilemma...

I've a little dilemma that comes out yesterday at work...

We're building an enterprise application that extensively use a database (SQL Server), and some actions on the database are performed with the use of transactions.

To perform an action on the database we always use stored procedure and the question that comes in my mind yesterday was this:

to perform a transaction, what is better? Using a stored procedure that performs the actions on the database in a transaction (something like this):

begin tran T

-- Action 1

-- Action 2

-- Action 3

commit tran T

or starting the transaction via my code (something like this):

Dim Transaction As SqlTransaction
Dim Command As SqlCommand
Connection.Open()
  Try
     Transaction = Connection.BeginTransaction()
     Command = New SqlCommand(...........,Connection, Transaction) 'Perform Action 1
     Command = New SqlCommand(...........,Connection, Transaction) 'Perform Action 2
     Command = New SqlCommand(...........,Connection, Transaction) 'Perform Action 3
     Transaction.Commit()
  Catch
    Transaction.Rollback()
    Throw
  Finally
        Connection.Close()
  End Try

What do you think? Advantages or disadvantages of the 2 methods?

Comments