SUMMARY
This article describes the ActiveX Data Objects (ADO) SHAPE
command syntax that produces hierarchical recordsets and explains how to
traverse hierarchical recordsets. Sample code is also provided.
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.
back to the top
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 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.
back to the top
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.
back to the top
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.
back to the top
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.
back to the top
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.
back to the top
Simplified Syntax
The SHAPE clause makes the hierarchical recordsets available.
This section provides the simplified syntax. 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]
back to the top
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.
back to the top
Examples
This section provides examples with diagrams. These examples use
tables from the Northwind sample database.
back to the top
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.
back to the top
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.
back to the top
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].*
back to the top
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.*
back to the top
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
back to the top
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
back to the top
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
back to the top
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
back to the top
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
back to the top
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
back to the top
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
back to the top
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.
back to the top
Visual Basic .NET SHAPE Test Program
The following Visual Basic .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.
- Create a new Windows Application project in Visual Basic
.NET.
- Add two TextBox controls and one Button control. TextBox1, TextBox2, and Button1 are added by default.
- Set the following properties in TextBox1 and TextBox2:
Multiline: True
Scrollbars: Vertical
- Enlarge both text boxes so that they are large enough to
display several lines of text.
- Add the following code to the top of the default form's
code module:
Imports System.Data.OleDb
- Click to expand the Windows Form Designer generated
code region, and add the following code after the InitializeComponent call to place a default SHAPE statement in TextBox1. After you copy the code, hide the Windows Form Designer region.
Me.TextBox1.Text = "SHAPE {SELECT * FROM CUSTOMERS} APPEND ({SELECT * FROM ORDERS} " & _
"AS CustOrders RELATE CustomerID TO CustomerID)"
- Add the following code under the Windows Form
Designer generated code region.
Note You
must change User ID <username> and password =<strong
password> to the correct values before you run this code. Make sure that User
ID has the appropriate permissions to perform this operation on the database.
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim drCust As OleDbDataReader
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Button1.Click
cn = New OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" & _
"Data Source=(local);Initial Catalog=Northwind;User ID=<username>;Password=<strong password>")
cn.Open()
cmd = New OleDbCommand(TextBox1.Text, cn)
drCust = cmd.ExecuteReader()
TextBox2.Clear()
ListChapteredFields(drCust, 0)
End Sub
Private Sub ListChapteredFields(ByVal dr As OleDbDataReader, ByVal lngLevel As Long)
Dim i As Integer
Dim drOrders As OleDbDataReader
dr.Read()
For i = 0 To dr.FieldCount - 1
LogText(Space(lngLevel * 3) & dr.GetName(i) & vbTab)
'Looking for FieldType of System.Data.IDataReader
If TypeOf dr(i) Is IDataReader Then
drOrders = dr.GetValue(i)
ListChapteredFields(drOrders, lngLevel + 1)
End If
Next
End Sub
Private Sub LogText(ByVal strLine As String)
If TextBox2.Text = "" Then
TextBox2.Text = strLine
Else
TextBox2.Text = TextBox2.Text & vbCrLf & strLine
End If
End Sub
- Make sure that you update the OleDbConnection object's connection string to use your server, user name, and
password.
- Run the project, and click the command button. Notice that
the hierarchy of fields appears in TextBox2.
- 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