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

Question

Say a stored procedure on an MSSQL server uses an SQL transaction using BEGIN TRANSACTION/COMMIT TRANSACTION , how is this different from starting and assigning one using ADO.NET using SqlConnection.BeginTransaction() ?

Answer 1

For ADO.NET, this does not matter. This is implicitly specified in MSDN, where for the SqlTransaction object, the Commit method is called "fail if the transaction is already rolling back on the server."

In addition, the SQL Server Profiler shows "SET THE INSULATION LEVEL OF BET OPERATIONS, STARTING, START TRAN" as soon as you execute .BeginTransaction when connecting.

However, for ADO (not .NET) this is not the case. This was used to provide good scenarios with efficient nested transactions (server trance was nested inside client ones). Despite the fact that I used this a lot, I can’t determine exactly what a “client transaction” is in this case.

Answer 2

If you are going to call multiple stored proc in a string and you want to be able to roll back, you need to manage the transaction from your code using SqlConnection.BeginTransaction (). Otherwise, it is the same.