Using SqlTransaction with MSSQL Trans?

  • Thread starter Thread starter yacrumb
  • Start date Start date
Y

yacrumb

Hello all,

I got seven sp's that uses transactions:
BEGIN TRAN
....
ROLLBACK TRAN
....
COMMIT

From ado.net I need to execute all of these using transaction:
Click to expand...
SqlTransaction tran = conn.BeginTransaction()
....
tran.Commit()
....
tran.Rollback()

Q: will a commit in sp's be rollbacked by ado.net's rollback? i.e. do
ado.net and mssql use the same transactions-counter?
 
Hi,

I believe you will get separate transactions and transaction from the
application will not be able to rollback changes in a database.
 
What you are suggesting -- using SqlTransaction -- is not a good idea
in this situation. It would be better to create a stored procedure to
wrap all of the transactions if you want to roll back all transactions
if one of the sprocs fails to commit its transaction. That way all of
your code is executing on the server, not partially on the client.

--Mary
 
do ado.net and mssql use the same transactions-counter?

Yes they do. But ADO.NET's implementation is a bit more complex than calling
BEGIN TRAN everytime you call SqlConnection.BeginTransaction. The best
approach as Mary suggested is to wrap everything in one stored proc and not
mix and match database transactions with ADO.NET transactions.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
Back
Top