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.

have you tried using a transactionscope? http://stackoverflow.com/questions/815586/entity-framework-using-transactions-or-savechangesfalse-and-acceptallchanges — Irfy, Dec 13 '12 at 16:15
I agreee just wrap the whole thing in a TransactionScope and it will just take care of it for you. — Ben Robinson, Dec 13 '12 at 16:17
One issue here is that `TRUNCATE` operations are not logged (unlike `DELETE FROM `) and, therefore, cannot be wrapped in a transaction. — uncoder, Jul 08 '14 at 22:03

score 23 · Accepted Answer · edited Oct 16 '19 at 19:53

23

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 !

edited Oct 16 '19 at 19:53

Vanity Slug - codidact.com

1,347
1
16
31

answered Dec 13 '12 at 16:16

JYL

8,228
5
39
63

@qakmak : so with the good DLL reference and the "`using System.Transactions;`" at the top of your file, is it working ? – JYL Dec 13 '12 at 17:02
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 Dec 13 '12 at 17:10
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](http://forums.asp.net/p/1239791/2262323.aspx/1?Re+Does+TransactionScope+work+really+with+MySql+). – JYL Dec 13 '12 at 17:21
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. – qakmak Dec 14 '12 at 05:23

score 3 · Answer 2 · edited Oct 23 '15 at 07:41

3

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

edited Oct 23 '15 at 07:41

Nekresh

2,948
23
28

answered Dec 13 '12 at 16:17

humblelistener

1,456
12
22

1

Commit is missing :) And close the scope before showing a message. – usr Dec 13 '12 at 16:18
but where is the TransactionScope ? ef5 the me not found the method or property. – qakmak Dec 13 '12 at 16:40
it can't be use . if second throw error. the first is success. why? – qakmak Dec 13 '12 at 17:14
2

@qakmak, add reference to System.Transactions.dll – Lzh May 21 '15 at 11:53

How use Transaction in EntityFramework 5?

2 Answers2