CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
disconnected layer of ADO.NET (see Chapter 22) generates strongly typed DataSet objects to represent
data from a given table in a relational database.
Note As you might know, building a SQL statement using string concatenation can be risky from a security
point of view (think: SQL injection attacks). The preferred way to build command text is to use a parameterized
query, which you will learn about shortly.
Adding the Deletion Logic
Deleting an existing record is as simple as inserting a new record. Unlike when you created the code for
InsertAuto(), this time you will learn about an important try/catch scope that handles the possibility of
attempting to delete a car that is currently on order for an individual in the Customers table. Add the
following method to the InventoryDAL class type:
public void DeleteCar(int id)
{
// Get ID of car to delete, then do so.
string sql = string.Format("Delete from Inventory where CarID = '{0}'",
id);
using(SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
try
{
cmd.ExecuteNonQuery();
}
catch(SqlException ex)
{
Exception error = new Exception("Sorry! That car is on order!", ex);
throw error;
}
}
}
Adding the Update Logic
When it comes to the act of updating an existing record in the Inventory table, the first thing you must
decide is what you want to allow the caller to change, whether it’s the car’s color, the pet name, the
make, or all of the above. One way to give the caller complete flexibility is to define a method that takes a
string type to represent any sort of SQL statement, but that is risky at best.
Ideally, you want to have a set of methods that allow the caller to update a record in a variety of
ways. However, for this simple data access library, you will define a single method that allows the caller
to update the pet name of a given automobile, like so:
public void UpdateCarPetName(int id, string newPetName)
{
// Get ID of car to modify and new pet name.
string sql = string.Format("Update Inventory Set PetName = '{0}' Where CarID = '{1}'",
841