Sams Teach Yourself ASP.NET 2.0 in 24 Hours, Complete Starter Kit

As we discussed in Hour 14, SQL is the language used by all modern databases to retrieve and modify data. We've already examined the SELECT statement, which is used to retrieve data. Let's now turn our attention to three other SQL statements: INSERT, UPDATE, and DELETE. These three statements are automatically generated by the SqlDataSource control when appropriately configured and, as you can probably guess, are instrumental in modifying a database's content.

A thorough understanding of the INSERT, UPDATE, and DELETE statements is not required. After all, the SqlDataSource will merrily create the necessary statements for you; all you need to do is check the appropriate check box! However, I do think it is worthwhile to have at least a cursory understanding of the syntax and semantics of these statements.

If you are already familiar with the basics of these three SQL statements, feel free to skip this section because it is intended for readers who are new to SQL. In the section "Editing and Deleting Data with the GridView," we'll begin our examination of modifying data through an ASP.NET page.

Examining the INSERT Statement

The INSERT statement, as its name implies, inserts a new record into a database table. The general syntax is as follows:

INSERT INTO TableName(Column1, Column2, ..., ColumnN) VALUES(Column1Value, Column2Value, ..., ColumnNValue)

Here Column1, Column2, ..., ColumnN is a comma-delimited list of the column names of the table whose values you are providing in the VALUES portion. You do not want to include column names for any Auto-increment columns; furthermore, you may optionally leave off a column if it either has a default value specified or accepts Nulls, in which case the default value will be used if it exists; otherwise, a Null value will be inserted. The Column1Value, Column2Value, ..., ColumnNValue is the place where you specify the values for the columns listed.

Now that we've discussed the general form of the INSERT statement, let's look at a more concrete example. The following INSERT statement was generated by the SqlDataSource control's wizard (refer to Listing 16.1, line 2):

INSERT INTO [Books] ([Title], [Author], [YearPublished], [Price], [LastReadOn], [PageCount]) VALUES (@Title, @Author, @YearPublished, @Price, @LastReadOn, @PageCount)

Note that the INSERT statement adds a new record to the Books table and provides values for all fields except for BookID. BookID is omitted from the column list because it is an Auto-increment column, which means that the database system will supply the value. (Providing a value for BookID will result in an error when the code is executed.)

Rather than providing specific values, the InsertCommand uses parameters@Title, @Author, @YearPublished, and so on. As we will see later in this hour, the DetailsView can be used to collect the visitor's input and use that input to insert a new record into the underlying database table. Specifically, when the visitor provides the data, the DetailsView control assigns it to the appropriate parameters of its data source control and then invokes the data source control's InsertCommand.

Deleting Data with the DELETE Statement

The general form of the DELETE statement is as follows:

DELETE FROM TableName WHERE whereCondition

The WHERE condition is optional, but you'll almost always want to include it because the DELETE statement deletes all records from the table TableName that match the WHERE condition. Therefore, if you omit the WHERE clause, all records from the table TableName will vanish into thin air!

Most commonly, the DELETE statement is used to delete one record at a time. For example, the DeleteCommand used by the SqlDataSource has a WHERE clause that's based on the Books table's primary key column, BookID. (Remember that the primary key column is what uniquely identifies each row in the table.)

DELETE FROM [Books] WHERE [BookID] = @BookID

In some circumstances you might want to relax the WHERE condition in the DELETE statement to delete multiple records. For example, if you wanted to delete all books that were published in 2005, you could use this statement:

DELETE FROM [Books] WHERE YearPublished = 2005

This might delete zero books, one, three, ... maybe all of them. The result would depend on the values of the YearPublished column.

Editing Data with UPDATE

The UPDATE statement is used to change the values of existing rows. With UPDATE you can specify what columns to change to what values; like the DELETE statement, the UPDATE statement contains a WHERE clause that specifies the scope of the update. That is, the UPDATE statement can be used to update a single row (which is how it is most commonly used), or it can be used to update a batch of records.

The general form of the UPDATE statement is as follows:

UPDATE TableName SET Column1 = Column1Value, Column2 = Column2Value, ... ColumnN = ColumnNValue, WHERE whereCondition

The column/value list after the UPDATE TableName SET portion indicates what columns' values are being changed and to what values. The whereCondition indicates what rows this update applies to; as with the DELETE statement, if you omit the WHERE clause, the update applies to all records in the table.

The UpdateCommand in Listing 16.1 (line 3) updates a single record in the Books table and updates the value of all columns except for the Auto-increment column:

UPDATE [Books] SET [Title] = @Title, [Author] = @Author, [YearPublished] = @YearPublished, [Price] = @Price, [LastReadOn] = @LastReadOn, [PageCount] = @PageCount WHERE [BookID] = @BookID

The WHERE clause is based on the primary key column, so only one record from the database table will be updated with this statement.

At this point we have examined how to configure a SqlDataSource to generate the INSERT, UPDATE, and DELETE commands through its wizard and have taken a cursory look at the related SQL statements. We're now ready to turn our attention to configuring the data Web controls to insert, update, and delete data. In the next section, "Editing and Deleting Data with the GridView," we'll see how to use the GridView to edit and delete data. Further on, in the "Inserting Data with the DetailsView" section, we'll see how to insert data using the DetailsView.