ASP.net 2.0 Data Tutorial 49: Inserting, updating, and deleting data using SqlDataSource

Return to the "ASP.net 2.0 Data Tutorial directory"

Introduction

As we discussed in the overview inserts, updates, and deletes data, the GridView control provides built-in updates and deletions, and the DetailsView and FormView controls have insert functionality in addition to these. These data modification features can be directly connected to the data source control without the need to write any code. Overview inserting, updating, and deleting data explains how to use ObjectDataSource to help the GridView, DetailsView, and FormView controls complete inserts, updates, and deletions. Where ObjectDataSource can work, SqlDataSource is fine.

Recall that to enable ObjectDataSource to support inserts, updates, and deletions, we need to define some object-layer methods for inserting, updating, and deleting actions. In SqlDataSource, we need to provide insert, UPDATE, and DELETE statements (or stored procedures). As we will see in this tutorial, these statements can be created either manually or automatically by the SqlDataSource Configure Data Source Wizard.

Note: As we have discussed the insertion, editing, and deletion features of the GridView, DetailsView, and FormView controls, we will focus on how to configure SqlDataSource to support these operations in this tutorial. If you need to brush up on how to implement this feature in GridView, DetailsView, and FormView, go back to the section "Edit insert and delete data", starting with an overview of inserting, updating, and deleting data.

First step: Specify INSERT, UPDATE, and DELETE statements

As we saw in the last two tutorials, to get the data from the SqlDataSource control, we need to set two properties:

1.ConnectionString, which specifies the database to which the query should be sent;

2.SelectCommand, which specifies the SQL statement or stored procedure used to return records.

For SelectCommand with parameters, the parameter values are specified by the SqlDataSource SelectParameters collection, which can contain hard-coded values and common parameter sources (such as QueryString, Session, Web control, and so on). You can also assign values programmatically. When the Select () method of the SqlDataSource control is invoked (either programmatically or by the data Web control), a database connection is established, then the parameter values are assigned to the query, and the command is sent to the database. The result is returned as a dataset or DataReader, depending on the value of the control's DataSourceMode property.

In addition to getting data, SqlDataSource can insert, update, and delete data in a very similar way by providing insert, UPDATE, and DELETE statements. Simply assign the insert, UPDATE, and DELETE statements to the InsertCommand, UpdateCommand, and DeleteCommand properties. If the statement has arguments (as is often the case), put these parameters in the InsertParameters, UpdateParameters, and DeleteParameters collections.

Once the value of the InsertCommand, UpdateCommand, or DeleteCommand is specified, the Allow insert, allow edit, or allow delete option in the smart tag of the corresponding data Web control becomes available. To illustrate this issue, we add the deletion function to the querying.aspx that was created in the tutorial using the SqlDataSource Control query data.

First, open insertupdatedelete.aspx and querying.aspx from the SqlDataSource folder. In the Querying.aspx designer, select the SqlDataSource and GridView (that is, Productsdatasource and GridView1) in the first example. After you have selected the two controls, select Copy (or direct CTRL + C) on the Edit menu. Then, go to the designer of insertupdatedelete.aspx and paste the two controls up. After you have the two controls on the insertupdatedelete.aspx, test the page in the browser. You should see the ProductID, ProductName, and UnitPrice of all the records in the Products table.

Figure I: All the products are listed and sorted by ProductID