DataSet中表示表之间关系

    两表之间存在父子关系，可以使用DataReleation类表示。

    DataRelation对象用于描述DataSet中多个DataTable对象之间的关系。每个DataSet都包含DataRelations的Relations集合，以查找和操纵相关表。

 

如下面代码，得到Customers表中各行中关联子表Orders表中的资料。

using System.Data.SqlClient;

        string sConnection = "Data Source=scott;Initial Catalog=northwind;Persist Security Info=True;User ID=sa;Password=sa123";
        DataSet dsSet = new DataSet();
        SqlDataAdapter sdaAdapter = null;

        /// <summary>
        /// 显示表中资料
        /// </summary>
        private void ShowData()
        {
            //建立Connection
            SqlConnection scConnection = new SqlConnection(sConnection);
            //建立Customer表Command
            SqlCommand scCommand = scConnection.CreateCommand();
            scCommand.CommandText = "select * from customers";
            //建立Adapter
            sdaAdapter = new SqlDataAdapter(scCommand);
            //得到数据
            sdaAdapter.Fill(dsSet, "customers");

            //建立Order表Command
            SqlCommand scOrderCommand = scConnection.CreateCommand();
            scOrderCommand.CommandText = "select * from Orders";
            SqlDataAdapter sdaOrder = new SqlDataAdapter(scOrderCommand);
            sdaOrder.Fill(dsSet, "orders");

            //建立关联

            DataRelation drCustRelection = dsSet.Relations.Add("CustOrders", 

dsSet.Tables["customers"].Columns["CustomerID"], dsSet.Tables["Orders"].Columns["CustomerID"]);

            foreach (DataRow drCust in dsSet.Tables["customers"].Rows)
            {
                DataTable dt = null;
                //通过Row.GetChildRows()方法得到子表资料
                foreach (DataRow drOrders in drCust.GetChildRows(drCustRelection))
                {
                    dt = dsSet.Tables["orders"].Clone();
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < dsSet.Tables["Orders"].Columns.Count; i++)
                    {
                        dr[i] = drOrders[i];
                    }
                    //得到关联表的资料
                    dt.Rows.Add(dr);
                }
            }
        }

 

多表关联例子：

 

using System.Data;
using System.Data.SqlClient;

namespace ManyRelations
{
    class Program
    {
        static void Main(string[] args)
        {
            // Specify SQL Server-specific connection string

            SqlConnection thisConnection = new SqlConnection(
                 @"Data Source=scott;Initial Catalog=northwind;Persist Security Info=True;User ID=sa;Password=sa123");

            DataSet thisDataSet = new DataSet();
            SqlDataAdapter custAdapter = new SqlDataAdapter(
                 "SELECT * FROM Customers", thisConnection);
            custAdapter.Fill(thisDataSet, "Customers");

            SqlDataAdapter orderAdapter = new SqlDataAdapter(
                 "SELECT * FROM Orders", thisConnection);
            orderAdapter.Fill(thisDataSet, "Orders");

            SqlDataAdapter detailAdapter = new SqlDataAdapter(
                 "SELECT * FROM [Order Details]", thisConnection);
            detailAdapter.Fill(thisDataSet, "Order Details");

            SqlDataAdapter prodAdapter = new SqlDataAdapter(
                 "SELECT * FROM Products", thisConnection);
            prodAdapter.Fill(thisDataSet, "Products");

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

            DataRelation orderDetailRel = thisDataSet.Relations.Add("OrderDetail",
                        thisDataSet.Tables["Orders"].Columns["OrderID"],
                        thisDataSet.Tables["Order Details"].Columns["OrderID"]);

            DataRelation orderProductRel = thisDataSet.Relations.Add(
              "OrderProducts", thisDataSet.Tables["Products"].Columns["ProductID"],
               thisDataSet.Tables["Order Details"].Columns["ProductID"]);

            foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows)
            {
                Console.WriteLine("Customer ID: " + custRow["CustomerID"]);

                foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))
                {
                    Console.WriteLine("\tOrder ID: " + orderRow["OrderID"]);
                    Console.WriteLine("\t\tOrder Date: " + orderRow["OrderDate"]);

                    foreach (DataRow detailRow in
                             orderRow.GetChildRows(orderDetailRel))
                    {
                        Console.WriteLine("\t\tProduct: " +
                        detailRow.GetParentRow(orderProductRel)["ProductName"]);
                        Console.WriteLine("\t\tQuantity: " + detailRow["Quantity"]);
                    }
                }
            }
            thisConnection.Close();
            Console.Write("Program finished, press Enter/Return to continue:");
            Console.ReadLine();
        }
    }
}