Accessing Multiple Tables in a DataSet | Beginning Visual C#supAND#174;/sup 2005

One of the big advantages of the ADO.NET model over previous data access models lies in the fact that the DataSet object tracks multiple tables and the relationships between them all within itself. This means that you can pass an entire set of related data between parts of your program in one operation, and the architecture inherently maintains the integrity of the relationships between the data.

Relationships in ADO.NET

The DataRelation object is used to describe the relationships between multiple DataTable objects in a DataSet. Each DataSet has a Relations collection of DataRelations that enables you to find and manipulate related tables.

Let's start with just the Customers and Orders tables. Each customer may place several orders; how can you see the orders placed by each customer? Each row of the Orders table contains the CustomerID of the customer placing the order; you match all the order rows containing a particular CustomerID with that customer's row in the Customers table, as shown in Figure 24-11.

image from book
Figure 24-11

The matching CustomerID fields in the two tables define a one-to-many relationship between Customers table and the Orders table. You can use that relationship in ADO.NET by creating a DataRelation object to represent it.

Creating a DataRelation Object

The DataSet has a Relations property that is a collection of all the DataRelation objects representing relationships between tables in this DataSet.

To create a new DataRelation, use the Add() method of Relations which accepts a string name for the relationship and two DataColumns — the parent column, followed by the child column. Thus, to create the relationship described above between the CustomerID column of the Customers table and the CustomerID table of the Orders table, you would use the following syntax, giving the relationship the name CustOrders:

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

You see this syntax again in the next example.

Navigating with Relationships

To use the relationship, you need to go from a row of one of your tables to the related rows in the other table. This is called navigating the relationship. Often navigations consist of traveling from a parent row in the first table to the related children in the other table. In the diagram shown earlier, the row in the Customers table can be considered the parent row and each of the related rows in the Orders table can be considered children. Navigations can also go in the opposite direction.

Fetching the Child Rows

Given a row in the parent table, how do you obtain all the rows in the child table that correspond to this row? You can retrieve this set of rows with the GetChildRows() method of the DataRow object. the DataRelation object that you have created between the parent and child tables is passed to the method, and a DataRowCollection object is returned, which is a collection of the related DataRow objects in the child DataTable.

For example, with the DataRelation that you created earlier, if the given DataRow in the parent DataTable (Customers) is customerRow, then

customerRow.GetChildRows(custOrderRel);

returns the collection of corresponding DataRow objects from the Orders table. You see how to handle this set of objects in the following Try It Out.

Try It Out – Getting the Related Rows

Follow these steps to create the DataRelationExample program in Visual Studio 2005:

Create a new console application called DataRelationExample in the directory C:\BegVCSharp\Chapter24.

Begin by adding the using directives for the ADO.NET classes you will be using:

#region Using Directives using System; using System.Data;            // Use ADO.NET namespace using System.Data.SqlClient;  // Use SQL Server data provider namespace using System.Collections.Generic; using System.Text; #endregion

Now add the following code to the Main() method:

static void Main(string[] args)  { // Specify SQL Server-specific connection string SqlConnection thisConnection = new SqlConnection( @"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); // Create DataAdapter object for update and other operations SqlDataAdapter thisAdapter = new SqlDataAdapter(  "SELECT CustomerID, CompanyName FROM Customers", thisConnection); // Create CommandBuilder object to build SQL commands SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); // Create DataSet to contain related data tables, rows, and columns DataSet thisDataSet = new DataSet(); // Set up DataAdapter objects for each table and fill SqlDataAdapter custAdapter = new SqlDataAdapter( "SELECT * FROM Customers", thisConnection); SqlDataAdapter orderAdapter = new SqlDataAdapter( "SELECT * FROM Orders", thisConnection); custAdapter.Fill(thisDataSet, "Customers"); orderAdapter.Fill(thisDataSet, "Orders"); // Set up DataRelation between customers and orders DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders", thisDataSet.Tables["Customers"].Columns["CustomerID"], thisDataSet.Tables["Orders"].Columns["CustomerID"]); // Print out nested customers and their order ids foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows) { Console.WriteLine("Customer ID: " + custRow["CustomerID"] +  " Name: " + custRow["CompanyName"]); foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel)) { Console.WriteLine("  Order ID: " + orderRow["OrderID"]); } } thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); }

Execute the application, and you will see the output shown in Figure 24-12.

Figure 24-12

How It Works

Before you construct the DataRelation, you need to create the DataSet object and link the database tables you are going to use with it, as shown here:

DataSet thisDataSet = new DataSet(); SqlDataAdapter custAdapter = new SqlDataAdapter(      "SELECT * FROM Customers", thisConnection); SqlDataAdapter orderAdapter = new SqlDataAdapter(      "SELECT * FROM Orders", thisConnection); custAdapter.Fill(thisDataSet, "Customers"); orderAdapter.Fill(thisDataSet, "Orders");

You create a DataAdapter object for each table you will reference. You then fill the DataSet with data from the columns you're going to work with; in this case, you're not worried about efficiency, so you just use all of the available columns (SELECT * FROM <table>).

Next, you make the DataRelation object and link it to the DataSet:

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

Now you're ready to find the customers and orders. First, let's set up a foreach loop to display the customer information for each customer:

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

You're just looping through the Rows collection of the Customers table, printing the CustomerID and CompanyName for each customer. Once you've displayed the customer, you'd like to display the related orders for that customer.

To do that, you add a nested foreach loop, initialized by calling the GetChildRows() method of DataRow. You pass your DataRelation object to GetChildRows(), and it returns a DataRowCollection containing just the related rows in the Orders table for this customer. To display these related rows, simply loop through each DataRow in this collection with your foreach loop:

   foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))    {       Console.WriteLine("  Order ID: " + orderRow["OrderID"]);    } }

