Como: Ativar filtragem para o controle SqlDataSource

Artigo
10/15/2014

The SqlDataSource control allows you to filter (sort or select) the results of a query without re-running the query.Ao adicionar filtragem a um SqlDataSourcecontrole, você pode alterar os dados que são disponibilizados pela SqlDataSource Após ter sido executada uma consulta, sem retornar ao banco de dados.

Este tópico mostra como ativar filtragem para um SqlDataSourcecontrole. 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.

Observação:
Creating a filter expression with parameters is not the same as creating a parameterized SelectCommand property and specifying SelectParameters property values.Um filtro parametrizado aplica diferentes exibições dos dados a um dataset em cache.Um comando select parametrizado executa uma consulta na fonte de dados.

Para ativar filtragem para um controle SqlDataSource

Create a SqlDataSource control with a valid connection string and select statement.Para obter mais informações, consulte Como: Conectar-se a um banco de dados SQL servidor usando o controle SqlDataSource.
Set the SqlDataSource control's DataSourceMode property to DataSet.
Set the SqlDataSource control's EnableCaching property to true.

The data returned by the query must be cached in order to support filtering.
Set the SqlDataSource control's CacheDuration property to the number of seconds you want the data to be cached.O número que você escolher depende do seu aplicativo.
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.

Filtragem com parâmetros

You will often want to filter the results of a query based on values that are known only at run time.Você pode criar expressões de filtro que contêm espaços reservados de parâmetro, e depois definir os parâmetros de filtro para preencher os espaços reservados.The filter parameters can get their values from controls, a query string, a cookie, Session variable, a profile property, or Form property value.

Para filtrar um controle SqlDataSource usando parâmetros

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.

Os seguintes exemplos mostram expressões de filtro parametrizadas.The second one includes multiple parameter placeholders.
```
FilterExpression="category = '{0}'"
FilterExpression="country = '{0}' AND city = '{1}'"
```
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:
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>
```
Observação:

The Name property for the parameter is required.Entretanto, os parâmetros são correlacionados aos espaços reservados em ordem sequencial, não pelo nome.

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>
```

Observação:
The Name property for the parameter is required.Entretanto, os parâmetros são correlacionados aos espaços reservados em ordem sequencial, não pelo nome.

Consulte também

Conceitos

Cache de dados usando controles de fonte de dados

Partilhar via