Using SqlTransaction with MSSQL Trans?

[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:

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?

 

[Val Mazur \(MVP\)]

Hi,

I believe you will get separate transactions and transaction from the
application will not be able to rollback changes in a database.

 

[Mary Chipman [MSFT]]

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

 

[Sahil Malik [MVP C#]]

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