Home   |   QuickStart Welcome   |   ASP.NET   |   Web Services   |   How Do I...?   
  |   I want my samples in...      

ASP.NET 2.0 Quickstart Tutorials

Advanced Data Scenarios

This topic describes how you can use some of the less obvious features of data controls to implement advanced scenarios.

Handling Null Values

Data controls support a variety of ways to handle null or missing data. To begin with, the GridView, FormView, and DetailsView all support an EmptyDataText or EmptyDataTemplate property that you can use to specify a rendering for the control when the data source returns no rows of data. Only one of EmptyDataText and EmptyData needs to be set (EmptyDataTemplate overrides when both are set). You can also specify a ConvertEmptyStringToNull property on BoundFields or data source parameter objects to specify that String.Empty values posted from the client should be converted to null before invoking the associated data source operation. ObjectDataSource also supports a ConvertNullToDbNull property that can be set to true when the associated method expects DbNull parameters instead of null (the TableAdapter classes in a Visual Studio DataSet have this requirement). You can also specify a NullDisplayText property on BoundField to specify a value for the field to display when the field value from the data source is returned as null. If this value is not changed during edit mode, the value will roundtrip as null back to the data source during an Update operation. Lastly, you can also specify a DefaultValue property on data source parameters to specify a default value for the parameter when the parameter value passed is null. These properties can a chaining effect, for example if both ConvertEmptyStringToNull and DefaultValue are set, a String.Empty value will first be converted to null and subsequently to the default value.

VB Handling Null Values
Run Sample View Source

You can use these null-handling properties to implement a DropDownList filter that displays all values from the data source initially until a filter value is selected. This is achieved by adding an item to the DropDownList with an empty string value and setting the ConvertEmptyString to null property on the associated ControlParameter for the DropDownList in the data source. Then in the data source SelectCommand, you can check for a null value in order to return all (unfiltered) values. The following example demonstrates this technique using a simple SQL command, although you could also perform the null check in the implementation of a stored procedure. Note the use of the AppendDataBoundItems property of the DropDownList, which allows values obtained from the DropDownList data source to be appended to the statically added "ALL" item in the Items collection. Also note that by default the SqlDataSource does not perform a Select operation if any of the associated parameters to the SelectCommand are null. To allow the select operation to execute even when a null parameter is passed, you can set the CancelSelectOnNullParameter property to false.

VB Show All Values on Null Selection
Run Sample View Source

Advanced Master Details

In preceding the topics of this tutorial, you've seen how a master-details scenario can be implemented by associating a ControlParameter to the SelectedValue property of GridView. The SelectedValue property returns the value of the first field specified by the DataKeyNames property. You can also specify multiple comma-separated field values for the DataKeyNames property, for example if you need more than one value to be passed to the details data source in a master-details scenario. The values of these additional key fields are exposed through the SelectedDataKey property, which returns a DataKey object of name/value pairs for key fields. A ControlParameter can even refer to these keys by setting the PropertyName property to an expression, like SelectedDataKey.Values("title_id")).
  <asp:ControlParameter Name="FirstKey" ControlID="MasterGrid" PropertyName="SelectedDataKey.Values[0]"/>
  <asp:ControlParameter Name="SecondKey" ControlID="MasterGrid" PropertyName="SelectedDataKey.Values[1]"/>
The following example demonstrates code to enumerate the DataKeys collection and for obtaining key field values from the GridView SelectedDataKey.

VB DataKeys
Run Sample View Source

The previous master-details samples in this tutorial showed the details control as a separate control on the form, but another common way to implement master-details is to show the details control as a nested control that is part of the rendering of the master control. To do this, you need both the details control and its associated data source to be contained in a template of the master control, with a data source parameter that obtains its value from a field of the master data source. Because there is not a declarative parameter object that can be associated in this way, you will have to set the parameter value programmatically in code. You can do this by handling an event on the master control to set the parameter value when an item of the master control is data-bound, for example in the FormView DataBound event. The following example demonstrates this technique.

