Accessing and Updating Data in ASP.NET: Updating Basics

By Scott Mitchell

A Multipart Series on ASP.NET's Data Source Controls

ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data. These controls allow page developers to declaratively access and modify data without writing any code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.

Data Source Control Basics - explores the concepts and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.

Accessing Database Data - shows how to use the SqlDataSource and AccessDataSource controls to query data from a relational database.

Filtering Database Data with Parameters - learn how to retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page, session variables, and so on.

Retrieving XML Data with XmlDataSource Control - see how to retrieve both remote and local XML data and display it in a data Web control.

Creating Custom Parameter Controls - learn how to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.

Examining the Data Source Control's Events - explore the events raised during a data source control's lifecycle.

Declaratively Caching Data - learn how to cache data to the data cache simply by setting a couple of data source control properties.

Programmatically Accessing Data using the Data Source Controls - programmatically retrieve, insert, delete, and update data using the SqlDataSource and AccessDataSource controls.

Inserting Data - learn how to insert data using a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.

Deleting Data - see how to delete data using a SqlDataSource control. Also looks at how to programmatically cancel a delete.

Updating Basics - learn the basics of updating database data using a SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.

Customizing the Editing Interface - see how to customize the GridView's columns to provide a customized editing interface that includes input validation and alternative user interface elements.

Handling Database NULL Valuese - explore how to extend the GridView's customized editing interface to handle database NULL values.

Using Optimistic Concurrency - see how to prevent concurrent users from overwritting one anothers changes by using concurrency control.

Filtering Data Using a CheckBoxList - learn how to filter data based on the end user's selections in a CheckBoxList.

(Subscribe to this Article Series! )

Introduction

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!

- continued -

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 Update method that, when invoked, kicks off the following updating workflow:

The data source's Updating event is fired
The updating "action" occurs
The data source's Updating event is fired

The updating "action" differs between the data source controls. For the AccessDataSource and SqlDataSource, the action involves connecting to the specified database and executing the UPDATE statement specified by the control's UpdateCommand property. 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 UpdateCommand, that UPDATE statement will use parameters like so:

UPDATE TableName SET ColumnName1 = @ParameterValue1 ColumnName2 = @ParameterValue2 ... ColumnNameN = @ParameterValueN WHERE PrimaryKeyColumnName = @PrimaryKeyValueToUpdate

Most ad-hoc 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. The 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:

UPDATE TableName SET ColumnName1 = @ParameterValue1 ColumnName2 = @ParameterValue2 ... ColumnNameN = @ParameterValueN WHERE PrimaryKeyColumnName1 = @PrimaryKeyValueToUpdate1 AND PrimaryKeyColumnName2 = @PrimaryKeyValueToUpdate2 AND ... AND PrimaryKeyColumnNameN = @PrimaryKeyValueToUpdateN

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 WHERE Clauses

In certain situations, the WHERE clause could be more broad. For example, in an employee database you could give all employees who work for a particular department a 10% raise via a single UPDATE statement by have a WHERE clause like the following:

UPDATE Employees SET Salary = Salary * 1.1 WHERE DepartmentID = @DepartmentID

Alternatively, you could give every employee a 10% raise by omitting the WHERE clause altogether!
However, such encompassing WHERE clauses are rarely used from pages in a web application. More commonly, a web page enables the visitor to update a single record at a time.

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 updating workflow for the AccessDataSource and SqlDataSource can be more specifically expressed as:

The data source's Updating event is fired
SqlConnection and SqlCommand (or OleDbConnection and OleDbCommand) objects are created
The command object's CommandText property is assigned the data source control's UpdateCommand property
The parameters in the data source control's UpdateParameters collection are added the command object's Parameters collection
A connection to the database is established and the command is executed, thereby deleting the data
The data source's Updated event is fired

Astute readers will notice the similarity between the AccessDataSource and SqlDataSource controls' inserting, deleting, and updating workflows. Regardless of what workflow is initiated, they all follow the same six steps: the firing of the appropriate pre-action event (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 (UpdateCommand and 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 Click event handler simply called the SqlDataSource control's Insert method, 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 the appropriate 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 Products table 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 ProductID, ProductName, CategoryID, UnitPrice, and Discontinued fields from the Products table and the corresponding CategoryName value from the Categories table. That is, configure the SqlDataSource so that it's SelectCommand contains the following query:

SELECT [ProductID], [ProductName], [Products].[CategoryID], [CategoryName], [UnitPrice], [Discontinued] FROM [Products] INNER JOIN [Categories] ON [Products].[CategoryID] = [Categories].[CategoryID] ORDER BY [ProductName]

The 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 UpdateCommand:

UPDATE [Products] SET [ProductName] = @ProductName, [CategoryID] = @CategoryID, [UnitPrice] = @UnitPrice, [Discontinued] = @Discontinued WHERE [ProductID] = @ProductID

Next, we need to add parameters to the UpdateParameters collection for each of the parameters above (@ProductName, @CategoryID, 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:

<asp:SqlDataSource ID="ProductsDataSource" runat="server" ConnectionString="..." SelectCommand="SELECT [ProductID], [ProductName], [Products].[CategoryID], [CategoryName], [UnitPrice], [Discontinued] FROM [Products] INNER JOIN [Categories] ON [Products].[CategoryID] = [Categories].[CategoryID] ORDER BY [ProductName]" UpdateCommand="UPDATE [Products] SET [ProductName] = @ProductName, [CategoryID] = @CategoryID, [UnitPrice] = @UnitPrice, [Discontinued] = @Discontinued WHERE [ProductID] = @ProductID"> <UpdateParameters> <asp:Parameter Name="ProductName" Type="String" /> <asp:Parameter Name="CategoryID" Type="Int32" /> <asp:Parameter Name="UnitPrice" Type="Decimal" /> <asp:Parameter Name="Discontinued" Type="Boolean" /> <asp:Parameter Name="ProductID" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource>

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 UpdateCommand

Rather than manually specifying the UpdateCommand, INSERT, UPDATE, and DELETE statements can be autogenerated through the SqlDataSource control's wizard. However, this option is only available if the SelectCommand returns the primary key column(s) in its select list and if all of the columns are returned from a single table. Moreover, the autogenerated INSERT, UPDATE, and DELETE statements correspond to the SelectCommand. That is, if the SelectCommand returns a primary key column and three additional columns, the UPDATE statement will update those three columns' values. In some cases, however, you might only want to let the user edit a subset of the fields that are displayed. In this case you'd need to alter the UPDATE statement and the UpdateParameters after they were autogenerated.
For these reasons, I usually prefer to manually construct the INSERT, UPDATE, and DELETE statements. For this example, we cannot use the autogeneration feature because the SelectCommand contains a JOIN, pulling back data from two tables. But for simpler scenarios where the data is being pulled from exactly one table and the fields returned in the SelectCommand are precisely the set of columns to be edited, the autogeneration feature may save time.

Next, add a GridView to the page and bind it to the SqlDataSource. Visual Studio will automatically add BoundFields for the ProductID, ProductName, CategoryID, CategoryName, and 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:

The GridView lists the contents of the Products table.

Making the GridView Editable

Making the GridView editable is as simple as checking a checkbox. If the GridView's underlying data source has an UpdateCommand specified (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 ShowEditButton property 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 named 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 ProductID was an IDENTITY column (i.e., one whose values are automatically assigned by the database) and therefore set its ReadOnly 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 ShowEditButton 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 CategoryID in the Products table - 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 CategoryName value. Go ahead and edit a record and put in a different value for its CategoryName field and then click Update. Despite the change, the CategoryName value is the same as it was prior to editing the row. This is because there is no @CategoryName parameter in the SqlDataSource and because the Products database table doesn't even have a CategoryName column, as it comes from the Categories table. If you want to change a product's category, you need to update the CategoryID value. Currently, the CategoryID value'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 CategoryID value from 1 to 4.

Having to change the CategoryID column is annoying because it means the user has to know that a CategoryID value of 1 corresponds to Beverages and a CategoryID of 4 corresponds to Dairy. Instead, we should replace the CategoryID 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 CategoryName BoundField's ReadOnly property 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 enter a CategoryID value outside of the range of valid CategoryIDs or if you enter a non-numeric Salary value. 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 as well.

Conclusion

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!

By Scott Mitchell

Attachments:

Download the code used in this article

Accessing and Updating Data in ASP.NET: Updating Basics

Introduction

An Overview of Updating Data Using the Data Source Controls

@ParameterValue1

@ParameterValue2

@ParameterValueN