MDAC 2.5 SDK - OLE DB Providers
Data Shaping Service for OLE DB

Relation-Based Hierarchical Rowsets

See Also                    Related Topics

The OLE DB Data Shaping Service can be used to create a relationship between two rowsets retrieved from parent and child tables that have at least one field in common. You can have exact control in building this type of hierarchy by specifying the relationship in the SHAPE command. Performance and data freshness should be considered when constructing relation-based hierarchies.

Parent-Child Relationships

If the parent columns in the RELATE clause form a key of the parent rowset, the relationship is one-to-one or one-to-many. If the parent columns do not form a key, it is a many-to-many or many-to-one relationship.

The following table illustrates the relationship possiblities.

The RELATE clause forms the same type relationships when relating a parent field to a child field or when relating a parent field to a command parameter. The following sections explain the similarities and differences.

Building Hierarchies Using Two Unrelated Rowsets

Once the connection is made and the SHAPE command invoked, the data is retrieved in its entirety as requested before any hierarchy building is attempted. The Data Shaping Provider creates an index on the related field in the child rowset, and a chapter column is added to the parent rowset. This index acts like a filter when child rows are accessed or when the application scrolls though the parent rows. The parent rowset is tied to the child rowset by this additional column. When the child rowset is accessed using the chapter handle, it contains only rows that match the current row in the parent. When another chapter handle is selected, the subset of child rows changes to reflect the correct relationship.

Using the following example of retrieving data from the Customers and Orders tables in the Sales database, the entire Customers table is loaded into a rowset, followed by the Orders table. Once retrieval is complete, an index is built that relates the child to the parent rowset based on a common field in the two tables. The chpOrders rowset contains all selected data from the Orders table that relates to each customer row.

SHAPE {select cust_id, cust_name from Customers}
   APPEND ({select * from Orders} 
   RELATE cust_id TO cust_id) 
      AS ChpOrders

This results in the following hierarchy:

This type of hierarchy offers the highest performance when navigating the results; however, the initial creation of the hierarchy can be quite time-consuming because the rows for all related rowsets must be fetched before the hierarchy can be created.