How to: Access Records in Related DataTables

  • Article

When tables in a dataset are related, a DataRelation object can make available the related records in another table. For example, a dataset containing Customers and Orders tables can be made available.

You can use a DataRelation object to locate related records by calling the GetChildRows method of a DataRow in the parent table; this method returns an array of related child records. Or you can call the GetParentRow method of a DataRow in the child table; this method returns a single DataRow from the parent table.

This Help page provides examples using typed datasets. For information on navigating relationships in untyped datasets, see Navigating DataRelations (ADO.NET).

Note

If you are working in a Windows Forms application and using the data-binding features to display data, the designer generated form may provide enough functionality for your application. For more information, see the pages on Binding Controls to Data in Visual Studio, specifically How to: Display Related Data in a Windows Forms Application and Walkthrough: Displaying Related Data on a Windows Form.

The following code examples demonstrate navigating up and down relationships in typed datasets. They use typed DataRows (NorthwindDataSet.OrdersRow) and the generated FindByPrimaryKey (FindByCustomerID) methods to locate a desired row and return the related records. The examples compile and run correctly only if you have:

  • An instance of a dataset named NorthwindDataSet with a Customers table

  • An Orders table

  • A relationship named FK_Orders_Customers relating the two tables available to the scope of your code

Additionally, both tables need to be filled with data for any records to be returned.

To return the child records of a selected parent record

  • Call the GetChildRows method of a specific Customers data row and return an array of rows from the Orders table:

    Dim customerID As String = "ALFKI" 
Dim orders() As NorthwindDataSet.OrdersRow

orders = CType(NorthwindDataSet.Customers.FindByCustomerID(customerID).
    GetChildRows("FK_Orders_Customers"), NorthwindDataSet.OrdersRow())

MessageBox.Show(orders.Length.ToString())

    string custID = "ALFKI";
NorthwindDataSet.OrdersRow[] orders;

orders = (NorthwindDataSet.OrdersRow[])northwindDataSet.Customers.
    FindByCustomerID(custID).GetChildRows("FK_Orders_Customers");

MessageBox.Show(orders.Length.ToString());

To return the parent record of a selected child record

  • Call the GetParentRow method of a specific Orders data row and return a single row from the Customers table:

    Dim orderID As Integer = 10707
Dim customer As NorthwindDataSet.CustomersRow

customer = CType(NorthwindDataSet.Orders.FindByOrderID(orderID).
    GetParentRow("FK_Orders_Customers"), NorthwindDataSet.CustomersRow)

MessageBox.Show(customer.CompanyName)

    int orderID = 10707;
NorthwindDataSet.CustomersRow customer;

customer = (NorthwindDataSet.CustomersRow)northwindDataSet.Orders.
    FindByOrderID(orderID).GetParentRow("FK_Orders_Customers");

MessageBox.Show(customer.CompanyName);

See Also

Concepts

Preparing Your Application to Receive Data

Fetching Data into Your Application

Binding Controls to Data in Visual Studio

Editing Data in Your Application

Validating Data

Saving Data

Other Resources

Relationships in Datasets

Overview of Data Applications in Visual Studio

Connecting to Data in Visual Studio