If a process calls only one stored procedure in one SqlCommand , just process the transaction inside the stored procedure, and there is no need to manage it from C # code. You will need to manage it only in C # code in order to support the transaction in several executions of SqlCommand .
FYI, transaction management in both layers is only necessary if one of the following conditions is true:
- C # code makes several
SqlCommand calls, which should be considered as one operation. - the stored procedure can / will be called outside this C # code, for example, by other stored procedures (in this case, perhaps there is no existing transaction at the time the stored procedure (procedures) is called.
Outside of the scenario described above, transaction management in both layers is pointless since there is only one transaction. If the transaction is started in C # code, then everything that happens in the stored procedure when calling BEGIN TRAN is that @@TRANCOUNT incremented. And the transaction is not really executed until @@TRANCOUNT returns to 0, issuing the same number of COMMIT as shown in @@TRANCOUNT (in this case, issues COMMIT in the stored procedure and again in C #, in this moment SQL Server actually executes "commit"). However, one ROLLBACK returns @@TRANCOUNT back to 0 regardless of how much it was in. And if this happens in Stored Proc, you cannot issue either COMMIT or ROLLBACK in C # code, because the transaction no longer exists, so you will need to check the active transaction first.
Assuming you are using at least SQL Server 2005, if not newer, be sure to use the T-SQL TRY / CATCH syntax to control COMMIT / ROLLBACK in the stored procedure. You will need the TRY / CATCH syntax to correctly fix errors and exit proc (s), even if you only manage the transaction in C # code.
For instance:
BEGIN TRY BEGIN TRAN; UPDATE Table1 ... ; UPDATE Table2 ... ; UPDATE Table3 ... ; COMMIT TRAN; END TRY BEGIN CATCH IF (@@TRANCOUNT > 0) BEGIN ROLLBACK TRAN; END; THROW;