[ Team LiB ]Recipe 3.14 Using the Shape Language to Retrieve Hierarchical Data
Problem
You want to use the Shape language with ADO.NET to retrieve hierarchical data from a
SQL Server.
Solution
Execute the SHAPE command as shown in the following example using the OLE DB
provider.
The sample code defines a SHAPE query to retrieve the TOP 5 Orders from Northwind
and the Order Details for each of the Orders. A DataReader based on the query is created.
The code iterates over the rows in the DataReader displaying the data for each Order row.
If the value for the column can be cast to the IDataReader interface, it is a DataReader
containing the Order Details for the Order row. The value for the column is cast to a
DataReader and the collection of records is iterated over and displayed.
The C# code is shown in Example 3-14
.
Example 3-14. File: ShapeForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.OleDb;

// . . .

StringBuilder result = new StringBuilder( );

// SHAPE SQL to retrieve TOP five Orders and associated Order Detail records.

OleDbDataReader orderDetailDR =
(OleDbDataReader)orderDR.GetValue(colOrder);
// Iterate over records in the Order Detail DataReader.
while(orderDetailDR.Read( ))
{
// Iterate over the Order Detail columns
// in the Data Reader.
for(int colOrderDetail = 0;
colOrderDetail < orderDetailDR.FieldCount;
colOrderDetail++)
{
result.Append(" " +
orderDetailDR.GetName(colOrderDetail) +
": " + orderDetailDR[colOrderDetail] +
Environment.NewLine);
}
result.Append(Environment.NewLine);
}
}
else
{
result.Append(orderDR.GetName(colOrder)+ ": " +
orderDR[colOrder] + Environment.NewLine);
}
}
result.Append(Environment.NewLine);
}

orderDR.Close( );
conn.Close( );