Wortzel's blog

DLINQ – Advanced topics – Transaction support – part 4

As I have mention before the DLINQ is a part of the ADO.NET family and this is a major reason for it to support transaction mechanisms. The DLINQ support the ADO.NET Transaction and the transaction scope. Let's see how we can use it at the following scenarios:

            ADO.NET Transaction

At ADO.NET Transaction we have to handle the entire transaction process. It includes: opening a connection, starting the transaction, commit and rollback when needed, finish the transaction and close the connection.

In a case that the connection object has already been initialized in the DataContext class, we should start the transaction by calling the BeginTransaction(). All the changes that will occur during the current session will be executed in the same transaction.

      DataClasses1DataContext db = new DataClasses1DataContext();

      // Get all customers list

      IQueryable<Customer> list = from x in db.Customers

                                  where x.Code > 0

                                  select x;

      Customer cust1 = list.Single<Customer>(x => x.Code == 1);

      Customer cust2 = list.Single<Customer>(x => x.Code == 2);

      // Update the first customer object

      cust1.FirstName = "Avi";

      cust1.LastName = "Wortzel";

      // Delete the second customer object

      db.Customers.Remove(cust2);

      // The update & delete operation will execte

      // in the same transaction       

      db.Connection.Open();

      db.Transaction = db.Connection.BeginTransaction();

      try

      {

          db.SubmitChanges();

          db.Transaction.Commit();

      }

      catch

      {

          db.Transaction.Rollback();

          // And do some error handling...

      }

      finally

      {

          db.Connection.Close();

          db.Transaction = null;

   }

            TransactionScope

When we are using the TransactionScope the transaction starts as a single transaction and will be promoted to a distributed transaction only if required (you can find more details here). The DataContext has fully integration with the TransactionScope object. The following example will demonstrate how easy is to use the transaction within the TransactionScope section.

      DataClasses1DataContext db = new DataClasses1DataContext();

      // Get all customers list

      IQueryable<Customer> list = from x in db.Customers

                                  select x;

      Customer cust1 = list.Single<Customer>(x => x.Code == 1);

      Customer cust2 = list.Single<Customer>(x => x.Code == 2);

      // Update the first customer object

      cust1.FirstName = "Avi";