Accessing and Updating Data in ASP.NET: Inserting Data

An Overview of Inserting Data Using the Data Source Controls

1. The data source's Inserting event is fired
2. The inserting "action" occurs
3. The data source's Inserted event is fired

Let's explore the inserting "action" for the AccessDataSource and SqlDataSource controls in more detail. The INSERT statement or stored procedure specified by the control's InsertCommand uses a parameterized query. That is, if you use an ad-hoc SQL statement for the InsertCommand, that INSERT statement will use parameters like so:

In the Filtering Database Data with Parameters article, we looked at using parameters in the SelectCommand to filter the results, such as SELECT * FROM Products WHERE Price < @UnitPrice. The parameter - @UnitPrice, in this instance - has its value specified via the data source control's SelectParameters, which can specify a source for the parameter value. The source may be: a hard-coded value like "3.95", which would return all products less than $3.95; the value of a Web control on the page, allowing the user to enter a price bound in a TextBox; from the querystring; from session state; and so on.

Likewise, the parameter values in the INSERT statement are assigned based on the parameters in the data source control's InsertParameters collection, and these parameters can use the same sources as the SelectParameters.

The AccessDataSource and SqlDataSource controls, behind the scenes, use the standard ADO.NET classes to perform their data access. That is, they connect to the database using a SqlConnection or OleDbConnection object and specify the command text and parameters via a SqlCommand or OleDbCommand object.

Given this information, the inserting workflow for the AccessDataSource and SqlDataSource can be more specifically expressed as:

1. The data source's Inserting event is fired
2. SqlConnection and SqlCommand (or OleDbConnection and OleDbCommand) objects are created
3. The command object's CommandText property is assigned the data source control's InsertCommand property
4. The parameters in the data source control's InsertParameters collection are added the command object's Parameters collection
5. A connection to the database is established and the command is executed, thereby inserting the data
6. The data source's Inserted event is fired

IDENTITY

Inserting Data Using a Manually-Created Web Form

Imagine that we were tasked with creating a page that allowed users to add new records to the Products table by specifying the new item's name, category, price, and discontinued status. We could create a simple Web Form that included TextBoxes, a DropDownList, and a CheckBox control to collect these input fields, along with an "Add Product" Button control that, when clicked, would insert the new product into the database.

In addition to these user input controls we could also add a SqlDataSource control to handle the actual insertion. We could set this control's InsertCommand to the following INSERT statement:

Note the use of the parameters following the VALUES keyword. These sources for these parameters' values would simply be the user input Web controls on the page. Map these parameters' values in the InsertParameters collection using ControlParameters that point to the appropriate Web controls on that page. There are a variety of ways to accomplish this. From the Designer, click on the SqlDataSource and go to the Properties window. There you will see an InsertQuery option that, if clicked, displays the Command and Parameter Editor shown below. Here you can specify the InsertCommand, the parameters, and their source. Note that each of the four parameters uses a Control as its Parameter source, with the ControlID drop-down list set to the appropriate Web control on the page.

Alternatively, these parameters can be specified via the SqlDataSource control's declarative syntax:

