Microsoft KB Archive/309487

= HOW TO: Read Hierarchical Data into a DataSet in Visual Basic .NET =

Article ID: 309487

Article Last Modified on 9/4/2003

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition

-



This article was previously published under Q309487



For a Microsoft Visual C# .NET version of this article, see 318454.

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

IN THIS TASK
SUMMARY
 * How to Read Hierarchical Data into a DataSet

REFERENCES



SUMMARY
Use this step-by-step guide to copy 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 and ADO type adChapter) to fill the contents of a DataSet. When the DataAdapter finds a chaptered column during a Fill operation, a DataTable is created for the chaptered column, and that table is filled with the columns and rows from the chapter. The name of the table created for the chaptered column takes its name from both the parent table name and the chaptered column name; it has the following syntax:

ParentTableNameChapteredColumnName

If a table already exists in the DataSet and its name matches the name of the chaptered column, the current table is filled with the chapter data. If there is no column in an existing table that matches a column found in the chapter, a new column is added.

When the Fill operation is completed, the DataSet will contain two tables: Customers and CustomersOrders. The CustomersOrders table represents the chaptered column. An additional column named Orders is added to the Customers table, and an additional column named CustomersOrders is added to the CustomersOrders table. The Orders column in the Customers table is set to auto-increment. A DataRelation, CustomersOrders, is created by using the columns that were added to the tables with Customers as the parent table.

The following sample code uses the Customers and Orders tables that are included in the Microsoft SQL Server Northwind database.  Create a new Visual Basic .NET Console application project.

Module1.vb is created by default. If the Code window is not open, right-click Module1.vb in Solution Explorer, and then click View Code. Delete all of the code from the Code window.  Type or paste the following code in the Code window: Imports System.Data.OleDb Module Module1

Sub Main Dim nwindConn As OleDbConnection = New OleDbConnection(&quot;Provider=MSDataShape;Data Provider=SQLOLEDB;&quot; & _                                                &quot;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind&quot;)

Dim custDA As OleDbDataAdapter = New OleDbDataAdapter(&quot;SHAPE {SELECT CustomerID, CompanyName FROM Customers} &quot; & _                                             &quot;  APPEND ({SELECT CustomerID, OrderID FROM Orders} AS Orders &quot; & _ &quot; RELATE CustomerID TO CustomerID)&quot;, nwindConn) Dim custDS As DataSet = New DataSet custDA.Fill(custDS, &quot;Customers&quot;) Dim CustomerRow, OrderRow As DataRow Dim OrderRows As DataRow Dim sline As String Dim i As Integer CustomerRow = custDS.Tables(&quot;Customers&quot;).Rows(0) Console.WriteLine(&quot;Customer Row&quot;) Console.WriteLine(&quot;Customer ID = &quot; & CustomerRow(&quot;CustomerId&quot;).ToString) Console.WriteLine(&quot;Orders for Customer ID &quot; & CustomerRow(&quot;customerID&quot;).ToString) ' Retrieve child rows for the order. OrderRows = CustomerRow.GetChildRows(&quot;CustomersOrders&quot;) ' Do something with the child rows collection. For i = 0 To OrderRows.Length - 1 OrderRow = OrderRows(i) ' Do something with the detail row. sline = (OrderRow(&quot;OrderId&quot;).ToString) Console.WriteLine(sline) Next Console.ReadLine End Sub

End Module  Modify the connection string as appropriate for your environment. Press F5 to build and run the project. The following output appears in the Command window:

Customer Row

Customer ID = ALFKI

Orders for Customer ID ALFKI

10643

10692

10702

10835

10952

11011

 Press ENTER to close the Command window.</li></ol>

back to the top

<div class="references_section">