Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How To Use the ADO SHAPE Command with a DataReader in Visual C# .NET

How To Use the ADO SHAPE Command with a DataReader in Visual C# .NET

View products that this article applies to.

This article was previously published under Q309130

↑ Back to the top

Summary

This article describes the ActiveX Data Objects (ADO) SHAPE command syntax that produces hierarchical recordsets and explains how to traverse hierarchical recordsets. This article also provides sample code.

You can use hierarchical recordsets as an alternative to JOIN and GROUP BY syntax when you need to access parent-child and summary data. Hierarchical recordsets are used in many products: Xbase products use the SET RELATION command, Microsoft Access uses segmented virtual tables internally for reports with grouping levels, and so forth.

Hierarchical recordsets are available through the MSDataShape provider, which the client cursor engine implements. Hierarchies enable you to build one or several recordsets, define groupings, and specify aggregate calculations over child recordsets. Although you can implement similar functionality through code, this functionality shifts much of the mundane work from the developer to the system.

Difference Between Hierarchical Recordsets and SQL JOIN and GROUP BY Statements

Hierarchical recordsets differ from SQL JOIN and GROUP BY statements. In a SQL JOIN statement, both the parent table fields and the child table fields are represented in the same recordset. In a hierarchical recordset, the recordset contains only fields from the parent table. In addition, the recordset contains an extra field that represents the related child data, which you can assign to a second recordset variable and traverse.

When use GROUP BY and aggregate operators to perform aggregate functions, only aggregate values appear in the recordset. In hierarchical recordsets, the aggregate values are represented in the parent recordset, and the detail records are represented in the child recordset.

Different Types of Shapes

You can create three types of shapes:
  • Relation-based
  • Parameter-based
  • Group-based
Each type has its own advantages and disadvantages. You need to choose the mechanism that best fits the needs of your application and the environment in which you will be running your application.

Relation-Based Hierarchy

Both the relation-based and parameter-based hierarchies produce a hierarchy that is otherwise represented by a SQL JOIN statement. However, these hierarchies differ in how they read the parent and child records. In the relation-based hierarchy, all of the parent and child records are read into a local cache before any processing continues. As a result, the relation-based hierarchy has a high initial overhead when you retrieve the records. However, the overhead is low after the initial retrieval.

Parameter-Based Hierarchy

Initially, parameter-based hierarchies only read the parent records and fetch the child records on demand. Although the initial overhead is reduced, you must issue a new child query for each parent record that you access, and you must maintain the connection to the data source for as long as the recordset is open.

Group-Based Hierarchy

The group-based hierarchy is equivalent to producing an aggregate SQL statement that is joined to a detail SQL statement. The group-based hierarchy is also equivalent to performing aggregate functions on non-normalized data. You cannot update the summary columns and calculated columns because they may be derived from more than one record. Like relation-based hierarchies, all records must be read up front.

Simplified Syntax

The SHAPE clause makes the hierarchical recordsets available. This section provides the simplified syntax; subsequent sections include examples with diagrams. Because the SHAPE syntax can become quite complex, the formal grammar for the SHAPE clause is provided at the end of the article to allow you to extend the examples. You can also use the program at the end of this article to test your own SHAPE statements. 
   SHAPE {parent-statement}
   APPEND Aggregate
        | ({child-statement} [As Alias]
          RELATE parent-field TO child-field | parameter-marker
                 [, parent-field TO child-field | parameter-marker ...])
          [, Aggregate | ({child statement})...]

   SHAPE {non-normalized-statement} [AS Alias]
   COMPUTE Aggregate
         | Alias
         | ({child-statement} [As Alias] RELATE parent-field TO
                                        child-field | parameter-marker)
           [, Aggregate | Alias | ({child-statement}...)]
   [BY grouping-field [, grouping-field]]

   SHAPE {non-normalized-statement} [AS Alias]
   BY grouping-field [, grouping-field]

Notes

  • If you select fields that have identical names from different tables, you may need to assign them an alias to ensure that the SHAPE parser works.
  • The SHAPE APPEND syntax functions similarly to an OUTER JOIN statement; a parent record is returned even if no child records exist for it.
  • Aggregates can only operate on fields in the immediate children of the recordset. To operate on fields in grandchildren and so forth, you must produce intermediate aggregates. See the Group Hierarchy with Aggregate example for an illustration.
  • If you use an aggregate function with the SHAPE APPEND syntax, the aggregate value occupies a field that is appended to the parent resultset, which also contains the fields from the parent statement. In contrast, SHAPE COMPUTE and SHAPE BY create a new parent level for the aggregates, and the non-normalized statement becomes the child recordset.
  • The SHAPE provider requires that you include Alias for the non-normalized statement in the COMPUTE clause when you use SHAPE COMPUTE. If you do not, you receive an error message that the functionality is not supported, even though you do not receive a syntax error.

