Hierarchical Rowsets

OLE DB Programmer's Reference

A hierarchical rowset is a collection of rowsets linked together by using chapters. Hierarchies model 1:n relationships between tables, such as a master-detail relationship between Customers, Orders, and Items. A hierarchical rowset can be stored persistently in a data source object or computed by a command. This section discusses only computed hierarchies.

Chapters are identifiers of groups of details within a rowset. Chapters work as distinct collections with a beginning and an end but also share the facilities, such as accessors and notifications, of the rowset to which they belong.

The following example illustrates how a computed hierarchy is generated. The database schema contains the following three tables:

CREATE TABLE customer_table          ( cust_no      INTEGER,          cust_name      VARCHAR(20),          city           VARCHAR(20),          PRIMARY KEY (cust_no) ) CREATE TABLE order_table          ( cust_no      INTEGER,          ord_no         INTEGER,          ord_date       DATETIME,          total          DECIMAL(9,2),          PRIMARY KEY (ord_no),          FOREIGN KEY (cust_no) REFERENCES customer_table ) CREATE TABLE order_item_table          ( ord_no       INTEGER,          item_no        INTEGER,          description    VARCHAR(30),          quantity       SMALLINT,          unit_price     DECIMAL(6,2),          PRIMARY KEY (ord_no, item_no),          FOREIGN KEY (ord_no) REFERENCES order_table )

The following extended SQL command generates a rowset hierarchy. The syntax of this query is used only for illustration. Providers may use other syntaxes to generate hierarchy.

SELECT (cust_no,          cust_name,          city,          SELECT (ord_no,                ord_date,                total,                SELECT (item_no,                         description,                         quantity,                         unit_price,                         (quantity * unit_price) AS ext_price                         )                   FROM order_item_table                   WHERE order_item_table.ord_no = order_table.ord_no                   AS item_rows                   )             FROM order_table             WHERE order_table.cust_no = customer_table.cust_no             AS order_rows          )    FROM customer_table

1998-2001 Microsoft Corporation. All rights reserved.