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 Microsoft 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, in which 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. The records in the child Recordset are grouped, typically using the BY clause, and one row is added to the parent Recordset for each resulting group in the child. If the BY clause is omitted, the child Recordset will form a single group and the parent Recordset will contain exactly one row. This is useful for computing "grand total" aggregates over the entire child Recordset.

Regardless of which way the parent Recordset is formed, it will contain a chapter column that is used to relate it to a child Recordset. If you wish, the parent Recordset may also have columns that contain aggregates (SUM, MIN, MAX, and so on) over the child rows. Both the parent and the child Recordset may have columns which contain an expression on the row in the Recordset, as well as columns which are new and initially empty.

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 (see The Data Environment Designer in the Visual Basic documentation) and another that displays hierarchical cursors (see Using the Microsoft Hierarchical Flexgrid Control in the Visual Basic documentation).

For examples of shape commands and their resulting hierarchies, see Using the Data Shaping Service for OLE DB: A Closer Look.