Skip to main content

How to Read Hierarchical Data into a DataSet (via .NET 247)

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 description for Class1

class Class1
/// The main entry point for the application

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());

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
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.
Post a Comment

Popular posts from this blog

Compact and Repair an Access Database. Add Ref. to : AdoDb, Jro

< ?xml version="1.0" encoding="utf-8" ?>

using ADODB;
using JRO;
using System.Configuration;
using System.Data.OleDb;
using System.IO;

public class CompactAndRepairAccessDb : System.Windows.Forms.Form
private System.ComponentModel.Container components = null;
private JRO.JetEngine jro;
private System.Windows.Forms.Button btnConfirm;
private System.Windows.Forms.TextBox tbxOriginalDbSize;
private System.Windows.Forms.TextBox tbxCompactedDbSize;
private OleDbConnection cnn;

public CompactAndRepairAccessDb() {

FileInfo fi = new FileInfo( ConfigurationSettings.AppSettings["PathOriginal"] );
int s = Convert.ToInt32( fi.Length/1000 );
this.tbxOriginalDbSize.Text = s.ToString() + " kb";

private void btnConfirm_Click(object sender, System.EventArgs e) {
// First close all instances of the database

VBScript to Automate login into gmail

Dim IE
Dim crtScreen
Set IE = CreateObject("InternetExplorer.Application")
USERNAME = "saudaziz"

With IE
.navigate ""
End With

'wait a while until IE as finished to load
Do while IE.busy
set WshShell = WScript.CreateObject("WScript.Shell")
Do While UCase(IE.Document.readyState) <> "COMPLETE"
WScript.Sleep 100
set WshShell=nothing
IE.document.all.Item("Email").value = USERNAME
IE.document.all.Item("pASSWD").value =pASSWORD
Set IE = Nothing