Examples

This section provides examples with diagrams. These examples use tables from the Northwind sample database.

Simple Relation Hierarchy

   SHAPE  {select * from customers}
   APPEND ({select * from orders} AS rsOrders
           RELATE customerid TO customerid)
which yields: 
   Customers.*
   rsOrders
        |
        +----Orders.*
In the previous diagram, the parent recordset contains all fields from the Customers table, as well as a field named rsOrders. rsOrders provides a reference to the child recordset and contains all of the fields from the Orders table. The other examples use a similar notation.

Parameterized Hierarchy

   SHAPE  {select * from customers}
   APPEND ({select * from orders where customerid = ?} AS rsOrders
           RELATE customerid TO PARAMETER 0)
This results in the same hierarchy as the simple relation hierarchy.

Compound Relation Hierarchy

This sample illustrates a three-level hierarchy of customers, orders, and order details: 
   SHAPE  {SELECT * from customers}
   APPEND ((SHAPE  {select * from orders}
            APPEND ({select * from [order details]} AS rsDetails
                   RELATE orderid TO orderid)) AS rsOrders
          RELATE customerid TO customerid)
which yields: 
   Customers.*
   rsOrders
        |
        +----Orders.*
             rsDetails
                 |
                 +----[Order Details].*

Multiple Relation Hierarchy

