CODEWITHSUNDEEP
c#.netsql-servert-sqltransactions
Cristian Rusanu
Cristian Rusanu

Reputation: 662

TransactionScope and transactions included in nested stored procedures

I'm using TransactionScope in my C# code. I will try to simplify as much as I can .

public void ProcessStrings(...)
{
    ...
    using (TransactionScope transactionScope = new TransactionScope())
    {
        ...
        if(someCondition)  
        {   
            try
            {  
                DeleteSomeStrings(...);
            }
            catch(Exception ex)
            {
                //log error
                ...
                throw;
            }
        }

        ...

        try
        {
            UpdateSomeStrings(...);
        }
        catch(Exception ex)
        {
            //log error
            ...
            throw;
        }

        ...

        transactionScope.Complete();
    }
    ...
}

The DeleteSomeStrings and UpdateSomeStrings methods call two stored procedures through ADO.NET. I need to use explicit transaction handling into these stored procedures as well because they are also called from other contexts: 

CREATE PROCEDURE [dbo].[DeleteSomeStrings]
...
--parameter list
...
AS
BEGIN TRY

    BEGIN TRAN
    ...
    COMMIT TRAN
END TRY

BEGIN CATCH
    ...
    ROLLBACK 
    ...
    RAISERROR(...)
END CATCH

and the second one has a similar structure:

CREATE PROCEDURE [dbo].[UpdateSomeStrings]
...
--parameter list
...
AS
BEGIN TRY

    BEGIN TRAN
    ...
    COMMIT TRAN
END TRY

BEGIN CATCH
    ...
    ROLLBACK 
    ...
    RAISERROR(...)
END CATCH

The problem is when DeleteSomeStrings succeeds and UpdateSomeStrings fails, the data that was processed in DeleteSomeStrings still gets committed. It looks like the TransactionScope is ignored in this scenario. I would expect everything to be rolled back. Is this the normal behavior and if so why?

Upvotes: 2

Views: 1273

Answers (1)

Cristian Rusanu
Cristian Rusanu

Reputation: 662

Managed to get it work by using SqlTransaction instead of TransactionScope and passing the same connection and transaction to the command objects that were used to execute the two stored procedures. It seams TransactionScope cannot be used in this scenario. Thanks for your comments.

Upvotes: 1

Related Questions