Friday, May 23, 2014

Using Database Transactions From ORM Tools Such As Entity Framework

A lot of my students and others have asked me about using database transactions from within ORM tools such as Entity Framework or Linq-to-SQL, etc. It really is very simple, if maybe not that intuitive. Here is a very simple example.

public class DBTransactions
{
   public void Sample()
   {
      // Declare your entities variable and your
      // transaction variable outside the scope of the
      // try block.

      YourDBEntities context;
      DbContextTransaction trans;
      try
      {
         // Instantiate your entities.
         context = new YourDBEntities();
         // Instantiate your transaction using the
         // entities context you just instantiated.

         trans = context.Database.BeginTransaction();
         // Do whatever database work you have to do.
         var Payment =
            context.payments.Where(e => e.payment_paid ==
            e.payment_due).Single();
         Payment.Paid_In_Full = true;
         // Save your work.
         context.SaveChanges();
         // Commit your transaction.
         trans.Commit();
      }
      catch (Exception e)
      {
         // In the event of an exception, rollback
         // your work.

         trans.Rollback();
      }
   }
}