Export (0) Print
Expand All
Debugging: Root Out Elusive Production Bugs with These Effective Techniques
Smart Tags: Simplify UI Development with Custom Designer Actions in Visual Studio
Ten Essential Tools: Visual Studio Add-Ins Every Developer Should Download Now
XML Comments: Document Your Code in No Time At All with Macros in Visual Studio
Expand Minimize

Navigating Multiple Related Tables in an ADO.NET Dataset

Visual Studio .NET 2003
 

Steve Stein
Visual Studio Team
Microsoft Corporation

February 2002

Summary: A dataset in ADO.NET is an in-memory representation of data that can contain multiple related data tables. This paper describes the methods of navigating these related data tables within a dataset. You will create a Windows® application in Visual Basic® .NET or Visual C#™ .NET that returns related records based on a selected record, as well as compile aggregate information for related records using expression columns. (14 printed pages)

Contents

Introduction
Prerequisites
Important Data Objects
Creating the Application
Navigating Related Records in Two Tables
Navigating Related Records in Three or More Tables
Navigating Many-to-Many Relationships
Expression Columns
Additional Information about Related Tables
Conclusion

Introduction

Because datasets can contain several related tables, understanding how to navigate from parent to child records and back is a fundamental task that may not be intuitive — especially in situations where you are trying to access data that is several tables deep in the relational hierarchy. You will also learn how to navigate a dataset that has tables related to each other through a third, intermediary table (a many-to-many relationship).

The following is an overview of the tasks covered in this paper:

  1. Creating a Windows application project.
  2. Displaying a list of data for initial selection.
  3. Returning records that are related to the selected record.
  4. Navigating multiple tables and multiple relational structures, and accessing the data directly in the dataset at run time.

In addition, this paper also describes some related tasks:

  1. Adding to a data table expression columns that are based on related data.
  2. Compiling aggregate information of related data.

Prerequisites

To get the most out of this article, you should have:

  • Knowledge of fundamental relational database concepts.
  • An active connection to the Northwind sample database, so that you can create and run the application.
  • General familiarity with ADO.NET datasets. For more information, see Introduction to Data Access with ADO.NET.

Important Data Objects

To work with related records in a dataset, you should have a basic understanding of several objects in the .NET Framework System.Data namespace and how they interact. These objects work together to provide the navigational functionality within a dataset.

These objects are used to facilitate relationships in a dataset:

  • DataSet - represents in memory the data that can contain multiple DataTable objects that can be related with DataRelation objects. For more information, see Introduction to Datasets.
  • DataTable - represents one complete table of data. The schema of a data table is defined by the DataColumnCollection that makes up the table. When two data tables are related together the DataRelation object uses DataColumn(s) from each table to relate the data. For more information, see Creating and Using DataTables.
  • DataRelation - ties multiple tables together and facilitates navigation to and from records in related tables. When accessing related records, a DataRelation object is passed to either the GetChildRows or GetParentRow method. It is the DataRelation object that determines what related table to query in order to return the related data associated with the GetChildRows or GetParentRow method call. For more information, see Introduction to DataRelation Objects.
  • DataRow - represents an individual record of data. The methods used to return related data, the GetChildRows and GetParentRow methods, are members of the DataRow object.
  • DataColumn - represents an individual field, that when combined together, define the schema for a DataTable. When two data tables are related together the DataRelation object uses data column(s) from each table to relate the data.

Creating the Application

This section establishes the starting point for this walkthrough. The following steps create a data connection, a data adapter, a dataset that contains related tables, and a few controls to select and display data.

To create a new Windows application

  1. From the File menu, point to New, and select Project. The New Project dialog box appears.
  2. In the Project Types pane, select Visual Basic Projects or Visual C# Projects, depending upon your language preference.
  3. In the Templates pane, select Windows Application, name it DataRelationExample, then click OK.

    The DataRelationExample project is added to Solution Explorer.

Connecting to the Database

This connection allows you to communicate with the data source from within the Visual Studio® integrated development environment (IDE).

To connect to the Northwind sample database in Server Explorer

  1. In Server Explorer, establish a data connection to the Northwind sample database. For more information, see Adding New Data Connections in Server Explorer.
