Transactions

I have already told you that when the SubmitChanges method is called, if a transaction is not already in scope, the SubmitChanges method will create a transaction for you. In doing so, all database modifications attempted during a single SubmitChanges call will be wrapped within a single transaction. This is very convenient, but, what if you need the transaction to extend beyond the scope of a single SubmitChanges method call?

I want to provide an example demonstrating how you would make updates made by multiple SubmitChanges method calls enlist in the same transaction. Even better, I want the SubmitChanges method calls to be updating different databases. In Listing 18-6, I will make changes to a record in both the Northwind database and the TestDB database I just created in the "Entity Class Inheritance" section. Normally, each call to the SubmitChanges method on each of those DataContext objects would be wrapped in its own individual transaction. In my example, I want both calls to the SubmitChanges method to be enlisted in the same transaction.

Since Listing 18-6 will have a little more going on than the typical example does, I will explain it as I go.

Example. Enlisting in Ambient Transactions

Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
TestDB testDb = new TestDB(@"Data Source=.SQLEXPRESS;Initial Catalog=TestDB");

Customer cust = db.Customers.Where(c => c.CustomerID == "LONEP").SingleOrDefault();
cust.ContactName = "Barbara Penczek";

Rectangle rect = (Rectangle)testDb.Shapes.Where(s => s.Id == 3).SingleOrDefault();
rect.Width = 15;

In the preceding code, I create my DataContext object for each database. I then query an entity object from each, and make a change to each entity object.

try
{
  using (System.Transactions.TransactionScope scope =
    new System.Transactions.TransactionScope())
  {
    db.SubmitChanges();
    testDb.SubmitChanges();
    throw (new Exception("Just to rollback the transaction."));
    //  A warning will result because the next line cannot be reached.
    scope.Complete();
  }
}
catch (Exception ex)
{
  Console.WriteLine(ex.Message);
}

In the preceding code, I instantiate a TransactionScope object so that there is an ambient transaction for the DataContext objects to enlist in for each call to the SubmitChanges method. After I call the SubmitChanges method on each DataContext, I intentionally throw an exception so that the scope.Complete method is not called and the transaction is rolled back.

Had I not wrapped the calls to the SubmitChanges method within the scope of the TransactionScope object, each SubmitChanges method call would have had its own transaction and its changes would have been committed once the call successfully completed.

Once the exception is thrown in the preceding code, the transaction goes out of scope, and since the Complete method was not called, the transaction is rolled back. At this point, all of the changes made to the database have been rolled back.

db.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, cust);
Console.WriteLine("Contact Name = {0}", cust.ContactName);

testDb.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, rect);
Console.WriteLine("Rectangle Width = {0}", rect.Width);

It is important to remember that, even though the changes were not successfully persisted to the database, the entity objects still contain the modified data. Remember, even when the SubmitChanges method does not complete successfully, the changes are maintained in the entity objects so that you can resolve concurrency conflicts and call the SubmitChanges method again. In this case, the SubmitChanges methods even completed successfully. Also, as you may recall from my section titled "The Results Set Cache Mismatch" in Chapter 16, querying the objects from the database again will not result in getting the current values from the database. The database query will only determine which entities should be included in the results set for the query. If those entities are already cached in the DataContext, the cached entity objects will be returned. So, to truly know what the values for the previously queried entity objects are in the database, the entity objects must first be refreshed by calling the Refresh method.

So, for each of the two retrieved entity objects, I first refresh it and then display to the console the entity object property I changed to prove that the changes were indeed rolled back. Let's look at the results:

Just to rollback the transaction.
Contact Name = Fran Wilson
Rectangle Width = 11

As you can see, the values were rolled back in the database.

If you get an exception of type "MSDTC on server `[server]SQLEXPRESS' is unavailable" when working with any of the examples using the TransactionScope object, make sure the service named Distributed Transaction Coordinator is started.