Controls You Can Use on Web Forms ASP.NET Data Source Controls SqlDataSource Control
The SqlDataSource control allows you to filter (sort or select) the results of a query without rerunning the query. By adding filtering to a SqlDataSource control, you can change what data is made available by the SqlDataSource control after a query has been run, without returning to the database.
This topic shows you how to enable filtering for a SqlDataSource control. 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 filtering can also include parameters that are based on the values of another control, cookies, sessions, or query strings. For example, if a DropDownList control contains city names, you can use the selected city 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.
- In Design view, from the Data tab of the Toolbox, drag a SqlDataSource control onto the page.
- Right-click the control and click Show Smart Tag.
- In the SqlDataSource Tasks menu, click Configure Data Source.
- Follow the Configure Data Source wizard to create or choose a connection and to create a query to return data from the data source. For details, see Walkthrough: Basic Data Access in Web Pages.
- In the Properties window, set the DataSourceMode property to DataSet.
- Set the EnableCaching property to true.
- Set the CacheDuration property to the number of seconds that you want the data to be cached. The number you choose depends on your application.
- Set the FilterExpression property to an expression specifying the data to return, as shown in the following example expression.
city = 'Seattle'
For information about the syntax of filter expressions, see RowFilter.
At times, you might 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, query strings, cookies, session variables, profile properties, or Form property values.
To filter a SqlDataSource control using parameters
- In Design view, click the SqlDataSource control to which you want to add filtering parameters.
- In the Properties window, set the FilterExpression property 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 expression includes multiple parameter placeholders.
category = '{0}'
state = '{0}’ AND city = '{1}'
For information about the syntax of filter expressions, see RowFilter.
- In the Properties window, click the ellipsis button next to FilterParameter.
- In the Parameter Collection Editor dialog box, click Add Parameter.
- In the Name field, type the name that you want to assign to the parameter.
The Name property for the parameter is required. However, parameters are matched to placeholders in sequential order, not by name.
NOTE: To edit the name after creating the parameter, click the name and then press F2 to put the name into editing mode.
- In the Parameter source drop down list, select the appropriate source for the parameter, such as a control on the Web page.
Depending on which parameter source that you have selected, the dialog box changes to include a way to specify the ID of the property value.
- If you selected Control as the parameter source, in the ControlID drop-down list, select the ID for the control that will provide the value for the parameter. Optionally, add a default value for the parameter; and then click OK.
- Repeat steps 4 through 7 for each parameter that you want to create.
- When you are finished creating parameters, click OK to close the Parameter Collection Editor dialog box.
the FilterExpression property 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 expression includes multiple parameter placeholders.
category = '{0}'
state = '{0}’ AND city = '{1}'
For information about the syntax of filter expressions, see RowFilter.
In the Properties window, click the ellipsis button next to FilterParameter.
In the Parameter Collection Editor dialog box, click Add Parameter.
In the Name field, type the name that you want to assign to the parameter.
The Name property for the parameter is required. However, parameters are matched to placeholders in sequential order, not by name.
NOTE: To edit the name after creating the parameter, click the name and then press F2 to put the name into editing mode.
In the Parameter source drop down list, select the appropriate source for the parameter, such as a control on the Web page.
Depending on which parameter source that you have selected, the dialog box changes to include a way to specify the ID of the property value.
If you selected Control as the parameter source, in the ControlID drop-down list, select the ID for the control that will provide the value for the parameter. Optionally, add a default value for the parameter; and then click OK.
Repeat steps 4 through 7 for each parameter that you want to create.
When you are finished creating parameters, click OK to close the Parameter Collection Editor dialog box.
|