VB Nested Master-Details
Run Sample View Source

This next example show a similar technique using DataList, handing the DataList ItemDataBound event to set the details data source parameter value.

VB Nested Master-Details (DataList)
Run Sample View Source

Handling Data Control Events

You already seen several examples of data control events in preceding sections of this tutorial. The data control events are meant to provide you with appropriate places in the page execution lifecycle where you can plus in your own custom code. Data controls generally expose events both before and after a particular operation occurs. The events that fire before an operation are typically named with an -ing suffix, while events that fire after an operation are named with a -ed suffix. The events supported by the GridView for example, include:
  • PageIndexChanging and PageIndexChanged - Raised before and after a paging operation
  • SelectedIndexChanging and SelectedIndexChanged - Raised before and after a selection occurs
  • Sorting and Sorted - Raised before and after a sorting operation
  • RowEditing and RowCancelingEdit - Raised before the row goes into edit mode, or before an edit mode is canceled
  • RowUpdating and RowUpdated - Raised before and after an update operation
  • RowDeleting and RowDeleted - Raised before and after a delete operation
  • RowDataBound - Raised when a row is data-bound
  • RowCreated - Raised when a row is created for rendering (as a TableRow)
  • RowCommand - Raised when a button command is fired from within the control
Data source controls also expose events, similar to data-bound control events. Both the SqlDataSource and ObjectDataSource controls support the following events:
  • Selecting and Selected - Raised before and after a select operation
  • Updating and Updated - Raised before and after an update operation
  • Deleting and Deleted - Raised before and after a delete operation
  • Inserting and Inserted - Raised before and after an insert operation
  • Filtering - Raised before a filter operation
The ObjectDataSource control additionally exposes events when the object specified by TypeName property is created or destroyed. You can actually set a custom object in the ObjectCreating event by setting the ObjectInstance property of the passed event args.
  • ObjectCreating and ObjectCreated - Raised before and after the object is created
  • ObjectDisposing - Raised before the object is disposed
Typically the events that fire before an operation occurs are used to cancel the operation set Cancel property of the events args to true, or to perform parameter validation or manipulation. The events that are raised after an operation are used to write custom code to respond to a given operation, or to check the success status of the operation. For example, you can check the RowsAffected by an Update, Insert, or Delete operation or check the Exception property to determine if an exception occurred during processing. You can also set the ExceptionHandled property of the event args to prevent the exception from bubbling up to the control or Page. The following example shows the various GridView and SqlDataSource events that can be handled and the relative order in which they are raised.

VB GridView RowUpdating Event
Run Sample View Source

This next example demonstrates a more practical handling of the DetailsView Inserting event to obtain a posted photo file from a FileUpload control inside the DetailsView to save the contents to disk before the DetailsView completes the insert operation to insert a record in a database for the posted photo file. For demonstration purposes, the actual file save code has been commented out, but you can still try out this sample by specifying the name of an already existing "Winter.jpg" image.

VB DetailsView ItemInserting Event
Run Sample View Source

Similar to the example above, this sample shows handling the FormView ItemDeleted event to remove the associated photo file from disk when the database record for the photo is deleted. Again, the actual file delete code has been commented out for demonstration purposes.

VB FormView ItemDeleted Event
Run Sample View Source

Working With Parameters

You can handle the events for Select, Update, Insert, Delete, and Filter operations to validation and manipulate the parameter values that are passed to these operations. Both the data-bound controls and data source controls expose appropriate events for this purpose. For example, in the GridView Updating event you can see the names and values of parameters in the Keys, NewValues, and OldValues dictionaries that will be handed off to the data source. On the data source side, you can handle the SqlDataSource Updating event to see these parameters as they are applied to the underlying command object that will be executed to complete the operation. Similarly you can handle an ObjectDataSource Updating event to view or change the parameter dictionaries that will be used to resolve the appropriate overload of the UpdateMethod. You can use these events to add or remove parameters from the dictionaries or commands, change their values, or simply validate that parameters have the correct input format.

