Nesting DataRelations (ADO.NET)

Article
10/26/2011

In a relational representation of data, individual tables contain rows that are related to one another using a column or set of columns. In the ADO.NET DataSet, the relationship between tables is implemented using a DataRelation. When you create a DataRelation, the parent-child relationships of the columns are managed only through the relation. The tables and columns are separate entities. In the hierarchical representation of data that XML provides, the parent-child relationships are represented by parent elements that contain nested child elements.

To facilitate the nesting of child objects when a DataSet is synchronized with an XmlDataDocument or written as XML data using WriteXml, the DataRelation exposes a Nested property. Setting the Nested property of a DataRelation to true causes the child rows of the relation to be nested within the parent column when written as XML data or synchronized with an XmlDataDocument. The Nested property of the DataRelation is false, by default.

For example, consider the following DataSet.

' Assumes connection is a valid SqlConnection.
Dim customerAdapter As SqlDataAdapter = New SqlDataAdapter( _
  "SELECT CustomerID, CompanyName FROM Customers", connection)
Dim orderAdapter As SqlDataAdapter = New SqlDataAdapter( _
  "SELECT OrderID, CustomerID, OrderDate FROM Orders", connection)

connection.Open()

Dim dataSet As DataSet = New DataSet("CustomerOrders")
customerAdapter.Fill(dataSet, "Customers")
orderAdapter.Fill(dataSet, "Orders")

connection.Close()

Dim customerOrders As DataRelation = dataSet.Relations.Add( _
  "CustOrders", dataSet.Tables("Customers").Columns("CustomerID"), _
  dataSet.Tables("Orders").Columns("CustomerID"))

// Assumes connection is a valid SqlConnection.
SqlDataAdapter customerAdapter = new SqlDataAdapter(
  "SELECT CustomerID, CompanyName FROM Customers", connection);
SqlDataAdapter orderAdapter = new SqlDataAdapter(
  "SELECT OrderID, CustomerID, OrderDate FROM Orders", connection);

connection.Open();

DataSet dataSet = new DataSet("CustomerOrders");
customerAdapter.Fill(dataSet, "Customers");
orderAdapter.Fill(dataSet, "Orders");

connection.Close();

DataRelation customerOrders = dataSet.Relations.Add(
  "CustOrders", dataSet.Tables["Customers"].Columns["CustomerID"],
  dataSet.Tables["Orders"].Columns["CustomerID"]);

Because the Nested property of the DataRelation object is not set to true for this DataSet, the child objects are not nested within the parent elements when this DataSet is represented as XML data. Transforming the XML representation of a DataSet that contains related DataSets with non-nested data relations can cause slow performance. We recommend that you nest the data relations. To do this, set the Nested property to true. Then write code in the XSLT style sheet that uses top-down hierarchical XPath query expressions to locate and transform the data.

The following code example shows the result from calling WriteXml on the DataSet.

<CustomerOrders>
  <Customers>
    <CustomerID>ALFKI</CustomerID>
    <CompanyName>Alfreds Futterkiste</CompanyName>
  </Customers>
  <Customers>
    <CustomerID>ANATR</CustomerID>
    <CompanyName>Ana Trujillo Emparedados y helados</CompanyName>
  </Customers>
  <Orders>
    <OrderID>10643</OrderID>
    <CustomerID>ALFKI</CustomerID>
    <OrderDate>1997-08-25T00:00:00</OrderDate>
  </Orders>
  <Orders>
    <OrderID>10692</OrderID>
    <CustomerID>ALFKI</CustomerID>
    <OrderDate>1997-10-03T00:00:00</OrderDate>
  </Orders>
  <Orders>
    <OrderID>10308</OrderID>
    <CustomerID>ANATR</CustomerID>
    <OrderDate>1996-09-18T00:00:00</OrderDate>
  </Orders>
</CustomerOrders>

Note that the Customers element and the Orders elements are shown as sibling elements. If you wanted the Orders elements to show up as children of their respective parent elements, the Nested property of the DataRelation would need to be set to true and you would add the following:

customerOrders.Nested = True

customerOrders.Nested = true;

The following code shows what the resulting output would look like, with the Orders elements nested within their respective parent elements.

<CustomerOrders>
  <Customers>
    <CustomerID>ALFKI</CustomerID>
    <Orders>
      <OrderID>10643</OrderID>
      <CustomerID>ALFKI</CustomerID>
      <OrderDate>1997-08-25T00:00:00</OrderDate>
    </Orders>
    <Orders>
      <OrderID>10692</OrderID>
      <CustomerID>ALFKI</CustomerID>
      <OrderDate>1997-10-03T00:00:00</OrderDate>
    </Orders>
    <CompanyName>Alfreds Futterkiste</CompanyName>
  </Customers>
  <Customers>
    <CustomerID>ANATR</CustomerID>
    <Orders>
      <OrderID>10308</OrderID>
      <CustomerID>ANATR</CustomerID>
      <OrderDate>1996-09-18T00:00:00</OrderDate>
    </Orders>
    <CompanyName>Ana Trujillo Emparedados y helados</CompanyName>
  </Customers>
</CustomerOrders>

Share via

Nesting DataRelations (ADO.NET)

See Also

Concepts

Other Resources

Additional resources