Filtering Data Using Data Source Controls
Data source controls provide a number of data services that make it easier to add advanced capabilities to your applications. This includes filtering data based on search criteria that you specify. Filtering is especially convenient when working with cached data, because you can provide search capabilities without having to re-run queries or call methods to read data.
To filter data, a data source control must be configured in these ways:
For the SqlDataSource or AccessDataSource controls, the DataSourceMode property must be set to DataSet.
For the ObjectDataSource control, the underlying source object must return a DataSet or DataTable object.
When using the XmlDataSource control, you can filter data using XPath queries. For more information, see Filtering Data Using the XmlDataSource Control.
Setting the Filter Expression
You specify the filter to be applied to the data returned by an ObjectDataSource, SqlDataSource, or AccessDataSource control by setting the data source control's FilterExpression property. The syntax for the filter expression is based on the syntax of the Expression property of the DataColumn class. The filter expression is applied when the data source control's Select method is called.
Providing Filter Parameters
You can provide a parameterized filter expression for an ObjectDataSource, SqlDataSource, or AccessDataSource control, which enables you to provide filter values at run time without writing any code to explicitly set the FilterExpression property. You specify filter expression parameters using the data source control's FilterParameters collection. The parameters can retrieve data from controls, the QueryString object, session state, user profile properties, and so on. For information on the types of parameters that can be used in the FilterParameters collection, see Using Parameters with Data Source Controls.
In the filter expression, you create placeholders that correspond to items in the data source control's FilterParameters collection. The placeholders are numbered, with 0 representing the first parameter in the collection. You specify a placeholder in the filter expression by placing the number of the filter parameter in '{' and '}' characters, as shown in the following example:
Country = '{0}' AND LastName LIKE '{1}'
.gif "Security note") Security Note: |
|---|
Because values from the FilterParameters collection are substituted into the FilterExpression string without encoding, you should validate all filter parameter values before applying a filter. You can use the Filtering event of the data source control to access and validate filter parameter values before the filter is applied. |
The following example shows a SqlDataSource control named EmployeeDetailsSqlDataSource that includes filter parameters. The parameter values used in the FilterExpression property are filled at run time from the property values of controls elsewhere on the page.
<%@ Page language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Northwind Employees</title>
</head>
<body>
<form id="form1" runat="server">
<h3>Northwind Employees</h3>
<table cellspacing="10">
<tr>
<td valign="top">
<table border="0">
<tr>
<td valign="top">Country</td>
<td><asp:DropDownList runat="server" id="CountryListBox" AppendDataBoundItems="True"
DataSourceID="CountrySqlDataSource"
DataTextField="Country" DataValueField="Country" >
<asp:ListItem Selected="True" Value="" >(Show All)</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td>Last Name</td>
<td><asp:TextBox runat="server" id="LastNameTextBox" Text="*" /></td>
</tr>
<tr>
<td></td>
<td><asp:Button runat="server" id="FilterButton" Text="Filter Results" /></td>
</tr>
</table>
</td>
<td valign="top">
<asp:GridView ID="EmployeesGridView"
DataSourceID="EmployeeDetailsSqlDataSource"
AutoGenerateColumns="false"
AllowSorting="True"
DataKeyNames="EmployeeID"
Gridlines="Both"
RunAt="server">
<HeaderStyle backcolor="Navy"
forecolor="White"/>
<RowStyle backcolor="White"/>
<AlternatingRowStyle backcolor="LightGray"/>
<EditRowStyle backcolor="LightCyan"/>
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" ReadOnly="true"/>
<asp:BoundField DataField="FirstName" HeaderText="First Name"/>
<asp:BoundField DataField="LastName" HeaderText="Last Name"/>
<asp:BoundField DataField="Country" HeaderText="Country"/>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
<asp:SqlDataSource ID="CountrySqlDataSource"
SelectCommand="SELECT DISTINCT Country FROM Employees"
EnableCaching="True"
CacheDuration="60"
ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
RunAt="server" />
<asp:SqlDataSource ID="EmployeeDetailsSqlDataSource"
SelectCommand="SELECT EmployeeID, LastName, FirstName, Country FROM Employees"
EnableCaching="True"
CacheDuration="60"
ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
FilterExpression="Country LIKE '{0}' AND LastName LIKE '{1}'"
RunAt="server">
<FilterParameters>
<asp:ControlParameter ControlID="CountryListBox" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="LastNameTextBox" PropertyName="Text" />
</FilterParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
<%@ Page language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Northwind Employees</title>
</head>
<body>
<form id="form1" runat="server">
<h3>Northwind Employees</h3>
<table cellspacing="10">
<tr>
<td valign="top">
<table border="0">
<tr>
<td valign="top">Country</td>
<td><asp:DropDownList runat="server" id="CountryListBox" AppendDataBoundItems="True"
DataSourceID="CountrySqlDataSource"
DataTextField="Country" DataValueField="Country" >
<asp:ListItem Selected="True" Value="" >(Show All)</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td>Last Name</td>
<td><asp:TextBox runat="server" id="LastNameTextBox" Text="*" /></td>
</tr>
<tr>
<td></td>
<td><asp:Button runat="server" id="FilterButton" Text="Filter Results" /></td>
</tr>
</table>
</td>
<td valign="top">
<asp:GridView ID="EmployeesGridView"
DataSourceID="EmployeeDetailsSqlDataSource"
AutoGenerateColumns="false"
AllowSorting="true"
DataKeyNames="EmployeeID"
Gridlines="Both"
RunAt="server">
<HeaderStyle backcolor="Navy"
forecolor="White"/>
<RowStyle backcolor="White"/>
<AlternatingRowStyle backcolor="LightGray"/>
<EditRowStyle backcolor="LightCyan"/>
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" ReadOnly="true"/>
<asp:BoundField DataField="FirstName" HeaderText="First Name"/>
<asp:BoundField DataField="LastName" HeaderText="Last Name"/>
<asp:BoundField DataField="Country" HeaderText="Country"/>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
<asp:SqlDataSource ID="CountrySqlDataSource"
SelectCommand="SELECT DISTINCT Country FROM Employees"
EnableCaching="True"
CacheDuration="60"
ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
RunAt="server" />
<asp:SqlDataSource ID="EmployeeDetailsSqlDataSource"
SelectCommand="SELECT EmployeeID, LastName, FirstName, Country FROM Employees"
EnableCaching="True"
CacheDuration="60"
ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
FilterExpression="Country LIKE '{0}' AND LastName LIKE '{1}'"
RunAt="server">
<FilterParameters>
<asp:ControlParameter ControlID="CountryListBox" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="LastNameTextBox" PropertyName="Text" />
</FilterParameters>
</asp:SqlDataSource>
</form>
</body>
</html>