Navigating Relational Data

Once a relation is established, you can use it navigate from a parent row to the associated child rows or from a child row to the parent. Use the GetChildRows( ) or GetParentRow( ) method of the DataRow object:

foreach (DataRow parent in ds.Tables["Categories"].Rows)

{

    // (Process the category row.)



    foreach (DataRow child in parent.GetChildRows("Cat_Prod"))

    {

        // (Process the products in this category.)

    }

}

Similarly, the reverse logic branches from a child to the related parent:

foreach (DataRow child in ds.Tables["Products"].Rows)

{

    // (Process the product row.)



    DataRow parent = child.GetParentRow("Cat_Prod");

    // (Process category for this product.)

}

This syntax presents an easy and elegant way to traverse hierarchical data. However, it isn’t the only way to handle relational data. You can simply use the DataTable.Select( ) method to extract matching rows from another table. The Select( ) method returns an array of DataRow objects based on a SQL expression (and optionally the DataViewRowState). For example, to traverse relational data, use the Select( ) method to retrieve rows from the child table that match the parent’s key field. It’s a little more work, but accomplishes the same task without requiring a DataRelation.

foreach (DataRow parent in ds.Tables["Categories"].Rows)

{

    // (Process the category row.)



    DataRow[] rows =

        ds.Tables["Products"].Select("CategoryID=" +

                                     parent["CategoryID"].ToString());



    foreach (DataRow row in rows)

    {

        // (Process the products in this category.)

    }

}

Of course, you have another option that doesn’t rely on DataRelation objects or the DataTable.Select( ) method: using a join query to combine multiple tables in the data source into a single retrieved table. This approach is useful if you want to display some sort of aggregate data but don’t need to access the tables separately or perform updates.

When developers begin ADO.NET programming, they commonly ask whether they should design with join queries or DataRelation objects in mind. One factor in making a decision is whether you plan to update the retrieved data. If so, separate tables and a DataRelation object offers the most flexibility. If you don’t need to deal with the data separately or update it later, a join query can be more efficient, although it can introduce additional complications.

Modeling a One-to-Many Relationship

Our next example demonstrates DataRelation objects in action with a simple master-detail view. Figure 11-1 shows the example, which presents a list of categories of the left and a list of the products in the currently selected category on the right.

Figure 11-1. Using relationships with a master-detail form

To implement this design, you simply need to fill a DataSet with both tables and define a DataRelation. Then when an item is selected in the first list, the corresponding rows are added to the second. The full code is shown in Example 11-1.

Example 11-1. A master-detail form

using System;

using System.Data;

using System.Drawing;

using System.Data.SqlClient;

using System.Windows.Forms;





public class MasterDetail : Form

{

    // (Designer code omitted.)



    private ListBox lstCategories = new ListBox();

    private ListBox lstProducts = new ListBox();

    private DataSet ds = new DataSet();

    private DataRelation relation;



    string connectionString = "Data Source=localhost;" +

                    "Initial Catalog=Northwind;Integrated Security=SSPI";

    string categorySQL = "SELECT * FROM Categories";

    string productSQL = "SELECT * FROM Products";



    private void MasterDetail_Load(object sender, System.EventArgs e)

    {

        // Create ADO.NET objects.

        SqlConnection con = new SqlConnection(connectionString);

        SqlCommand com = new SqlCommand(categorySQL, con);

        SqlDataAdapter adapter = new SqlDataAdapter(com);



        // Execute the command.

        try

        {

            con.Open();

            adapter.Fill(ds, "Categories");

            adapter.SelectCommand.CommandText = productSQL;

            adapter.Fill(ds, "Products");

        }

        catch (Exception err)

        {

            MessageBox.Show(err.ToString());

        }

        finally

        {

            con.Close();

        }



        // Add the relation.

        DataColumn parentCol = 

                      ds.Tables["Categories"].Columns["CategoryID"];

        DataColumn childCol = ds.Tables["Products"].Columns["CategoryID"];

        relation = new DataRelation("Cat_Prod", parentCol, childCol);

        ds.Relations.Add(relation);



        // Show the category list.

        foreach (DataRow row in ds.Tables["Categories"].Rows)

        {

            lstCategories.Items.Add(row["CategoryName"]);

        }

    }



    private void lstCategories_SelectedIndexChanged(object sender, 

                                                    System.EventArgs e)

    {

        lstProducts.Items.Clear();



        // Find the corresponding parent row.

        DataRow[] rows = ds.Tables["Categories"].Select("CategoryName='" +

                         lstCategories.Text + "'");

        DataRow parent = rows[0];



        // Browse through all the children.

        foreach (DataRow child in parent.GetChildRows(relation))

        {

            lstProducts.Items.Add(child["ProductName"]);

        }

    }

}

