Accessing and Updating Data in ASP.NET: Updating BasicsBy Scott Mitchell
ASP.NET 2.0's data source controls make it possible to retrieve, insert, update, and delete data without having to write the tedious dedata access code that was so commonplace in ASP.NET version 1.x. Previous installments of this article series have looked at returning, inserting, and deleting data using the SqlDataSource control. However, we have yet to look at updating data.
In addition to the SqlDataSource control, ASP.NET 2.0 introduces three new data Web controls: the GridView, the DetailsView, and the FormView. These controls can work in unison with the data source controls to facilitate inserting, updating, and deleting data. In this article we will examine basics of using the SqlDataSource and GridView controls to update records from a database. In particular, we look at enabling editing support in the GridView. Future installments will explore more advanced updating scenarios, such as customizing the GridView's editing interface and programmatically setting the updated values. Read on to learn more!
An Overview of Updating Data Using the Data Source Controls
The AccessDataSource, SqlDataSource, and ObjectDataSource controls all support inserting, updating, and deleting capabilities. In a nutshell, all three have a
Updatemethod that, when invoked, kicks off the following updating workflow:
- The data source's
Updatingevent is fired
- The updating "action" occurs
- The data source's
Updatingevent is fired
UPDATEstatement specified by the control's
UpdateCommandproperty. For the ObjectDataSource, the updating "action" involves creating an instance of the data source's underlying object and invoking the specified
UpdateMethod. This article focuses on the SqlDataSource control; for a detailed look at updating with the ObjectDataSource, refer to the "Editing, Inserting, and Deleting" tutorials in my Working with Data in ASP.NET 2.0 tutorial series.
Let's explore the updating "action" for the AccessDataSource and SqlDataSource controls in more detail. The
UPDATE statement or stored procedure specified by the control's
UpdateCommand uses a
parameterized query. That is, if you use an ad-hoc SQL statement for the
statement will use parameters like so:
UPDATE statements look like the one above: they specify a table name, a set of columns to update, the values to use
when updating the columns, and (usually) a single condition in their
WHERE clause specifies a particular primary key value to update. In the case where a table has a composite primary key
(i.e., a primary key made up of multiple columns), then the
WHERE clause would compare each key column, 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
UPDATE statement are assigned based on the parameters in the data source
control's UpdateParameters collection, and these parameters can use the same sources as the SelectParameters.
|Using Less Rigid |
In certain situations, the |
Alternatively, you could give every employee a 10% raise by omitting the
However, such encompassing
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
object and specify the command text and parameters via a
Given this information, the updating workflow for the AccessDataSource and SqlDataSource can be more specifically expressed as:
- The data source's
Updatingevent is fired
OleDbCommand) objects are created
- The command object's
CommandTextproperty is assigned the data source control's
- The parameters in the data source control's UpdateParameters collection are added the command object's
- A connection to the database is established and the command is executed, thereby deleting the data
- The data source's
Updatedevent is fired
Updating, in the case of the updating workflow); creation of the ADO.NET objects and execution of the appropriate command with the appropriate set of parameters (
UpdateCommandand UpdateParameters, respectively, in the case of the updating workflow); and firing of the appropriate post-action event (
Updated, in the case of the updating workflow).
Using Data Web Controls to Update Data
When we examined inserting data and deleting data we looked at doing so both through manually-created Web Forms and through data Web controls, like the GridView or DetailsView. In the manually-created Web Forms example for inserting, for example, we added TextBoxes, DropDownLists, and other Web controls to collect the values for the new record. A SqlDataSource control was added whose InsertParameters referred to these controls. The Web Form also included a Button whose
Clickevent handler simply called the SqlDataSource control's
Insertmethod, thereby kicking off the inserting workflow.
Updating data through manually-created Web Forms is a bit more difficult and less practical because the Web controls that make up the updating
interface must also be populated with the values of the record being edited. For example, to let a visitor edit a database record you first need
to let them choose which record to edit. Once they've selected this record, you need to show it's existing values in editable fields (textboxes,
cehckboxes, and so on), let the user make any modifications, and then save them. We can easily create the Web controls necessary to
facilitate an editing interface and it is simple enough to use a SqlDataSource control with appropriate UpdateParameters to execute
UPDATE statement with values based on the Web controls' values. However, what is more challenging is loading the
selected record's values into the Web controls. Granted, this is not that difficult to accomplish, but it isn't as easy as pointing and
clicking and requires the page developer to write a bit of code.
Fortunately, ASP.NET 2.0's data Web controls can provide an editing interface. In this article we will look at using the GridView control to update data, although the DataList, DetailsView, and FormView controls are also suitable choices.
Using the GridView Control to Facilitate Updating
The GridView control makes displaying, editing, and deleting data as easy as pointing and clicking. To demonstrate this functionality, let's look at using a GridView to list all of the records from the
Productstable in the Northwind database in a GridView and then make the grid editable.
Start by adding a SqlDataSource control to the page and specify its
SelectCommand such that it returns the
Discontinued fields from the
table and the corresponding
CategoryName value from the
Categories table. That is, configure the SqlDataSource so
SelectCommand contains the following query:
SelectCommand can be specified through the data source's wizard, via the SelectQuery property in the Properties window,
or declaratively. Previous installments in this article series have covered these steps in detail.
With the SqlDataSource control's
SelectCommand specified, we are ready to specify its
UpdateCommand. This can be done
manually through the UpdateQuery property in the Properties window or by entering the statement and UpdateParameters declaratively.
Enter the following
UPDATE statement for the
Next, we need to add parameters to the UpdateParameters collection for each of the parameters above (
and so forth). Add a Parameter object for each of the parameters. If you are configuring this information via the UpdateQuery property from the
Properties window, then your screen should look like the following:
The SqlDataSource's resulting declarative markup should look like so:
Note that each parameter in the UpdateParameters collection is a Parameter object. Recall that when using manually-created Web Forms in the inserting and deleting scenarios, we would use ControlParameter objects that referenced the particular Web control that held the parameter's value. When editing with a GridView we'll
|Autogenerating the |
Rather than manually specifying the |
For these reasons, I usually prefer to manually construct the
Next, add a GridView to the page and bind it to the SqlDataSource. Visual Studio will automatically add BoundFields for the
UnitPrice fields and a CheckBoxField for the
Discontinued field (since it is a bit field).
At this point, we have a page with a GridView that displays the records from the
Products table. If you view this page in a browser,
your screen should look similar to the following screenshot:
Making the GridView Editable
Making the GridView editable is as simple as checking a checkbox. If the GridView's underlying data source has an
UpdateCommandspecified (as our's does), then the GridView's smart tag will include a checkbox titled "Enable Editing." Check this checkbox to enable editing. What checking this checkbox does is add a CommandField to the GridView's field list. The CommandField is a column that shows Edit-related or Delete-related buttons, depending on its property settings. Checking "Enable Editing" adds a CommandField with its
ShowEditButtonproperty set to True. This displays a column of Edit buttons.
When the visitor clicks the Edit button that row becomes editable. The Edit button disappears and in its place Update and Cancel buttons appear. Furthermore, the columns in the GridView become editable, too. In particular, BoundFields turn into TextBox Web controls; CheckBoxFields, which are displayed as unclickable checkboxes in the read-only status, become clickable. This interface allows the user to make changes to the selected row's values.
If the Cancel button is clicked, the row reverts to its pre-editing interface and any changes are not saved. If the Update button is clicked, the SqlDataSource's updating workflow begins, thereby updating the database with the user's changes. After updating the data, the GridView is returned to its pre-editing state.
To illustrate the GridView's editing capabilities, let's step through the editing process from the end user's perspective. The screenshot below shows the GridView when one of its record's Edit buttons has been clicked. There has been a postback and the selected row is now editable. The Edit button has been replaced with Update and Cancel buttons and the row is now shown as TextBoxes and an enabled CheckBox.
As you can see in the screenshot above, the
ProductID column is not editable. The BoundField has a property
ReadOnly. If it
is set to True then the field does not become editable. When we bound the SqlDataSource to the GridView it identified that
IDENTITY column (i.e., one whose values are automatically assigned by the database) and therefore set its
property to True.
Now go ahead and change the values of the
ProductName column and click Update. Clicking Update
causes a postback. On postback, the GridView takes the values from the input controls in its editing interface and automatcially populates
those into the corresponding UpdateParameters. It then automatically starts the SqlDataSource's updating workflow, which causes the
database to be updated. The updated data is then rebound to the GridView.
To recap: configuring the GridView to support editing simply adds a CommandField with its
property set to True. When a particular record in the grid is edited, the GridView's columns switch to their editing interface.
This causes the BoundFields to be rendered as TextBoxes and CheckBoxFields as clickable checkboxes. Moreover, the Edit button is replaced
by two new buttons: Update and Cancel. Clicking Update causes a postback that saves the changes to the underlying database.
Shortcomings of the GridView's Default Editing Interface
While the GridView's default editing interface makes editing data as easy as checking a checkbox, the default editing interface is somewhat lacking. If a table being edited has foreign keys to other tables - like the
Productstable - then the default editing interface is rather lacking. To see why, try editing a record and changing its category. How do you do this? You might think that you could just change the
CategoryNamevalue. Go ahead and edit a record and put in a different value for its
CategoryNamefield and then click Update. Despite the change, the
CategoryNamevalue is the same as it was prior to editing the row. This is because there is no
@CategoryNameparameter in the SqlDataSource and because the
Productsdatabase table doesn't even have a
CategoryNamecolumn, as it comes from the
Categoriestable. If you want to change a product's category, you need to update the
CategoryIDvalue. Currently, the
CategoryIDvalue's editing interface is rendered as a TextBox, meaning if you want to change a product's category from, say, Beverages to Dairy, you need to change its
CategoryIDvalue from 1 to 4.
Having to change the
CategoryID column is annoying because it means the user has to know that a
of 1 corresponds to Beverages and a
CategoryID of 4 corresponds to Dairy. Instead, we should replace the
TextBox with a DropDownList listing the available categories. We'll examine how to do this in a future installment of this series.
For now, just leave the
CategoryID as a TextBox, but set the
to True (since modifications to this column are ignored).
Moreover, the default editing interface lacks any input validation. For example, if you omit the
ProductName value and click Update,
a database-level exception will be thrown because the
ProductName column does not allow
NULLs. Similarily, if you
CategoryID value outside of the range of valid
CategoryIDs or if you enter a non-numeric
It is possible to add validation Web controls to the GridView's editing interface. We'll see how to accomplish this in a future installment
In this article we looked at how to use the SqlDataSource and GridView controls to update data from a database. The SqlDataSource control encapsulates many of the data access tasks, such as connecting to the database, creating the command object, and executing the parameterized query. Likewise, the GridView control handles the task of generating the editing interface and assigning the user's entries to the data source's UpdateParameters. A future installment of this article series will explore more advanced updating scenarios, including how to customize the GridView's editing interface.
Until then... Happy Programming!