在DataSet中访问多张表，随笔

今天学习了DataSet中访问多张表的知识&＃xff0c;现在记录下来&＃xff0c;供以后温习。。。高手就路过吧

DataSet中Relations属性返回DataRelation集合&＃xff0c;表示DataSet中各各表之间的关系。

要创建DataRelation &＃xff0c;可以使用DataSet.Relations.Add()方法。可以给该方法传递接受关系的字符串&＃xff0c;和两个列(父列在前&＃xff0c;子列在后)&＃xff0c;如&＃xff1a;

            DataRelation custOrderRel &＃61; thisDataSet.Relations.Add("CustOrder", thisDataSet.Tables["Customers"].Columns["CustomerID"], thisDataSet.Tables["Orders"].Columns["CustomerID"]);

然后就可以靠这个关系导航了

我们可以使用DataRow的GetChildRow()方法从父对象导航到子对象的相关联行&＃xff0c;给这个方法传递这两个表之间的关系对象&＃xff0c;如&＃xff1a;

foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows)
            {
                Console.WriteLine("CustomerID:{0}\nCustomer Company:{1}", custRow["CustomerID"], custRow["CompanyName"]);
                foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))
                {
                    Console.WriteLine("\t customer orderID:{0}", orderRow["OrderID"]);
                }
            }

如果想从子对象逆导航到父对象的相关联行&＃xff0c;可以使用DataRow的GetParentRow&＃xff08;&＃xff09;方法&＃xff0c;同样也需要给这个方法传递这两个表之间的关系对象。

下面是课本的一个例子&＃xff1a;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace GetChildRow
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection thisConnection &＃61; new SqlConnection(&＃64;"Data Source&＃61;WENGJIXI;" &＃43;
            &＃64;"Initial Catalog&＃61;NorthWind;" &＃43;
            &＃64;"Integrated Security&＃61;true;");

            SqlDataAdapter thisAdapter &＃61; new SqlDataAdapter("select CustomerID,CompanyName from Customers", thisConnection);
            SqlCommandBuilder thisBuilder &＃61; new SqlCommandBuilder(thisAdapter);

            DataSet thisDataSet &＃61; new DataSet();
            SqlDataAdapter custAdapter &＃61; new SqlDataAdapter("select * from Customers", thisConnection);
            SqlDataAdapter orderAdapter &＃61; new SqlDataAdapter("select * from Orders", thisConnection);
            custAdapter.Fill(thisDataSet, "Customers");
            orderAdapter.Fill(thisDataSet, "Orders");

            DataRelation custOrderRel &＃61; thisDataSet.Relations.Add("CustOrder", thisDataSet.Tables["Customers"].Columns["CustomerID"], thisDataSet.Tables["Orders"].Columns["CustomerID"]);

            foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows)
            {
                Console.WriteLine("CustomerID:{0}\nCustomer Company:{1}", custRow["CustomerID"], custRow["CompanyName"]);
                foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))
                {
                    Console.WriteLine("\t customer orderID:{0}", orderRow["OrderID"]);
                }
            }

            thisConnection.Close();
            Console.ReadLine();
        }
    }
}