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();
}
}
On the line entitiesContext.ExecuteFunction()
I get exception Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0
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.
UPDATE:
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?
Best Answer
I solved my problem.
In the stored procedure there is a
ROLLBACK
andCOMMIT
keywords. But there is noBEGIN TRANSACTION
anywhere in the procedure. From the beginning, I thought it is strange.As you know
COMMIT
decrements@@TRANCOUNT
by 1. Or to be more precise: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.So I added
BEGIN TRANSACTION
to the stored procedure and it works fine.