Home > Data Access in Web Forms > Data Source Controls Overview > Filtering Data Using Data Source Controls

Binding to Data Using a Data Source Control   Using Parameters with 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 with AccessDataSource Control Run Sample | View Source

Filtering with SqlDataSource Control Run Sample | View Source

To filter data, a data source control must be configured as follows:

• 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.
• For the XmlDataSource control, you can filter data using XPath queries. For more information, see Filtering Data Using the XmlDataSource Control.

You specify the filter to be applied by setting the data source control’s FilterExpression property.

<asp:sqldatasource ...
   ...
   filterexpression="filterCondition"
   ...
</asp:sqldatasource>

where filterCondition can be one or more key=value parameter pairs, expressed as follows.

filterexpression="Country={0}"

You can add one or more criteria to the filterCondition, separated by operators somewhat similar to defining the WHERE clause of an SQL statement.

"Country='{0}' AND State LIKE '%{1}%'"

You then specify where the filter expression will get its values, by defining FilterParameters on the data source control ( more on this in a while ).

In the filter expression, the unknown values are defined as placeholders enclosed within opening and closing curly braces { }, as shown above.

The placeholders are indexed, or numbered, with 0 representing the first parameter in the collection.

The index denotes which of the FilterParameters defined for the data source control will be used to obtain its value.

As earlier mentioned, the values for filter expressions are derived from the FilterParameters collection defined for the data source control.

Filter expression parameters are defined as follows:

<asp:sqldatasource ...
   ...
   filterexpression="filterCondition">

   <filterparameters>
      <asp:controlparameter controlid="controlID" propertyname="controlProperty" />
   </filterparameters>

</asp:sqldatasource>

where controlProperty is the name of the property of the control named controlID, the value of which will be the value for filterCondition.

To better visualize, let us fill in the blanks, like

... filterexpression="Country='{0}'">

<filterparameters>
   <asp:controlparameter controlid="dropDownList1" propertyname="SelectedValue" />
</filterparameters>

Parameters can retrieve data from any property value of any control on the page ( as shown above ), or from the request querystring object, session variables, stored 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.

The filter is applied to the data returned by the data source control at run time.

NOTE: Because values from the FilterParameters collection are substituted into the FilterExpression string without encoding, it may be prudent to validate all filter parameter values before applying a filter, though this would require writing your own logic for the purpose. A handler for the Filtering event of the data source control can be designed to access and validate filter parameter values before the filter is applied.

Valid syntax for filter expressions are based on the Expression property of the DataColumn class.

The below code snippet shows an SqlDataSource control using a filter expression, the value of which will be obtained at run time from the property value of a control defined elsewhere on the page.

<asp:sqldatasource id="articles" runat="server"
   connectionstring="<%$ ConnectionStrings:aspnet %>" 
   selectcommand="SELECT title, url, added, updated FROM aspx_articles ORDER BY title" 
   filterexpression="title LIKE '%{0}%' or url LIKE '%{0}%'">

   <filterparameters>
      <asp:controlparameter controlid="searchBox" propertyname="Text" />
   </filterparameters>

</asp:sqldatasource>

 Show me 

In that example, the value for the filter expression is obtained from the Text property of a ( TextBox ) control named searchBox.

Note that while this method works well with single word searches, there will be situations where the search may not return the desired results due to the limitation that we cannot change the behavior of the built-in logic on the data source control itself.

For example, searching for simple phrases may or may not return the desired results, because the built-in logic evaluates expressions as a whole and may find fewer or no matches of a phrase, depending on how the SQL LIKE expression evaluates, as you will see if you test out the search sample thoroughly.

The better approach here is to use the ADO.NET classes and build the search logic yourself, as shown in this alternate example.

Although a bit more involved, the search logic splits phrases into individual words ( or portions thereof ) that are then passed on to the SQL WHERE clause to define the criteria for the result set. This method proves to be much more accurate.

 Show me 

Binding to Data Using a Data Source Control   Using Parameters with Data Source Controls