When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, November 22, 2006

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.

  • 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


    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!

    - continued -

    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 data source control event 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

    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
       'Set parameter value through e.InputParameters...
       

    e.InputParameters("departmentID").Value = 7


    End Sub

    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!

  • By Scott Mitchell


    Further Readings:


  • Programmatically Setting the ObjectDataSource's Parameter Values [VB] [C#]
  • Handling BLL- and DAL-Level Exceptions in an ASP.NET Page [VB] [C#]
  • Change the Value of the Data Source Control's Update and Insert Parameters
  • Handle the Data Source Control Exception on Your Own
  • Attachments


  • Download the code and examples for this article (in ZIP format)
  • 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! )



    ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article