Exception during transactions using EF's DbContext.ExecuteSqlCommand()

Question

Using a try-catch structure i'm trying to figure what to do if an exception is caught in any point of the transaction. Below one sample of code: ``` try { DbContext.ExecuteSqlCommand("BEGIN TRANSACTION"); //Line 1 DBContext.ExecuteSqlCommand("Some Insertion/Deletion Goes Here"); //Line 2 DbContext.ExecuteSqlCommand("COMMIT"); //Line 3 } catch(Exception) { } ``` If the expection was caught executing 'Line 1' nothing must be done besides alerting the error. If it was caught executing the second line i don't know if i need to try to rollback the transaction that was sucessfully opened and the same occurs in case something went wrong with the third line. Should i just send a rollback anyway? Or send all the commands straight to the bank in a single method call? Inside the try-catch there's a loop performing many transactions like the one in the sample (and i need lots of small transactions instead of just a big one so i can reuse the SQL's '_log' file properly and avoid it to grow unneces…

Accepted Answer

Think this will help:

```
using (var ctx = new MyDbContext())
{
    // begin a transaction in EF – note: this returns a DbContextTransaction object
    // and will open the underlying database connection if necessary
    using (var dbCtxTxn = ctx.Database.BeginTransaction())
    {
       try
       {
            // use DbContext as normal - query, update, call SaveChanges() etc. E.g.:
           ctx.Database.ExecuteSqlCommand(
               @"UPDATE MyEntity SET Processed = ‘Done’ "
               + "WHERE LastUpdated < ‘2013-03-05T16:43:00’");

var myNewEntity = new MyEntity() { Text = @"My New Entity" };
           ctx.MyEntities.Add(myNewEntity);
           ctx.SaveChanges();

dbCtxTxn.Commit();
       }
       catch (Exception e)
       {
           dbCtxTxn.Rollback();
       }
    } // if DbContextTransaction opened the connection then it will close it here
}
```

taken from: https://entityframework.codeplex.com/wikipage?title=Improved%20Transaction%20Support

Basically the idea of it is your transaction becomes part of the using block, and within that you have a try/catch with the actual sql. If anything fails within the try/catch, it will be rolled back

Exception during transactions using EF's DbContext.ExecuteSqlCommand()

Solution

Problem

Related problems