Accessing and Updating Data in ASP.NET: Examining the Data Source Control's Events
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.
Parameter controls for use in the data source controls' parameters collections.IDENTITY column value for the just-inserted record. |
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 ...
|
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
|
See Change the Value of the Data Source Control's Update and Insert Parameters for more information on programmatically assigning the SqlDataSource control's parameters.
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
|
For more information on programmatically setting the parameters in an ObjectDataSource, check out Programmatically Setting the ObjectDataSource's Parameter Values [VB] [C#].
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.
For more information on handling exceptions in the post-action event using the ObjectDataSource, check out Handling BLL- and DAL-Level Exceptions in an ASP.NET Page [VB] [C#]. For an example using the SqlDataSource control, check out Fredrik Normen's blog, specifically Handle the Data Source Control Exception on Your Own.
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.
Happy Programming!
Further Readings:
Attachments
| 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.
Parameter controls for use in the data source controls' parameters collections.IDENTITY column value for the just-inserted record. |