Now you repeat the process for each customer. You added some leading spaces to the display of the OrderID, so the orders for each customer are displayed indented underneath the customer information. With the indented display, you can see the parent-child relationship between each customer and its orders more clearly. Customer ID "Zachary Zithers Ltd." has no Orders because you just added it to the table in the previous examples.

That's one relation between two tables — let's go further and look at relations between more tables. Let's extend this program to see what specific items each customer is placing in each order and what the names of the products are. This information is available through the other tables in the Northwind database. Let's review these relationships; an easy way to see these is to look at a database diagram for the Northwind database, as shown in Figure 24-13.

image from book
Figure 24-13

The lines between the tables represent the relationships, with the line on each side going to the column that identifies the relationship. A primary key-foreign key relationship is shown with a key symbol by the parent column and an infinity symbol by the child column.

In the next Try It Out, you're going to display the details of each customer order, including the product names, by following the relationships between four tables in the diagram above: Customers, Orders, Order Details, and Products tables.

Try It Out – Working with Multiple Relations

Follow these steps to create the ManyRelations program in Visual Studio 2005:

Create a new console application called ManyRelations in the directory C:\BegVCSharp\ Chapter24.

Begin by adding the using directives for the ADO.NET classes you will be using:

#region Using Directives using System; using System.Data;            // Use ADO.NET namespace using System.Data.SqlClient;  // Use SQL Server data provider namespace using System.Collections.Generic; using System.Text; #endregion

Now add the following code to the Main() method:

static void Main(string[] args)  { // Specify SQL Server-specific connection string SqlConnection thisConnection = new SqlConnection( @"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); DataSet thisDataSet = new DataSet(); SqlDataAdapter custAdapter = new SqlDataAdapter( "SELECT * FROM Customers", thisConnection); custAdapter.Fill(thisDataSet, "Customers"); SqlDataAdapter orderAdapter = new SqlDataAdapter( "SELECT * FROM Orders", thisConnection); orderAdapter.Fill(thisDataSet, "Orders"); SqlDataAdapter detailAdapter = new SqlDataAdapter( "SELECT * FROM [Order Details]", thisConnection); detailAdapter.Fill(thisDataSet, "Order Details"); SqlDataAdapter prodAdapter = new SqlDataAdapter( "SELECT * FROM Products", thisConnection); prodAdapter.Fill(thisDataSet, "Products"); DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders", thisDataSet.Tables["Customers"].Columns["CustomerID"], thisDataSet.Tables["Orders"].Columns["CustomerID"]); DataRelation orderDetailRel = thisDataSet.Relations.Add("OrderDetail", thisDataSet.Tables["Orders"].Columns["OrderID"], thisDataSet.Tables["Order Details"].Columns["OrderID"]); DataRelation orderProductRel = thisDataSet.Relations.Add( "OrderProducts",thisDataSet.Tables["Products"].Columns["ProductID"], thisDataSet.Tables["Order Details"].Columns["ProductID"]); foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows) { Console.WriteLine("Customer ID: " + custRow["CustomerID"]); foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel)) { Console.WriteLine("\tOrder ID: " + orderRow["OrderID"]); Console.WriteLine("\t\tOrder Date: " + orderRow["OrderDate"]); foreach (DataRow detailRow in  orderRow.GetChildRows(orderDetailRel)) { Console.WriteLine("\t\tProduct: " +  detailRow.GetParentRow(orderProductRel)["ProductName"]); Console.WriteLine("\t\tQuantity: " + detailRow["Quantity"]); } } } thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); }

Execute the application, and you will see output like the following (I've shown an abbreviated version here, with only the last part of the output):

Customer ID: WOLZA         ...         Order ID: 10998                 Order Date: 4/3/1998 12:00:00 AM                 Product:                  Quantity: 12                 Product: Sirop                  Quantity: 7                 Product: Longlife Tofu                 Quantity: 20                 Product: Rhnbru Klosterbier                 Quantity: 30         Order ID: 11044                 Order Date: 4/23/1998 12:00:00 AM                 Product: Tarte au sucre                 Quantity: 12 Customer ID: ZACZI

How It Works

As usual, you begin by initializing a connection and then creating a new DataSet. Next, you create a data adapter for each of the four tables that will be used:

SqlDataAdapter custAdapter = new SqlDataAdapter(      "SELECT * FROM Customers", thisConnection); custAdapter.Fill(thisDataSet, "Customers");     SqlDataAdapter orderAdapter = new SqlDataAdapter(      "SELECT * FROM Orders", thisConnection); orderAdapter.Fill(thisDataSet, "Orders");     SqlDataAdapter detailAdapter = new SqlDataAdapter(      "SELECT * FROM [Order Details]", thisConnection); detailAdapter.Fill(thisDataSet, "Order Details");     SqlDataAdapter prodAdapter = new SqlDataAdapter(      "SELECT * FROM Products", thisConnection); prodAdapter.Fill(thisDataSet, "Products");

Next, you build DataRelation objects for each of the relationships between the four tables:

DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders",             thisDataSet.Tables["Customers"].Columns["CustomerID"],             thisDataSet.Tables["Orders"].Columns["CustomerID"]);     DataRelation orderDetailRel = thisDataSet.Relations.Add("OrderDetail",             thisDataSet.Tables["Orders"].Columns["OrderID"],             thisDataSet.Tables["Order Details"].Columns["OrderID"]);     DataRelation orderProductRel = thisDataSet.Relations.Add(   "OrderProducts",thisDataSet.Tables["Products"].Columns["ProductID"],    thisDataSet.Tables["Order Details"].Columns["ProductID"]);

The first relationship is exactly the same as in the previous example. The next one adds the relationship between Orders and Order Details, using the OrderID as the linking column. The last relationship is the one between Order Details and Products, using ProductID as the linking column. Notice that in this relationship, Products is actually the parent table (second of the three parameters). This is because it is the one side of the one-to-many relation (one Product may appear in many Orders).

Now that you've set up the relationships you can do processing with them. Again, the basic structure is a nested foreach loop, this time with three nested levels:

foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows) {    Console.WriteLine("Customer ID: " + custRow["CustomerID"]);        foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))    {       Console.WriteLine("\tOrder ID: " + orderRow["OrderID"]);       Console.WriteLine("\t\tOrder Date: " + orderRow["OrderDate"]);           foreach (DataRow detailRow in                 orderRow.GetChildRows(orderDetailRel))       {          Console.WriteLine("\t\tProduct: " +           detailRow.GetParentRow(orderProductRel)["ProductName"]);          Console.WriteLine("\t\tQuantity: " + detailRow["Quantity"]);       }    } }

Just as before, you output the data for the parent row, then use GetChildRows() to obtain the child rows related to this parent. The outer loop is the same as the previous example. Next, you print out the additional detail of the OrderDate to the OrderID and then get the OrderDetails for this OrderID.

The innermost loop is different; to get the Product row, you call GetParentRow(), which gets the parent object, going from the many side to the one side of the relationship. Sometimes, this navigation from child to parent is called navigating upstream, as opposed to the normal parent-to-child downstream navigation. Upstream navigation requires the GetParentRow() call.

The output of the program shows all the details of the orders processed for each customer, indented to show the parent and child hierarchy. Again, the Customer ID ZACZI has no orders because you just added it to the table in the previous examples.