Date: 05/28/10 (Asp Dot Net)    Keywords: sql

Hi everybody!
This is the question about how to use DataRelation object.
Let's say I have 2 tables:
SELECT customerId, customerName FROM customers
and
SELECT orderId, customerId, orderName FROM orders
which are related by customerId.

Assume I have method

getDataSet(sqlTextArray)

which returns me the DataSet based on array of queries sqlTextArray

So, then I can write:

String q1 = 
   "SELECT c.customerName, r.orderId, r.orderName "+
   "FROM customers AS c, orders as r "+
   "WHERE (c.ustomerId = r.customerId) ";
String[] q = new String[] { q1 };

DataSet d = getDataSet(q);
DataTable t = d.Tables[0];
for (int i=0; i < t.Rows.Count; i++) {
   String s = t.Rows[i]["customerName"] + ", " + 
      t.Rows[i]["orderId"] + ", " + t.Rows[i]["orderName"] + ".";
   Console.WriteLine(s);
}

String[] q = new String[] { 
   "SELECT customerId, customerName FROM customers", 
   "SELECT orderId, customerId, orderName FROM orders" 
};
DataSet d = getDataSet(q);
d.Relations.Add(new DataRelation("customer2order",
   d.Tables[0].Columns["customerId"],
   d.Tables[1].Columns["customerId"]));

Source: http://aspdotnet.livejournal.com/104432.html