Parent/Child DataRelation. The Basics.
In short, a parent – child relation is one where (in database terms) two tables have a one-to-many relationship.
One example would be Author and Books, one Author may have written many books, but a Book can only have one Author. This is of course an extreme simplification.
In ADO.Net you can easily set up this relation between two tables in a DataSet using a DataRelation.
All you need is two tables where one is considered the parent and one and is considered the child and a name for their relation.
Once this is setup, you can easily get the child rows (if any) for every row in the parent table.
Create a new C# console application and copy this, hopefully self-explanatory code.
static void Main(string[] args)
{
DataSet ds = new DataSet("DataSet");
// Table for parents
DataTable parentTable = new DataTable("Parents");
parentTable.Columns.Add("ParentId", typeof(int));
parentTable.Columns.Add("ParentName", typeof(string));
//Create some parents.
parentTable.Rows.Add(new object[] { 1, "Parent # 1" });
parentTable.Rows.Add(new object[] { 2, "Parent # 2" });
parentTable.Rows.Add(new object[] { 3, "Parent # 3" });
ds.Tables.Add(parentTable);
// Table for childrend
DataTable childTable = new DataTable("Childs");
childTable.Columns.Add("ChildId", typeof(int));
childTable.Columns.Add("ChildName", typeof(string));
childTable.Columns.Add("ParentId", typeof(int));
//Create some childs.
childTable.Rows.Add(new object[] { 1, "Child # 1", 1 });
childTable.Rows.Add(new object[] { 2, "Child # 2", 2 });
childTable.Rows.Add(new object[] { 3, "Child # 3", 1 });
childTable.Rows.Add(new object[] { 4, "Child # 4", 3 });
childTable.Rows.Add(new object[] { 5, "Child # 5", 3 });
ds.Tables.Add(childTable);
// Create their relation.
DataRelation parentChildRelation = new DataRelation("ParentChild", parentTable.Columns["ParentId"], childTable.Columns["ParentId"]);
ds.Relations.Add(parentChildRelation);
// Display each parent and their children based on the relation.
foreach (DataRow parent in parentTable.Rows)
{
// Get children
DataRow[] children = parent.GetChildRows(parentChildRelation);
Console.WriteLine("\n{0}, has {1} children", parent["ParentName"].ToString(), children.Count<DataRow>());
foreach (DataRow child in children)
{
Console.WriteLine("\t{0}", child["ChildName"].ToString());
}
}
}
Running the above code should give the following output:
Parent # 1, has 2 children
Child # 1
Child # 3
Parent # 2, has 1 children
Child # 2
Parent # 3, has 2 children
Child # 4
Child # 5
On a more advanced level, we can have this relation in the database via a foreign key and then use a typed DataSet
So create two tables in SQL Server with this relation (this is the same as above):
create table Parent(ParentId int primary key, ParentName nvarchar(10))
create table Child(ChildId int primary key, ChildName nvarchar(10), ParentId int)
alter table Child add constraint FK_Child_Parent foreign key(ParentId) references Parent(ParentId)
insert into Parent values (1, 'Parent # 1')
insert into Parent values (2, 'Parent # 2')
insert into Parent values (3, 'Parent # 3')
insert into Child values (1, 'Child # 1', 1)
insert into Child values (2, 'Child # 2', 2)
insert into Child values (3, 'Child # 3', 1)
insert into Child values (4, 'Child # 4', 3)
insert into Child values (5, 'Child # 5', 3)
--drop table Child
--drop table Parent
Then create a new C# console application, add a new DataSet. Call it ParentChildDataSet.xsd, then drag the Parent and Child tables just created onto the designer.
Copy code as follows:
static void Main(string[] args)
{
// DataSet and adapters.
ParentChildDataSet ds = new ParentChildDataSet();
ParentChildDataSetTableAdapters.ParentTableAdapter pta = new GetChildRowsTest.ParentChildDataSetTableAdapters.ParentTableAdapter();
ParentChildDataSetTableAdapters.ChildTableAdapter cta = new GetChildRowsTest.ParentChildDataSetTableAdapters.ChildTableAdapter();
// Fill the tables.
pta.Fill(ds.Parent);
cta.Fill(ds.Child);
// Display, in this case there is a data relation in the dataset, no need to provide this in GetChildRows.
// we will also have the added benefit of typed column names.
foreach (ParentChildDataSet.ParentRow parent in ds.Parent)
{
ParentChildDataSet.ChildRow[] children = parent.GetChildRows();
Console.WriteLine("\n{0}, has {1} children", parent.ParentName, children.Count<ParentChildDataSet.ChildRow>());
foreach (ParentChildDataSet.ChildRow child in children)
{
Console.WriteLine("\t{0}", child.ChildName);
}
}
}
Run it and you should get the same output as above.
One thing to notice, and this sometimes catches people out. You HAVE to Fill the Child table. Otherwise it will return no children for the parent.
Try to comment out the following line:
cta.Fill(ds.Child);
and rerun it. This will now return 0 children for the parents.
This is because the DataSet is separate from the database, it is just a disconnected representation. This means that if you, for example, add a parent row in the database,
you need to call Fill again to get it into the dataset. It may also be that when you fill the Child table, you may have a filter saying that only rows that meets a certain
criteria is to be included in the datatable in the dataset. I.e. you may have 20 rows in database, but the representation off that table in the dataset may only contain 10.
Hard to explain, but I hope it makes sense. In short, if you haven’t explicitly filled the child table, GetChildRows() will return 0, it will not query the database for you.
Finally, you can also do this using LINQ. Again, create a new C# console application, add a new LINQ to SQL class. Call it ParentChild.dbml.
Again, drag the Parent and Child tables to the designer. This will create the DataContext etc. Then simply copy the code as follows:
static void Main(string[] args)
{
ParentChildDataContext pcdc = new ParentChildDataContext();
var parentQuery = from p in pcdc.Parents select p;
foreach (Parent parent in parentQuery)
{
Console.WriteLine("\n{0}, has {1} children", parent.ParentName, parent.Childs.Count);
foreach (Child child in parent.Childs)
{
Console.WriteLine("\t{0}", child.ChildName);
}
}
}
Hopefully this has given some pointer on how this works.
Comments
- Anonymous
January 16, 2014
Parent/child code shown here is outdated. We should use CTE Common Table Expression for these types of queries instead an old-school for-loop.