Enable Filtering for the sqldatasource Control

The sqldatasource control allows you to filter (sort or select) query results without re-running the query. ForwardSqldatasource Control to add a filter, which can be changed after running the querySqldatasourceProvides available data without returning to the database.

This topic demonstrates howSqldatasource Control to enable filtering. BindSqldatasourceThe data binding control (such as the gridview control) of the control only displays the filtered results.

To use filtering, you mustSqldatasourceControl is set to return information in the dataset and cache its results. You can specifySqldatasourceThe filter expression of the rowfilter attribute of the dataview object under the control.

This filter can include parameters based on other controls, cookies, session variables, or query string values. For example, if a dropdownlist control contains a city name, you can useDropdownlistSelect the city in the control as the filter parameter.

 

Enable Filtering for the sqldatasource Control

2. CreateSqldatasourceControl. For more information, see How to: Use the sqldatasource control to connect to the SQL Server database.

4. SetSqldatasourceSet the performancemode attribute of the control to dataset.

6. SetSqldatasourceSet the enablecaching attribute of the control to true.

   To support filtering, the data returned by the query must be cached.

8. SetSqldatasourceThe cacheduration attribute of the control is set to the number of seconds for data caching. The number you select depends on your application.Program.

9. Set the filterexpression attribute of the control to the expression used to specify the returned data, as shown in the following example:

   Copy code

   Country = 'Germany'

   For more information about the filter expression syntax, seeRowfilter.

   Enable the filteredSqldatasourceControls will be similar to the following:

   copy the Code

   When you select a command for the database, bind it to thisSqldatasourceOnly the filtering results are displayed.

Filter with Parameters

You often want to filter query results based on only known values at run time. You can create a filter expression that includes a placeholder parameter, and then define a filter parameter to fill the placeholder. Filter parameters can be obtained from controls, query strings, cookies, session variables, configuration file attributes, or form attribute values.

Filter the sqldatasource control using parameters

2. set the filterexpression attribute in the sqldatasource control to an expression, this expression contains placeholders that represent the value of the filter parameter. Use the placeholder syntax { n }, where, n indicates the order of parameters.

   the following example shows a parameterized filter expression. The second expression contains multiple parameter placeholders.

   copy the Code

   filterexpression =" Category = '{0}' "filterexpression =" Country = '{0}} 'and city =' {1} '"

3. Create a filterparameters elementSqldatasourceChild level of the element. For each filter parameter placeholder, add an element of any of the following types:

   • Controlparameter

   • Cookieparameter

   • Formparameter

   • Profileparameter

   • Sessionparameter

   • Parameter

   The following example shows how to createDropdownlistFilter parameters for obtaining the value of the control:

   Copy code

   <Filterparameters> <asp: controlparameter name = "categorylist" controlid = "dropdownlist1" propertyvalue = "selectedvalue"/> </filterparameters>

   Note:
   The name attribute of the parameter is required. However, parameters will match the placeholder by name instead of by order.

   The following example shows a complete parameter filter.SqldatasourceControls:

   Copy code

   <Asp: sqldatasource id = "sqldatasource1" enablecaching = "true" datasourcemode = "dataset" runat = "server" selectcommand = "select * from MERs" connectionstring = "<% $ connectionstrings: northwindconnectionstring1 %> "filterexpression =" Country = '{0}' "> <filterparameters> <asp: controlparameter name = "countryparam" controlid = "dropdownlist1" propertyname = "selectedvalue"/> </filterparameters> </ASP: sqldatasource>