Updating, Deleting, and Inserting Data with the SqlDataSource

In Hour 14, "Accessing Data with the Data Source Web Controls," we saw how to use the SqlDataSource control to retrieve data from a SQL Server 2005 Express Edition database. Then, in the preceding hour, we saw how to use ASP.NET's data Web controls to display that data. The data source control and data Web control architecture provides a clean separation between accessing data and working with it.

In addition to being able to retrieve data, the data source controls can also be used to modify the underlying data. The SqlDataSource control can be configured to insert new records into the database and delete or modify existing records. After the SqlDataSource has been configured to support inserting, updating, and deleting, this functionality can be utilized by the GridView and DetailsView controls, allowing users not only to view, but also update, insert, and delete data from a database via a web page.

Adding support for inserts, updates, and deletions with the SqlDataSource control is as easy as checking a check box. To illustrate this process, take a moment to create a new ASP.NET web page called RichDataSourceEx.aspx. Next, drag on a SqlDataSource control and configure the data source. As before, select the appropriate database or the connection string in the first step and proceed to the Configure the Select Statement screen.

Make sure the Books table is selected in the drop-down list and then check the * option to retrieve all columns from the table. At this point you have configured the SqlDataSource control to retrieve all records and columns from the Books table using the query SELECT * FROM [Books], just like we did in Hour 14. To set up the SqlDataSource to support updating, deleting, and inserting, click the Advanced button, which will bring up the Advanced SQL Generation Options dialog box (see Figure 16.1).

Figure 16.1. Add inserting, updating, and deleting support from the Advanced SQL Generation Options dialog box.

This dialog box has two check boxes: Generate INSERT, UPDATE, and DELETE Statements and Use Optimistic Concurrency. To enable the SqlDataSource's capabilities for inserting, updating, and deleting data, simply check the first check box. As we'll see in the section "Looking at the Data Modification SQL Statements," in addition to the SELECT statement, SQL includes INSERT, UPDATE, and DELETE statements for inserting, updating, and deleting data. When we check the Generate INSERT, UPDATE, and DELETE Statements check box, the SqlDataSource automatically creates these data modification SQL statements in addition to the SELECT statement.

By the Way

The Generate INSERT, UPDATE, and DELETE Statements check box will be selectable only if the database table you selected in the Configure the Select Statement screen returns the table's primary key. The SqlDataSource wizard can automatically generate INSERT, UPDATE, and DELETE statements only when the SELECT statement provides a means to uniquely identify each row. For our example, you must include BookID in the column list, either by selecting * (which will return all columns) or checking the BookID column name.

If you forgot to make the BookID column in the Books table a primary key column, take a moment to review the discussion on the creation of the Books table and the role of primary key columns in Hour 13, "An Introduction to Databases."

The second check box, Use Optimistic Concurrency, is selectable only if the first check box is checked. If checked, it allows updates and deletions to occur only if the data being updated or deleted has not changed since the data was last accessed from the database. That probably doesn't make a whole lot of sense at this point, but don't fret; it's a topic we'll return to later in this hour after we've actually built an ASP.NET page that provides support for modifying data.

For now, just check the first check box, Generate INSERT, UPDATE, and DELETE Statements, leaving the Use Optimistic Concurrency check box unchecked. Click the OK button to return to the Configure the Select Statement screen. Click Next to go to the Test Query screen and then Finish to complete the SqlDataSource wizard.

Looking at the SqlDataSource Control's Declarative Markup

After you've completed the SqlDataSource control's wizard, take a minute to view the declarative markup generated by the wizard. Go to the Source view; you should see markup identical to that shown in Listing 16.1.

Listing 16.1. The SqlDataSource Control's Markup Contains Commands for Deleting, Inserting, and Updating