Note that this code deliberately avoids data binding, which we’ll consider in Chapter 12. Data binding can simplify the code used to fill the control, but our approach offers more flexibility, particularly if you need to use an unusual control that doesn’t really support data binding. One such control is the TreeView (see Figure 11-2).

Figure 11-2. Using relationships with a TreeView

In Example 11-2, we put similar code and the same relation to work by filling a TreeView with a hierarchical list of products grouped by categories.

Example 11-2. A hierarchical TreeView

using System;

using System.Data;

using System.Data.SqlClient;

using System.Windows.Forms;



public class HierarchicalTreeView : System.Windows.Forms.Form

{

    // (Designer code omitted.)



    private TreeView tree = new TreeView();



    string connectionString = "Data Source=localhost;" +

                    "Initial Catalog=Northwind;Integrated Security=SSPI";

    string categorySQL = "SELECT * FROM Categories";

    string productSQL = "SELECT * FROM Products";



    private void HierarchicalTreeView_Load(object sender, 

                                           System.EventArgs e)

    {

        // Create ADO.NET objects.

        SqlConnection con = new SqlConnection(connectionString);

        SqlCommand com = new SqlCommand(categorySQL, con);

        SqlDataAdapter adapter = new SqlDataAdapter(com);

        DataSet ds = new DataSet();



        // Execute the command.

        try

        {

            con.Open();

            adapter.Fill(ds, "Categories");

            adapter.SelectCommand.CommandText = productSQL;

            adapter.Fill(ds, "Products");

        }

        catch (Exception err)

        {

            MessageBox.Show(err.ToString());

        }

        finally

        {

            con.Close();

        }



        // Add the relation.

        DataColumn parentCol = 

                   ds.Tables["Categories"].Columns["CategoryID"];

        DataColumn childCol = ds.Tables["Products"].Columns["CategoryID"];

        DataRelation relation = new DataRelation("Cat_Prod", parentCol,

                                                  childCol);

        ds.Relations.Add(relation);



        // Fill the tree.

        foreach (DataRow parent in ds.Tables["Categories"].Rows)

        {

            TreeNode nodeParent =

                     tree.Nodes.Add(parent["CategoryName"].ToString());

            foreach (DataRow child in parent.GetChildRows(relation))

            {

                nodeParent.Nodes.Add(child["ProductName"].ToString());

            }		

        }

    }

}

Modeling a Many-to-Many Relationship

So far, the examples have focused on one-to-many relationships. In a one-to-many relationship, a single parent (in this case, a category) can be linked to multiple child records (such as products). In a many-to-many relationship, categories can pertain to more than one product, and products can belong to more than one category.

A many-to-many relationship is actually built out of two one-to-many relationships with an intermediate table. One example is in the pubs database, which uses a many-to-many relationship between books and authors. In this case, there is a one-to-many relationship between authors and the records in the TitleAuthor table, and another one-to-many relationship between titles and the records in that table. Thus, to model this type of relationship in ADO.NET code, you simply need to create two DataRelation objects.

Example 11-3 shows a full example that uses two DataRelation objects to navigate a many-to-many relationship.

Example 11-3. Navigating a many-to-many relationship

// ManyToMany.cs - Navigate a many-to-many relationship.



using System;

using System.Data;

using System.Data.SqlClient;



public class ManyToMany

{

    public static void Main() 

    {

        string connectionString = "Data Source=localhost;" +

                   "Initial Catalog=pubs;Integrated Security=SSPI";

        string SQL = "SELECT au_lname, au_fname, au_id FROM Authors";



        // Create ADO.NET objects.

        SqlConnection con = new SqlConnection(connectionString);

        SqlCommand cmd = new SqlCommand(SQL, con);

        SqlDataAdapter adapter = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();



        try

        {

            con.Open();

            adapter.Fill(ds, "Authors");

    

            cmd.CommandText = "SELECT au_id, title_id FROM TitleAuthor";

            adapter.Fill(ds, "TitleAuthor");



            cmd.CommandText = "SELECT title_id, title FROM Titles";

            adapter.Fill(ds, "Titles");

        }

        catch (Exception err)

        {

            Console.WriteLine(err.ToString());

        }

        finally

        {

            con.Close();

        }



        // Create the relationships between the tables.

        // Connect Titles to TitleAuthor.

        DataRelation titles_titleAuthor;

        titles_titleAuthor = new DataRelation("", 

          ds.Tables["Titles"].Columns["title_id"], 

          ds.Tables["TitleAuthor"].Columns["title_id"]);



        // Connect TitleAuthor to Authors.

        DataRelation authors_titleAuthor;

        authors_titleAuthor = new DataRelation("", 

          ds.Tables["Authors"].Columns["au_id"], 

          ds.Tables["TitleAuthor"].Columns["au_id"]);



        // Add the relations to the DataSet.

        ds.Relations.Add(titles_titleAuthor);

        ds.Relations.Add(authors_titleAuthor);



        // Navigate through the results.

        foreach (DataRow rowAuthor in ds.Tables["Authors"].Rows)

        {

            Console.WriteLine(rowAuthor["au_fname"] + " " + 

                              rowAuthor["au_lname"]);



            foreach (DataRow rowTitleAuthor in

             rowAuthor.GetChildRows(authors_titleAuthor))

            {

                foreach (DataRow rowTitle in

                 rowTitleAuthor.GetParentRows(titles_titleAuthor))

                {

                    Console.WriteLine("\t" + rowTitle["title"]);

                }

            }



            Console.WriteLine();

        }

    }

}

