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

Use this step-by-step guide to copy 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 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: 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.

1. Create a new Visual Basic .NET Console application project.
   
   Module1.vb is created by default.
2. If the Code window is not open, right-click Module1.vb in Solution Explorer, and then click View Code.
3. Delete all of the code from the Code window.
4. Type or paste the following code in the Code window:

   Imports System.Data.OleDb
   Module Module1
   
       Sub Main()
           Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" & _
                                                    "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")
   
           Dim custDA As OleDbDataAdapter = New OleDbDataAdapter("SHAPE {SELECT CustomerID, CompanyName FROM Customers} " & _
                                                 "  APPEND ({SELECT CustomerID, OrderID FROM Orders} AS Orders " & _
                                                 "  RELATE CustomerID TO CustomerID)", nwindConn)
           Dim custDS As DataSet = New DataSet()
           custDA.Fill(custDS, "Customers")
     Dim CustomerRow, OrderRow As DataRow
           Dim OrderRows As DataRow()
           Dim sline As String
           Dim i As Integer
           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 To OrderRows.Length - 1
               OrderRow = OrderRows(i)
               ' Do something with the detail row.
               sline = (OrderRow("OrderId").ToString)
               Console.WriteLine(sline)
           Next
           Console.ReadLine()
       End Sub
   
   End Module
   					

5. Modify the connection string as appropriate for your environment.
6. 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
7. Press ENTER to close the Command window.

For additional information about a similar topic in Visual Basic 6.0, click the following article number to view the article in the Microsoft Knowledge Base: For more information about ADO.NET objects and syntax, refer to the following topic in the Microsoft .NET Framework Software Development Kit (SDK) documentation: