Programming C# 3.0, 5th Edition

Deleting Relational Data

Deleting a customer is a bit trickier than adding or modifying one, because the relational database is going to enforce referential integrity. That is, to avoid data inconsistency, the relational database (e.g., SQL Server) is going to ensure that the rows in CustomerAddress be deleted before the rows in Address or Customer are deleted. Ideally, in fact, you'd like the entire deletion of all the related rows, in all the related tables, to be within a transaction so that if any of the deletions fail, the entire set is "rolled back" to its initial state. That way, you don't end up with orphaned address records, or customers who have somehow lost data. (See the sidebar "Data Consistency" later in this chapter.)

The easiest way to do this is to ask the database to help you. After all, this is what databases do for a living. One solution they offer is stored procedures (they offer others, such as cascading deletes, etc.). To create a stored procedure, begin by right-clicking on the Stored Procedures file folder of your data connection and choose Add New Stored Procedure, as shown in Figure 15-10.

Figure 15-10. Adding a new stored procedure

Replace the code in the prototype stored procedure with that shown in Example 15-4. You don't need to fully understand the SQL at this point; you can trust that it will properly delete customers with the given first and last names ...