CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
{
throw new Exception("Sorry!
Database error! Tx failed...");
}
// Commit it!
tx.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
// Any error will roll back transaction.
tx.Rollback();
}
}
Here, you use an incoming bool parameter to represent whether you will throw an arbitrary
exception when you attempt to process the offending customer. This allows you to simulate an
unforeseen circumstance that will cause the database transaction to fail. Obviously, you do this here
only for illustrative purposes; a true database transaction method would not want to allow the caller to
force the logic to fail on a whim!
Note that you use two SqlCommand objects to represent each step in the transaction you will kick off.
After you obtain the customer’s first and last name based on the incoming custID parameter, you can
obtain a valid SqlTransaction object from the connection object using BeginTransaction(). Next, and
most importantly, you must enlist each command object by assigning the Transaction property to the
transaction object you have just obtained. If you fail to do so, the Insert/Delete logic will not be under a
transactional context.
After you call ExecuteNonQuery() on each command, you throw an exception if (and only if) the
value of the bool parameter is true. In this case, all pending database operations are rolled back. If you
do not throw an exception, both steps will be committed to the database tables once you call Commit().
Now compile your modified AutoLotDAL project to ensure you do not have any typos.
Testing Your Database Transaction
You could update your previous AutoLotCUIClient application with a new option to invoke the
ProcessCreditRisk() method; instead, however, you will create a new Console Application named
AdoNetTransaction to accomplish this. Set a reference to your AutoLotDAL.dll assembly and import the
AutoLotConnectedLayer namespace.
Next, open your Customers table for data entry by right-clicking the table icon from the Server
Explorer and selecting Show Table Data. Now add the following new customer who will be the victim of
a low credit score:
•
CustID: 333
•
FirstName: Homer
•
LastName: Simpson
Finally, update your Main() method as follows:
static void Main(string[] args)
{
Console.WriteLine("***** Simple Transaction Example *****\n");
857