Databases Reference
In-Depth Information
In a web application, however, it is often sufficient to handle the optimistic concurrency
errors by simply displaying them to the user and asking him to either refresh the current
or go back to the previous page, which automatically discards the changes he made and
reloads the latest version of the entity from the database.
Transactions
Transactions help applications to make sure that any changes leave the database in a
consistent state. If multiple changes are made as part of a single transaction, the database
server ensures that either all of them complete successfully or any incomplete changes are
rolled back to the state before the transaction began. By default, database servers, and
Microsoft SQL Server in particular, execute each SQL statement in a separate transaction.
Although this behavior is sufficient to ensure that any single request leaves the database
in a consistent state, when multiple records need to be modified, a transaction that spans
multiple requests is required.
As an example, let's consider a scenario of adding a new product to the Northwind data-
base. Perhaps this is done by an automated inventory import process, reading data from a
spreadsheet or a flat file. The file contains a list of products along with their categories,
and the process needs to link each new product to an existing category or create a new
category if it doesn't already exist. Here is how to implement it using Entity Framework
(the actual values are hard-coded here as an illustration, of course):
Category wines = context.Categories.First(c => c.CategoryName == “Wines”);
if (wines == null)
{
wines = new Category() { CategoryName = “Wines” };
context.Categories.AddObject(wines);
}
Product p = new Product() { ProductName = “Mukuzani”, Category = wines };
context.Products.AddObject(p);
context.SaveChanges();
Notice that it is possible for this code to create two entities, one for a new Product and
one for a new Category. Both of these entities are submitted to the database when the
SaveChanges
method of the
ObjectContext
is called. However, if for any reason a new
Product cannot be saved (perhaps there is already another product with this name in our
database), you don't want to have an orphaned Category without any Products in it. To
implement this requirement, you can submit both the new Category and the new Product
as part of a single transaction so that if the new Product cannot be inserted, the new
Category is removed as well.
using (TransactionScope transaction = new TransactionScope())
using (NorthwindEntities context = new NorthwindEntities())
{
