Walkthrough: Filtering Rows in Tables with a Parent-Child Relationship

Article
08/31/2009

[Note: This document is a preliminary version of a walkthrough that will be released with the Beta 2 version of Visual Studio 2010 and ASP.NET 4.]

Introduction

This walkthrough shows how to filter rows in tables with a parent-child relationship.

Filtering table rows refers to the ability provided to the user to choose (filter) the table rows to display based on a selected column value, it also defines whether data fields are displayed or in what order they are displayed.

This walkthrough illustrates the following tasks:

Set the parent table for selecting a foreign-key. The setting includes a T:System.Web.UI.WebControls.LinqDataSource control to access the database that contains the parent table. It also includes a T:System.Web.UI.WebControls.GridView control to display the parent table and enable the selection of the foreign-key value.
Set the child table for displaying filtered rows. The setting includes a T:System.Web.UI.WebControls.LinqDataSource control to access the database that contains the child table. It also includes a T:System.Web.UI.WebControls.GridView control to display the child table filtered rows.
Set child table rows filtering. The T:System.Web.UI.WebControls.QueryExtender control is set in such a way to enable child table rows filtering based on the foreign-key selected by the user in the parent table.

The example shown in this topic uses tables from the AdventureWorks database. More specifically, it uses the child Product table that contains the foreign-key column ProductCategory defined in the parent table ProductCategories. The relationship between tables is one-to-many that is a single category can contain many products. The following figure shows the relationship between the tables:

By default, Dynamic Data includes templates that let you perform filtering for Boolean, foreign-key, and enumeration column values. In these templates, filtering is defined declaratively by using the ASP.NET T:System.Web.UI.WebControls.QueryExtender control .

A Visual Studio project with source code is available at this location: Download LINQ to SQL version.

The following figure shows the steps that Dynamic Data performs to filter rows in tables that have a parent-child relationship.

The figure illustrates the following sequence:

1. The parent data-bound control passes the user's selected foreign-key to the T:System.Web.UI.WebControls.QueryExtender control.

2. The T:System.Web.UI.WebControls.QueryExtender control uses this foreign-key value to create the query filtering information. It then passes this filtering information to the data source control that fetches child rows.

3. The child data source control passes the filtering query information to the data source provider.

4. The data source provider passes the query to the database.

5. The database returns the filtered table rows.

6. The data source provider sends this data back to the child data source control.

7. This control finally passes the filtered table rows to the child data-bound control for display.

Prerequisites

This walkthrough assumes that you have created a Dynamic Data Web site and that you are using a data context that is based on LINQ to SQL. For more information, see Walkthrough: Creating a New Dynamic Data Web Site using Scaffolding.

Setting the Parent Table for Selecting a Foreign-Key

In this section you will set the parent table for selecting a foreign-key. The setting includes a T:System.Web.UI.WebControls.LinqDataSource control to access the database that contains the parent table. It also includes a T:System.Web.UI.WebControls.GridView control to display the parent table and enable the selection of the foreign-key value.

To set the parent table for selecting a foreign key

8. In Solution Explorer, right-click the project name, and then click Add New Item.

9. Under Installed Templates in the right pane, select Web Form.

In the Name box, enter ParentChild.aspx.

10. Switch to Design modality by clicking on the Design tab.

11. From the Data tab of the Toolbox, add a T:System.Web.DynamicData.DynamicDataManager control to the page.

The T:System.Web.DynamicData.DynamicDataManager control must be included on a page in order to support Dynamic Data for data-bound controls. The markup for the T:System.Web.DynamicData.DynamicDataManager control must precede the markup for any controls that use Dynamic Data. We will register the necessary data-bound controls to be managed in later steps.

12. From the Data tab of the Toolbox, add a T:System.Web.UI.WebControls.LinqDataSource control to the page.

13. From the LinqDataSource Tasks menu click on the Configure Data Source.

14. The Choose a Context Object dialog box is displayed.

15. Choose the context object AdventureWorksLTDataContext.

16. Click the Next button.

17. The Configure Data Selection dialog box is displayed.

18. In the Table drop-down list control, select the parent table ProductCategories.

19. In the Select box leave the box with the asterisk checked. This will select all the rows of the ProductCategories table.

20. Click the Finish button.

21. From the Data group of the Toolbox, add a T:System.Web.UI.WebControls.GridView control to the page. This control is used to display the ProductCategories table data fields.

22. From the GridView Tasks menu in the Choose Data Source drop-down list control, select LinqDataSource1. This is the ID of the data source control created in the previous steps.

23. Enable paging and selection by checking the related check-boxes.

24. Switch to source modality, by clicking the Source tab.

25. For display clarity above the T:System.Web.UI.WebControls.GridView control enter the following markup:

 <h2>Parent Table: ProductCategories</h2>

26. In the <Coulmns> tag, delete all the <asp:BoundField> controls.

27. Enable automatic columns generation, set page size, and set the selection index to a category that is not empty. The following example shows how to set these properties.

    <asp:GridView ID="GridView1" runat="server"      DataSourceID="LinqDataSource1"      AutoGenerateColumns="True"     PageSize="5" SelectIndex="4">    </asp:GridView>

