CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
Adding a Transaction Method to InventoryDAL
Now let’s look at how you work with ADO.NET transactions programmatically. Begin by opening the
AutoLotDAL code library project you created earlier and add a new public method named
ProcessCreditRisk() to the InventoryDAL class to deal with perceived a credit risks (note that this
example avoids using a parameterized query to keep the implementation simple; however, you’d want
use such a query for a production-level method).
// A new member of the InventoryDAL class.
public void ProcessCreditRisk(bool throwEx, int custID)
{
// First, look up current name based on customer ID.
string fName = string.Empty;
string lName = string.Empty;
SqlCommand cmdSelect = new SqlCommand(
string.Format("Select * from Customers where CustID = {0}", custID), sqlCn);
using (SqlDataReader dr = cmdSelect.ExecuteReader())
{
if(dr.HasRows)
{
dr.Read();
fName = (string)dr["FirstName"];
lName = (string)dr["LastName"];
}
else
return;
}
// Create command objects that represent each step of the operation.
SqlCommand cmdRemove = new SqlCommand(
string.Format("Delete from C ustomers where CustID = {0}", custID), sqlCn);
SqlCommand cmdInsert = new SqlCommand(string.Format("Insert Into CreditRisks" +
"(CustID, FirstName, LastName) Values" +
"({0}, '{1}', '{2}')", custID, fName, lName), sqlCn);
// You will get this from the connection object.
SqlTransaction tx = null;
try
{
tx = sqlCn.BeginTransaction();
// Enlist the commands into this transaction.
cmdInsert.Transaction = tx;
cmdRemove.Transaction = tx;
// Execute the commands.
cmdInsert.ExecuteNonQuery();
cmdRemove.ExecuteNonQuery();
// Simulate error.
if (throwEx)
856