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

Question

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()?

Answer 1

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.

Answer 2

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.