Mega Code Archive

 

Categories / C# / ADO Database

 

More than one Relations

using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; class Program {     static void Main(string[] args) {         SqlConnection thisConnection = new SqlConnection(@"Server=(local)\sqlexpress;Integrated Security=True;" +              "Database=northwind");         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();     } }