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

This article was previously published under Q318454
This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft VB .NET version of this article, see 309487.

This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System.Data
  • System.Data.OleDb

IN THIS TASK

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
Properties

Article ID: 318454 - Last Review: 02/27/2014 21:11:55 - Revision: 2.3

  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Visual C# .NET 2003 Standard Edition
  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET 1.1
  • kbnosurvey kbarchive kbhowtomaster kbsystemdata KB318454
Feedback