This sample illustrates a hierarchy that involves a parent recordset and two child recordsets, one of which is parameterized: 
   SHAPE  {SELECT * FROM customers}
   APPEND ({SELECT *
            FROM orders
            WHERE orderdate < #1/1/1998# AND customerid = ?}
            RELATE customerid TO PARAMETER 0) AS rsOldOrders,
          ({SELECT *
            FROM orders
            WHERE orderdate >= #1/1/1998#}
            RELATE customerid TO customerid) AS rsRecentOrders
which yields: 
   Customers.*
   rsOldOrders
        |
        +----Orders.*
   rsRecentOrders
        |
        +----Orders.*

Hierarchy with Aggregate

   SHAPE  (select * from orders}
   APPEND ({select od.orderid, od.UnitPrice * od.quantity as ExtendedPrice
            from [order details] As od}
          RELATE orderid TO orderid) As rsDetails,
          SUM(ExtendedPrice) AS OrderTotal
which yields: 
   Orders.*
   rsDetails
       |
       +----orderid
            ExtendedPrice
   OrderTotal

Group Hierarchy

   SHAPE  {select customers.customerid AS cust_id, orders.*
           from customers inner join orders
           on customers.customerid = orders.customerid} AS rsOrders
   COMPUTE rsOrders BY cust_id
which yields: 
   rsOrders
       |
       +----cust_id
            Orders.*
   cust_id

Group Hierarchy with Aggregate

NOTE: The inner SHAPE clause in this example is identical to the statement that is used in the Hierarchy with Aggregate example. 
   SHAPE
           (SHAPE   {select customers.*, orders.orderid, orders.orderdate
                     from customers inner join orders
                     on customers.customerid = orders.customerid}
            APPEND  ({select od.orderid,
                             od.unitprice * od.quantity as ExtendedPrice
                      from [order details] as od} AS rsDetails
                    RELATE orderid TO orderid),
                    SUM(rsDetails.ExtendedPrice) AS OrderTotal) AS rsOrders
   COMPUTE  rsOrders,
            SUM(rsOrders.OrderTotal) AS CustTotal,
            ANY(rsOrders.contactname) AS Contact
   BY       customerid
which yields: 
   rsOrders
        |
        +----Customers.*
             orderid
             orderdate
             rsDetails
                  |
                  +----orderid
                       ExtendedPrice
             OrderTotal
   CustomerTotal
   Contact
   customerid

Multiple Groupings

   SHAPE
          (SHAPE  {select customers.*,
                          od.unitprice * od.quantity as ExtendedPrice
                   from (customers inner join orders
                   on customers.customerid = orders.customerid) inner join
                   [order details] as od on orders.orderid = od.orderid}
                          AS rsDetail
           COMPUTE ANY(rsDetail.contactname) AS Contact,
                   ANY(rsDetail.region) AS Region,
                   SUM(rsDetail.ExtendedPrice) AS CustTotal,
                   rsDetail
           BY customerid) AS rsCustSummary
   COMPUTE rsCustSummary
   BY      Region
which yields: 
   rsCustSummary
        |
        +-----Contact
              Region
              CustTotal
              rsDetail
                   |
                   +----Customers.*
                        ExtendedPrice
              customerid
   Region

Grand Total

   SHAPE
          (SHAPE  {select customers.*,
                          od.unitprice * od.quantity as ExtendedPrice
                   from (customers inner join orders
                   on customers.customerid = orders.customerid) inner join
                   [order details] as od on orders.orderid = od.orderid}
                          AS rsDetail
           COMPUTE ANY(rsDetail.contactname) AS Contact,
                   SUM(rsDetail.ExtendedPrice) AS CustTotal,
                   rsDetail
           BY customerid) AS rsCustSummary
   COMPUTE SUM(rsCustSummary.CustTotal) As GrandTotal,
           rsCustSummary
Note the missing BY clause in the outer summary. This defines the Grand Total because the parent rowset contains a single record with the grand total and a pointer to the child recordset. 
   GrandTotal
   rsCustSummary
        |
        +-----Contact
              CustTotal
              rsDetail
                  |
                  +----Customers.*
                       ExtendedPrice
              customerid

Complex Hierarchy

This example illustrates a hierarchy that contains one parent rowset, two child rowsets (one of which is parameterized), and a group detail. 
   SHAPE {select customers.* from customers} AS rsDetail
   COMPUTE rsDetail,
           ANY(rsDetail.companyname) AS Company,
           ({select * from orders where customerid = ?}
                   RELATE customerid TO PARAMETER 0) AS rsOrders,
           COUNT(rsOrders.orderid) AS OrderCount
   BY customerid
which yields: 
rsDetail
        |
        +----Customers.*
   Company
   rsOrders
        |
        +----Orders.*
   OrderCount
   customerid

Grouped Parent Related to Grouped Child

   SHAPE
          (SHAPE  {select * from customers}
           APPEND ((SHAPE {select orders.*, year(orderdate) as OrderYear,
                                  month(orderdate) as OrderMonth
                           from orders} AS rsOrders
                    COMPUTE rsOrders
                    BY customerid, OrderYear, OrderMonth)
                    RELATE customerid TO customerid) AS rsOrdByMonth )
           AS rsCustomers
   COMPUTE rsCustomers
   BY      region
which yields: 
   rsCustomers
        |
        +-----customers.*
              rsOrdByMonth
                 |
                 +-----rsOrders
                            |
                            +---- Orders.*
                       customerid
                       OrderYear
                       OrderMonth
   region

SHAPE Clause Formal Grammar

  <shape-command>      ::=  SHAPE <table-exp> [AS <alias>]
                            [<shape_action>]

  <shape-action>       ::=  APPEND <aliased-field-list>
                            | COMPUTE <aliased-field-list>
                              [BY <field-list>]
                            | BY <field-list>

  <table-exp>          ::=  {<native-sql-statement>}
                            | ( <shape-command> )
 
  <aliased-field-list> ::=  <aliased-field> [, <aliased-field...]
 
  <aliased-field>      ::=  <field-exp> [AS <alias>]
 
  <field-exp>          ::=  ( <relation-exp> ) | <calculated-exp>
 
  <relation_exp>       ::=   <table-exp> [AS <alias>] RELATE
                             <relation-cond-list>
 
  <relation-cond-list> ::=   <relation-cond> [, <relation-cond>...]
 
  <relation-cond>      ::=   <field-name> TO <child-ref>
 
  <child-ref>          ::=   <field-name> | PARAMETER <param-ref>
 
  <param-ref>          ::=   <name> | <number>
 
  <field-list>         ::=   <field-name [, <filed-name>]
 
  <calculated-exp>     ::=   SUM (<qualified-field-name>)
                             | AVG (<qualified-field-name>)
                             | MIN (<qualified-field-name>)
                             | MAX (<qualified-field-name>)
                             | COUNT (<alias>)
                             | SDEV (<qualified-field-name>)
                             | ANY (<qualified-field-name>)
                             | CALC (<expression>)
 
  <qualified-field-name>::=  <alias>.<field-name> | <field-name>
 
  <alias>               ::=  <quoted-name>
 
  <field-name>          ::=  <quoted-name>
 
  <quoted-name>         ::=  "<string>" | '<string>' | <name>
 
  <name>                ::=  alpha [ alpha | digit | _ | # ...]
 
  <number>              ::=  digit [digit...]
 
  <string>              ::=  unicode-char [unicode-char...]
 
  <expression>          ::=  an expression recognized by the Jet
                             Expression service whose operands are
                             other non-CALC columns in the same row.

Visual C# .NET SHAPE Test Program

The following Visual C# .NET code enables you to type your own SHAPE command and display the field hierarchy or indicate the location of the syntax error. This sample uses the ADO.NET DataReader object to retrieve the hierarchical data.

Important: You must use the OLE DB managed provider with the MSDataShape provider. You cannot use the SQL or ODBC managed providers.
  1. Create a new Windows Application project in Visual C# .NET. Form1 is added to the project by default.
  2. Add two TextBox controls and one Button control to Form1. TextBox1, TextBox2, and Button1 are added by default.
  3. Set the following properties in TextBox1 and TextBox2:
    Multiline: True
    Scrollbars: Vertical
  4. Enlarge both text boxes so that they are large enough to display several lines of text.
  5. Add the following code to the top of the code module for Form1:
    using System.Data.OleDb;
  6. Locate the following declaration: 
    public class Form1 : System.Windows.Forms.Form
    Add the following declarations after the above declaration:
    OleDbConnection cn = new OleDbConnection();
OleDbCommand cmd;
OleDbDataReader drCust;
  7. Add the following code to the Form1_Load event procedure:
    //Set the default SHAPE to Customers - Orders
textBox1.Text = "SHAPE {SELECT * FROM CUSTOMERS} APPEND ({SELECT * FROM ORDERS} 
                AS CustOrders RELATE CustomerID TO CustomerID)";
//Deselect the text so that it is not highlighted on start up.
textBox1.Select(0,0);
  8. Add the following code after the Form1_Load() function:
    private void ListChapteredFields(OleDbDataReader dr, int intLevel) {
  OleDbDataReader drOrders;
  //Read the next row.
  dr.Read();

  for (int i = 0; i < dr.FieldCount; i++) {
        //Indent based on the level of the hierarchy.
        string strSpaces = new String(' ', intLevel * 3);
        //Get the field name.
	LogText(strSpaces + dr.GetName(i) + "\t");
        strSpaces = null;
	//Looking for FieldType of System.Data.IDataReader, a child recordset.
	if (dr[i] is IDataReader) {
		drOrders = (OleDbDataReader) dr.GetValue(i);
		//Recursively call for each level of the hierarchy.
		ListChapteredFields(drOrders, intLevel + 1);
	}
   }
}

private void LogText(string strLine) { 
  //At the first column, do not add a hard return.
  if (textBox2.Text == "") 
	textBox2.Text = strLine;
  else
        //Place each column on a new line.
        textBox2.Text = textBox2.Text + "\r\n" + strLine;
}
  9. On Form1, double-click Button1 to open the Code window and display the Click event for Button1. Add the following code to the Click event of Button1:
      //Modify the following line to connect to your SQL Server.
  cn.ConnectionString = "Provider=MSDataShape;Data Provider=SQLOLEDB;
                        Data Source=SQLServerName;Initial Catalog=Northwind;
                        User ID=login;Password=password;";
  cn.Open();

  textBox2.Text = "";
  //textBox1.Text is the SHAPE statement.
  cmd = new OleDbCommand(textBox1.Text, cn);
  drCust = cmd.ExecuteReader();
  ListChapteredFields(drCust, 0);

  //Clean up.
  drCust.Close();
  cn.Close();
  10. Make sure that you update the connection string to use your server, user name, and password.
  11. Press the F5 key to build and run the project, and click the command button. Notice that the hierarchy of fields appears in TextBox2.
  12. Type (or copy and paste) a different SHAPE command in TextBox1, and click the command button. Notice that the hierarchy of fields appears in TextBox2.

↑ Back to the top

Keywords: KB309130, kbsystemdata, kbhowtomaster, kbdatabase

↑ Back to the top

Article Info
Article ID : 309130
Revision : 8
Created on : 7/15/2004
Published on : 7/15/2004
Exists online : False
Views : 385