The output for this application shows the list of books written by every author, grouped by author. Because some books are written by more than author, they appear more than once in the listing. Here’s a partial excerpt of the output:

...



Sylvia Panteley

        Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean



Albert Ringer

        Is Anger the Enemy?

        Life Without Fear



Anne Ringer

        The Gourmet Microwave

        Is Anger the Enemy?



...

Creating Expression-Based Columns Using Relations

One interesting application of the DataRelation object is the use of calculated columns. Once you have defined a DataRelation, you can use the relationship to create a calculated column that incorporates values from a related table. This allows you to create a column in a child table that contains information about the parent, or a column in a parent table with aggregate information about all children.

First, create a new DataColumn object with an Expression property that uses the syntax Child(RelationName).ColumnName or Parent(RelationName).ColumnName. For example, the following DataColumn could be used in the Products table in Examples 11-1 or 11-2 to retrieve the corresponding CategoryID. (The last parameter sets the DataColumn.Expression property.)

ds.Tables["Products"].Columns.Add("ProductCount", typeof(Int32),

 "Parent(Cat_Prod).CategoryID");

On the other hand, if you want to return information about a child, you need to use a SQL aggregate function such as COUNT, MIN, MAX, SUM, or AVG to process the information from all rows. Here’s a DataColumn that can be used in the Categories table to retrieve the total number all products in a category:

ds.Tables["Categories"].Columns.Add("ProductCount", typeof(Int32), 

"Count(Child(Cat_Prod).ProductID)");

Example 11-4 incorporates this technique into a full example that outputs information about the total price and average price of products in a category.

Example 11-4. Creating expression-based columns with a relation

// CaclulatedColumn.cs - Use a relationship with a calculated column



using System;

using System.Data;

using System.Data.SqlClient;



public class CalculatedColumn

{

    public static void Main() 

    {

        string connectionString = "Data Source=localhost;" +

                   "Initial Catalog=Northwind;Integrated Security=SSPI";

        string SQL = "SELECT * FROM Categories";



        // Create ADO.NET objects.

        SqlConnection con = new SqlConnection(connectionString);

        SqlCommand cmd = new SqlCommand(SQL, con);

        SqlDataAdapter adapter = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();



        try

        {

            con.Open();

            adapter.Fill(ds, "Categories");

    

            cmd.CommandText = "SELECT * FROM Products";

            adapter.Fill(ds, "Products");

        }

        catch (Exception err)

        {

            Console.WriteLine(err.ToString());

        }

        finally

        {

            con.Close();

        }



        // Create the relationships between the tables.

        DataColumn parentCol = 

                   ds.Tables["Categories"].Columns["CategoryID"];

        DataColumn childCol = ds.Tables["Products"].Columns["CategoryID"];

        DataRelation relation = new DataRelation("Cat_Prod", parentCol,

                                                  childCol);

        ds.Relations.Add(relation);



        // Create a calculated column showing average product price.

        ds.Tables["Categories"].Columns.Add("AveragePrice", 

          typeof(Decimal), "AVG(Child(Cat_Prod).UnitPrice)");



        // Create a calculated column showing total price for all products

        // in a category.

        ds.Tables["Categories"].Columns.Add("TotalPrice", 

          typeof(Decimal), "SUM(Child(Cat_Prod).UnitPrice)");



        // Display table information.

        foreach (DataRow row in ds.Tables["Categories"].Rows)

        {

            Console.WriteLine(row["CategoryName"]);

            Console.Write("\tAverage price in this category: ");

            Console.WriteLine(row["AveragePrice"]);

            Console.Write("\tPrice to purchase one of everything: ");

            Console.WriteLine(row["TotalPrice"]);

            Console.WriteLine();

        }

    }

}

Here’s a partial sampling of output from this example:

...

Confections

        Average price in this category: 25.16

        Price to purchase one of everything: 327.08



Dairy Products

        Average price in this category: 28.73

        Price to purchase one of everything: 287.3



Grains/Cereals

        Average price in this category: 20.25

        Price to purchase one of everything: 141.75

...

Tip

Relational expressions can also be used to define a custom sort order. This technique is explored in Chapter 12.