28. Switch to Design modality by clicking on the Design tab.

29. From the DynamicDataManager Tasks menu, click Register Controls.

30. The DataControlReference Collection Editor is displayed.

31. In the Members pane, click the Add button. A DataControl entry is displayed in the Members box.

32. In the DataControl properties pane in the ControlID drop-down list control box select GridView1.

33. Click OK.

34. Right-click on the page and select View in Browser. The ProductCategories table rows are displayed.

35. Save the ParentChid.aspx file.

Setting the Child Table for Displaying Filtered Rows

In this section you will set the child table for displaying filtered rows. The setting includes a T:System.Web.UI.WebControls.LinqDataSource control to access the database that contains the child table. It also includes a T:System.Web.UI.WebControls.GridView control to display the child table filtered rows.

To set the child table for displaying filtered rows

36. While in the ParentChild.aspx file, switch to Design modality by clicking on the Design tab.

37. From the Data tab of the Toolbox, add a T:System.Web.UI.WebControls.LinqDataSource control to the page.

38. From the LinqDataSource Tasks menu click on the Configure Data Source.

39. The Choose a Context Object dialog box is displayed.

40. In the Choose your context object drop-down list control box, choose the context object AdventureWorksLTDataContext.

41. Click the Next button.

42. The Configure Data Selection dialog box is displayed.

43. In the Table drop-down list control box, select the child table Products.

44. In the Select box leave the box with the asterisk checked. This will select all the rows of the Products table.

45. Click the Finish button.

46. From the Data group of the Toolbox, add a T:System.Web.UI.WebControls.GridView control to the page. This control is used to display the Products table data fields.

48. From the GridView Tasks menu in the Choose Data Source drop-down list control, select LinqDataSource2. This is the ID of the data source control created in the previous steps.

49. Enable paging and selection by checking the related check-boxes.

50. Switch to source modality, by clicking the Source tab.

51. For display clarity above the T:System.Web.UI.WebControls.GridView control enter the following markup:

 <h2>Child Table: Products</h2>

52. In the <Coulmns> tag, delete all the <asp:BoundField> controls.

53. Enable automatic columns generation and set page size. The following example shows how to set these properties.

        <asp:GridView ID="GridView2" runat="server"          DataSourceID="LinqDataSource2"          AutoGenerateColumns="True" PageSize="5">        </asp:GridView>

54. Switch to Design modality by clicking on the Design tab.

55. From the DynamicDataManager Tasks menu, click Register Controls.

56. The DataControlReference Collection Editor is displayed.

57. In the Members pane, click the Add button. A DataControl entry is displayed in the Members box.

58. In the DataControl properties pane in the ControlID drop-down list control box select GridView2.

59. Click OK.

60. Right-click on the page and select View in Browser. The Products table rows are displayed.

61. Save the ParentChid.aspx file.

Set the Child Table Row Filtering

In this final section you will set the child table rows filtering. The T:System.Web.UI.WebControls.QueryExtender control is set in such way to enable child table rows filtering based on the foreign-key selected by the user in the parent table.

To set the child table row filtering

62. In the ParentChild.aspx page, add a T:System.Web.UI.WebControls.QueryExtender control and set its P:System.Web.UI.WebControls.QueryExtender.TargetControlID property to the ID of the data source control that is associated with the child table whose rows you want to filter.

The following example shows the markup for a T:System.Web.UI.WebControls.QueryExtender control that specifies the data source control LinqDataSource2 is associated with the child table.

       <asp:QueryExtender ID="QueryExtenderID"          TargetControlID="LinqDataSource2" runat="server" >       </asp:QueryExtender>

63. Add a T:System.Web.DynamicData.ControlFilterExpression object as a child of the T:System.Web.UI.WebControls.QueryExtender control. The following example shows the markup for adding a T:System.Web.DynamicData.ControlFilterExpression object to the control.

      <asp:QueryExtender ID="QueryExtenderID"        TargetControlID="LinqDataSource2" runat="server" >       <asp:ControlFilterExpression />     </asp:QueryExtender>

64. Set the ControlID property of the T:System.Web.DynamicData.ControlFilterExpression object to the data-bound control associated with the parent table.

65. Set the Column property of the T:System.Web.DynamicData.ControlFilterExpression object to the column in the parent table that is used for filtering. This column represents a foreign key in the child table.

The following example shows the markup for a

T:System.Web.DynamicData.ControlFilterExpression object that points to a data-bound control and a foreign-key associated with the parent table.

        <asp:QueryExtender ID="QueryExtenderID"          TargetControlID="LinqDataSource2" runat="server" >         <asp:ControlFilterExpression ControlID="GridView1"           Column="ProductCategory" />       </asp:QueryExtender>

66. Save the file.

67. In Solution Explorer, right-click the ParentChild.aspx page and then select View in Browser.

68. In the ProductCategories parent table select a category.

Dynamic Data filters the Products table rows based on the category that you selected.