Security Note Storing connection-string details (such as the server name, user name, and password) can have implications for the security of your application. Using Windows Integrated Security is a more secure way to control access to a database. For more information, see Database Security.
  1. Expand the Northwind data connection in Server Explorer until you can see all the tables.

Creating Data Adapters and Connections

This step will create the connection and data adapters which are used to connect and exchange data between your application and the data source.

To create the data adapters and connections

  1. Drag the Customers table from Server Explorer onto the form. A connection and data adapter appear in the component tray.
  2. Select the Connection and set the Name property to dcNorthwind.
  3. Select the data adapter and set the Name property to daCustomers.
  4. From Server Explorer, drag the Orders table onto the form. A second data adapter appears in the component tray.
  5. Select the new data adapter and set the Name property to daOrders.

Generating the Dataset

Using the data adapters that were just added to the form, generate a dataset that includes the Customers and Orders tables.

To generate the dataset that will contain the related data tables

  1. From the Data menu, select Generate Dataset. The Generate Dataset dialog box appears.
    Tip   Moving focus to the form will make the Data menu available.
  2. Click New and name the dataset dsNorthwind.
  3. Select both the Customers and Orders tables.
  4. Select the Add this dataset to the designer check box, then click OK.

    A file named dsNorthwind.xsd is added to the project in Solution Explorer, and an instance of the dataset appears in the component tray.

Creating the Relationship

Generating a dataset does not automatically create a relationship between the tables that are in it. The relationship can be created programmatically or visually, using the XML Designer. This article uses the XML Designer.

To create the relationship between the Customers and Orders tables

  1. In Solution Explorer, double-click the dsNorthwind.xsd file. The file opens in the XML Designer.
  2. From the XML Schema tab of Toolbox, drag a Relation onto the Orders table.
  3. In the Edit Relation dialog box, set the following properties:
    Element Setting
    Name CustomersOrders
    Parent Customers
    Child Orders
    Key Fields CustomerID
    Foreign Key Fields CustomerID
  4. Click OK to create the relation and close the dialog box.
  5. From the File menu, select Save All to save the project.

Displaying the Data

This application uses a combo box, list box, and rich text box for selecting and displaying data.

