DataRelations in ADO.NET

http://msdn.microsoft.com/en-us/library/cc188919.aspx#S1

SELECT    c.CustomerID,    c.CompanyName,    c.ContactName,    o.OrderID,    o.OrderDate,    od.ProductID,    p.ProductName,    od.UnitPrice,   od.Quantity

FROM    Customers c    INNER JOIN Orders o ON c.CustomerID = o.CustomerID

    INNER JOIN [Order Details] od ON o.OrderID = od.OrderID

    INNER JOIN Products p ON od.ProductID = p.ProductID

Figure 2 Creating a DataSet

string sCn = ". . . ";

SqlConnection oCn = new SqlConnection (sCn);

DataSet oDs = new DataSet();

//— Fill the customer DataTable

string sSqlCustomer = "SELECT CustomerID, CompanyName, ContactName FROM Customers";

SqlDataAdapter oDaCustomer = new SqlDataAdapter(sSqlCustomer, oCn);

oDaCustomer.Fill(oDs, "Customer");

//— Fill the order DataTable

string sSqlOrder = "SELECT CustomerID, OrderID, OrderDate FROM Orders";

SqlDataAdapter oDaOrder = new SqlDataAdapter(sSqlOrder, oCn);

oDaOrder.Fill(oDs, "Order");

//— Fill the order detail DataTable

string sSqlOrderDetail = "SELECT od.OrderID, od.ProductID,

                p.ProductName, " +

                " od.UnitPrice, od.Quantity FROM [Order Details] od " +

                " INNER JOIN Products p ON od.ProductID = p.ProductID";

SqlDataAdapter oDaOrderDetail = new SqlDataAdapter(sSqlOrderDetail, oCn);

oDaOrderDetail.Fill(oDs, "OrderDetail");

Figure 3 Creating DataRelation Objects

//— Create the DataRelation and relate the customers to their orders

DataRelation oDr_Customer2Order = new DataRelation("Customer2Order",

            oDs.Tables["Customer"].Columns["CustomerID"],

            oDs.Tables["Order"].Columns["CustomerID"]);

oDs.Relations.Add(oDr_Customer2Order);

//— Create the DataRelation and relate the orders to their order details

DataRelation oDr_Order2OrderDetail = new        

            DataRelation("Order2OrderDetail",

            oDs.Tables["Order"].Columns["OrderID"],

            oDs.Tables["OrderDetail"].Columns["OrderID"]);

oDs.Relations.Add(oDr_Order2OrderDetail);

dataGrid1.DataSource = oDs;

ForeignKeyConstraint oFKey;

oFKey = new ForeignKeyConstraint("CustomerForeignkey",

    oDs.Tables["Customer"].Columns["CustomerID"],  oDs.Tables["Order"].Columns["CustomerID"]);

oFKey.DeleteRule = Rule.Cascade;

oFKey.UpdateRule = Rule.Cascade;

oDs.Tables["Customer"].Constraints.Add(oFKey);

oDs.EnforceConstraints = true;

Figure 4 Looping Through Child Rows

//— Retrieve the child rows for the first customer

DataRow[] oRows = oDs.Tables["Customer"].Rows[0].GetChildRows(oDr_Customer2Order);

string sMsg = "The orders for the first customer are: \n";

//— Loop through the child rows for the first customer

for (int i=0; i < oRows.Length; i++)

{

//— Grab the values for each child row of the first customer

    DataRow oRow = oRows[i];

    sMsg += "\t" + oRow["CustomerID"].ToString() +

            " " + oRow["OrderID"].ToString() +

            " " + oRow["OrderDate"].ToString() + "\n";

}

//— Display the values of the child rows

MessageBox.Show(sMsg);

DataRow oRow =  oDs.Tables["Order"].Rows[0].GetParentRow(oDr_Customer2Order);

MessageBox.Show(oRow["CompanyName"].ToString());

MessageBox.Show(oDs.GetXml());