|
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
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
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
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
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)
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
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
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
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
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
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
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
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)
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
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
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)
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
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
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
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)
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
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
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
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) {
DataBind();
}
C#
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
The following example illustrates binding to a property of another control.
VB Data Binding to a Server Control Property
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
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
The following example illustrates binding to a Hashtable.
VB Data Binding to a Hashtable
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
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"]) %>
C#
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}") %>
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") %>
C#
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
|