ADO.NET 3.5 Cookbook, 2nd Edition

4.10. 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 using the OLE DB provider.

The solution defines a SHAPE query to retrieve the TOP 2 Sales.SalesorderHeader records from AdventureWorks and the Sales.SalesOrderDetail records for each. A DataReader based on the query is created. The code iterates over the rows in the DataReader displaying the data for each Sales.SalesorderHeader row. If the value for the column can be cast to the IDataReader interface, it is a DataReader containing the Sales.SalesOrderDetail for the Sales.SalesorderHeader row. The value for the column is cast to a DataReader and the collection of records is iterated over and displayed.

The C# code in Program.cs in the project ShapeProviderRetrieveHierarchicalData is shown in Example 4-11.

Example 4-11. File: Program.cs for ShapeProviderRetrieveHierarchicalData solution

using System;
using System.Data;
using System.Data.OleDb;

namespace ShapeProviderRetrieveHierarchicalData
{
    class Program
    {
        static void Main(string[] args)
        {
            string oledbConnectString = "Provider=MSDataShape;" +
              "Data Provider=SQLOLEDB;Data Source=(local);" +
              "Initial Catalog=AdventureWorks;Integrated Security=SSPI;";

            // SHAPE SQL to retrieve TOP two orders and associated details.
            string shapeText =
                "SHAPE {SELECT TOP 2 * from Sales.SalesOrderHeader} AS Header " +
                "APPEND ({SELECT * from Sales.SalesOrderDetail} AS Detail " +
                "RELATE SalesOrderID TO SalesOrderID)";

            // Create the connection.
            OleDbConnection connection = new OleDbConnection(oledbConnectString);

            // Create a command and fill a DataReader with the
            // SHAPE result set.
            OleDbCommand command = new OleDbCommand(shapeText, connection);
            connection.Open();
            OleDbDataReader dr = command.ExecuteReader();

            // Iterate over the collection of rows in the DataReader.
            while (dr.Read())
            {
                Console.WriteLine("---ORDER---");
                // Iterate over the collection of Order columns in the DataReader.
                for (int colOrder = 0; colOrder < dr.FieldCount; colOrder++)
                {
                    if (dr[colOrder] is IDataReader)
                    {
                        // The column is an IDataReader interface.
                        Console.WriteLine("\n\t---{0} result set---",
                            dr.GetName(colOrder).ToUpper());

                        // Create a DataReader for the Order Detail from the
                        // IDataReader interface column.
                        OleDbDataReader orderDetailDR =
                            (OleDbDataReader)dr.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++)
                            {
                                Console.WriteLine("\t{0}: {1}",
                                    orderDetailDR.GetName(colOrderDetail),
                                    orderDetailDR[colOrderDetail]);
                            }
                            Console.WriteLine();
                        }
                    }
                    else
                    {
                        Console.WriteLine("{0}: {1}", dr.GetName(colOrder),
                            dr[colOrder]);
                    }
                }
            }

            dr.Close();
            connection.Close();

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

Partial output is shown in Figure 4-11.

Figure 4-11. Output for ShapeProviderRetrieveHierarchicalData solution

Discussion

You can retrieve hierarchical result sets or chapters (OLE DB type DBTYPE_HCHAPTER) from SQL Server using the OLE DB .NET data provider. The chapter is returned as a field in the data reader with a data type of Object, which is a DataReader.

Hierarchical result sets combine the results for multiple queries into a single structure. They are generated using the Data Shaping Service for OLE DB first introduced in ADO 2.0. This provider supports the Shape language, allowing the result set hierarchies to be constructed. Shaping is an alternative to JOIN and GROUP BY syntax that you can use to access parent/child data and associated summary data.

The connection string using data shaping is shown here:

	Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=(local);
	    Initial Catalog=AdventureWorks;Integrated Security=SSPI;

For more information about data shaping or the MSDataShape provider, see "Data Shaping Service for OLE DB" in the MSDN library.