呼叫使用实体框架内幕交易的存储过程 [英] Call stored procedure inside transaction using Entity Framework

问题描述

我试图运行使用实体框架下交易。内幕交易的范围我所说的从数据库存储过程。

I am trying to run the following transaction using Entity Framework. Inside transaction scope I call stored procedure from the DB.

 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();
     }
 }

在该行 entitiesContext。 ExecuteFunction来（）我得到异常事务计数后EXECUTE指示BEGIN和COMMIT语句数不匹配。上一个计数= 1，当前计数= 0

我的存储过程不使用任何交易，不调用任何其他函数或过程。所以我不明白为什么我不能执行内幕交易strored程序

My stored procedure doesn't use any transactions and doesn't call any others functions or procedures. So I don't understand why I can't execute strored procedure inside transaction.

更新：

呵呵，我发现这个存储过程：

Oh, I found this in the stored procedure:

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

可能是提交后的异常被抛出。但如何摆脱这种错误？

May be after commit exception is thrown. But how to escape this error?

推荐答案

我解决我的问题。

在存储过程中有一个 ROLLBACK 和 COMMIT 关键字。但没有 BEGIN TRANSACTION 在程序中的任何地方。从一开始，我以为这是奇怪的。

In the stored procedure there is a ROLLBACK and COMMIT keywords. But there is no BEGIN TRANSACTION anywhere in the procedure. From the beginning, I thought it is strange.

正如你所知道 COMMIT 递减 @ @TRANCOUNT 1。或者更精确地说：

As you know COMMIT decrements @@TRANCOUNT by 1. Or to be more precise:

如果@@ TRANCOUNT为1，COMMIT TRANSACTION使得因为事务开始时的
数据库的永久部分执行的所有数据修改
，释放事务占用的资源，并递减
@@ TRANCOUNT为0。如果@@ TRANCOUNT是大于1，COMMIT TRANSACTION
只减1 @@ TRANCOUNT和交易保持活跃。

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.

在我来说，我开始代码中的事务。和 COMMIT 在程序试图提交我的交易和减量 @@ TRANCOUNT ，但尚未完成。

In my case I begin a transaction in the code. And COMMIT in the procedure is trying to commit my transaction and decrement @@TRANCOUNT but it isn't completed yet.

所以我添加 BEGIN TRANSACTION 来存储过程，它工作正常。

So I added BEGIN TRANSACTION to the stored procedure and it works fine.

这篇关于呼叫使用实体框架内幕交易的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！