How the Data Shape Provider Works

[Previous] [Next]

When you submit a query on a connection that loads the data shape provider, the provider parses the query string to determine whether that string includes keywords that the provider uses to build hierarchical recordsets. If no such keywords are used, the data shape provider passes the query string to the OLE DB provider you've specified as the data provider.

Parsing a Hierarchical Query String

If the data shape provider finds keywords, it breaks the query string into distinct queries, passes those queries along to the data provider, processes the results, and organizes the data into the hierarchy you requested. For example, let's take a look at the query string used earlier in the chapter:

 strSQL = "SHAPE {SELECT * FROM Customers} AS Customers " & _ "APPEND ({SELECT * FROM Orders} AS Orders " & _ "RELATE CustomerID TO CustomerID) AS Orders" 

This query is composed of two simple query strings that retrieve the contents of two separate tables (Customers and Orders), and it specifies how to relate the results of the two queries. In this case, you're asking the data shape provider to relate the two queries based on the contents of the CustomerID field for each query. Thus, only the records from the Orders table that have the same CustomerID value as the current record returned by the Customers query will be visible.

The SHAPE keyword invokes the data shape provider's logic. The APPEND keyword tells the data shape provider to add a field to the initial recordset. With this added field, the orders for each customer appear to be contained in a Field object in the resulting Recordset object. The following code prints all orders for each customer:

 rsCustomers.Open strSQL, cnNorthwind, adOpenStatic, _ adLockBatchOptimistic, adCmdText Set rsOrders = rsCustomers.Fields("Orders").Value Do While Not rsCustomers.EOF Debug.Print rsCustomers!CustomerID & " _ " & rsCustomers!CompanyName Do While Not rsOrders.EOF Debug.Print vbTab & rsOrders!OrderID & " _ " & rsOrders!OrderDate rsOrders.MoveNext Loop rsCustomers.MoveNext Loop 

Each time you move to the next record in the rsCustomers Recordset, only those orders for the current customer are visible in the rsOrders Recordset. While the query syntax is rather complicated, examining the data in the hierarchical Recordset is fairly simple. In this example, I refer to the customer data as the "parent data" and the orders data as the "child data."

Using Grid Controls with Hierarchical Recordsets

Microsoft Visual Basic 6 includes a control designed to display the results of hierarchical queries: MSHFlexGrid, which is the Microsoft Hierarchical FlexGrid Control 6.0 (OLEDB). When you use this control to display the contents of a hierarchical Recordset, the parent data resembles the results of a standard query and is displayed in a simple grid, similar to a Microsoft Excel spreadsheet. The child data is displayed in an additional column or columns in the grid. In this example, the orders for each customer appear as a grid within a grid in a kind of tree structure as shown in Figure 14-1. You can use the symbol to the left of the customer information to expand or hide the order information.

Figure 14-1 A hierarchical recordset in the Hierarchical FlexGrid Control.

While this method of envisioning the hierarchy makes sense and is how you want to display the data, it's not how ADO stores the data. Let's take a closer look at the two main types of hierarchies and how ADO maintains the data in each case.