14

Do we really require transaction handling in c# as well database store process both side

C#:

Using(transaction with transaction scope)
{
     Execute stored proc;
     Transaction. Complete;
}

SQL stored procedure:

Create process
As
Begin try
    Begin transaction
    Commit
End try
Begin catch
    Rollback
End catch
18

First, you should always have proper transaction handling in all of your procedures so that it does not matter if they are called by app code, by another procedure, individually in an ad-hoc query, by a SQL Agent job, or some other means. But single DML statements, or code that doesn't make any modifications, doesn't need an explicit Transaction. So, what I am recommending is:

  • Always have the TRY / CATCH structure so that errors can be properly bubbled up
  • Optionally include the 3 Transaction handling pieces in the code below if you have multiple DML statements (since a single statement is a transaction in itself). HOWEVER, outside of adding some additional code where it isn't specifically needed, if one prefers to have a consistent template, then it doesn't hurt to keep in the 3 Transaction-related IF blocks. But in that case I would still recommend not keeping the 3 Transaction-related IF blocks for SELECT-only (i.e. read-only) procs.

When doing 2 or more DML statements, you need to use something along the lines of the following (which can also be done for single DML operations if one prefers to be consistent):

CREATE PROCEDURE [SchemaName].[ProcedureName]
(
    @Param  DataType
    ...
)
AS
SET NOCOUNT ON;
DECLARE @InNestedTransaction BIT;

BEGIN TRY

    IF (@@TRANCOUNT = 0)
    BEGIN
        SET @InNestedTransaction = 0;
        BEGIN TRAN; -- only start a transaction if not already in one
    END;
    ELSE
    BEGIN
        SET @InNestedTransaction = 1;
    END;

    -- { 2 or more DML statements (i.e. INSERT / UPDATE / DELETE) }

    IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
    BEGIN
        COMMIT;
    END;

END TRY
BEGIN CATCH

    IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
    BEGIN
        ROLLBACK;
    END;

    DECLARE @ErrorMessage   NVARCHAR(4000) = ERROR_MESSAGE(),
            @ErrorState     INT = ERROR_STATE(),
            @ErrorSeverity  INT = ERROR_SEVERITY();

    -- optionally concatenate ERROR_NUMBER() and/or ERROR_LINE() into @ErrorMessage

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    RETURN;

END CATCH;

When doing just 1 DML statement or just a SELECT, you can get away with just the following:

CREATE PROCEDURE [SchemaName].[ProcedureName]
(
    @Param  DataType
    ...
)
AS
SET NOCOUNT ON;

BEGIN TRY

    -- { 0 or 1 DML statements (i.e. INSERT / UPDATE / DELETE) }

END TRY
BEGIN CATCH

    DECLARE @ErrorMessage   NVARCHAR(4000) = ERROR_MESSAGE(),
            @ErrorState     INT = ERROR_STATE(),
            @ErrorSeverity  INT = ERROR_SEVERITY();

    -- optionally concatenate ERROR_NUMBER() and/or ERROR_LINE() into @ErrorMessage

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    RETURN;

END CATCH;

Second, you should handle the transaction at the app layer only if you need to execute more than 1 query/stored procedure and they all need to be grouped into an atomic operation. Doing a single SqlCommand.Execute___ only needs to be in a try / catch, but not in a Transaction.

But, does it hurt to do a Transaction at the app layer when only making a single call? If it requires MSDTC (Microsoft Distributed Transaction Coordinator) then it is a bit heavier on the system to do this at the app layer when not expressly needed. Personally, I prefer to avoid app layer-based transactions unless absolutely necessary as it cuts down on the potential for orphaned transactions (if something went wrong with the app code prior to doing the commit or rollback). I also have found that it sometimes make debugging certain situations a little more difficult. But that being said, I don't see anything technically wrong with also handling the transaction at the app layer when making a single proc call; again, a single DML statement is its own transaction and doesn't need any explicit transaction handling at either layer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.