Important: You should especially validate parameter inputs for the Filtering event, which do not get SQL encoded before applied to the FilterExpression associated to the DataView object.

The following example demonstrates handling various data control events to enumerate the parameters collections passed via the event args. Notice that this sample sets the InsertVisible property of the BoundField associated to the OrderID primary key field to false, because the OrderID field is an identity column in the underlying database and should not be passed to Insert operations (the database increments this value automatically when an insert occurs). Also note that the OrderID field is marked as the primary key in DataKeyNames, so the original value of this field is retained in the Keys dictionary passed by the data-bound control. Values entered by the user to input controls are passed in a NewValues dictionary, except those fields marked as ReadOnly=false. The original values of non-key fields are also retained in an OldValues dictionary by the data-bound control for passing to the data source. These parameter values are appended to the command by SqlDataSource in the order of NewValues, Keys, and OldValues, although by default the data source does not append OldValues when ConflictDetection is set the OverwriteChanges. For more information on how OldValues is used by data sources, refer to the Using Conflict Detection section below. Notice also that the data source renames parameters from Keys or OldValues with an "original_" prefix by default, as specified by the OldValuesParameterFormatString property.

VB Data Parameters
Run Sample View Source

You can change the order in which parameters are appended to the command by SqlDataSource by adding static Parameter object to the parameters collection for a data source operation in the preferred order. The SqlDataSource automatically reorders parameters passed by the data-bound control according to the order of these Parameter objects. This is useful when the ProviderName property of the data source is set to System.Data.OleDb, which does not have support for named parameters so the order in which parameters are appended to the command must match the order of anonymous parameter placeholders ('?') in the command. When using named parameters, the order of parameters is insignificant. You can specify the Type property of a Parameter object to force the values passed by the data-bound control to be converted to an appropriate data type before executing the command or method. Likewise, you can set the Size property of a Parameter to dictate the Size of the DbParameter in a SqlDataSource command (required for input/output, output, and return value parameters).

VB Parameter Order and Types
Run Sample View Source

The default convention for parameter names requires that new values are named according to the fields selected by the data source Select operation, while original (old) values are named with the "original_" prefix. You can customize the names of parameters by handling an appropriate event to change the value of a parameter before the data source operation is executed. For example, if the update operation of a SqlDataSource is associated to a stored procedure that takes different parameter names than those that match the default naming convention, you can modify the parameter names in the SqlDataSource Updating event before the stored procedure is called. The example below demonstrates this technique.

VB Renaming Parameters
Run Sample View Source

ObjectDataSource does not rely on a particular order of parameters, but instead simply looks for a method with matching parameter names. Note that the Type or Size of a parameter is not used by ObjectDataSource for the sake of resolving method overloads. Only the names of the parameters are matched, so if you have two methods on your business object that have the same name and parameter names, but different parameter types, ObjectDataSource will not be able to distinguish between them. You can change the names and values of ObjectDataSource parameters in events, similar to the SqlDataSource example above. However, if you are using DataObjectTypeName to specify a particular data object type to be passed to Update, Insert, and Delete operations, you will not be able to modify the parameter names -- only the values. If you need to modify parameter names, do not use DataObjectTypeName and instead just construct appropriate data objects in the data source events manually in code.

All of the data source parameters we have used so far have been Input parameters, used for passing values into a data source operation. Parameters can also be bidirectional, such as InputOutput, Output, and ReturnValue parameters. You can specify the directionality of a parameter using the Parameter object's Direction property. To retrieve the values of these parameters after a data source operation completes, handle the appropriate post-operation event, such as the Selected, Updated, Inserted, or Deleted event, to obtain the parameter value from the event arguments passed to these events. The SqlDataSourceStatusEventArgs has a Command property you can use to obtain return values and output parameters, shown in the example below. Note that it is important to set the Size property of the Parameter object in SqlDataSource to an appropriate value for bidirectional parameters.

