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.

Answer 1

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"));

Answer 2

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 !