CODEWITHSUNDEEP
.netsql-serverado.nettransactions
Jonathon Watney
Jonathon Watney

Reputation: 22128

What's the difference between an SQL transaction at the stored procedure level and one at the SqlConnection level?

Say a stored procedure on MSSQL Server uses an SQL transaction using BEGIN TRANSACTION/COMMIT TRANSACTION, how does that differ from beginning and commiting one using ADO.NET via SqlConnection.BeginTransaction()?

Upvotes: 6

Views: 3160

Answers (2)

Sylvain
Sylvain

Reputation: 19249

If you are going to call multiple stored proc in a row and you want to be able to rollback, then you have to manage the transaction from you code using SqlConnection.BeginTransaction(). Otherwise it's the same.

Upvotes: 2

GSerg
GSerg

Reputation: 78175

For ADO.NET, it's no difference. It's implicitly stated in MSDN where for SqlTransaction object the Commit method is said to "fail if the transaction has already been rolled back on the server."

Also, SQL Server Profiler shows "SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRAN" as soon as you execute .BeginTransaction on a connection.

For ADO (not .NET), however, it's not. That used to allow nice scenarios with, effectively, nested transactions (server trans were nested inside client ones). Despite I have used that a lot, I can't define exactly what a "client transaction" was in that case.

Upvotes: 3

Related Questions