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

Article translations Article translations
Article ID: 318454 - View products that this article applies to.
This article was previously published under Q318454
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

SUMMARY

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

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.

REFERENCES

For more information about ADO.NET objects and syntax, refer to the following topic in the Microsoft .NET Framework Software Development Kit (SDK) documentation:
Accessing Data with ADO.NET
http://msdn2.microsoft.com/en-us/library/e80y5yhx(vs.71).aspx
(c) Microsoft Corporation 2002, All Rights Reserved. Contributions by Ramesh Thyagarajan, Microsoft Corporation.

Properties

Article ID: 318454 - Last Review: February 27, 2014 - Revision: 2.3
APPLIES TO
  • 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
Keywords: 
kbnosurvey kbarchive kbhowtomaster kbsystemdata KB318454

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com