Select rows in dataset table based on other dataset table

Navigating a Relationship Between Tables 

One of the primary functions of a DataRelation is to allow navigation from one DataTable to another within a DataSet. This allows you to retrieve all the related DataRow objects in one DataTable when given a single DataRow from a related DataTable. For example, after establishing a DataRelation between a table of customers and a table of orders, you can retrieve all the order rows for a particular customer row using GetChildRows.

The following code example creates a DataRelation between the Customers table and the Orders table of a DataSetand returns all the orders for each customer.

VB

C#

C++

F#

JScript

DataRelation customerOrdersRelation =
    customerOrders.Relations.Add("CustOrders",
    customerOrders.Tables["Customers"].Columns["CustomerID"],
    customerOrders.Tables["Orders"].Columns["CustomerID"]);

foreach (DataRow custRow in customerOrders.Tables["Customers"].Rows)
{
    Console.WriteLine(custRow["CustomerID"].ToString());

    foreach (DataRow orderRow in custRow.GetChildRows(customerOrdersRelation))
    {
        Console.WriteLine(orderRow["OrderID"].ToString());
    }
}

The next example builds on the preceding example, relating four tables together and navigating those relationships. As in the previous example, CustomerID relates the Customers table to the Orders table. For each customer in theCustomers table, all the child rows in the Orders table are determined, in order to return the number of orders a particular customer has and their OrderID values.
The expanded example also returns the values from the OrderDetails and Products tables. The Orders table is related to the OrderDetails table using OrderID to determine, for each customer order, what products and quantities were ordered. Because the OrderDetails table only contains the ProductID of an ordered product, OrderDetails is related to Products using ProductID in order to return the ProductName. In this relation, the Products table is the parent and the Order Details table is the child. As a result, when iterating through the OrderDetails table, GetParentRow is called to retrieve the related ProductName value.
Notice that when the DataRelation is created for the Customers and Orders tables, no value is specified for thecreateConstraints flag (the default is true). This assumes that all the rows in the Orders table have a CustomerID value that exists in the parent Customers table. If a CustomerID exists in the Orders table that does not exist in theCustomers table, a ForeignKeyConstraint causes an exception to be thrown.
When the child column might contain values that the parent column does not contain, set the createConstraints flag tofalse when adding the DataRelation. In the example, the createConstraints flag is set to false for the DataRelationbetween the Orders table and the OrderDetails table. This enables the application to return all the records from theOrderDetails table and only a subset of records from the Orders table without generating a run-time exception. The expanded sample generates output in the following format.

Customer ID: NORTS
        Order ID: 10517
              Order Date: 4/24/1997 12:00:00 AM
                 Product: Filo Mix
                Quantity: 6
                 Product: Raclette Courdavault
                Quantity: 4
                 Product: Outback Lager
                Quantity: 6
        Order ID: 11057
              Order Date: 4/29/1998 12:00:00 AM
                 Product: Outback Lager
                Quantity: 3

The following code example is an expanded sample where the values from the OrderDetails and Products tables are returned, with only a subset of the records in the Orders table being returned.

VB

C#

C++

F#

JScript

DataRelation customerOrdersRelation =
    customerOrders.Relations.Add("CustOrders",
    customerOrders.Tables["Customers"].Columns["CustomerID"],
    customerOrders.Tables["Orders"].Columns["CustomerID"]);

DataRelation orderDetailRelation =
    customerOrders.Relations.Add("OrderDetail",
    customerOrders.Tables["Orders"].Columns["OrderID"],
    customerOrders.Tables["OrderDetails"].Columns["OrderID"], false);

DataRelation orderProductRelation =
    customerOrders.Relations.Add("OrderProducts",
    customerOrders.Tables["Products"].Columns["ProductID"],
    customerOrders.Tables["OrderDetails"].Columns["ProductID"]);

foreach (DataRow custRow in customerOrders.Tables["Customers"].Rows)
{
    Console.WriteLine("Customer ID: " + custRow["CustomerID"]);

    foreach (DataRow orderRow in custRow.GetChildRows(customerOrdersRelation))
    {
        Console.WriteLine("  Order ID: " + orderRow["OrderID"]);
        Console.WriteLine("\tOrder Date: " + orderRow["OrderDate"]);

        foreach (DataRow detailRow in orderRow.GetChildRows(orderDetailRelation))
        {
            Console.WriteLine("\t Product: " +
                detailRow.GetParentRow(orderProductRelation)["ProductName"]);
            Console.WriteLine("\t Quantity: " + detailRow["Quantity"]);
        }
    }
}