Working with Multiple Tables and Relationships

Sen Thomas

The next example shows a more advanced use of the DataSet that, in addition to providing disconnected
data, uses table relationships. This example demonstrates how to retrieve some records from
the Categories and Products tables of the Northwind database and how to create a relationship
between them so that it’s easy to navigate from a category record to all of its child products and
create a simple report.
The first step is to initialize the ADO.NET objects and declare the two SQL queries (for retrieving
categories and products), as shown here:
string connectionString =
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string sqlCat = "SELECT CategoryID, CategoryName FROM Categories";
string sqlProd = "SELECT ProductName, CategoryID FROM Products";
SqlDataAdapter da = new SqlDataAdapter(sqlCat, con);
DataSet ds = new DataSet();

Next, the code executes both queries, adding two tables to the DataSet. Note that the connection
is explicitly opened at the beginning and closed after the two operations, ensuring the best
possible performance.

try
{
con.Open();
// Fill the DataSet with the Categories table.
da.Fill(ds, "Categories");
// Change the command text and retrieve the Products table.
// You could also use another DataAdapter object for this task.
da.SelectCommand.CommandText = sqlProd;
da.Fill(ds, "Products");
}
finally
{
con.Close();
}

In this example, the same DataAdapter is used to fill both tables. This technique is perfectly
legitimate, and it makes sense in this scenario because you don’t need to reuse the DataAdapter
to update the data source. However, if you were using the DataAdapter both to query data and to
commit changes, you probably wouldn’t use this approach. Instead, you would use a separate
DataAdapter for each table so that you could make sure each DataAdapter has the appropriate
insert, update, and delete commands for the corresponding table.
At this point you have a DataSet with two tables. These two tables are linked in the Northwind
database by a relationship against the CategoryID field. This field is the primary key for the Categories
table and the foreign key in the Products table. Unfortunately, ADO.NET does not provide
any way to read a relationship from the data source and apply it to your DataSet automatically.
Instead, you need to manually create a DataRelation that represents the relationship.
A relationship is created by defining a DataRelation object and adding it to the DataSet.
Relations collection. When you create the DataRelation, you specify three constructor arguments:
the name of the relationship, the DataColumn for the primary key in the parent table, and the
DataColumn for the foreign key in the child table.
Here’s the code you need for this example:

// Define the relationship between Categories and Products.

DataRelation relat = new DataRelation("CatProds",
ds.Tables["Categories"].Columns["CategoryID"],
ds.Tables["Products"].Columns["CategoryID"]);
// Add the relationship to the DataSet.
ds.Relations.Add(relat);

Once you’ve retrieved all the data, you can loop through the records of the Categories table and
add the name of each category to the HTML string:

StringBuilder htmlStr = new StringBuilder("");
// Loop through the category records and build the HTML string.
foreach (DataRow row in ds.Tables["Categories"].Rows)
{
htmlStr.Append("");
htmlStr.Append(row["CategoryName"].ToString());
htmlStr.Append("

    ");
    ...

    Here’s the interesting part. Inside this block, you can access the related product records for the
    current category by calling the DataRow.GetChildRows() method. Once you have this array of product
    records, you can loop through it using a nested foreach loop. This is far simpler than the code
    you’d need in order to look up this information in a separate object or to execute multiple queries
    with traditional connection-based access.
    The following piece of code demonstrates this approach, retrieving the child records and completing
    the outer foreach loop:
    ...
    // Get the children (products) for this parent (category).
    DataRow[] childRows = row.GetChildRows(relat);
    // Loop through all the products in this category.
    foreach (DataRow childRow in childRows)
    {
    htmlStr.Append("
  • ");

    • htmlStr.Append(childRow["ProductName"].ToString());
    htmlStr.Append("");
    }
    htmlStr.Append("
");
}

The last step is to display the HTML string on the page:
HtmlContent.Text = htmlStr.ToString();

:

Post Comments ()