How use Transaction in EntityFramework 5?

Question

I want to trancate some table same time. If one not success, must be all rolback.

Something like that:

ctx.Database.ExecuteSqlCommand("truncate table tb_expensesall");
ctx.Database.ExecuteSqlCommand("truncate table tb_wholesale");
ctx.Database.ExecuteSqlCommand("truncate table tb_singlesale");
ctx.Database.ExecuteSqlCommand("truncate table tb_purchase");

But the problem is , I dont know how use transaction for this.

I trying this:

using (gasstationEntities ctx = new gasstationEntities(Resources.CONS))
{
    ctx.Database.Connection.Open();
    DbTransaction tr = ctx.Database.Connection.BeginTransaction();

    try
    {
        ctx.Database.ExecuteSqlCommand("truncate table tb_expensesall");
        ctx.Database.ExecuteSqlCommand("truncate table tb_wholesale");
        ctx.Database.ExecuteSqlCommand("truncate table tb_singlesale");
        ctx.Database.ExecuteSqlCommand("truncate table tb_purchase");
        //commit the transaction
        tr.Commit();
        new MessageWindow(this, Resources.GetString("Warn"), Resources.GetString("DeleteSuccess"));
    }
    catch (Exception ex)
    {
        //return
        tr.Rollback();
    }
    //close
    ctx.Database.Connection.Close();
}

The problem here: tr.Commit(); and the Exception tell me :

{System.InvalidOperationException: Connection must be valid and open to rollback transaction

And the tr.Rollback(); throw exception to. the exception is:

{System.InvalidOperationException: Connection must be valid and open to rollback transaction

The realy funy thing is , the table truncate is success. what? the commmit is throw exception . and it can be success? i can't understand.

Please tel me what is goning on . if you give me a solution, that's even better.

Solution 1

Add reference to System.Transactions, import using System.Transactions; and then try to encapsulate your code by

using (gasstationEntities ctx = new gasstationEntities(Resources.CONS))
{
   using (var scope = new TransactionScope())
   {
      [... your code...]

      scope.Complete();
   }
}

If exception occurs, scope.Complete() is not called and the rollback is automatic.

EDIT : I've just seen your MySql tag. If this doesn't work, have a look here !

Solution 2

Try this, Technically, the using should commit the transaction when there are no exceptions, but in case of exception, the using will automatically rollback it.

using (var txn = new TransactionScope())
{
    ctx.Database.ExecuteSqlCommand("truncate table tb_expensesall");
    ctx.Database.ExecuteSqlCommand("truncate table tb_wholesale");
    ctx.Database.ExecuteSqlCommand("truncate table tb_singlesale");
    ctx.Database.ExecuteSqlCommand("truncate table tb_purchase");
    txn.Complete();
}
new MessageWindow(this, Resources.GetString("Warn"), Resources.GetString("DeleteSuccess"));

Comments

[usr]

Commit is missing :) And close the scope before showing a message.

[qakmak]

but where is the TransactionScope ? ef5 the me not found the method or property.

[JYL]

@qakmak : so with the good DLL reference and the "using System.Transactions;" at the top of your file, is it working ?

[qakmak]

but the problem is , if have a exception in the ExecuteSqlCommand2 . the ExecuteSqlCommand1 is success. how let the command success together or not success together. for exsample: ctx.Database.ExecuteSqlCommand("truncate table tb_expensesall"); ctx.Database.ExecuteSqlCommand("truncate table tb_wholesale"); throw new Exception("error"); scope.Complete(); if there is have error , the two ExecuteSqlCommand succwss. why? the scope not execute method Complete.

[qakmak]

it can't be use . if second throw error. the first is success. why?

[JYL]

So the Mysql transaction problem with TransactionScope is probably not fixed yet... As said in the link, you can try the old way. See that exemple.

[qakmak]

the old way can't rooback transaction to. and i find the problem . we can't use trancate table. we must use delete table. now i'm use TransactionScope and Entity Framework can be work. i just change the t-sql command truancate table XX to delte from XX.

[Lzh]

@qakmak, add reference to System.Transactions.dll