Call stored procedure inside transaction using Entity Framework

c# entity-framework stored-procedures transactionscope

Question

I'm attempting to use Entity Framework to execute the following transaction. I invoke a database stored procedure inside the transaction scope.

 using (mother_Entities entitiesContext = context.Value)
 {
    using (var transactionScope = new TransactionScope())
    {   
        // a lot of create, insert, update operations goes here
        ...
        entitiesContext.SaveChanges();

        //Execute stored procedure:
        var paramMessage = new ObjectParameter("MESSAGE", "");
        var paramMotherid = new ObjectParameter("MOTHERID", motherProductId);
        var paramTochteridlist = new ObjectParameter("TOCHTER_ID_LIST", string.Join(";", motherIds));
        var paramError = new ObjectParameter("ERROR", typeof(int));
        var paramErrorText = new ObjectParameter("ERR_TEXT", typeof(string));

        entitiesContext.ExecuteFunction("SP_DOCUWARE_UPDATE", paramMessage, paramMotherid,
                                            paramTochteridlist, paramError, paramErrorText);

        ...
        transactionScope.Complete();
     }
 }

On the hookentitiesContext.ExecuteFunction() I receive exemptionTransaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0

Both transactions and other functions or processes are not used by my stored method. Therefore, I'm perplexed as to why I can't execute a stored method inside of a transaction.

UPDATE:

I saw this in the saved procedure, oh, I'm sorry.

...
    IF @COMMIT = 1
    BEGIN
        IF @CANCEL = 1 
            ROLLBACK
        ELSE
            COMMIT
    END
    ELSE IF @CHECK = 1
        ROLLBACK
    END
...

Perhaps the exception is thrown after the commit. But how can I avoid this mistake?

1
1
10/8/2013 1:47:31 PM

Accepted Answer

My issue was resolved.

There is a stored procedure thatROLLBACK and COMMIT keywords. Yet, there isn'tBEGIN TRANSACTION any stage of the process. I immediately felt that was unusual.

You are aware,COMMIT decrements @@TRANCOUNT by 1. or, to be more exact

If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction, and decrements @@TRANCOUNT to 0. If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active.

I start a transaction in the code in my scenario. AndCOMMIT The process is attempting to decrement and commit my transaction.@@TRANCOUNT although it hasn't been finished yet.

I thus addedBEGIN TRANSACTION the saved method, and everything runs smoothly.

3
10/9/2013 7:33:34 AM

Popular Answer

Before committing the inner transaction, you cannot commit the outer transaction (SaveChanges) (Complete). The phone call toSaveChanges commits the inner transaction internally as well. (Not confirmed.)



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow