Accessing and Updating Data in ASP.NET 2.0: Examining the Data Source Control's Events
By Scott Mitchell
A Multipart Series on ASP.NET 2.0'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 2.0's new 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.
Creating Custom Parameter Controls - learn how
to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
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.
Introduction
The data source controls in ASP.NET 2.0 provide a simple, declarative approach
for accessing and modifying data. The Data Source Control Basics
article that kicked off this article series examined how to add data source controls to a page, specify the data to retrieve and/or
modify, and bind that data to a data Web control (such as a GridView, DetailsView, or FormView). While point-and-click data access
is useful for prototyping and for beginners or hobbyists who have little to no programming experience, wizards and the like
are generally frowned upon by more seasoned developers since they typically offer ease of use at the cost of extensibility and
customizability. The ASP.NET 2.0 data source controls, however, provide the best of both worlds, in my opinion - they can be quickly
and easily configured to work with data, yet through a myriad of events that are raised during the data source
controls' lifecycles, provide the flexibility for use in more advanced scenarios.
The SqlDataSource and ObjectDataSource controls are the two most commonly used data source controls, and they provide a means
for retrieving and modifying data from either a database or object library, respectively. Both controls raise pre- and post-action
events when selecting, inserting, updating, and deleting data. For example, the SqlDataSource and ObjectDataSource raise their
Selecting events immediately before performing the SQL query or invoking the object method to retrieve data.
After the data has been retrieved, the Selected events fires.
By creating a Selecting event handler, you can examine and massage the parameters used in selecting data; the
Selected event indicates, among other things, if an exception occurred during the operation. Similarly named
pre- and post-action events fire during the updating, inserting, and deleting processes, as well.
Having a solid understanding of the data source events and event lifecycle has a slew of benefits. Many real-world scenarios
require that the parameters being used for selecting, inserting, updating, or deleting be programmatically assigned or altered,
which can be accomplished in the appropriate pre-action event. Moreover, to gracefully handle database- or object library-level
exceptions in the page, the post-action event handlers should be used. And when debugging, the pre-action events provide an
opportunity for examining what parameters (if any) are being used to query the data. Read on to learn more!
The Data Source Control Event Pattern
The SqlDataSource and ObjectDataSource controls provide methods for selecting, inserting, updating, and deleting data -
Select(), Insert(), Update(), and Delete(). These methods can be invoked
programmatically or, as is commonly the case, automatically from a data Web control that has been bound to the data source control.
When the SqlDataSource control's Select() method is invoked, it established a connection to the specified database,
executes the specified SelectCommand
query, and returns the results in either a DataView or DataReader (depending on the value of the
DataSourceMode
property). When the Select() method is invoked on the ObjectDataSource, the configured object is instantiated
and it's specified method is invoked. The results of this method are then returned from the Select() method.
Despite the internal differences of the Select() method for the SqlDataSource and ObjectDataSource controls,
both controls adhere to the same event pattern. For the four methods - Select(), Insert(), Update(), and Delete() -
the SqlDataSource and ObjectDataSource controls raise pre- and post-action events. One event precedes the action, one event follows it.
The events are aptly named in the past and present tenses of the methods. The Selecting event fires before
the data is retrieved and then, once the underlying select has been performed, the Selected event fires.
The following diagram illustrates this pattern.
The diagram above shows the pattern for the ObjectDataSource's Select() method. The same pattern is used for the
Insert(), Update(), and Delete() methods and for the SqlDataSource control. While the
concepts are the same for the SqlDataSource and ObjectDataSource controls, the implementation details differ. The remainder of
this article examines
Examining the Pre-Action Events for the SqlDataSource Control
The pre-action event handlers for the SqlDataSource control are passed, among other bits of information, a reference to the
Command object used to perform the database action. The Command object contains information about the command to be issued
(via the CommandText property), which will be the ad-hoc SQL statement or stored procedure name. It also has a
Parameters collection that constitutes the parameters used in the query.
If you need to massage the parameters used in the SELECT, INSERT, UPDATE, or
DELETE statements, you can do so through the appropriate pre-action event handler. For example, imagine that
the SqlDataSource control is setup with a SelectCommand like:
SELECT ...
FROM Employees
WHERE Salary > @Salary
In the SqlDataSource's <SelectParameters> collection there would be a parameter. The parameter's source could
be specified declaratively, using a hard-coded value or pulling its value from a Web control on the page, a querystring value,
and so on. Regardless, the value of the parameter can be examined and modified (if needed) in the Selecting event
handler. (See Filtering Database Data with Parameters for more information on using parameters with the
data source controls.)
For the SqlDataSource control, you can access the parameter value using e.Command.Parameters("parameterName")
to programmatically set (or change) the @Salary parameter:
Protected Sub CategoriesDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles CategoriesDataSource.Selecting
'Set parameter value through e.Command.Parameters... e.Command.Parameters("@Salary").Value = 50000
End Sub
The download available at the end of this article provides a helpful method for displaying the CommandText and
all of the Command's parameters when retrieving or modifying data through the SqlDataSource control. Such output can be quite
helpful when debugging.
Examining the Pre-Action Events for the ObjectDataSource Control
Like the SqlDataSource, the ObjectDataSource's pre-action events provide an opportunity to customize the parameters being passed into
the configured object method. Instead of receiving a Command object in the event handler, the ObjectDataSource's pre-action
event handlers are passed a dictionary object called InputParameters that contains information about the incoming
parameters and their values.
For example, imagine that our business object was invoking a method called GetEmployeesByDepartmentID(departmentID),
where the Integer input parameter departmentID is used internally to return only those employees belonging to the
specified department. To programmatically assign this input parameter value in the ObjectDataSource's Selecting
event handler, use the following code:
Protected Sub CategoriesDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceSelectingEventArgs) Handles CategoriesDataSource.Selecting
'Set parameter value through e.InputParameters... e.InputParameters("departmentID").Value = 7
End Sub
Examining the Post-Action Events for the ObjectDataSource and SqlDataSource Controls
After the specified action has been completed, the SqlDataSource and ObjectDataSource controls raise their corresponding
post-action events (Selected, Inserted, Updated, or Deleted). In the
post-action event handler, the number of affected rows is reported as is whether an exception occurred. The ObjectDataSource's
post-action event handler is also passed the value returned by the invoked object library method (if any).
An exception could occur if the database was down, an illegal parameter
value was used, the action violated a constraint, or for some other reason. Moreover, if an exception has transpired, the
ExceptionHandled property can be set to True to indicate that the exception should be suppressed.
Conclusion
When retrieving or modifying data with the SqlDataSource or ObjectDataSource controls, pre- and post-action events fire. By creating
event handlers for these events, we can examine the underlying workflow, massage the inputs, and examine the results. The pre-action
event handlers are most often used to programmatically assign values to the data source control's parameters. The post-action event
handlers are most commonly used to determine how many rows were affected or to detect if the operation raised an exception.
The pre- and post-action event handlers are also quite helpful when debugging.
A Multipart Series on ASP.NET 2.0'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 2.0's new 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.
Creating Custom Parameter Controls - learn how
to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
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.
