DataRow.GetChildRows() & DataRow.GetParentRow(), DataRelation in C#
As we know, we can create a relation between two tables as a primary-foreign key relationship in SQL Server. Sometimes we may require having a relation between two C# data tables. For example, let’s have two C# data tables Categories, Products. Each category can have multiple products; each product mapped to only one category. Open Microsoft Visual Studio => create new Windows Forms application and name it as CSharpDataRelation. Add below code to create Categories & Products data tables.
//create DataSet object
DataSet ds = new DataSet();
//Add Categories table to DataSet object ds
DataTable dtCategories = ds.Tables.Add("Categories");
dtCategories.Columns.Add("CategoryId");
dtCategories.Columns.Add("CategoryName");
dtCategories.Rows.Add("1", "Electronics");
dtCategories.Rows.Add("2", "Games");
dtCategories.Rows.Add("3", "Softwares");
//Add Products table to DataSet object ds
DataTable dtProducts = ds.Tables.Add("Products");
dtProducts.Columns.Add("ProductId");
dtProducts.Columns.Add("CategoryId");
dtProducts.Columns.Add("ProductName");
dtProducts.Rows.Add("1", "1", "Dell Laptop");
dtProducts.Rows.Add("2", "1", "Sony Mobile");
dtProducts.Rows.Add("3", "2", "XCOM: Enemy Unknown for Xbox 360");
dtProducts.Rows.Add("4", "2", "Microsoft Halo 3 for Xbox 360");
dtProducts.Rows.Add("5", "3", "Microsoft Office 2016");
dtProducts.Rows.Add("6", "3", "Microsoft Office 365");
Create data relation between Categories & Products tables using DataRelation on Id columns as shown below.
DataRelation categoryProductRelation = ds.Relations.Add("CategoryProduct",
ds.Tables["Categories"].Columns["CategoryId"],
ds.Tables["Products"].Columns["CategoryId"]);
As you see above, datarelation created between Categories table CategoryId column and Products table ProductId column. Now we can get the child rows based on parent row by using GetChildRows() method as shown below.
//Get Child Rows based on Parent Id. Here display all Products for each Category
foreach (DataRow categoryRow in ds.Tables["Categories"].Rows)
{
string sCategory = categoryRow["CategoryName"].ToString();
string sProducts = string.Empty;
foreach (DataRow productRow in categoryRow.GetChildRows(categoryProductRelation))
{
sProducts = sProducts + " " + productRow["ProductName"].ToString();
}
Console.WriteLine("{0} category products are: {1}", sCategory, sProducts);
}
Above code displays the each category and it’s associated products by using GetChildRows() method.
If we need the parent record for any child record, apply GetParentRow() method for child row to find the parent row as below.
//Display Category name for Microsoft Office 201 product
foreach (DataRow productRow in ds.Tables["Products"].Rows)
{
if (productRow["ProductName"].ToString() == "Microsoft Office 2016")
{
DataRow categoryRow = productRow.GetParentRow(categoryProductRelation);
Console.WriteLine("{0} product belongs to {1} category",
productRow["ProductName"].ToString(),
categoryRow["CategoryName"].ToString());
}
}
Here we are displaying parent record Category Name for child record “Microsoft Office2016” product by using GetParentRow() method.
Total Source Code:
using System;
using System.Data;
namespace CSharpDataRelations
{
class Program
{
static void Main(string[] args)
{
//create DataSet object
DataSet ds = new DataSet();
//Add Categories table to DataSet object ds
DataTable dtCategories = ds.Tables.Add("Categories");
dtCategories.Columns.Add("CategoryId");
dtCategories.Columns.Add("CategoryName");
dtCategories.Rows.Add("1", "Electronics");
dtCategories.Rows.Add("2", "Games");
dtCategories.Rows.Add("3", "Softwares");
//Add Products table to DataSet object ds
DataTable dtProducts = ds.Tables.Add("Products");
dtProducts.Columns.Add("ProductId");
dtProducts.Columns.Add("CategoryId");
dtProducts.Columns.Add("ProductName");
dtProducts.Rows.Add("1", "1", "Dell Laptop");
dtProducts.Rows.Add("2", "1", "Sony Mobile");
dtProducts.Rows.Add("3", "2", "XCOM: Enemy Unknown for Xbox 360");
dtProducts.Rows.Add("4", "2", "Microsoft Halo 3 for Xbox 360");
dtProducts.Rows.Add("5", "3", "Microsoft Office 2016");
dtProducts.Rows.Add("6", "3", "Microsoft Office 365");
//Create Data Relation between Categories & Products Table
DataRelation categoryProductRelation = ds.Relations.Add("CategoryProduct",
ds.Tables["Categories"].Columns["CategoryId"],
ds.Tables["Products"].Columns["CategoryId"]);
//Get Child Rows based on Parent Id. Here display all Products for each Category
foreach (DataRow categoryRow in ds.Tables["Categories"].Rows)
{
string sCategory = categoryRow["CategoryName"].ToString();
string sProducts = string.Empty;
foreach (DataRow productRow in categoryRow.GetChildRows(categoryProductRelation))
{
sProducts = sProducts + " " + productRow["ProductName"].ToString();
}
Console.WriteLine("{0} category products are: {1}", sCategory, sProducts);
}
Console.WriteLine("===========================================================================");
//Display Category name for Microsoft Office 201 product
foreach (DataRow productRow in ds.Tables["Products"].Rows)
{
if (productRow["ProductName"].ToString() == "Microsoft Office 2016")
{
DataRow categoryRow = productRow.GetParentRow(categoryProductRelation);
Console.WriteLine("{0} product belongs to {1} category",
productRow["ProductName"].ToString(),
categoryRow["CategoryName"].ToString());
}
}
Console.ReadLine();
}
}
}
Run the application and displays the output as below. It lists the products of each category & also shows the category of the product “Microsoft Office 2016”. In this way we can use GetChildRows() and GetParentRow() methods to get the child and parent rows.
