Transactions in ADO.Net or in the Database?

[Guest]

Is it best practice to handle transactions in ADO.Net or in the database
level within stored procedures?

 

[Bruce Barker]

it will depend. if using sqlserver, transactions hold locks and may cause
contention issues, so you want them open fo as short a time as possible. if
you can do the transaction with a single sp call, then you have reduced the
time down to the minimum.

if you did

tran = conn.BeginTransaction()

// execute sql statement

tran.Commit()

then you have added network hops (real slow in server time), to the amount
of time the transaction locks are held.

-- bruce (sqlwork.com)