VB Return Values and Output Parameters
Run Sample View Source

The ObjectDataSourceStatusEventArgs type supports an OutputParameters collection and ReturnValue property for this purpose, demonstrated in this next sample. Note that in this case, the return value of the Update operation is used to determine the number of affected rows by that operation. To pass this value back to the data-bound control (for example through the GridViewUpdatedEventArgs AffectedRows property, you can set the AffectedRows property of the ObjectDataSourceStatusEventArgs to this return value).

VB Return Values and Output Parameters (Object)
Run Sample View Source

Another common use for output parameters is to retrieve the primary key value of a row inserted into a database that is an Identity column (where the key value is not specified as a parameter of the insert, but is instead automatically generated by the database server when the insert occurs. This next example demonstrates this technique.

VB Retrieving Identity After Insert
Run Sample View Source

Using Conflict Detection

As mentioned in previous topics, the data-bound control passes values to a data source in separate Keys, Values (new values), and OldValues dictionaries. By default the SqlDataSource and ObjectDataSource ignore the OldValues dictionary and instead only apply Keys and Values. This behavior is determined by the ConflictDetection property of the data source, which is set to OverwriteChanges by default. The OverwriteChanges mode essentially means "only match primary key values for the sake of updating or deleting a record". This behavior means that the record is updated or deleted regardless of whether the underlying values of the record have changed. Often it is more desirable to only allow Update or Delete operations to succeed when the values of the row match exactly what was selected initially. This way, if another user updates a row between the time you select the row and update it, your update operation will fail. Data sources support this approach by setting the ConflictDetection property to CompareAllValues In this mode, the data source applies to OldValues parameters to the command or method, which can use these values to ensure an update or delete operation matches all of these values of the record before updating or deleting the record.

The code example below shows typically SQL commands for the OverwriteChanges and CompareAllValues mode of the SqlDataSource control. The ID field is assumed to be the primary key field. Notice that the latter commands compare all the original values of the row in the WHERE clause, not just the primary key.

SELECT [ID], [Name], [Address] from [Contacts]

-- OverwriteChanges
UPDATE [Contacts] SET [Name] = @Name, [Address] = @Address WHERE [ID] = @original_ID
DELETE FROM [Contacts] WHERE [ID] = @original_ID

-- CompareAllValues
UPDATE [Contacts] SET [Name] = @Name, [Address] = @Address WHERE [ID] = @original_ID 
  AND [Name] = @original_Name AND [Address] = @original_Address
DELETE FROM [Contacts] WHERE [ID] = @original_ID AND [Name] = @original_Name 
  AND [Address] = @original_Address
The following example shows the enumerations of parameters at the data-bound control level and the data source level for the Keys, Values, and OldValues dictionaries, when ConflictDetection is set to CompareAllValues. For demonstration purposes, the actual Update, Delete, and Insert operations are cancelled. Notice also that OldValues is not required for an Insert operation. ConflictDetection is only meaningful for Updates and Deletes.

VB Conflict Detection Parameters
Run Sample View Source

The next example shows the behavior when a conflict occurs. To run this sample, open two instances of the sample in separate browser windows (click "Run Sample" twice). Then click the Edit button for the same row in both windows, to place the row in edit mode. In the first window, change a row value and click Update, and note that the update succeeds. In the second window, you can enter new values for the same row and click update, but the Update will not succeed because the underlying row values where changed by the first update operation. The sample checks that the AffectedRows property of the Updated or Deleted event args is 0 to confirm that a conflict has occurred.

C# Conflict Detection (Update and Delete)
Run Sample View Source

When using templated UI for Updates or Deletes, the old values are retained for fields that are two-way data-bound using Bind statements. For Deletes, this means that you must use Bind statements to data-bound values in the ItemTemplate in order to retain old values for delete operations. The example below demonstrates this technique.

VB Conflict Detection Using Templates
Run Sample View Source

You can handle conflict detection errors gracefully by giving the user an option to commit or abort the operation when the underlying row has changed, by displaying the changed values to the user so they can compare to their own updates. The following example demonstrates a possible approach for handling conflict detection errors. Note that the DetailsView RowUpdated event args passes dictionaries that you can use to check the values entered by the user. You can also set the KeepInEditMode property of this event args to keep the DetailsView in edit mode while the user makes a decision about how to handle the conflict. You can exercise this sample in the same way as the previous example, by opening two windows to create a conflicting update.

VB Handling Conflicting Updates
Run Sample View Source

This next example shows the same scenario as above using ObjectDataSource. Note that because the data source ConflictDetection property is set to CompareAllValues, the data source looks for an overload of UpdateContact that accepts original values for each field of the Contact object.

VB Conflict Detection w/ ObjectDataSource
Run Sample View Source

You can also use the DataObjectTypeName property in conjunction with CompareAllValues. In this case, the ObjectDataSource looks for an overload of UpdateContact that accepts exactly two parameters, both of type Contact. The first parameter will be a Contact object populated with the new values for the update, while the second parameter will be an Contact object populated with the original values.

VB Conflict Detection w/ ObjectDataSource (DataObjectTypeName)
Run Sample View Source

Advanced Paging/Sorting

Selection in the GridView control is purely a UI concept, where the SelectedIndex property correspond to the index of the currently selected row among the visible rows of the grid. If you have enabled paging and sorting in the grid, the SelectedIndex of the grid remains the same after a paging or sorting operation, so that a new row ends up selected after these operations. In some circumstances it is better to retain the selection of a given row even when that row is not currently visible on the current page of the grid. The example below shows a technique for retaining the selected row across a sorting or paging operation.

VB Maintaining Selection After Sort
Run Sample View Source

GridView and DetailsView support a special mode for paging and sorting that takes advantage of client-side callbacks to the server to obtain data for the new page or newly sorted data. To enable this feature, set the EnableSortingAndPagingCallbacks property to true. Notice that when a page or sort operation is performed, the page does not need to postback in order to retrieve new values (although a client script callback to the server is made). This feature is not supported when the GridView or DetailsView contains templated fields. Showing a Select button in a CommandField is also not supported when this feature is enabled.

VB Sorting and Paging Using Callbacks
Run Sample View Source

Although the GridView, DetailsView, and FormView provide a default rendering for the pager UI, you can also customize the rendering for the pager by setting the PagerTemplate property. Inside this template you can place Button controls with the CommandName property set to Page, and the CommandArgument property set to First, Prev, Next, Last, or <number>, where <number> is the value of a specific page index. The example below shows a PagerTemplate defined for both the GridView and DetailsView controls.

VB Pager Template
Run Sample View Source

Databinding in Code

As discussed in the Databinding in Templates section, ASP.NET includes a declarative data binding syntax for associating data source fields with properties of controls in a data-bound template. You can also use the <%# ... > syntax to data bind arbitrary values in code, such as Page and control properties, collections, expressions, and even results returned from method calls. To force the data binding to evaluate, you must call the DataBind method on the Page or control containing the data binding expression. The following table shows some examples of the data binding syntax in ASP.NET.

Simple property Customer: <%# custID %>
Collection Orders: <asp:ListBox id="List1" datasource='<%# myArray %>' runat="server">
Expression Contact: <%# ( customer.FirstName + " " + customer.LastName ) %>
Method result Outstanding Balance: <%# GetBalance(custID) %>

Although this syntax looks similar to the ASP shortcut for Response.Write -- <%= %> -- its behavior is quite different. Whereas the ASP Response.Write shortcut syntax was evaluated when the page was processed, the ASP.NET data binding syntax is evaluated only when the DataBind method is invoked.

DataBind is a method of the Page and all server controls. When you call DataBind on a parent control, it cascades to all of the children of the control. So, for example, DataList1.DataBind() invokes the DataBind method on each of the controls in the DataList templates. Calling DataBind on the Page -- Page.DataBind() or simply DataBind() -- causes all data binding expressions on the page to be evaluated. DataBind is commonly called from the Page_Load event, as shown in the following example.

protected void Page_Load(Object Src, EventArgs E) {

You can use a binding expression almost anywhere in the declarative section of an .aspx page, provided it evaluates to the expected data type at run time. The simple property, expression, and method examples above display text to the user when evaluated. In these cases, the data binding expression must evaluate to a value of type String. In the collection example, the data binding expression evaluates to a value of valid type for the DataSource property of ListBox. You might find it necessary to coerce the type of value in your binding expression to produce the desired result. For example, if count is an integer:

Number of Records:  <%# count.ToString() %>
The ASP.NET data binding syntax supports binding to public variables, properties of the Page, and properties of other controls on the page. The following example illustrates binding to a public variable and simple property on the page. Note that these values are initialized before DataBind() is called.

VB Data Binding to a Property on the Page
Run Sample View Source

The following example illustrates binding to a property of another control.

VB Data Binding to a Server Control Property
Run Sample View Source

List server controls like DropDownList, ListBox and HTMLSelect use a collection as a data source. The following examples illustrate binding to usual common language runtime collection types. These controls can bind only to collections that support the IEnumerable, ICollection, or IListSource interface. Most commonly, you'll bind to ArrayList, Hashtable, DataView and DataReader. The following example illustrates binding to an ArrayList.

VB Data Binding DropDownList
Run Sample View Source

The following example illustrates binding to a DataView. Note that the DataView class is defined in the System.Data namespace.

VB Data Binding to a DataView
Run Sample View Source

The following example illustrates binding to a Hashtable.

VB Data Binding to a Hashtable
Run Sample View Source

Often, you'll want to manipulate data before binding to your page or a control. The following example illustrates binding to an expression and the return value of a method.

VB Databinding to Methods and Expressions
Run Sample View Source

The ASP.NET Page Framework supplies a static method that evaluates late-bound data binding expressions and optionally formats the result as a string. DataBinder.Eval is convenient in that it eliminates much of the explicit casting the developer must do to coerce values to the desired data type. It is particularly useful when data binding controls within a templated list, because often both the data row and the data field must be cast.

Consider the following example, where an integer will be displayed as a currency string. With the standard ASP.NET data binding syntax, you must first cast the type of the data row in order to retrieve the data field, IntegerValue. Next, this is passed as an argument to the String.Format method.

<%# String.Format("{0:c}", ((DataRowView)Container.DataItem)["IntegerValue"]) %>

This syntax can be complex and difficult to remember. In contrast, DataBinder.Eval is simply a method with three arguments: the naming container for the data item, the data field name, and a format string. In a templated control like FormView, GridView, DetailsView, DataList or Repeater, the naming container is always Container.DataItem. Page is another naming container that can be used with DataBinder.Eval. As discussed in previous section, ASP.NET 2.0 also include a new simplified syntax for DataBinder.Eval, simply Eval, that can be used inside a data-bound control template to resolve to Container.DataItem automatically. The simplified Eval syntax is discussed in the preceding section, Databinding in Templates.

<%# DataBinder.Eval(Container.DataItem, "IntegerValue", "{0:c}") %>
<%# Eval("IntegerValue", "{0:c}") %>

The format string argument is optional. If it is omitted, DataBinder.Eval returns a value of type object, as shown in the following example.

<%# (bool)DataBinder.Eval(Container.DataItem, "BoolValue") %>

It is important to note that DataBinder.Eval can carry a noticeable performance penalty over the standard data binding syntax because it uses late-bound reflection. Use DataBinder.Eval judiciously, especially when string formatting is not required.

VB Data Binding Using DataBinder.Eval
Run Sample View Source