class Program
{
static void Main(string[] args)
{
SqlConnection thisConnection = new SqlConnection(
@"Data Source=.\SQLEXPRESS;" +
@"AttachDbFilename='C:\SQL Server 2000 Sample Databases\NORTHWND.MDF';"
+
@"Integrated Security=true;Connect Timeout=30;User Instance=true");
DataSet thisDataSet = new DataSet();
//为每一张表创建一个DataAdapter对象，然后填充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对象并将其连接到DataSet上。注：Customers是主表，Orders是子表
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 datailRow in orderRow.GetChildRows(orderDetailRel))
{
Console.WriteLine("\t\tProduct:" +
datailRow.GetParentRow(orderProductRel)["ProductName"]);
Console.WriteLine("\t\tQuantity:" + datailRow["Quantity"]);
}
}
}
thisConnection.Close();
Console.WriteLine();
}
}