To add controls to select and display the data

  1. In Solution Explorer, right-click Form1 (.cs or .vb, depending on your application's language), then select View Designer from the shortcut menu.
  2. On the left half of the form, add a ListBox control, and set its Name property to lbOrders.
  3. On the right half of the form, add a RichTextBox control, and set its Name property to rtbDetails.
  4. Above the list box, add a ComboBox control, and set its Name property to cbCustomers.
  5. Save the project.

Figure 1. Suggested control layout on the form

Now you are ready to start adding functionality to the application.

To set the combo box to display the company name

  • Select the combo box (cbCustomers) and set the following properties:
    Property Setting
    DataSource DsNorthwind1
    DisplayMember Customers.CompanyName
    ValueMember Customers.CustomerID

Filling the Tables with Data

You must add code to the application to fill the tables with data.

To fill the Customers and Order tables in the dataset (dsNorthwind1) with data

  1. Double-click an empty area on the form to create an event handler for the Form1_Load event.
  2. Add the following code:
    ' Visual Basic
    Private Sub Form1_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
       ' Turn off constraints in the dataset.
       DsNorthwind1.EnforceConstraints = False
    
       ' Fill the tables with data.
       daOrders.Fill(DsNorthwind1)
       daCustomers.Fill(DsNorthwind1)
    
       ' Turn constraints back on.
       DsNorthwind1.EnforceConstraints = True
    End Sub
    
    // C#
    private void Form1_Load(object sender, System.EventArgs e)
    {
       // Turn off constraints in the dataset.
       dsNorthwind1.EnforceConstraints = false;
    
       // Fill the tables with data.
       daOrders.Fill(dsNorthwind1);
       daCustomers.Fill(dsNorthwind1);
    
       // Turn constraints back on.
       dsNorthwind1.EnforceConstraints = true;
    }
    
  3. Save the project.
  4. Press F5 to run the application. The combo box now contains a list of company names.
  5. Close the form.

Navigating Related Records in Two Tables

Here is a basic explanation of how to access data between two tables in a dataset that participate in a one-to-many relationship. After you select a data row, you can return its related records by calling the GetChildRows or GetParentRow method and passing it the appropriate data relation.

Note   The GetChildRows method will return data in an array of DataRow objects, whereas the GetParentRow method will return a single data row.

To demonstrate this, you will add some code to the application that returns all the orders (child rows) for the customer selected in the combo box. Changing the selected customer in the combo box raises the ComboBox.SelectedIndexChanged event and fills the list box with the OrderID of each order for the selected customer.

You call the GetChildRows method, based on the customer selected in the combo box. All of the related records from the Orders table are assigned to the array of data rows named draOrders.

Note   The next section will add functionality to display the list of related orders in the list box. To verify that the array actually contains the related records, the length of the array (which is the total number of orders for the selected customer) is displayed as the form caption.

To create an event handler to fetch orders for a selected customer

  1. In Solution Explorer, right-click Form1 and select View Designer from the shortcut menu.
  2. Double-click the combo box to create an event handler for the SelectedIndexChanged event.
  3. Add the following code:
    ' Visual Basic
    Private Sub cbCustomers_SelectedIndexChanged _
    (ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles cbCustomers.SelectedIndexChanged
       ' Declare a string to hold the selected customers CustomerID.
       Dim SelectedCustomerID As String
       SelectedCustomerID = cbCustomers.SelectedValue.ToString()
       ' Declare a data row to hold the selected customer's record.
       Dim drSelectedCustomer As DataRow
       drSelectedCustomer = _
          DsNorthwind1.Customers.FindByCustomerID _
          (SelectedCustomerID)
    
       ' Declare an array of data rows to hold the related records.
       Dim draOrders As DataRow()
       draOrders = drSelectedCustomer.GetChildRows("CustomersOrders")
       ' Display the length of the array (number of orders)
       ' and the CustomerID in the form caption.
       Me.Text = draOrders.Length.ToString() & " orders for " & _
          SelectedCustomerID
    End Sub
    
    // C#
    private void cbCustomers_SelectedIndexChanged
    (object sender, System.EventArgs e)
    {
       // Declare a string to hold the selected customers CustomerID.
       String SelectedCustomerID;
       SelectedCustomerID = cbCustomers.SelectedValue.ToString();
    
       // Declare a data row to hold the selected customer's record.
       DataRow drSelectedCustomer;
       drSelectedCustomer = 
          dsNorthwind1.Customers.FindByCustomerID(SelectedCustomerID);
    
       // Declare an array of data rows to hold the related records.
       DataRow[] draOrders;
       draOrders = drSelectedCustomer.GetChildRows("CustomersOrders");
    
       // Display the length of the array (number of orders)
       // and the CustomerID in the form caption.
       this.Text = draOrders.Length.ToString() + 
       " orders for " + SelectedCustomerID;
    }
    
  4. Save the project.
  5. Run the application.

    Select a different customer, and examine the form's caption. The total number of orders is displayed for the selected customer, as well as the selected customer's CustomerID.

  6. Close the form.

Displaying the Related Records

Now that you have the related records of the selected customer (stored in the array of data rows), you will display them for interaction with the user. The application iterates through the array of data rows returned from the GetChildRows method, adding the OrderID of each related record as an individual item in the list box.

Note   Although this example programmatically iterates through the array of related data rows, the list box could have been bound to the related records through the DataSource property, DataMember property, and ValueMember property using the Properties window.

To display the related records in the list box

  1. In Solution Explorer, right-click Form1 and select View Code from the shortcut menu.
  2. In the SelectedIndexChanged event handler for the combo box created in the previous procedure, add the following code to the handler (below the code already in the handler):
    ' Visual Basic
    ' When the selected customer changes, clear the list of orders.
    lbOrders.Items.Clear()
    rtbDetails.Text = ""
    
    ' Add the OrderID of each related order to the list box.
    Dim drOrder As DataRow
    For Each drOrder In draOrders
       lbOrders.Items.Add(drOrder("OrderID"))
    Next
    
    // C#
    // When the selected customer changes, clear the list of orders.
    lbOrders.Items.Clear();
    rtbDetails.Text = "";
    
    // Add the OrderID of each related order to the list box.
    foreach(DataRow drOrder in draOrders)
    {
       lbOrders.Items.Add(drOrder["OrderID"]);
    }
    
  3. Save the project.
  4. Run the application.

    A list of orders appear in the list box. Select a different customer in the combo box, and the list of orders is updated.

  5. Close the form.

Navigating Related Records in Three or More Tables

Navigating three or more tables is as simple as working with two. To learn how to work with more than two tables, you will add the OrderDetails table and the Products table to the dsNorthwind dataset. When an order is selected in the list box, the details from that order are displayed in the rich text box.

Now you will add two additional tables to the existing dataset and create new DataRelation objects to tie all the tables together.

To add the OrderDetails and Products tables to the dsNorthwind dataset

  1. In Solution Explorer, right-click Form1 and select View Designer from the shortcut menu.
  2. From Server Explorer, drag the Order Details table onto the form. A new data adapter appears in the component tray.
  3. Select the new data adapter and set its Name property to daOrderDetails.
  4. From Server Explorer, drag the Products table onto the form. A new data adapter appears in the component tray.
  5. Select the new data adapter and set its Name property to daProducts.

The new tables have only been added to the form, so you must regenerate the dataset whenever additional tables are added.

To regenerate the dsNorthwind dataset

  1. From the Data menu, select Generate Dataset.
    Tip   Moving focus to the form will make the Data menu available.
  2. Click Existing, then select the dsNorthwind dataset.
  3. Select all four tables (Customers, Orders, OrderDetails, and Products).
  4. Clear the Add this dataset to the designer check box, then click OK. The dataset is regenerated with the additional tables.
    Note   If you get a dialog box that says, "The file has been modified outside of the source editor. Do you want to reload it?" click Yes.

Remember, generating a dataset does not automatically create relationships between the tables that are in it.

To create the relationships

  1. In Solution Explorer, double-click the dsNorthwind.xsd file. The file opens in the XML Designer.
  2. From the XML Schema tab of the Toolbox, drag a Relation onto the OrderDetails table.
  3. In the Edit Relation dialog box, set the following:
    Element Setting
    Name OrdersOrderDetails
    Parent Orders
    Child OrderDetails
    Key Fields OrderID
    Foreign Key Fields OrderID
  4. Click OK to create the relation and close the dialog box.
  5. From the XML Schema tab of the Toolbox, drag a Relation onto the OrderDetails table.
  6. In the Edit Relation dialog box, set the following:
    Element Setting
    Name ProductsOrderDetails
    Parent Products
    Child OrderDetails
    Key Fields ProductID
    Foreign Key Fields ProductID
  7. Click OK to create the relation and close the dialog box.
  8. Save the project.

The OrderDetails and Products tables have been added to the dataset, but you still need to add code that will fill them with data at run time.

To fill the tables with data

  1. In Solution Explorer, right-click Form1 and select View Code from the shortcut menu.
  2. In the Form1_Load event handler, add the following code between the Fill the tables with data comment and the daOrders.Fill(dsNorthwind1) line:
    ' Visual Basic
    daOrderDetails.Fill(dsNorthwind1)
    daProducts.Fill(dsNorthwind1)
    
    // C#
    daOrderDetails.Fill(dsNorthwind1);
    daProducts.Fill(dsNorthwind1);
    

Fill the Rich Text Box with Data

You will now add code to the project that displays all of the order details in the rich text box when a specific order is selected in the list box.

The following code calls the GetChildRows method based on the order selected in the list box. All of the related records in the OrderDetails table are assigned to the array of data rows named draOrderDetails. The contents of each data row is displayed in the rich text box.

Note   Notice how the nested For Each loops first pick a data row, then loop through all the columns in that data row, accessing the entire related record.

To set the Rich Text Box to display all the order details

  1. In Solution Explorer, right-click Form1 and select View Designer from the shortcut menu.
  2. Double-click the list box to create an event handler for the SelectedIndexChanged event of the list box lbOrders.
  3. Add the following code:
    ' Visual Basic
    Private Sub lbOrders_SelectedIndexChanged _
    (ByVal sender As System.Object, ByVal e As System.EventArgs) _
     Handles lbOrders.SelectedIndexChanged
       ' Clear the rich text box when a new order is selected.
       rtbDetails.Clear()
    
       ' Declare an integer to hold the selected OrderID.
       Dim SelectedOrderID As Integer
       ' Cast the selected item as an integer.
       SelectedOrderID = CType(lbOrders.SelectedItem, Integer)
    
       ' Declare a data row to hold the selected Order record.
       Dim drSelectedOrder As DataRow
       drSelectedOrder = _
          DsNorthwind1.Orders.FindByOrderID(SelectedOrderID)
    
       ' Declare an array of data rows to hold the related records.
       Dim draOrderDetails() As DataRow
       draOrderDetails = _
          drSelectedOrder.GetChildRows("OrdersOrderDetails")
    
       Dim details As String = ""
       Dim drDetails As DataRow
       Dim dcDetails As DataColumn
       For Each drDetails In draOrderDetails
          For Each dcDetails In drDetails.Table.Columns
             details &= dcDetails.ColumnName & ": "
             details &= drDetails(dcDetails).ToString()
             details &= ControlChars.CrLf
          Next
          details &= ControlChars.CrLf
       Next
       rtbDetails.Text = details
    End Sub
    
    // C#
    private void lbOrders_SelectedIndexChanged
    (object sender, System.EventArgs e)
    {
       // Clear the rich text box when a new order is selected.
       rtbDetails.Clear();
    
       // Declare an integer to hold the selected OrderID.
       int SelectedOrderID;
       // Cast the selected item as an integer.
       SelectedOrderID = (int)lbOrders.SelectedItem;
    
       // Declare a data row to hold the selected order record.
       DataRow drSelectedOrder;
       drSelectedOrder = 
          dsNorthwind1.Orders.FindByOrderID(SelectedOrderID);
    
       // Declare an array of data rows to hold the related records.
       DataRow[] draOrderDetails;
       draOrderDetails = 
          drSelectedOrder.GetChildRows("OrdersOrderDetails");
    
       string details = "";
       foreach(DataRow drDetails in draOrderDetails)
       {
          foreach(DataColumn dcDetails in drDetails.Table.Columns)
          {
             details += dcDetails.ColumnName + ": ";
             details += drDetails[dcDetails].ToString() + "\n";
          }
          details += "\n";
       }
       rtbDetails.Text = details;
    }
    
  4. Save the project.
  5. Run the application.
  6. Select an order in the list box. The details are displayed in the rich text box.
  7. Select a different order in the list box. The order details in the rich text box are updated.

Navigating Many-to-Many Relationships

Tables that participate in a many-to-many relationship are typically connected through a third table that facilitates integrity within the data. In the Northwind database, the Orders and Products tables are related in such a way. There may be orders that contain many products, just as there may be products that are sold on many orders. These two tables are connected through the OrderDetails table, which uses columns from each table to establish its own uniqueness and relate the data. Navigating three tables that participate in a many-to-many relationship is not very different from working with tables that participate in one-to-many relationships.

To navigate this many-to-many relationship, you access the products based on the individual record in the order details table, which returns the product name and displays it in the order details.

You use the GetParentRow method to access the product name from the Products table. Calling the GetParentRow method returns a single data row, whereas calling the GetChildRows method returns an array of rows (as shown in the previous example).

To get the product name from the order details record

  1. In Solution Explorer, right-click Form1 and select View Code from the shortcut menu.
  2. In the SelectedIndexChanged event handler for the list box (lbOrders), add the following code between the For Each lines:
    ' Visual Basic
    details &= "Product name: " & _
       CType(drDetails.GetParentRow("ProductsOrderDetails") _
       ("ProductName"), String) & ControlChars.CrLf
    
    // C#
    details += "Product Name: " + 
       drDetails.GetParentRow("ProductsOrderDetails")["ProductName"] 
       + "\n";
    
  3. Save the project.
  4. Run the application.
  5. Select an order in the list box.

    The product name is displayed with the details in the rich text box. There is now related data from all four tables displayed on the form.

    Figure 2. The form displaying the product name in addition to the order details

  6. Select a different order in the list box. The order details in the rich text box are updated.
  7. Close the form.

Expression Columns

In addition to containing static data, a DataColumn can be assigned a value based on the results of an expression. The expression is a string assigned to the DataColumn.Expression property.

When using expressions with related data, a data column can contain:

  • Values from calculating related data columns.
  • Aggregate information from related data columns.
  • Results of a logical comparison of related data.

To illustrate the value expression columns when working with related data, an example of each of these uses will be described and added to the DataRelationExample application.

Adding an Expression Column Containing Calculated Values

Calculated columns contain the results of mathematical operations. Calculations can be performed on values taken from existing columns. A new column named Total will be added to the OrderDetails table and will contain the value returned by the expression UnitPrice * Quantity (the total dollar value of the order).

To add an expression column

  1. In Solution Explorer, right-click Form1 and select View Code from the shortcut menu.
  2. Add the following code above the code already in the Form1_Load event handler:
    ' Visual Basic
    ' Create an expression column named Total in the OrderDetails table.
    Dim dcTotal as DataColumn = new DataColumn("Total")
    dcTotal.DataType = System.Type.GetType("System.Decimal")
    dcTotal.Expression = "UnitPrice * Quantity"
    DsNorthwind1.Order_Details.Columns.Add(dcTotal)
    
    // C#
    // Create an expression column named Total in the OrderDetails table.
    DataColumn dcTotal = new DataColumn("Total");
    dcTotal.DataType = System.Type.GetType("System.Decimal");
    dcTotal.Expression = "UnitPrice * Quantity";
    dsNorthwind1.Order_Details.Columns.Add(dcTotal);
    
  3. Run the application.
  4. Select an order in the list box.

    Examine the details in the rich text box and notice that each record has a new Total column that displays the product of the UnitPrice and Quantity fields.

  5. Close the form.

Adding an Expression Column Containing Aggregate Information

The Expression property supports several aggregate functions (Sum, Avg, Count, and so on). For more information, see DataColumn.Expression property.

To demonstrate the compilation of aggregate information, you will add a new column named OrderTotal to the Orders table. This column will use the Sum function to return the total dollar amount of all child order-detail records, based on the order selected in the list box (lbOrders). This value will then be displayed in the rich text box above the details for each order.

To create the OrderTotal column

  1. In Solution Explorer, right-click Form1 and select View Code from the shortcut menu.
  2. In the Form1_Load event handler, add the following code below the code that creates the Total column in the OrderDetails table:
    ' Visual Basic
    ' Create an expression column named OrderTotal in the Orders table.
    Dim dcOrderTotal as DataColumn = new DataColumn("OrderTotal")
    dcOrderTotal.DataType = System.Type.GetType("System.Decimal")
    dcOrderTotal.Expression = "Sum(Child.Total)"
    DsNorthwind1.Orders.Columns.Add(dcOrderTotal)
    
    // C#
    // Create an expression column named OrderTotal in the Orders table.
    DataColumn dcTotal2 = new DataColumn("OrderTotal");
    dcTotal2.DataType = System.Type.GetType("System.Decimal");
    dcTotal2.Expression = "Sum(Child.Total)";
    dsNorthwind1.Orders.Columns.Add(dcTotal2);
    

To display the aggregate information above all the order details

  1. In the lbOrders_SelectedIndexChanged event handler, add the following code below the (Dim details As String = "" or string details = "") line:
    ' Visual Basic
    details = "Order Total: " & String.Format("{0:c}",  _
    DsNorthwind1.Orders.FindByOrderID(CType(lbOrders.SelectedItem, _
    Integer))("OrderTotal")) & ControlChars.CrLf
    
    // C#
    details = "Order Total: " + 
    String.Format("{0:c}",dsNorthwind1.Orders.FindByOrderID
    ((int)lbOrders.SelectedItem)["OrderTotal"]) + "\n"; 
    
  2. Run the application.
  3. Select an order in the list box.

    Notice that the sum of all the details from the selected order is displayed in the first line in the rich text box.

  4. Select another order in the list box, and the display is updated to reflect the newly selected order.
  5. Close the form.

Adding an Expression Column Containing a Logical Evaluation

The Expression property can populate a data column based on evaluated values in other columns. For example, a column named OrderSize in the Orders table may contain a value of "Big" if the total order is greater than 1000, or "Small" if the total order is less than 1000.

To demonstrate this type of expression, you will add code to the DataRelationExample application that will do the following:

  1. Add a data column named OrderSize to the Orders table.
  2. Populate the OrderSize column depending on the value of the related order details.
  3. Display the value of the OrderSize column along with the OrderTotal value at the top of the rich text box.

To add code to create the OrderSize column

  1. In Solution Explorer, right-click Form1 and select View Code from the shortcut menu.
  2. In the Form1_Load event handler, add the following code below the code that creates the OrderTotal column in the Orders table:
    ' Visual Basic
    ' Create an expression column named OrderSize in the Orders table.
    Dim dcOrderSize as DataColumn = new DataColumn("OrderSize")
    dcOrderSize.DataType = System.Type.GetType("System.String")
    dcOrderSize.Expression = "IIF(Sum(Child.Total)<1000,'Small','Big')"
    DsNorthwind1.Orders.Columns.Add(dcOrderSize)
    
    // C#
    // Create an expression column named OrderSize in the Orders table.
    DataColumn dcOrderSize = new DataColumn("OrderSize");
    dcOrderSize.DataType = System.Type.GetType("System.String");
    dcOrderSize.Expression = "IIF(Sum(Child.Total)<1000,'Small','Big')";
    dsNorthwind1.Orders.Columns.Add(dcOrderSize);
    

To display the OrderSize value

  1. In the lbOrders_SelectedIndexChanged event handler, add the following code above the first For Each line:
    ' Visual Basic
    details &= " (" & CType(DsNorthwind1.Orders.FindByOrderID _
    (CType(lbOrders.SelectedItem, Integer))("OrderSize"), String) & ")" _
    & ControlChars.CrLf
    
    // C#
    details += " (" + dsNorthwind1.Orders.FindByOrderID
    ((int)lbOrders.SelectedItem)["OrderSize"] + ")\n";
    
  2. Run the application.
  3. Select an order in the list box.
  4. Examine the first line in the rich text box. The OrderSize of the selected order is displayed to the right of the order total.
  5. Select another order in the list box and the display is updated to reflect the newly selected order.
  6. From the Debug menu, select Stop Debugging.

Additional Information about Related Tables

There are other items worth mentioning that will help expand upon the material in this article.

Order of Filling Related Data Tables is Important

The order in which related data tables are filled can have a significant effect on the output of your data, and must be considered when designing your application. For example, notice how the Customers table is the last table to be filled. When the Customers table is filled, the combo box is populated with the CustomerName values. When the combo box is populated, the SelectedIndexChanged event is raised. This causes the code in the event handler to execute. Because the Orders table has not been filled yet, the GetChildRows method returns zero (0) records, and the form's caption displays incorrect information. Try it: Change the code to fill the Customers table first and run the application. The form's caption displays zero (0) orders for ALFKI, which is incorrect.

Returning Specific Versions of Related Records

Specific versions of data rows can be returned by passing the desired DataRowVersion as a second (optional) parameter to the GetChildRows or GetParentRow method. Using the application as an example, if you only wanted to see the original orders for a specific customer, the code in the SelectedIndexChanged event of the combo box could be changed to resemble the code below. Because no changes are being made to the data in this application, the following code will not have any apparent effect. It is shown for explanatory purposes only.

' Visual Basic
' Fill the array with only original child rows of the selected
' customer.
Dim draOrders As DataRow() = DsNorthwind1.Customers.FindByCustomerID _
(cbCustomers.SelectedValue.ToString()).GetChildRows _
("CustomersOrders", DataRowVersion.Original)

// C#
// Fill the array with only original child rows of the selected
// customer.
DataRow draOrders = dsNorthwind1.Customers.FindByCustomerID
(cbCustomers.SelectedValue.ToString()).GetChildRows
("CustomerOrders", DataRowVersion.Original);

Conclusion

To access the related records of a particular data row, call the GetChildRows or GetParentRow method of that row, passing the data relation that connects the data row to its related records. The related record (or records) are then accessed by examining the data row (or array of data rows) returned by the method call.

Values in related records can be calculated, aggregated, and evaluated logically by assigning a valid expression string to the DataColumn.Expression property, and adding the data column to the proper DataTable.Columns collection.

Show:
© 2015 Microsoft