C# 2005 数据库访问（四）

 在DataSet中访问多个表。

       ADO.NET模型与原来的数据访问模型相比，有一个最大的优点：DataSet对象可以记录多个表和他们之间的关系。也就是说在一个操作的不同程序段之间传递完整的相关数据集，体系结构内在地维护数据之间关系的完整性。

       DataRelation对象用于描述在DataSet中的多个DataTabels之间的关系。每一个DataSet都有DataRelations的DataRelation集合，这可以确认和操作相关的表。

        下面我们就以Customers和Orders表为例。在两个表中的匹配的CustomerID域定义Customers表和Orders表之间的一对多的关系，为此要创建一个代表这种关系的DataRelation对象。

下面为导航关系代码，关键代码已经加粗：

using System;

using System.Data;

using System.DataSqlClient;

class DataRelationExample{

    public static void Main(){

        //Specify SQL Server-specific connection string

        SqlConnection thisConnection = new SqlConnection(

        @"Data Source=(local);Integrated Security=SSPI;Initial Catalog=northwind");

        // Open connection

        thisConnection.Open();

        // Create DataAdapter object for update and other operations

        SqlDataAdapter thisAdapter=new SqlDataAdapter(

        "SELECT CustomerID,CompanyName FROM Customers",thisConnection);

        // Create CommandBuilder object to build SQL commands

        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

        // Create DataSet to contain related data tables,rows,and columns

        DataSet thisDataSet = new DataSet ();

        // Set up DataAdapter object for each tabel and fill

        SqlDataAdapter custAdapter = new SqlDataAdapter("SELECT * FROM Customers",thisConnection);

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

        custAdapter.Fill(thisDataSet,"Customers");

        orderAdapter.Fill(thisDataSet,"Orders");

        // Set up DataRelation between customers and orders

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

        // Print out nested customers and their order ids

        foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows){

               Console.WriteLine("Customer ID:" +custRow["CustomerID"]+"Name:"+custRow.[""CompanyName]);

               foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel)){

                      Console.WriteLine("      OrderID:" + orderRow["OrderID"]);

               }

        }                                

        thisConnection.Close();

    }

}

        下面将进一步扩展此程序，研究每一位顾客在订单中所定购的集体产品，以及具体产品的名称。为此需要Customers,Orders,Orders Details,Products这4个表。与前面的代码相似，首先初始化连接，然后为每一表创建一个DataAdapter对象：

       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");

        为4个表之间的每一种关系构建DataRelation对象：

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

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

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

        下面是这些关系的处理工作，利用一个foreach三级嵌套来完成：

         foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows){

               Console.WriteLine("Customer ID:" +custRow["CustomerID"]);

               foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel)){

                      Console.WriteLine("/tOrderID:" + orderRow["OrderID"]);

                      Console.WriteLine("/tOrder Date:" + orderRow["OrderDate"]);

                      foreach (DataRow detailRow in orderRow.GetChildRows(orderDetailRel)){

                             Console.WriteLine("/t/tProduct:" + detailRow.GetParentRow(orderProductRel)["ProductName"]);

                             Console.WriteLine("/tQuantity:" + detailRow["Quantity"]);

                      }

               }

        }

        上面代需要注意的地方：为了获取Product行，可以调用GetParentRow()，可以获得父对象，从关系的“多”端进入“一”端。有时这种从子对象进入父对象的导航也称为“逆流”导航。从父对象进入子对象的导航也称为“顺流”导航。

        ChildRelations和ParentRelations集合

        DataTable具有属性ChildRelatons，它保存所有的DataRelations的集合，当前的表以父关系的身份加入到DataRelations中。使用方法：

         foreach(DataRelation thisRelation in thisTable.ChildRelations){

                 foreach(DataRow thisParentRow in thisTable.Rows){

                          foreach(DataRow thisChildRow in thisParentRow.GetChildRows(thisRelation)){

                                 //...process child rows...

                          }

                 }

         }

         在DataTable上还有ParentRelation集合，可以按类似的方式使用DataTable。

<注：>本文参考文献

《C#入门经典》清华大学出版社