Do we need to apply Sql Transaction in both C# code and Stored Procedure also?

Question

I have a stored procedure which is updating multiple tables in SQL Server. This procedure is being used from C# code.

If I apply the transaction only in C# code would it be a good practice?

Do I need to apply transaction in both C# code and stored procedure?

Thanks

Answer 1

If the process is only calling a single stored procedure in a single SqlCommand, then just handle the transaction inside the stored procedure and there is no need to manage it from the C# code. You would only need to manage it in the C# code in order to maintain the transaction across multiple SqlCommand executions.

FYI, managing the transaction in both layers is only necessary if both of the following are true:

• the C# code is making multiple SqlCommand calls that need to be considered a single operation
• the stored procedure(s) can/will be called by outside of this C# code, such as by other stored procedures (in which case there might not be an existing Transaction at the time the stored procedure(s) is/are called.

Outside of the above scenario, managing the transaction in both layers is pointless as there is only a single transaction. If the transaction is started in the C# code, then all that happens in the stored procedure when BEGIN TRAN is called is that @@TRANCOUNT is incremented. And the transaction is not truly committed until the @@TRANCOUNT comes back down to 0 by issuing the same number of COMMITs as shown in @@TRANCOUNT (in this case, issuing a COMMIT in the stored procedure and again in the C# code, at which point SQL Server actually does the real "commit"). However, a single ROLLBACK brings @@TRANCOUNT back to 0 no matter what number it was at. And if that happens in the Stored Proc, you cannot issue either a COMMIT or ROLLBACK in the C# code as the transaction no longer exists, so you would need to test for an active transaction first.

Assuming that you are using at least SQL Server 2005, if not newer, be sure to use the T-SQL TRY / CATCH syntax to manage the COMMIT / ROLLBACK within the stored procedure. You will need the TRY / CATCH syntax to properly catch errors and exit the proc(s) even if you are only managing the transaction in the C# code.

For example:

BEGIN TRY

  BEGIN TRAN;

     UPDATE Table1 ... ;

     UPDATE Table2 ... ;

     UPDATE Table3 ... ;

  COMMIT TRAN;

END TRY
BEGIN CATCH

  IF (@@TRANCOUNT > 0)
  BEGIN
    ROLLBACK TRAN;
  END;

  THROW; -- if using SQL Server 2012 or newer, else use RAISERROR

END CATCH;