在数据的关系表示形式中,各个表都包含使用一个列或一组列来相互关联的行。在 ADO.NET DataSet 中,表之间的关系使用 DataRelation 来实现。当创建 DataRelation 时,列的父子关系仅通过关系来管理。表和列是独立的实体。在 XML 提供的数据的分层表示形式中,父子关系通过包含嵌套子元素的父元素来表示。为了方便子对象在 DataSet 与 XmlDataDocument 同步或使用 WriteXml 以 XML 数据形式来编写时进行嵌套,DataRelation 会公开 Nested 属性。如果将 DataRelation 的 Nested 属性设置为 true,将使关系的子行在以 XML 数据形式编写或与 XmlDataDocument 同步时嵌套在父列中。默认情况下,DataRelation 的 Nested 属性为 false。例如,考虑以下 DataSet:[Visual Basic]
Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=Northwind;")
Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
Dim orderDA As SqlDataAdapter = New SqlDataAdapter("SELECT OrderID, CustomerID, OrderDate FROM Orders", nwindConn)nwindConn.Open()Dim custDS As DataSet = New DataSet("CustomerOrders")
custDA.Fill(custDS, "Customers")
orderDA.Fill(custDS, "Orders")nwindConn.Close()Dim custOrderRel As DataRelation = custDS.Relations.Add("CustOrders",
custDS.Tables("Customers").Columns("CustomerID"),
custDS.Tables("Orders").Columns("CustomerID"))
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;" +
"Integrated Security=SSPI;Initial Catalog=Northwind;");
SqlDataAdapter custDA = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
SqlDataAdapter orderDA = new SqlDataAdapter("SELECT OrderID, CustomerID, OrderDate FROM Orders", nwindConn);nwindConn.Open();DataSet custDS = new DataSet("CustomerOrders");
custDA.Fill(custDS, "Customers");
orderDA.Fill(custDS, "Orders");nwindConn.Close();DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
custDS.Tables["Customers"].Columns["CustomerID"],
custDS.Tables["Orders"].Columns["CustomerID"]);
因为对于该 DataSet,DataRelation 的 Nested 属性未设置为 true,所以当该 DataSet 表示为 XML 数据时,子对象将不会嵌套在父元素中。以下代码显示对 DataSet 调用 WriteXml 将生成的输出。<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>
请注意,Customers 元素和 Orders 元素显示为同辈元素。如果您要让 Orders 元素显示为它们各自父元素的子元素,则需要将 DataRelation 的 Nested 属性设置为 true,为此将添加以下代码:[Visual Basic]
custOrderRel.Nested = True
[C#]
custOrderRel.Nested = true;
以下代码显示当 Orders 元素嵌套在它们各自的父元素中时所生成的输出的可能形式。<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>
Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=Northwind;")
Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
Dim orderDA As SqlDataAdapter = New SqlDataAdapter("SELECT OrderID, CustomerID, OrderDate FROM Orders", nwindConn)nwindConn.Open()Dim custDS As DataSet = New DataSet("CustomerOrders")
custDA.Fill(custDS, "Customers")
orderDA.Fill(custDS, "Orders")nwindConn.Close()Dim custOrderRel As DataRelation = custDS.Relations.Add("CustOrders",
custDS.Tables("Customers").Columns("CustomerID"),
custDS.Tables("Orders").Columns("CustomerID"))
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;" +
"Integrated Security=SSPI;Initial Catalog=Northwind;");
SqlDataAdapter custDA = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
SqlDataAdapter orderDA = new SqlDataAdapter("SELECT OrderID, CustomerID, OrderDate FROM Orders", nwindConn);nwindConn.Open();DataSet custDS = new DataSet("CustomerOrders");
custDA.Fill(custDS, "Customers");
orderDA.Fill(custDS, "Orders");nwindConn.Close();DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
custDS.Tables["Customers"].Columns["CustomerID"],
custDS.Tables["Orders"].Columns["CustomerID"]);
因为对于该 DataSet,DataRelation 的 Nested 属性未设置为 true,所以当该 DataSet 表示为 XML 数据时,子对象将不会嵌套在父元素中。以下代码显示对 DataSet 调用 WriteXml 将生成的输出。<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>
请注意,Customers 元素和 Orders 元素显示为同辈元素。如果您要让 Orders 元素显示为它们各自父元素的子元素,则需要将 DataRelation 的 Nested 属性设置为 true,为此将添加以下代码:[Visual Basic]
custOrderRel.Nested = True
[C#]
custOrderRel.Nested = true;
以下代码显示当 Orders 元素嵌套在它们各自的父元素中时所生成的输出的可能形式。<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>
[Visual Basic, C#] 以下示例创建一个新的 DataRelation 并将其添加到 DataSet 的 DataRelationCollection 中。[Visual Basic]
Private Sub CreateRelation()
' Get the DataColumn objects from two DataTable objects in a DataSet.
Dim parentCol As DataColumn
Dim childCol As DataColumn
' Code to get the DataSet not shown here.
parentCol = DataSet1.Tables("Customers").Columns("CustID")
childCol = DataSet1.Tables("Orders").Columns("CustID")
' Create DataRelation.
Dim relCustOrder As DataRelation
relCustOrder = New DataRelation("CustomersOrders", parentCol, childCol)
' Add the relation to the DataSet.
DataSet1.Relations.Add(relCustOrder)
End Sub
[C#]
private void CreateRelation() {
// Get the DataColumn objects from two DataTable objects in a DataSet.
DataColumn parentCol;
DataColumn childCol;
// Code to get the DataSet not shown here.
parentCol = DataSet1.Tables["Customers"].Columns["CustID"];
childCol = DataSet1.Tables["Orders"].Columns["CustID"];
// Create DataRelation.
DataRelation relCustOrder;
relCustOrder = new DataRelation("CustomersOrders", parentCol, childCol);
// Add the relation to the DataSet.
DataSet1.Relations.Add(relCustOrder);
}
from table1, table2
where table1.col1 = table2.col2
Dim custOrderRel As DataRelation = custDS.Relations.Add("CustOrders", _
custDS.Tables("Customers").Columns("CustomerID"), _
custDS.Tables("Orders").Columns("CustomerID")) Dim custRow As DataRow
Dim orderRow As DataRowFor Each custRow in custDS.Tables("Customers").Rows
Console.WriteLine(custRow("CustomerID"))
For Each orderRow in custRow.GetChildRows(custOrderRel)
Console.WriteLine(orderRow("OrderID"))
Next
Next
[C#]
DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
custDS.Tables["Customers"].Columns["CustomerID"],
custDS.Tables["Orders"].Columns["CustomerID"]);
foreach (DataRow custRow in custDS.Tables["Customers"].Rows)
{
Console.WriteLine(custRow["CustomerID"]);
foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))
Console.WriteLine(orderRow["OrderID"]);
}
下一示例以上例为基础,将四个表关联在一起,并在导航这些关系。如上例所示,CustomerID 使 Customers 表与 Orders 表相关。对于 Customers 表中的每个客户,将确定 Orders 表中的所有子行,以返回特定客户的订单数以及他们的 OrderID 值。该扩展示例还将返回 OrderDetails 表和 Products 表中的值。Orders 表使用 OrderID 与 OrderDetails 表相关,以确定在每一客户订单中订购的产品及数量。由于 OrderDetails 表只包含已订购产品的 ProductID,OrderDetails 将使用 ProductID 与 Products 相关,以返回 ProductName。在这一关系中,Products 表为父表,而 Order Details 表为子表。因此,当循环访问 OrderDetails 表时,将调用 GetParentRow 来检索相关的 ProductName 值。请注意,当为 Customers 表和 Orders 表创建 DataRelation 时,没有为 createConstraints 标志指定任何值(默认为 true)。它假定 Orders 表中的所有行都具有一个存在于父 Customers 表中的 CustomerID 值。如果 CustomerID 存在于 Customers 表之外的 Orders 表中,则 ForeignKeyConstraint 将引发异常。如果子列可能包含父列不包含的值,添加 DataRelation 时请将 createConstraints 标志设置为 false。在该示例中,对于 Orders 表和 OrderDetails 表之间的 DataRelation,createConstraints 标志将设置为 false。这样,应用程序就可以返回 OrderDetails 表中的所有记录并只返回 Orders 表中记录的子集,而不会生成运行时异常。该扩展示例生成以下格式的输出。 Customer ID: NORTS
Order ID: 10517
Order Date: 4/24/1997 12:00:00 AM
Product: Filo Mix
Quantity: 6
Product: Raclette Courdavault
Quantity: 4
Product: Outback Lager
Quantity: 6
Order ID: 11057
Order Date: 4/29/1998 12:00:00 AM
Product: Outback Lager
Quantity: 3
以下代码示例是一个扩展示例,在该示例中将返回 OrderDetails 表和 Products 表中的值,并只返回 Orders 表中记录的子集。[Visual Basic]
Dim custOrderRel As DataRelation = custDS.Relations.Add("CustOrders", _
custDS.Tables("Customers").Columns("CustomerID"), _
custDS.Tables("Orders").Columns("CustomerID"))Dim orderDetailRel As DataRelation = custDS.Relations.Add("OrderDetail", _
custDS.Tables("Orders").Columns("OrderID"), _
custDS.Tables("OrderDetails").Columns("OrderID"), false)Dim orderProductRel As DataRelation = custDS.Relations.Add("OrderProducts", _
custDS.Tables("Products").Columns("ProductID"), _
custDS.Tables("OrderDetails").Columns("ProductID"))Dim custRow, orderRow, detailRow As DataRowFor Each custRow In custDS.Tables("Customers").Rows
Console.WriteLine("Customer ID:" & custRow("CustomerID").ToString()) For Each orderRow In custRow.GetChildRows(custOrderRel)
Console.WriteLine(" Order ID: " & orderRow("OrderID").ToString())
Console.WriteLine(vbTab & "Order Date: " & orderRow("OrderDate").ToString()) For Each detailRow In orderRow.GetChildRows(orderDetailRel)
Console.WriteLine(vbTab & " Product: " & detailRow.GetParentRow(orderProductRel)("ProductName").ToString())
Console.WriteLine(vbTab & " Quantity: " & detailRow("Quantity").ToString())
Next
Next
Next
[C#]
DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
custDS.Tables["Customers"].Columns["CustomerID"],
custDS.Tables["Orders"].Columns["CustomerID"]);DataRelation orderDetailRel = custDS.Relations.Add("OrderDetail",
custDS.Tables["Orders"].Columns["OrderID"],
custDS.Tables["OrderDetails"].Columns["OrderID"], false);DataRelation orderProductRel = custDS.Relations.Add("OrderProducts",
custDS.Tables["Products"].Columns["ProductID"],
custDS.Tables["OrderDetails"].Columns["ProductID"]);foreach (DataRow custRow in custDS.Tables["Customers"].Rows)
{
Console.WriteLine("Customer ID: " + custRow["CustomerID"]); foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))
{
Console.WriteLine(" Order ID: " + orderRow["OrderID"]);
Console.WriteLine("\tOrder Date: " + orderRow["OrderDate"]); foreach (DataRow detailRow in orderRow.GetChildRows(orderDetailRel))
{
Console.WriteLine("\t Product: " + detailRow.GetParentRow(orderProductRel)["ProductName"]);
Console.WriteLine("\t Quantity: " + detailRow["Quantity"]);
}
}
}