Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

How to: Filter Table Rows Using Values from a Parent Table in Dynamic Data

[This documentation is for preview only, and is subject to change in later releases. Blank topics are included as placeholders.]

ASP.NET Dynamic Data enables you to use page markup to filter the child table rows to display based on the foreign-key values that the user selects in a parent table. When the user selects a value in the parent table only the child table rows that contain that value are displayed. This lets you perform data filtering without requiring knowledge of the data source control and of the database query details.

The following figure shows the steps that Dynamic Data follows in order to implement table row filtering

Dynamic Data Child Table Rows Filtering Elements

Dynamic Data Query Elements
  1. The parent data-bound control passes the user's selected foreign-key to the QueryExtender control.

  2. The QueryExtender control uses this foreign-key value to create the query filtering information. It then passes this filtering information to the child data source control.

  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.

To filter table rows using values from a parent table

  1. In Visual Studio 2010 or Visual Web Developer 2010 Express, in a Dynamic Data Web site, open an ASP.NET Web page. open an ASP.NET Web page that contains a data source control to access the database and a related data-bound control to display the tables. For more information, see Walkthrough: Filtering Table Rows in Dynamic Data

  2. Add a QueryExtender control to the page and set its 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 QueryExtender control that specifies the data source control LinqDataSource1 associated with the child table.

    <asp:QueryExtender ID="QueryExtender1" 
        TargetControlID="LinqDataSource1" runat="server"/>
    </asp:QueryExtender>
    
    
  3. Add a ControlFilterExpression object as a child of the QueryExtender control.

    The following example shows the markup for adding a ControlFilterExpression object to the control.

    <asp:QueryExtender  
        TargetControlID="LinqDataSource1"  
        runat="server">
        <asp:ControlFilterExpression   />
    </asp:QueryExtender>  
    
    
  4. Set the ControlID property of the ControlFilterExpression object to the data-bound control associated with the parent table.

  5. Set the Column property of the 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 ControlFilterExpression object that points to a data-bound control and a foreign-key associated with the parent table.

    <asp:QueryExtender  
        TargetControlID="LinqDataSource1"  
        runat="server">
        <asp:ControlFilterExpression
            ControlD="ParentGridView"
            Column="ProductCategory" />
    </asp:QueryExtender>
    
    

The following example shows how to filter child table rows by using a foreign-key value from a parent table. The example uses the ProductCategories and Products tables from the AdventureWorksLT database. These tables have a master-detail relationship. In the example, the Products table rows are filtered based on the ProductCategory value that is assigned by the user in the ProductCategories table.


<%@ Page Language="C#" AutoEventWireup="true" 
    CodeFile="TableRowsDataKeyFiltering.aspx.cs" 
    Inherits="Samples_TableRowsDataKeyFiltering" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Parent Foreign Key Table Row Filtering</title>
</head>
<body>
    <h3>Parent Foreign Key Table Row Filtering</h3>

    <form id="form1" runat="server">
    <div>
        <asp:DynamicDataManager  ID="DynamicDataManager1" runat="server">
            <DataControls>
                <asp:DataControlReference ControlID="GridView1" />
                <asp:DataControlReference ControlID="GridView2" />
            </DataControls>
        </asp:DynamicDataManager>

         <h4>Parent Table: <%=LinqDataSource2.GetTable().DisplayName%></h4>

            Select a category in the 
            <code><%=LinqDataSource2.GetTable().DisplayName%></code> 
            parent table to filter the rows in the 
            <code><%=LinqDataSource1.GetTable().DisplayName%></code> 
            child table. 

          <asp:GridView ID="GridView2" runat="server" 
                DataSourceID="LinqDataSource2" 
                AutoGenerateColumns="false"
                AutoGenerateSelectButton="true" 
                AllowPaging="True" AllowSorting="True">

                <Columns>
                    <asp:DynamicField DataField="Name" />
                </Columns>

            </asp:GridView>

            <aspX:LinqDataSource ID="LinqDataSource2" runat="server" 
                ContextTypeName="AdventureWorksLTDataContext"
                TableName="ProductCategories">
            </aspX:LinqDataSource>


        <hr />

        <h4>Child Table: <%=LinqDataSource1.GetTable().DisplayName%></h4>

        <asp:GridView ID="GridView1" runat="server"
            DataSourceID="LinqDataSource1" AutoGenerateColumns="false"
            AllowPaging="true" AllowSorting="true">

           <Columns>
                <asp:DynamicField DataField="Name" />
                <asp:DynamicField DataField="Color" />
                <asp:DynamicField DataField="ProductCategory" />
           </Columns>

        </asp:GridView>


         <aspX:QueryExtender ID="QueryExtender1" 
            TargetControlID="LinqDataSource1" runat="server">
            <asp:ControlFilterExpression 
                ControlID="GridView2" 
                Column="ProductCategory" />  
        </aspX:QueryExtender>

        <aspX:LinqDataSource ID="LinqDataSource1" runat="server" 
            TableName="Products" 
            ContextTypeName="AdventureWorksLTDataContext"/>

    </div>
    </form>
</body>
</html>



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.DynamicData;
using System.Collections.Specialized;


public partial class Samples_TableRowsDataKeyFiltering : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {
        MetaTable table = LinqDataSource2.GetTable();
        Title = table.DisplayName;

        // Set the primary key to a category 
        // for which child values exist.
        int TouringBikes = 7;
        OrderedDictionary values = new OrderedDictionary();
        values.Add("ProductCategoryID", TouringBikes);
        string[] keyNames = { "ProductCategoryID" };
        DataKey key = new DataKey(values, keyNames);
        ((IPersistedSelector)GridView2).DataKey = key;
    }
}


This example requires the following:

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.