Dataset Relations属性

Dataset Relations属性

示例来自 MSDN，将四个表关联在一起，适应于在操作多表时，有时需要关联表有时不需要关联的情况下（见 CommunityServer 的 cs_shared_Threads_GetThreadSet 存储过程也属此类应用）。

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

namespace TestDataRelation
{

    class Program
    {
        static void Main()
        {
            PrintOrders("server=(local); uid=sa; pwd=; database=Test_DataSet_DataRelation");
        }

        private static void PrintOrders(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet customerOrders = new DataSet();

                connection.Open();

                SqlDataAdapter reader;

                reader = new SqlDataAdapter("SELECT * FROM Customers", connection);
                reader.Fill(customerOrders, "Customers");

                reader = new SqlDataAdapter("SELECT * FROM Orders", connection);
                reader.Fill(customerOrders, "Orders");

                reader = new SqlDataAdapter("SELECT * FROM OrderDetails", connection);
                reader.Fill(customerOrders, "OrderDetails");

                reader = new SqlDataAdapter("SELECT * FROM Products", connection);
                reader.Fill(customerOrders, "Products");

                connection.Close();

                DataRelation customerOrdersRelation = customerOrders.Relations.Add("CustOrders", customerOrders.Tables["Customers"].Columns["CustomerID"], customerOrders.Tables["Orders"].Columns["CustomerID"]);
                DataRelation orderDetailRelation = customerOrders.Relations.Add("OrderDetail", customerOrders.Tables["Orders"].Columns["OrderID"], customerOrders.Tables["OrderDetails"].Columns["OrderID"], false);
                DataRelation orderProductRelation = customerOrders.Relations.Add("OrderProducts", customerOrders.Tables["Products"].Columns["ProductID"], customerOrders.Tables["OrderDetails"].Columns["ProductID"]);

                // 打印所有订单及订单详情
                foreach (DataRow custRow in customerOrders.Tables["Customers"].Rows)
                {
                    Console.WriteLine("客户 ID: " + custRow["CustomerID"] + "\t客户姓名: " + custRow["CustomerName"]);
                    Console.WriteLine("----------------------------------------");

                    foreach (DataRow orderRow in custRow.GetChildRows(customerOrdersRelation))
                    {
                        Console.WriteLine("  订单号: " + orderRow["OrderID"]);
                        Console.WriteLine("\t订单日期: " + orderRow["OrderDate"]);

                        foreach (DataRow detailRow in orderRow.GetChildRows(orderDetailRelation))
                        {
                            Console.WriteLine("\t Product: " + detailRow.GetParentRow(orderProductRelation)["ProductName"]);
                            Console.WriteLine("\t Quantity: " + detailRow["Quantity"]);
                        }

                        Console.WriteLine();
                    }
                }

            }
        }
    }

}

MSDN:

请注意，当为 Customers 表和 Orders 表创建 DataRelation 时，没有为 createConstraints 标志指定任何值（默认为 true）。它假定 Orders 表中的所有行都具有一个存在于父 Customers 表中的 CustomerID 值。如果 CustomerID 存在于 Customers 表之外的 Orders 表中，则 ForeignKeyConstraint 将引发异常。

如果子列可能包含父列不包含的值，添加 DataRelation 时请将 createConstraints 标志设置为 false。在该示例中，对于 Orders 表和 OrderDetails 表之间的 DataRelation，createConstraints 标志将设置为 false。这样，应用程序就可以返回 OrderDetails 表中的所有记录并只返回 Orders 表中记录的子集，而不会生成运行时异常。

posted on 2008-12-17 15:09  yongshi123 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/yongshi123/articles/1356857.html