Practical .NET

ASP.NET: Exploiting DataSources' Events

If you're using the ASP.NET DataSources, you may be missing an important part of their functionality: the power of their events. But sometimes the right event isn't on the DataSource.

• By Peter Vogel
• 05/31/2011

Using the events that are fired by the DataSources when doing retrievals and updates gives you additional control over your application. The DataSources fire *ing events before doing anything (e.g. the Deleting event fires before issuing a delete) and fires *ed events afterwards (e.g. the Deleted event fires after a delete is performed). While the events fired by the DataSources are similar, the parameters passed by the various DataSources are different. In many ways, the parameters passed to the SqlDataSource's events give you more control than any of the other DataSources.

For instance, in the SQLDataSource, the e parameter gives you access to the Command object being used and all of its properties. The SQLDataSource's *ing events are the best place to set values in any parameters that your SQL statements are using.

This example ties the SqlDataSource's SelectCommand to a stored procedure called GetCustomersByRegion that requires a parameter called "Region":

this.CustomerDataSource.SelectCommand = "GetCustomersByRegion";
this.CustomerDataSource.SelectCommandType =
                         SqlDataSourceCommandType.StoredProcedure;
this.CustomerDataSource.SelectParameters.Add(
                         "Region", System.Data.DbType.String, "SP");

This example sets the Region parameter using the property of an object pulled from the Session object in the Selecting event:

protected void CustomerDataSource_Selecting(object sender,
 SqlDataSourceSelectingEventArgs e)
{ 
  e.Command.Parameters["@Region"].Value =
    ((northwndModel.Employee)this.Session["SalesPerson"]).Region;        
}

In any DataSource, the e parameter's Cancel method lets you stop the DataSource from doing whatever action triggered the event. You could, for instance, validate any data in the Updating event and, if you find a problem, prevent the update by setting the Cancel property to True.

There's nothing wrong with that, but I'd recommend using the similarly named event on the DataView that the DataSource is bound to. The e parameter passed to the RowUpdating event on the GridView gives you access to the data in the row being updated (including the data originally retrieved and the current data), simplifying your validation code. Using the DataView event also means that you're not tied to any particular DataSource. If you decide to switch from SQL to Entity Framework, for instance, you won't have to change any code if you've used the DataView's events.

Tailoring Queries
In the Selecting event, additional options are available through the SelectArguments object that's available through the e parameter's Arguments property. For instance, you can set a sort order on a SQL statement being used to retrieve the data through the SelectArguments' SortExpression property. This example sorts by the City column, in descending order:

e.Arguments.SortExpression = "City Desc";

Where a lot of records are being retrieved, you may want to retrieve in batches. The SelectArguments object gives you two key tools for doing this: the StartRowIndex property (which specifies how many to skip over) and the TotalRowCount (which specifies the number to retrieve). This example skips the first 20 records and asks for the next 10 (presumably, to retrieve the third batch of 10 records):

e.Arguments.StartRowIndex = 20;
e.Arguments.TotalRowCount = 10;

In some cases, when retrieving data in batches, you may want to know how many records you're retrieving altogether. Not all DataSources support returning the total number of rows (the SqlDataSource doesn't, for instance) so you should first check the RetrieveTotalRowCount property to determine if your DataSource does (or just read the documentation). You can then, after doing your first data retrieval, pull the number of rows being retrieved from the SelectArgument's TotalRowCount property.

This example skips checking the TotalRowCount if it's zero (indicating that the count hasn't yet been retrieved), but if the property has a value, uses it to calculate the number of pages that will be required if records are retrieved in batches of 10:

pageCount = -1;
if (e.Arguments.TotalRowCount > 0) 
{
 if (e.Arguments.RetrieveTotalRowCount)
 {
  pageCount = e.Arguments.TotalRowCount / 10;
 }
}

Checking for Problems
In all the DataSources, the e parameter on the *ed events lets you handle problems. If everything has gone well, the Exception property on the e property will be null or Nothing. If the Exception property isn't null you can retrieve the related Exception object from the property and decide if you're willing to handle the error. If you are, you need to set the ExceptionHandled property on the e parameter to true, otherwise the error will bubble up to the ASP.NET error handler and your application will be terminated. This example just displays the Exceptions message in a Label on the page:

if (e.Exception != null)
{
 this.ErrorLabel.Text = e.Exception.Message;
 e.ExceptionHandled = true;
}

In the *ed events for some of the DataSources, you'll also have access to an AffectedRows property, which can be useful If you've turned on optimistic locking. For instance, if an update is rejected because another user has changed the record, no exception is raised. Instead, the AffectedRows property is set to zero, indicating that the target row was been updated (or deleted) by some other user. In that case, the appropriate thing to do is to tell the user and refresh the associated DataView to show the user the latest data:

if (e.AffectedRows == 0)
{
 this.ErrorLabel.Text = "Update lost";
 this.GridView.DataBind();
}

Here again, though, consider looking at the similar event on the DataView. In the RowUpdated event on the GridView, for instance, the e parameter also has a KeepInEditMode property that prevents the row the user is editing from switching back to display mode. All by itself that makes it easier for the user to correct their problem and try submitting their change again.

With these tools you're ready to take full advantage of all of the DataSources' events.