How to Read Hierarchical Data into a DataSet

http://www.kbalertz.com/Feedback_309487.aspx (via .NET 247)

This example uses the Customers and Orders tables that are included with the Microsoft SQL Server sample Northwind database:
1. Create a new Visual C# Console Application project. Class1.cs is created by default.
2. If the Code window is not open, right-click Class1.cs in Solution Explorer, and then click View Code.
3. Delete all of the code from the Code window.
4. Paste the following code into the Code window:

using System;
using System.Data;
using System.Data.OleDb;


namespace CSharpSample
{
///

/// Summary description for Class1
///

class Class1
{
///

/// The main entry point for the application
///

[STAThread]
static void Main(string[] args)
{
//
// TODO: Add code to start application here
OleDbConnection nwindConn = new OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
OleDbDataAdapter custDA = new OleDbDataAdapter("SHAPE {SELECT CustomerID, CompanyName FROM Customers} APPEND ({SELECT CustomerID, OrderID FROM Orders} AS Orders RELATE CustomerID TO CustomerID)", nwindConn);

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");
DataRow CustomerRow, OrderRow;
DataRow[] OrderRows;
String sline;
int i;
CustomerRow = custDS.Tables["Customers"].Rows[0];
Console.WriteLine("Customer Row");
Console.WriteLine("Customer ID = " + CustomerRow["CustomerId"].ToString());
Console.WriteLine("Orders for Customer ID " + CustomerRow["customerID"].ToString());
// Retrieve child rows for the order
OrderRows = CustomerRow.GetChildRows("CustomersOrders");
// Do something with the child rows collection

for (i=0; i<=OrderRows.Length-1;++i)
{
OrderRow = OrderRows[i];
sline = (OrderRow["OrderId"].ToString());
Console.WriteLine(sline);
}
Console.ReadLine();
//
}
}
}


5. Modify the connection string as appropriate for your environment.
6. Press the F5 key to build and run the project. Note that the program's output appears as follows in the Command window:
Customer Row
Customer ID = ALFKI
Orders for Customer ID ALFKI
10643
10692
10702
10835
10952
11011
7. When you are finished, press ENTER to close the Command window.
When the Fill operation is complete, the DataSet contains two tables: Customers and CustomersOrders. The CustomersOrders table represents the chaptered column. An additional column that is named Orders is added to the Customers table. An additional column that is named CustomersOrders is added to the CustomersOrders table. The Orders column in the Customers table is set automatically increment. A DataRelation that is named CustomersOrders is created by using the columns that were added to the tables with Customers as the parent table.