[View full width]

 1: <asp:SqlDataSource  runat="server" ConnectionString="<%$  ConnectionStrings:ConnectionString %>"  2:     DeleteCommand="DELETE FROM [Books] WHERE [BookID] = @BookID" InsertCommand="INSERT  INTO [Books] ([Title], [Author], [YearPublished], [Price], [LastReadOn], [PageCount])  VALUES (@Title, @Author, @YearPublished, @Price, @LastReadOn, @PageCount)"  3:     SelectCommand="SELECT * FROM [Books]" UpdateCommand="UPDATE [Books] SET [Title] =  @Title, [Author] = @Author, [YearPublished] = @YearPublished, [Price] = @Price,  [LastReadOn] = @LastReadOn, [PageCount] = @PageCount WHERE [BookID] = @BookID">  4:     <DeleteParameters>  5:         <asp:Parameter Name="BookID" Type="Int32" />  6:     </DeleteParameters>  7:     <UpdateParameters>  8:         <asp:Parameter Name="Title" Type="String" />  9:         <asp:Parameter Name="Author" Type="String" /> 10:         <asp:Parameter Name="YearPublished" Type="Int32" /> 11:         <asp:Parameter Name="Price" Type="Decimal" /> 12:         <asp:Parameter Name="LastReadOn" Type="DateTime" /> 13:         <asp:Parameter Name="PageCount" Type="Int32" /> 14:         <asp:Parameter Name="BookID" Type="Int32" /> 15:     </UpdateParameters> 16:     <InsertParameters> 17:         <asp:Parameter Name="Title" Type="String" /> 18:         <asp:Parameter Name="Author" Type="String" /> 19:         <asp:Parameter Name="YearPublished" Type="Int32" /> 20:         <asp:Parameter Name="Price" Type="Decimal" /> 21:         <asp:Parameter Name="LastReadOn" Type="DateTime" /> 22:         <asp:Parameter Name="PageCount" Type="Int32" /> 23:     </InsertParameters> 24: </asp:SqlDataSource> 

In Hour 14 we saw the declarative markup of the SqlDataSource when it was configured to issue just a SELECT statement, and it was much simpler than the 24 lines of markup shown in Listing 16.1. However, the simplicity from Hour 14 was possible because we were only retrieving data from a database; here, we need to include markup that specifies how to also insert, update, and delete that data.

In addition to the SelectCommand on line 3, there are three additional command statements on lines 2 and 3: DeleteCommand, InsertCommand (both on line 2), and UpdateCommand (line 3). These command statements specify the SQL statements that will be passed to the underlying database when inserting, updating, or deleting the data. In addition to these command statements, there are a number of related parameters, spanning from line 4 through line 23. Recall that the SQL statements in a SqlDataSource control can use parameters, which are placeholders for values to be inserted at a later point in time. The parameters are denoted in the command statement using @ParameterName.

The parameters are further defined in the <DeleteParameters>, <UpdateParameters>, and <InsertParameters> sections. For example, in the DeleteCommand statement, a single parameter, @BookID, is used to uniquely identify the row that's to be deleted. Then, in the <DeleteParameters> section (lines 46), a single <asp:Parameter> element identifies that the BookID parameter is an integer. Notice, however, that no value is specified for this parameter. If you'll remember back to Hour 14 when we used the SqlDataSource wizard's capabilities to add filter expressions, we provided a hard-coded value for the WHERE clause. This hard-coded value then appeared in the associated <asp:Parameter> element.

The <asp:Parameter>s in the <DeleteParameters>, <UpdateParameters>, and <InsertParameters> sections, however, lack any hard-coded values. The reason is that their values will be determined at runtime. That is, the value of the @BookID parameter for the DeleteCommand will depend on what row the user has decided to delete; that particular row's BookID will be used as the value for the @BookID parameter.

Later in this hour we'll be looking at how to work with the GridView and DetailsView controls to insert, update, and delete data. At that point it will become clearer how visitors will indicate what record they want to delete or update and how the data Web control plugs that information into the data source control. For now, though, just understand that checking the Generate INSERT, UPDATE, and DELETE Statements check box in the Advanced SQL Generation Options dialog box causes the data source control to provide commands and parameters for inserting, updating, and deleting the data specified in the Configure the Select Statement screen of the wizard.