Once the Web controls have been added to the page and the SqlDataSource's InsertCommand and InsertParameters properties have been correctly configured, inserting a new record is as simple as calling the data source control's Insert() method. That is, the only code you need to write is the following line of code (which would be placed in the "Add Product" Button's Click event handler):

Inserting Data Using a DetailsView Control

Start by adding a SqlDataSource control to the page and use the same InsertCommand as in the previous example:

Next, add the parameters to the control's InsertParameters collection. Rather than using ControlParameters, use the default Parameter object. Also, the DetailsView we will create for this demo will not include an interface for the user to specify the category. Therefore, set the corresponding Parameter object's DefaultValue to "1". This will assign every product added through this page to the Beverages category.

Next, add a DetailsView to the page and set its DataSourceID property to the ID of the SqlDataSource control (AddProductDataSource). From the DetailsView's smart tag, check the "Enable Inserting" checkbox. This adds a CommandField to the DetailsView and sets its ShowInsertButton property to True. A CommandField with this configuration displays a "New" Button when the DetailsView control is in its ReadOnly mode. When the "New" Button is clicked, a postback ensues and the DetailsView is shifted into its Insert mode, causing the CommandField to display "Insert" and "Cancel" Buttons.

The SqlDataSource control does not contain a value for its SelectCommand, so no data will be displayed in the DetailsView. In fact, for this example we want the DetailsView to always be in an insertable mode (that is, we don't want to make the user have to click "New" to start adding a new record). Set the DetailsView's DefaultMode property to Insert to indicate that the DetailsView control should display just its inserting interface.

Next, add two BoundFields and a CheckBoxField to the DetailsView, setting the HeaderText and DataField properties so that they are bound to the ProductName, UnitPrice, and Discontinued columns used by the SqlDataSource. Finally, set the AutoGenerateRows property to False.

You can accomplish these two tasks from the Fields dialog box or by manually entering the control's declarative markup. To use the Fields dialog box, click the Edit Fields link from the DetailsView's smart tag. Add the two BoundFields and CheckBoxField and set their properties from the list on the right. To set the AutoGenerateRows property to False, simply uncheck the "Auto-generate fields" checkbox in the lower left corner.

Alternatively, you can specify the DetailsView's fields and set the AutoGenerateRows property to False through the declarative syntax:

That's all there is to it! When a user visits this page and enters the name, price, and discontinued status of a product and clicks Insert, a postback occurs. The DetailsView automatically assigns the values assigned to its input controls to the SqlDataSource's corresponding InsertParameters before starting the inserting workflow. The net result is that a new record is inserted into the database without writing a lick of code and without having to manually map the SqlDataSource's InsertParameters to their sources (since the DetailsView does this for us automatically when Insert is clicked).

This example illustrates only the simplest facilities of the DetailsView and omits important steps in a real-world application, such as input validation and customizing the inserting interface. For example, since the ProductName column is required, the insert will fail if the user leaves this textbox empty. Likewise, if the user puts in an invalid unit price value (like "expensive!"), an error will occur when attempting to insert the illegal value into the database. The download at the end of this article includes another DetailsView example that illusrated adding validation controls and customizing the inserting interface to prompt the user for the new product's category via a drop-down list of available category choices. For more information on customizing the DetailsView's inserting interface, see Customizing the Data Modification Interface (VB Version) (C# version).

Inserting and Returning the Value of the Just-Inserted Record's IDENTITY Column

When using IDENTITY columns a common requirement is to be able to retrieve the value of the just-inserted record's IDENTITY column. Perhaps after inserting a new record you want to automatically whisk the user to a details page where you need to pass along the IDENTITY column value through the querystring, or maybe you want to add additional records in a child table and need the just-inserted parent record's IDENTITY column value to properly link the child records to the parent. In either case, in Microsoft SQL Server you can use the SCOPE_IDENTITY() keyword to get the IDENTITY column value of the just-insert record.

In order to pull back this information when using the SqlDataSource we need to do the following:

1. Create a stored procedure that returns the just-inserted record's IDENTITY column value using an OUTPUT parameter. See Retrieving Scalar Data from a Stored Procedure for more information on this topic.
2. Configure the SqlDataSource to use this stored procedure. This involves updating the InsertCommand to the name of the stored procedure created from step 1, setting the data source control's InsertCommandType to StoredProcedure, and adding an output parameter to the InsertParameters collection.
3. To access the resulting output parameter's value we need to create an event handler for the SqlDataSource's Inserted event. Recall that this event fires after the insert "action" has been performed. Once we have the IDENTITY value of the just-inserted record we can use it as needed.

Next, update the SqlDataSource to use the AddProductAndReturnNewProductIDValue as its InsertCommand instead of an ad-hoc SQL statement. Also, add an ouput parameter to the InsertParameters collection. Note that the output parameter in the InsertParameters collection is a Parameter object whose Direction property is set to Output:

Adding an output parameter to the SqlDataSource's InsertParameters collection adds an output parameter to the Parameters collection of the internal SqlCommand object used by the data source control during the inserting "action." The value of this parameter can be examined in the Inserted event handler. As the following event handler code shows, the internal SqlCommand object is accessible through the e.Command property in the event handler. Here we can grab the specific parameter instance and insepct its Value property to determine the IDENTITY column value of the just-inserted record:

Conclusion

Until then... Happy Programming!