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

c# entity-framework-6 sql-server

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 unnecessarily).

If any of the transactions go wrong i'll just need to delete them all and inform what happen't, but i can't turn that into one big transaction and just use rollback otherwise it will make the log file grow up to 40GB.

1
2
7/11/2014 7:17:22 PM

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

6
7/11/2014 7:23:43 PM

Popular Answer

As of Entity Framework 6, ExecuteSqlCommand is wrapped with its own transaction as explained here: http://msdn.microsoft.com/en-gb/data/dn456843.aspx

Unless you explicitly need to roll multiple sql commands into a single transaction, there is no need to explicitly begin a new transaction scope.

With respect to transaction log growth and assuming you are targeting Sql Server then setting the transaction log operation to simple will ensure the log gets recycled between checkpoints.

Obviously if the transaction log history is not being maintained across the entire import, there is no implicit mechanism to rollback all the data in case of failure. Keeping it simple, I would probably just add a 'created' datetime field to the table and delete from the table based on a filter to the created field if I needed to delete all rows in case of error.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow