Walkthrough: Filtering Data in a Web Page Using Declarative Syntax

The QueryExtender control is used to create filters for data that is retrieved from a data source, without having to create explicit queries in the data source. The QueryExtender control has the following benefits:

• Provides richer expressions for filtering than writing a Where clause.

• Provides a common query language for the LinqDataSource control, EntityDataSource control, and third-party data sources. For example, if you use the QueryExtender with these data source controls, you can provide searching capability in a Web page without writing a model-specific Where clause or eSQL statement.

• Supports a variety of filtering options that can be used individually or together.

You can use the QueryExtender control in the markup of a Web page to filter data by using only declarative syntax.

This walkthrough illustrates data filtering by selecting values from the Products table in the AdventureWorks sample database.

This walkthrough illustrates the following tasks:

• Creating a data-driven ASP.NET Web site.

• Adding data to the Web site using Visual Studio 2010 or Visual Web Developer 2010 Express.

• Using the LinqDataSource control to access the data.

• Using filter options to create filtered queries that display only selected records.

• Displaying data with the GridView control.

Prerequisites

In order to complete this walkthrough, you will need the following:

• Visual Studio 2010 or Visual Web Developer 2010 Express.

• SQL Server Express. If you have SQL Server installed, you can use that, but you must make small adjustments to some of the procedures.

• The AdventureWorks sample database. In this walkthrough you will add the database file to the Web site and connect to the .mdf file. For information about how to connect to the database in Visual Studio, see How to: Connect to the AdventureWorksLT Database using an .MDF File. You can also download the AdventureWorks sample database and connect to it in Visual Studio. For information about how to install AdventureWorks and create a connection to it in Visual Studio, see How to: Set Up an AdventureWorksLT Sample Database for ASP.NET Development.

Creating an ASP.NET Web Site

To begin, you will create a Web site. If you have already created an ASP.NET Web site, you can use that site in this walkthrough.

This walkthrough uses a Web site project. You could use a Web application project instead. For information about the difference between these Web project types, see Web Application Projects versus Web Site Projects.

To create an ASP.NET Web site

1. Start Visual Studio or Visual Web Developer.

2. In the File menu, click New Web Site. (If you do not see this option, click New, and then click Web Site.)

   The New Web Site dialog box is displayed.

3. Under Installed Templates, click Visual Basic or Visual C# and then select ASP.NET Web Site.

4. In the Web location box, select File System and enter the name of the folder where you want to keep the pages of the Web site. For example, enter the folder name C:\Websites\FilterDemo.

5. Click OK.

   Visual Studio creates a Web project that includes prebuilt functionality for layout (a master page, the Default.aspx and About.aspx content pages, and a cascading style sheet), for Ajax (client script files), and for authentication (ASP.NET membership).

   For this walkthrough, you will not use the Default.aspx page. Instead, you will create a new page that does not use the master page.

6. Close the Default.aspx page.

7. Right-click the root folder of the Web site and then click Add New Item.

8. Under Installed Templates, select Web Form and then click Add.

   Visual Studio creates a new page named Default2.aspx.

9. Change the default name to FilterDemo.aspx and then click Add.

10. Save the page.

Adding Data to the Web Site

In this section, you will add the AdventureWorks sample database to your Web site. If the Web site is already connected to the AdventureWorks database, you do not need to perform this procedure.

To add a database file to the project

• Add the AdventureWorks.mdf sample database file to the Web site and connection to it in Visual Studio.

  For information about how to install AdventureWorks and create a connection to it in Visual Studio, see How to: Connect to the AdventureWorksLT Database using an .MDF File.

  Note

  The procedure for installing the AdventureWorks.mdf and the AdventureWorksLT.mdf sample database file is similar. During the installation, you will select the AdventureWorks sample database instead of the AdventureWorksLT.

The next step is to create the data model. You can use the use LINQ to SQL or Entity Data model. In this walkthrough, you use the use LINQ to SQL data model.

To create a data model using LINQ to SQL

1. In Solution Explorer, right-click the Web site, click Add, and then click New Item.

2. Under Installed Templates, select Data, and then select LINQ to SQL Classes.

3. In the Name box, enter a name for the database model. For example, you can enter the name AdventureWorks.dbml and then click Add.

   Note

   If you are prompted to create an App_Code folder, click Yes.

   The Object Relational Designer is displayed.

4. On the design surface, click the Server Explorer link.

5. In Server Explorer, expand the AdventureWorks database, expand the Tables node, and then drag the Product table into the designer window.

   The table and its columns are represented as entities in the designer window.

6. Save the .dbml file and close it.

7. In the Build menu, click the Build command.

Adding the Data Source Control

The next step is to add a data source control to a Web page and configure it to work with the database. The QueryExtender control, which enables filtering, supports the LinqDataSource and the EntityDataSource controls. In this walkthrough, you will use the LinqDataSource control.

To add the data source control

1. Switch to or open the FilterDemo.aspx page that you created earlier.

2. Switch to Design view.

3. In the Toolbox, from the Data tab, drag a LinqDataSource control onto the page.

4. In the smart tag menu for the LinqDataSource control, click Configure Data Source.

5. Select Show only DataContext objects.

6. Under Choose your context object, select AdventureWorksDataContext and then click Next.

7. Under Table, select Products (Table<Product>).

8. Under GroupBy, select None.

9. Under Select, select all the columns and then click Finish.

   Note

   To select all the columns, you can check the * check box. In order for the table and columns to be populated, you must have built the Web application after you created the data model.

10. In the Properties window, make sure that the following two properties are set:

    • The ContextTypeName property is set to AdventureWorksDataContext.

    • The TableName property is set to "Products".

11. Switch to Source view.

    The markup will resemble the following example:

    <asp:LinqDataSource ID="LinqDataSource1" runat="server"
        ContextTypeName="AdventureWorksDataContext" EntityTypeName=""  
        TableName="Products" >
    </asp:LinqDataSource>
    

Filtering Data Using Declarative Syntax

In this section, you will add search capability to the Web page. To do so, you will add controls that accept user input. You will also use the QueryExtender control and set its filter options based on user input. The QueryExtender control lets you do this by using declarative syntax. For this walkthrough, you will use the following filter option to provide search capability in the Web site:

• SearchExpression, which you will use to search for product names that match a specified string value.

• RangeExpression, which you will use to search for products whose ReorderPoint column value is in a specified range.

• PropertyExpression, which you will use to search for products that are classified as finished goods.

• CustomExpression, which you will use to execute a user-defined LINQ query.

The results of the filtered data will be displayed in a GridView control.

To add the filtering control

1. Switch to Source view.

2. In the body tag, after the closing tag of the LinqDataSource control, add the following markup to the page:

   <asp:QueryExtender runat="server" TargetControlID="LinqDataSource1">
   </asp:QueryExtender>
   

   This adds the QueryExtender control to the page and sets its associated data source control to be the LinqDataSource control that you added earlier.

   Note

   You can add the QueryExtender control to the page by dragging the control from the Toolbox. However, the control does not support adding a data source or expressions in the designer.

Searching for Strings

Next, you will add an SearchExpression object and configure it to perform a "starts with" search using the value in a text box.

To search for a string

1. From the Toolbox, in the Standard node, drag a TextBox control onto the page.

2. In front of the opening tag of the TextBox, enter Search: to provide a caption.

3. Set the ID property to SearchTextBox.

   The markup for the text box control resembles the following example.

   Search: <asp:TextBox ID="SearchTextBox" runat="server" />
   

4. Between the opening and closing tags of the QueryExtender control, add the following SearchExpression filter:

   <asp:SearchExpression SearchType="StartsWith" DataFields="Name">
     <asp:ControlParameter ControlID="SearchTextBox" />
   </asp:SearchExpression>
   

   The search expression searches the Name column for products that start with the string that is entered in the SearchTextBox control.

5. From the Toolbox, drag a Button control onto the page and set its Text property to Search.

   The markup for the button will resemble the following:

   <asp:Button ID="Button1" runat="server" Text="Search" />
   

6. From the Toolbox, drag a GridView control onto the page and set its properties as shown in the following example:

   <asp:GridView ID="GridView1" runat="server"  
       DataSourceID="LinqDataSource1"   
       DataKeyNames="ProductID"  AllowPaging="True">
   </asp:GridView>
   

   The result of the search will be displayed in the GridView control.

Testing the Search Filter

You can now test the search filter you have created.

