Data Shaping Summary
The following describes concepts of data shaping, hierarchical recordsets, reshaping, grandchild aggregates, and the shape command syntax.
Data Shaping
Data shaping enables you to define the columns of a shaped Recordset, the relationships between the entities represented by the columns, and the manner in which the Recordset is populated with data.
Columns of a shaped Recordset may contain data from a data provider such as SQL Server, references to another Recordset, values derived from a calculation on a single row of a Recordset, values derived from an operation over a column of an entire Recordset; or they may be a newly fabricated, empty column.
When you retrieve the value of a column that contains a reference to another Recordset, ADO automatically returns the actual Recordset represented by the reference. A Recordset that contains another Recordset is called a hierarchical recordset. Hierarchical recordsets exhibit a parent-child relationship, wherein the parent is the containing recordset, and the child is the contained recordset. The reference to a Recordset is actually a reference to a subset of the child, called a chapter. A single parent may reference more than one child Recordset.
The shape command syntax enables you to programmatically create a shaped Recordset. You can then access the components of the Recordset programmatically or through an appropriate visual control. A shape command is issued like any other ADO command text.
You can make hierarchical Recordset objects in two ways with the shape command syntax. The first appends a child Recordset to a parent Recordset. The parent and child typically have at least one column in common: the value of the column in a row of the parent is the same as the value of the column in all rows of the child.
The second way generates a parent Recordset from a child Recordset. There must be a chapter column in the parent that references the child Recordset. You can create other parent columns by computing an aggregate operation over a column of the child, calculating expressions on a row of a Recordset, specifying a grouping column with the BY keyword, or appending a new, empty column.
You can nest hierarchical Recordset objects to any depth (that is, create child Recordset objects of child Recordset objects, and so on).
You can access the Recordset components of the shaped Recordset programmatically or through an appropriate visual control.
Microsoft provides a visual tool that generates shape commands for you (see the topic in the Visual Basic documentation titled, "The Data Environment Designer") and another visual tool that displays hierarchical cursors (see the topic in the Visual Basic documentation titled, "Using the Microsoft Hierarchical Flexgrid Control").
Reshaping
A Recordset created by a clause of a shape command may be assigned an alias name (typically with the AS keyword). The alias of a shaped Recordset can be referenced in an entirely different command. That is, you may reuse, or reshape, a previously shaped Recordset in a new shape command. To support this feature, ADO provides a property, Reshape Name.
Reshaping has two main functions. The first is to associate an existing Recordset with a new parent Recordset.
Example
. . .
rs1.Open "SHAPE {select * from Customers} " & _
"APPEND ({select * from Orders} AS chapOrders " & _
"RELATE CustomerID to CustomerID)", cn
rs2.Open "SHAPE {select * from Employees} " & _
"APPEND (chapOrders RELATE EmployeeID to EmployeeID)", cn
. . .
The second function is to enable non-chaptered access to existing child Recordset objects, using the syntax "SHAPE <recordset reshape name>".
Note You may not append columns to an existing Recordset, reshape a parameterized Recordset or the Recordset objects in any intervening COMPUTE clause, or perform aggregate operations on any Recordset decendant from the Recordset being reshaped. The Recordset being reshaped and the new shape command must both use the same Connection.
Grandchild Aggregates
The chapter column created in a clause of a shape command may be given chapter-alias name (typically with the AS keyword). You may identify any column, in any chapter, of the shaped Recordset with a fully qualified name identifying the child containing the column. For example, if the parent chapter, chap1, contains a child chapter, chap2, that has an amount column, amt, then the qualified name would be chap1.chap2.amt. The qualified name may then be used as an argument to one of the aggregate functions (SUM, AVG, MAX, MIN, COUNT, STDEV, or ANY).
Parameterized Commands with Intervening COMPUTE Commands
A typical parameterized shape APPEND command has a clause that creates a parent Recordset with a query command, and another clause that creates a child Recordset with a parameterized query command—that is, a command containing a parameter placeholder (a question mark, "?"). The resulting shaped Recordset has two levels wherein the parent occupies the upper level and the child occupies the lower level.
The clause that creates the child Recordset may now be an arbitrary number of nested shape COMPUTE commands, where the most deeply nested command contains the parameterized query. The resulting shaped Recordset has multiple levels, wherein the parent occupies the uppermost level, the child occupies the lowermost level, and an arbitrary number of Recordsets generated by the shape COMPUTE commands occupy the intervening levels.
The typical use for this feature is to invoke the aggregate function and grouping abilities of shape COMPUTE commands to create intervening Recordset objects with analytical information about the child Recordset. Furthermore, because this is a parameterized shape command, each time a chapter column of the parent is accessed, a new child Recordset may be retrieved. Because the intervening levels are derived from the child, they too will be recomputed.
Persisting Hierarchical Recordsets
You can save a hierarchical Recordset to a file in either ADTG or XML format by calling the Save method. However, two limitations apply when saving hierarchical Recordsets in XML format: You cannot save in XML if the hierarchical Recordset contains pending updates, and you cannot save a parameterized hierarchical Recordset.
For more information on the Data Shaping provider, see Microsoft Data Shaping Service for OLE DB (ADO) and The Data Shaping Service for OLE DB (OLE DB).