Programming ASP.NET 3.5, 4th Edition

Updating with SQL and ADO.NET

There are two aspects to writing web applications that allow users to update data. The first aspect is to provide the user with a form that facilitates data modification. The second is to provide the programmatic support for the update: how do you insert new records, or modify or delete existing records once you know what changes you want to make, if you use the ADO.NET object model directly?

Updating data in a database is simple if you update a single table, but once you update related tables, things get complicated. You can use transactions to ensure the integrity of your data, as we will show shortly.

The simplest way to update the database using ADO.NET objects directly is to generate a SQL Insert, Update, or Delete statement, and execute it using the Command object’s ExecuteNonQuery method. To demonstrate this, you’ll create a new web page called UpdatingDBDirectly.aspx. This will display the contents of the ProductCategory table in a GridView and a small form to allow you to add records from that table, as shown in Figure 9-13. You will also be able to select a row to either edit or delete from the database. An extra bit of complexity here is to ensure that any new categories have a valid parent category (by enforcing the selection through a DropDownList) and that those parent categories cannot be changed (by not including them in the GridView).