To test the search filter

1. Press CTRL+F5 to run the page.

2. Enter a string in the search text box and then click Search.

   For example, enter Ch and then click Search. Notice that results that are shown in the GridView control contain only product names that start with "Ch".

   Note

   The LINQ data provider is not case sensitive. However, if you use a data provider that supports the ComparisonType property, you can change the case sensitivity.

3. Close the browser.

Searching a Range of Values

Next, you will add an RangeExpression object and configure it to search the ReorderPoint column for products that have the ReorderPoint in the range specified by values in two text boxes.

To search a range

1. Switch to Source view.

2. From the Toolbox, drag two TextBox controls onto the page. You can position the text boxes under the search text box.

   You will use the text boxes to specify the range to start from and end at.

3. In front of the opening tag of the first TextBox control, enter From:, and in front of the opening tag of the second TextBox control, enter To:.

4. Set the ID of the first text box to FromTextBox and set the ID of the second text box to ToTextBox.

   The markup for the text box controls resembles the following example.

   From: <asp:TextBox ID="FromTextBox" runat="server" ></asp:TextBox>
   To: <asp:TextBox ID="ToTextBox" runat="server" ></asp:TextBox>
   

5. Between the opening and closing tags of the QueryExtender control, add the following RangeExpression filter:

   <asp:RangeExpression DataField="ReorderPoint" MinType="Inclusive"  
       MaxType="Inclusive">
   </asp:RangeExpression>
   

   This searches the ReorderPoint column and includes the values that you specify in the text boxes in the search.

6. Between the opening and closing tags of the RangeExpression filter, add the following markup:

   <asp:ControlParameter ControlID="FromTextBox" />
   <asp:ControlParameter ControlID="ToTextBox" />
   

   This configures the text boxes to provide the parameter values for the filter. The markup for the completed RangeExpression filter resembles the following example:

   <asp:RangeExpression DataField="ReorderPoint" MinType="Inclusive"  
       MaxType="Inclusive">
     <asp:ControlParameter ControlID="FromTextBox"/>
     <asp:ControlParameter ControlID="ToTextBox"/>
   </asp:RangeExpression>
   

Testing the Range Filter

You can now test the Range filter you have created.

To test the range filter

1. Press CTRL+F5 to run the page.

2. Enter values in the From and To text boxes and then click Search.

   For example, enter a range from 300 to 400 and click the Search button. The data that is returned includes product names that have a reorder point between 300 and 400. Notice that the results that are shown in the GridView control contain only product names with Reorder values between 300 and 400.

3. Close the browser.

Searching Based on a Boolean Value

Next, you will add an PropertyExpression object and configure it to filter the data based on a Boolean value. The Boolean value is specified in a check box control.

To search based on a Boolean value

1. From the Toolbox, drag a CheckBox control onto the page and set the ID property to MakeCheckBox. You can position the check box under the text boxes you added earlier.

2. In front of the opening tag of the CheckBox control, enter Make More to provide a caption for the check box.

   The markup resembles the following example.

   Make More: <asp:CheckBox ID="MakeCheckBox" runat="server" />
   

3. Between the opening and closing tags of the QueryExtender control, under the range expression, add the following markup:

   <asp:PropertyExpression></asp:PropertyExpression>
   

   This adds the property expression filter option to the page.

4. Between the opening and closing tags of the PropertyExpression filter, add a ControlParameter control.

5. Set the ID of the control ControlParameter parameter to the MakeCheckBox.

6. Set the Name property of the ControlParameter parameter to MakeFlag, which is the column to filter by.

   The markup will resemble the following:

   <asp:PropertyExpression>
       <asp:ControlParameter ControlID="MakeCheckBox" Name="MakeFlag" />
   </asp:PropertyExpression>
   

Testing the Boolean Filter

You can now test the Boolean filter you have created.

To test the Boolean filter

1. Press CTRL+F5 to run the page.

2. Select the Make More check box and then click Search.

   The data that is returned includes only product names that have the value in the MakeFlag column set to true (the column is checked.)

3. Close the browser.

Using Multiple Filters

You can use multiple filters at the same time. If you use multiple filters, the query generates AND clauses for the filters. In this section, you will test all the filters together.

To test multiple filters

1. Press CTRL+F5 to run the page

2. In the Search text box, enter Ch.

3. Select the Make More check box.

4. In the From text box, enter 500.

5. In the To text box, enter 1000.

6. Click Search.

   The data that is returned includes products names that starts with "Ch", that have the Makeflag column checked, and that have ReorderPoint value between 500 and 1000.

Using a Custom Query to Filter Data

In this section, you will filter the data by using a custom LINQ query. To do so, you will do the following:

• Create a custom LINQ query in the class file for the page.

• Add a data source control and a QueryExtender control to the page.

• Add a custom expression to the QueryExtender control to execute the custom LINQ query.

• Add a GridView control to display the results of the query.

You can use either the CustomExpression filter or the MethodExpression filter to execute the query. The behavior of these filters is similar. In this walkthrough, you will use the CustomExpression filter. For information about how to use the method expression filter, see MethodExpression.

The next step is to create the custom LINQ query.

To create a custom LINQ query

1. In Solution Explorer, right-click the Web application, click Add, and then click New Item.

2. Under Installed Templates, select Web, and then select Web Form. You can use the default name for the page.

3. Check Place code in separate file and then click Add.

4. In Solution Explorer, right-click the page that you just created and then click View Code.

5. Add the following namespaces to by using the using (C#) or Imports (Visual Basic) directives.

   Imports System.Web.UI.WebControls.Expressions
   Imports System.Data.Linq
   

   using System.Web.UI.WebControls.Expressions;
   using System.Linq;
   

6. Add the following method to the class.

   Protected Sub FilterProducts(ByVal sender As Object, ByVal e As CustomExpressionEventArgs)
           e.Query = From p In e.Query.Cast(Of Product)() _
                     Where p.ListPrice >= 3500 _
                     Select p
       End Sub
   

   protected void FilterProducts(object sender, CustomExpressionEventArgs e)
   {
           e.Query = from p in e.Query.Cast<Product>()
                     where p.ListPrice >= 3500
                     select p;
   }
   

   The query searches the ListPrice column for products that have a list price of 3500 or more.

The next step is to add the data source control to the page and configure it to work with the data model. In this walkthrough, you will use the LinqDataSource control.

To add the data source control

1. Switch to Design view.

2. In the Toolbox, expand Data and then drag a LinqDataSource control onto the page.

3. Click the smart tag and then click Configure Data Source.

4. Select Show only DataContext objects.

5. Under Choose your context object, select AdventureWorksDataContext and then click Next.

6. Under Table, select Products (Table<Product>).

7. Under GroupBy, select None.

8. Under Select, select all the columns and then click Finish.

   Note

   To select all the columns, you can check the * check box.

9. In the Properties window, set the ContextTypeName property to AdventureWorksDataContext.

10. In the Properties window, set the TableName property to "Products".

11. Switch to Source view.

    The markup will resemble the following example:

    <asp:LinqDataSource ID="LinqDataSource1" runat="server"
        ContextTypeName="AdventureWorksDataContext" EntityTypeName=""  
        TableName="Products" >
    </asp:LinqDataSource>
    

Executing the Custom filter

In this section, you will add will add the filtering controls that will enable you to execute the custom query.

To add the filtering control

1. Switch to Source view.

2. In the body element, after the closing tag of the LinqDataSource control, add the following markup to the page:

   <asp:QueryExtender runat="server" TargetControlID="LinqDataSource1">
   </asp:QueryExtender>
   

   This adds the QueryExtender control to the page and sets its associated data source control to be the LinqDataSource control that you added earlier.

3. Between the opening and closing tags of the QueryExtender control, add a CustomExpression filter and set its OnQuerying attribute to FilterProducts.

   The markup will resemble the following:

   <asp:CustomExpression OnQuerying="FilterProducts"></asp:CustomExpression>
   

4. From the Toolbox, drag a GridView control onto the page and set its properties as shown in the following example:

   <asp:GridView ID="GridView1" runat="server"  
       DataSourceID="LinqDataSource1"   
       DataKeyNames="ProductID"  AllowPaging="True">
   </asp:GridView>
   

   The result of filtered the data will be displayed in the GridView control.

Testing the Custom Filter

You can now test the custom filter.

To test the Web site

• Press CTRL+F5 to run the page.

  The data that is returned includes only product that have a list price of 3500 or more.

Code Example

After you have completed this walkthrough, the markup for the page you created will resemble the following example. (This example shows only the markup that follows the DOCTYPE declaration.)

<html xmlns="https://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Filter Demo</title>
</head>
<body>
    <form id="form1" runat="server">
    Search:<asp:TextBox ID="SearchTextBox" runat="server" />
    <p>

        Make More:<asp:CheckBox ID="MakeCheckBox" runat="server" />
    <p>
        From:<asp:TextBox ID="FromTextBox" runat="server" ></asp:TextBox>
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        To:<asp:TextBox ID="ToTextBox" runat="server" ></asp:TextBox>
    </p>
    <p>
    <asp:Button ID="Button1" runat="server" Text="Search" />
    </p>
    <asp:LinqDataSource ID="LinqDataSource1"  
        ContextTypeName="FilterDemo.AdventureWorksDataContext"  
        TableName="Products" runat="server"> 
    </asp:LinqDataSource>
    <asp:QueryExtender runat="server" TargetControlID="LinqDataSource1">

    <asp:SearchExpression SearchType="StartsWith" DataFields="Name" >
    <asp:ControlParameter ControlID="SearchTextBox" />
    </asp:SearchExpression>

    <asp:RangeExpression DataField="ReorderPoint" MinType="Inclusive" MaxType="Inclusive">
    <asp:ControlParameter  ControlID="FromTextBox" />
    <asp:ControlParameter  ControlID="ToTextBox" />
    </asp:RangeExpression>

<asp:PropertyExpression>
    <asp:ControlParameter ControlID="MakeCheckBox" Name="MakeFlag" />
    </asp:PropertyExpression>
    </asp:QueryExtender>

<asp:GridView ID="GridView1" runat="server"  
     DataSourceID="LinqDataSource1" AllowPaging="True"    
     DataKeyNames="ProductID>
</asp:GridView>
    </form>

    </body>
    </html>

After you have completed this walkthrough, the markup for the custom filter page will resemble the following example. (This example shows only the markup that follows the DOCTYPE declaration.)

<html xmlns="https://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Custom Filter</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    </div>
    <asp:LinqDataSource ID="LinqDataSource1" runat="server" 
        ContextTypeName="AdventureWorksDataContext" EntityTypeName="" 
        TableName="Products">
    </asp:LinqDataSource>

    <asp:QueryExtender ID="QueryExtender1" runat="server" TargetControlID="LinqDataSource1">
        <asp:CustomExpression OnQuerying="FilterProducts"></asp:CustomExpression>
    </asp:QueryExtender>

    <asp:GridView ID="GridView1" runat="server"  
        DataSourceID="LinqDataSource1"   
        DataKeyNames="ProductID"  AllowPaging="True">
    </asp:GridView>
    </form>
</body>
</html>

After you have completed this walkthrough, the code for the custom LINQ query in the page class file will resemble the following example.

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.Expressions
Imports System.Data.Linq

Partial Class CustomVB
    Inherits System.Web.UI.Page

    Protected Sub FilterProducts(ByVal sender As Object, ByVal e As CustomExpressionEventArgs)
        e.Query = From p In e.Query.Cast(Of Product)() _
            Where p.ListPrice >= 3500 _
            Select p
    End Sub

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.Expressions;
using System.Linq;

public partial class _Default : System.Web.UI.Page 
{
    protected void FilterProducts(object sender, CustomExpressionEventArgs e)
    {
        e.Query = from p in e.Query.Cast<Product>()
                  where p.ListPrice >= 3500
                  select p;
    }
}

Next Steps

In this walkthrough, you used the QueryExtender control to filter data by using declarative syntax. The QueryExtender control provides more filter options than have been illustrated in this walkthrough. You might want to try the following:

• Sort the data by adding the OrderByExpression and the ThenBy filters to the Web page.

• Create a custom filter by using the MethodExpression filter. For more information, see MethodExpression.

  Note

  The QueryExtender control provides the DynamicFilter and ControlFilterExpression controls that you can use in ASP.NET Dynamic Data Web sites to provide additional filtering. These filters are supported only in ASP.NET Dynamic Data Web sites. For more information, see QueryExtender.

See Also

Reference

QueryExtender