HOW TO: Read Hierarchical Data into a DataSet in Visual C# .NET (318454)

SUMMARY

This article describes how to load hierarchical data into a DataSet by using the Fill method of the DataSet class.

back to the top

How to Read Hierarchical Data into a DataSet

You can use hierarchical rowsets, or chapters (OLE DB type DBTYPE_HCHAPTER; ADO type adChapter), to fill the contents of a DataSet. When the DataAdapter encounters a chaptered column during a Fill operation, a DataTable is created for the chaptered column. That table is filled with the columns and rows from the chapter. The table that is created for the chaptered column is named by using both the parent table name and the chaptered column name. The table name has this form: ParentTableNameChapteredColumnName. If a table in the DataSet that matches the name of the chaptered column already exists, the current table is filled with the chapter data. If there is no column in an existing table that matches a column in the chapter, a new column is added.

Before the tables in the DataSet are filled with the data in the chaptered columns, a relation is created between the parent and child tables of the hierarchical rowset by adding an integer column to both the parent and the child table, setting the parent column to automatically increment, and creating a DataRelation by using the added columns from both tables. The added relation is named by using the parent and the child column names with this form: ParentColumnName_ChildColumnName.

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>
        /// Summary description for Class1
        /// </summary>
        class Class1
        {
             /// <summary>
             /// The main entry point for the application
             /// </summary>
             [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.

back to the top