CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
Note The IsolationLevel property of a transaction object allows you to specify how aggressively a
transaction should be guarded against the activities of other parallel transactions. By default, transactions are
isolated completely until committed. Consult the .NET Framework 4.5 SDK documentation for full details regarding
the values of the IsolationLevel enumeration.
Beyond the members defined by the IDbTransaction interface, the SqlTransaction type defines an
additional member named Save(), which allows you to define save points. This concept allows you to
roll back a failed transaction up until a named point, rather than rolling back the entire transaction.
Essentially, when you call Save() using a SqlTransaction object, you can specify a friendly string
moniker. When you call Rollback(), you can specify this same moniker as an argument to perform an
effective partial rollback. Calling Rollback() with no arguments causes all of the pending changes to be
rolled back.
Adding a CreditRisks Table to the AutoLot Database
Now let’s look at how you use ADO.NET transactions. Begin by using the Server Explorer of Visual Studio
to add a new table named CreditRisks to the AutoLot database, which has the same exact columns as the
Customers table you created earlier in this chapter: CustID, which is the primary key; FirstName; and
LastName. As its name suggests, CreditRisks is where you banish the undesirable customers who fail a
credit check (see Figure 21-15).
Figure 21-15. The interconnected Orders, Inventory, and Customers tables
Like the earlier savings-to-checking money transfer example, this example, where you move a risky
customer from the Customers table into the CreditRisks table, should occur under the watchful eye of a
transactional scope (after all, you will want to remember the ID and names of those who are not
creditworthy). Specifically, you need to ensure that either you successfully delete the current credit risks
from the Customers table and add them to the CreditRisks table, or you need to ensure that neither of
these database operations occurs.
Note In a production environment, you would not need to build a whole new database table to capture high-risk
customers; instead, you could add a Boolean column named IsCreditRisk to the existing Customers table.
However, this new table lets you play with a simple transaction.
855