Navigate Master/Details Tables in DataSet using DataRelation

The ADO.NET DataSet object allows you to load multiple DataTable objects in Tables Collection. These tables may or may not have any relation between them and you can use them without worrying about their relations. Most development scenarios require you to load two related database tables in DataSet and you not only have to create relations between the tables but sometimes you may also have to display them as Master/Details hierarchy. In this tutorial I will show you how you can navigate related parent and child table rows with the help of DataRelation object.

DataRelation is one of the least talked about feature of ADO.NET DataSet. It can do wonders if you know how to use it for your advantage. It can generate parent child or nested tables display if you know how to play with it. Following figure shows you hierarchy view of two database tables Categories and Products which I am going to implement in this tutorial.

To start this tutorial I have created one ASP.NET page with only Label control on it to display the output shown in above figure. In the Page_Load event, I am creating two SqlDataAdapter objects to fill two database tables in DataSet as shown in the code below:

string constr = "Server=TestServer;Database=SampleDatabase;uid=test;pwd=test;"; 

string query1 = "SELECT CategoryID, CategoryName FROM Categories"; 
string query2 = "SELECT ProductName, CategoryID FROM Products"; 

SqlDataAdapter categoriesAdapter = new SqlDataAdapter(query1, constr); 
SqlDataAdapter productsAdapter = new SqlDataAdapter(query2, constr); 

DataSet ds = new DataSet(); 

categoriesAdapter.Fill(ds, "Categories"); 
productsAdapter.Fill(ds, "Products");

Notice when I am filling DataSet object I am giving tables useful names such as Categories and Products. It is useful to give tables such names because by default DataSet use its default table naming convention when you fill DataSet object using SqlDataAdapter. Now I have two tables in DataSet and to create DataRelation object I need the reference of parent table primary key column and matching foreign key column of child table. 

DataColumn parentColumn = s.Tables["Categories"].Columns["CategoryID"]; 
DataColumn childColumn = ds.Tables["Products"].Columns["CategoryID"];

Once you have parent and child tables columns you can create DataRelation object. Keep in mind that you have to add this object in DataSet Relations collection as shown below:

DataRelation relation = new DataRelation("Categories_Products", parentColumn, childColumn); 
ds.Relations.Add(relation);

Finally I will show you how you can navigate related tables in DataSet. First I am starting a foreach loop to iterate all the rows in Categories table and formatting CategoryName column data with html h4 heading tag. Then I am retrieving all the related child rows of each parent row with the help of GetChildRows method available in DataRow class in ADO.NET. This method need same relation name as parameter which you have created at the time of DataRelation object creation. This method returns the array of matching DataRow objects from the child table which I am iterating with another foreach loop to generate html bulleted lists. Following code listing shows the code for both foreach loops used to get the above output.

StringBuilder sb = new StringBuilder(""); 

foreach (DataRow parentRow in ds.Tables["Categories"].Rows) 
{ 
   sb.Append("<h4>"); 
   sb.Append(parentRow["CategoryName"].ToString()); 
   sb.Append("</h4>"); 

   DataRow[] childRows = parentRow.GetChildRows("Categories_Products"); 

   sb.Append("<ul>"); 

   foreach (DataRow childRow in childRows) 
   { 
      sb.Append("<li>"); 
      sb.Append(childRow["ProductName"].ToString()); 
      sb.Append("</li>"); 
   } 
   
   sb.Append("</ul>"); 
}

Label1.Text = sb.ToString();