How to: Enable Filtering for the SqlDataSource Control

The SqlDataSource control allows you to filter (sort or select) the results of a query without re-running the query. By adding filtering to a SqlDataSourcecontrol, you can change what data is made available by the SqlDataSource after a query has been run, without returning to the database.

This topic shows you how to enable filtering for a SqlDataSourcecontrol. Data-bound controls that are bound to the SqlDataSource control, such as a GridView control, will show only the filtered results.

To use filtering, you must set the SqlDataSource control to return information in a dataset and to cache its results. You can then specify a filter expression that is applied as a RowFilter property to the DataView object that underlies the SqlDataSource control.

The filter can include parameters that are based on the values of another control, cookies, Session variables, or query strings. For example, if a DropDownList control contains city names, you can use the city selected in the DropDownList control as your filtering parameter.

Note

Creating a filter expression with parameters is not the same as creating a parameterized SelectCommand property and specifying SelectParameters property values. A parameterized filter applies different views of the data to a cached dataset. A parameterized select command performs a query against the data source.

To enable filtering for a SqlDataSource control

1. Create a SqlDataSource control with a valid connection string and select statement. For more information, see How to: Connect to a SQL Server Database Using the SqlDataSource Control.

2. Set the SqlDataSource control's DataSourceMode property to DataSet.

3. Set the SqlDataSource control's EnableCaching property to true.

   The data returned by the query must be cached in order to support filtering.

4. Set the SqlDataSource control's CacheDuration property to the number of seconds you want the data to be cached. The number you choose depends on your application.

5. Set the control's FilterExpression property to an expression specifying the data to return, as in the following example:

   country = 'Germany'
   

   For information about the syntax of filter expressions, see RowFilter.

   The SqlDataSource control with filtering enabled will be similar to the following:

   <asp:SqlDataSource 
       ID="SqlDataSource1" 
       DataSourceMode="DataSet"
       EnableCaching="true"
       Runat="server" 
       SelectCommand="Select * From Customers"
       ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
       FilterExpression="country = 'Germany'">
   </asp:SqlDataSource>
   

   When the select command is executed against a database, controls that are bound to this SqlDataSource control will display only filtered results.

Filtering with Parameters

You will often want to filter the results of a query based on values that are known only at run time. You can create filter expressions that include parameter placeholders, and then define filter parameters to fill the placeholders. The filter parameters can get their values from controls, a query string, a cookie, Session variable, a profile property, or Form property value.

To filter a SqlDataSource control using parameters

1. Set the FilterExpression property in the SqlDataSource control to an expression that includes a placeholder for the filter parameter value. The placeholders use the syntax {n}, where n indicates the sequential order of the parameter.

   The following examples show parameterized filter expressions. The second one includes multiple parameter placeholders.

   FilterExpression="category = '{0}'"
   FilterExpression="country = '{0}' AND city = '{1}'"
   

2. Create a FilterParameters element as a child of your SqlDataSource element. For each filter parameter placeholder, add an element of one of the following types:

   • ControlParameter

   • CookieParameter

   • FormParameter

   • ProfileParameter

   • SessionParameter

   • Parameter

   The following example shows how to create a filter parameter that gets its value from a DropDownList control:

   <FilterParameters>
     <asp:ControlParameter
      Name="CategoryList" ControlID="DropDownList1" 
      PropertyValue="SelectedValue" />
   </FilterParameters>
   

   Note

   The Name property for the parameter is required. However, parameters are matched to placeholders in sequential order, not by name.

   The following example shows how a complete SqlDataSource control with a parameterized filter:

   <asp:SqlDataSource 
     ID="SqlDataSource1" 
     EnableCaching="true"
     DataSourceMode="DataSet"
     Runat="server" 
     SelectCommand="Select * from Customers"
     ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString1 %>"
     FilterExpression="country = '{0}'">
     <FilterParameters>    <asp:ControlParameter      Name="countryparam"      ControlID="DropDownList1"      PropertyName="SelectedValue" />  </FilterParameters>
   </asp:SqlDataSource>
   

See Also

Concepts

Caching Data Using Data Source Controls