Navigating Relationships
Navigating Relationships
When you work with a DataSet object, you often need to navigate from one DataTable object to another. For example, when you work with a customer, you often start by retrieving that customer's DataRow object in the TblCustomer DataTable. To get to your customer's orders, you use the customer's primary key to search the TblOrder DataTable for orders that belong to that customer. This means the TblOrder DataTable must contain a customer foreign key, which is one or more columns that identify the customer who owns the order, based on a unique key in TblCustomer.
The unique key is usually the primary key, but it doesn't have to be. For example, the primary key on the employee table might be a GUID, but the employee table might also contain a social security number column that is configured as a unique key. Human resource records might be related to the employee table based on the social security number instead of the GUID.
A DataRelation object provides the means for navigating from one DataTable object to another by using the unique key value on the parent table to search for foreign keys in the child table. The DataRelation object can also use a specific foreign key value to find the parent DataRow object. The parent and child DataTable objects must be within the same DataSet object in order for you to create a DataRelation object that allows you to navigate between the parent and child DataTable objects. The DataSet object holds all of its DataRelation objects in the Relationships collection. Each DataTable object also contains a ChildRelations and ParentRelations collection. The following code snippet shows how you can create a DataRelation object and use it to navigate from a parent DataRow object to its child DataRow objects and from a child DataRow object to its parent DataRow object.
Visual Basic
'Create tables Dim ds1 As New DataSet() Dim dt1 As DataTable = ds1.Tables.Add("dt1") Dim dt2 As DataTable = ds1.Tables.Add("dt2") 'Create columns Dim parentPk As DataColumn = dt1.Columns.Add("pk", GetType(Integer)) dt2.Columns.Add("pk", GetType(Integer)) Dim childFk As DataColumn = dt2.Columns.Add("fk", GetType(Integer)) 'Create relation Dim rel As DataRelation = New DataRelation("dt1dt2", parentPk, childFk) ds1.Relations.Add(rel) 'Add some data dt1.Rows.Add(1) dt1.Rows.Add(2) dt1.Rows.Add(3) dt1.Rows.Add(4) dt1.Rows.Add(5) dt2.Rows.Add(1, 1) dt2.Rows.Add(2, 1) dt2.Rows.Add(3, 2) dt2.Rows.Add(4, 2) dt2.Rows.Add(5, 2) 'get a parent row Dim parentRow As DataRow = dt1.Rows(1) 'Get Children Dim children As DataRow() = parentRow.GetChildRows(rel) 'Get Parent again Dim myParent As DataRow = children(0).GetParentRow(rel)
C#
//Create tables DataSet ds1 = new DataSet(); DataTable dt1 = ds1.Tables.Add("dt1"); DataTable dt2 = ds1.Tables.Add("dt2"); //Create columns DataColumn parentPk = dt1.Columns.Add("pk", typeof(int)); dt2.Columns.Add("pk", typeof(int)); DataColumn childFk = dt2.Columns.Add("fk", typeof(int)); //Create relation DataRelation rel = new DataRelation("dt1dt2", parentPk, childFk); ds1.Relations.Add(rel); //Add some data dt1.Rows.Add(1); dt1.Rows.Add(2); dt1.Rows.Add(3); dt1.Rows.Add(4); dt1.Rows.Add(5); dt2.Rows.Add(1,1); dt2.Rows.Add(2,1); dt2.Rows.Add(3,2); dt2.Rows.Add(4,2); dt2.Rows.Add(5,2); //Get a parent row DataRow parentRow = dt1.Rows[1]; //Get Children DataRow[] children = parentRow.GetChildRows(rel); //Get Parent Again DataRow myParent = children[0].GetParentRow(rel); 