Microsoft.NET

……………………………………………….Expertise in .NET Technologies

ADO.NET Core Classes:DataRelations – Part 10

Posted by Ravi Varma Thumati on November 19, 2009

Constraints showed how to use ForeignKeyConstraint rules to help ensure the integrity of relational data. Though ForeignKeyConstraint objects are important for data validation, they don’t provide any benefit when it comes to navigating the DataSet. For example, even if you define a link between two tables with a ForeignKeyConstraint, you can’t use this link to create a master-detail list.

ADO.NET provides another object that can help in this situation: the DataRelation. You can use a DataRelation to automatically generate and apply a ForeignKeyConstraint, or you can use it simply as a navigational aid. The benefit of the DataRelation is that it allows you to navigate disconnected data using a defined relationship between tables. For example, if you select a single parent row, you can use the DataRelation to retrieve a list of child records. Conversely, you can use the DataRelation to discover the parent of any child row.

This chapter explains how to create a DataRelation, use it for record navigation, and build a master-detail list.

DataRelation Object Overview

Every DataSet contains a DataRelationCollection object, which contains DataRelation objects. Each DataRelation defines a relationship between two tables. There are two reasons why you might define DataRelation objects:

  • To provide better error checking (for example, spotting an orphaned child before you reconnect to update the data source). This functionality is provided through the ForeignKeyConstraint, which the DataRelation can create implicitly.
  • To provide better navigation.

A typical DataRelation requires three pieces of information: a descriptive name of your choosing (which doesn’t signify anything or relate to the data source), a reference to the parent DataColumn, and a reference to the child DataColumn. You can add this information with the DataRelation constructor:

DataRelation relation = new DataRelation(“Name”, ParentCol, ChildCol);

Once you’ve defined the DataRelation object, you need to add it to the DataSet:

ds.Relations.Add(relation);

For example, to create a relationship between product categories and products, use the following code:

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);

If you attempt to create a DataRelation, and the parent record column isn’t unique, or there are child records that refer to nonexistent parents, an exception is thrown.

DataRelations and Constraints

At first glance, the DataRelation seems to duplicate some of the functionality provided by the ForeignKeyConstraint to enforce referential integrity. However, this isn’t really the case. The DataRelation actually uses the Constraint classes. By default, when you create a new DataRelation, a UniqueConstraint is created automatically for the parent column and added to the parent table if it doesn’t already exist. At the same time, a ForeignKeyConstraint is created for the child column and added to the child table. Thus, the rules governing relational integrity are exactly the same as those discussed in constraints.

In some cases, you might want to create a DataRelation without generating any constraints. This allows you to use a relation to aid navigation but not enforce it as a data validation rule. One reason why you might not want to enforce a relationship could be because you have queried only a subset of the total parent rows in the data source. Consequently, there may be child rows that reference a valid parent row that isn’t a part of the DataSet.

To make sure constraints are generated, you must use the DataRelation constructor that accepts an additional Boolean value. If you supply false, the Constraint objects aren’t generated.

DataRelation relation = new DataRelation(“Name”, ParentCol,

ChildCol, false);

You can still use the DataRelation for navigation, but referentially integrity will not be enforced on the DataSet.

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 DataView. 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).

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: