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
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("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
- 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.
REFERENCES
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:
189657 HOWTO: Use the ADO SHAPE Command
For more information about ADO.NET objects and syntax, refer to the following topic in the Microsoft .NET Framework Software Development Kit (SDK) documentation:
Retrieving Data Using DataReader
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
Keywords: kbhowtomaster kbsystemdata KB309487