Shaped Recordsets

Learn to use the SHAPE statement to create hierarchical recordsets

By organizing data in SQL Server or another relational database, you can retrieve data in a variety of ways. One data-retrieval method that developers frequently use is hierarchical retrieval. For instance, suppose your database contains information about customers, orders, and products. You can retrieve data by extracting all the orders for a particular customer or product. Hierarchical retrieval is easy to do with SQL to a point. You can use SQL to create a recordset that lists customer and order information. However, this process becomes cumbersome when you try to work on several related recordsets. If you use a JOIN to link several tables, you can create one recordset that contains all the data, and you can use this recordset two or three times. This method works, but the recordset may not have all the data you need, or it may have too much data. If you are trying to create a hierarchy of data, such as customer and order information, SQL does not lend itself to performing this type of data manipulation efficiently or elegantly. Most developers create multiple recordsets to solve this problem. One recordset becomes the master, and the other becomes the detail. If you create a group of related recordsets containing the data you need, you can walk through the recordsets in your code and manipulate the data.

Microsoft introduced the hierarchical recordset in ADO 2.0 with the SHAPE command (included in the ADO Client Cursor Engine), which generates hierarchical recordsets. SHAPE groups commands together and returns a set of recordsets matching the commands. The SHAPE command uses the following syntax:

SHAPE {parent-command} [[AS] table-alias]APPEND {child-command}RELATE(parent-column TO child-column)

The SHAPE command in Listing 1 extracts two related recordsets: Customer and Orders. You can access each recordset directly in an application.

If you examine the first SHAPE statement in Listing 1, you can see the two SELECT statements that create the recordsets. The parent recordset is the first SELECT statement (Customer table). The APPEND statement adds the second recordset (Orders table) to the hierarchical recordset. Finally, the RELATE statement links the two tables as a JOIN statement does in SQL.

Visual Basic (VB) 6.0 supports hierarchical recordsets and adds GUI support with the Hierarchical Flexgrid. You can use the SHAPE command directly in ADO or with the Data Environment designer in VB. The Data Environment designer in Visual InterDev does not support hierarchical recordsets.

Creating Hierarchical Data Commands

Let's explore how to use hierarchical recordsets with the Data Environment. In the Northwind sample database create a new VB project, add a Data Environment to it (Projects, Add Data Environment), and name it envNorthwind on the Properties toolbar. Next, point the default connection to your SQL Server database, and name it Northwind.

Now, build the hierarchical recordset. First, add a data command to the Data Environment by clicking the Add Data command key. This data command generates the parent recordset in the hierarchy. The SQL statement for this data command is:

SELECT CompanyName, City, CustomerID FROM Customers

Name this data command Customer, and close its properties. Next, right-click Customer and select the Add Child command. The data-command properties for another command appear. Name this command Orders, and set its SQL statement to:

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate,ShipVia, Freight, ShipAddress, ShipName, ShipCity, ShipRegion, ShipPostalCode,ShipCountry FROM Orders

Next, click the Relation tab. This tab defines the hierarchical relationship between two recordsets. When you select the option to create the child command, the Data Environment automatically sets the Relate to a Parent Command Object check box. To relate existing commands, you can check this box and select the parent data command.

The Relation Definition section on the bottom of the property page controls the fields that link the recordsets. Screen 1 shows the tab with the settings for this example. The Parent Fields and Child Fields/Parameters lists match fields in the parent and child recordsets. These fields relate (join) the recordsets. After you add a new child recordset, you must set these properties. First, select the parent field. Then select the child field, and click Add to add the relation to the list. After you make these changes, click OK to close the properties and create the SHAPE statement.

Note that the fields you use to relate two recordsets must be in both recordsets. For instance, you can't relate on CustomerID if CustomerID is not in both recordsets. Also, if you change one of the recordsets in a relation, go back to the Relation tab for the child recordset and check the relationship parameters. If you delete a related field from one recordset, the Data Environment removes that field from the Relation properties and you must add it back.

The Data Environment in your project now appears like the one in Screen 2. You can see the hierarchy of the two recordsets and the fields in each.

To display the SHAPE statement, right-click the parent recordset in the hierarchy, and select Hierarchy Information to display the dialog box in Screen 3. If you need to copy the SHAPE statement, you can do so in the Hierarchy Information dialog box. This copy feature is handy when you need to create a SHAPE statement and use it directly in ADO. Use the Data Environment designer to create it, then copy it to the clipboard and paste it into your code.

Using the Recordsets

To gain the most control and flexibility, you can programmatically use the hierarchical recordset by accessing it directly in your application's code. Also, use the Hierarchical Flexgrid with the hierarchical recordset. The Hierarchical Flexgrid works with either an ADO recordset or a Data Environment hierarchical data command.

For example, suppose you want to bind the Hierarchical Flexgrid to your Data Environment hierarchical data command. First, right-click the Toolbox and select Components to add a form to your project. Next, add the Hierarchical Flexgrid to the Toolbox. Then, select the Hierarchical Flexgrid, as Screen 4 shows, and click OK.

Draw the grid on the form, and set the grid's properties to:

PropertyValueDataSourceenvNorthwindDataMemberCustomer

These two settings link the grid to the Data Environment command. The first property selects the Data Environment instance. The second property selects the data command. Note that you are using the parent command and not the child command. Next, right-click the grid, and select Retrieve Structure to display the headers for the data columns in design mode.

Now you can execute the application. The results should resemble the form in Screen 5, page 69. To hide and show the detail data, click the + or ­ in the first column. You can expand the use of your hierarchical recordsets to include more than one child command.

The SHAPE command supports other features, such as aggregate functions, to perform operations on the underlying recordset. The aggregate functions let you compute totals, sums, and other functions on data from a child command.

To use aggregate functions, add a new data command and name it ProductOrder. Set its connection to Northwind and its SQL statement to:

SELECT ProductID, ProductName FROM Products

Then add a child command and name it OrderProductDetails:

SELECT OrderID, ProductID, UnitPrice, Quantity 
FROM "Order Details"

Click the Relation tab, and relate OrderProductDetails to the ProductOrder command. Relate the two commands on the ProductID field. Close the properties for this command, and open the properties for ProductOrder.

Click the Aggregates tab, then click the Add key to add a new aggregate. Table 1 lists the available aggregate functions. Name it OrderQuantity. Select Sum as the aggregate function to use. Next, select OrderProductDetails in the Aggregate On list to link the aggregate operation to that child data command. The aggregate function can operate now on the child recordset. Select Quantity in the Field list to define the field the sum operation will act on. Close the Properties menu.

Right-click ProductOrder, and select Hierarchy Information. Listing 2 contains the resulting SHAPE statement. This SHAPE statement looks similar to the statement in Listing 1, except that Listing 2's SHAPE statement uses the SUM function to summarize the Quantity field. The aggregate functions operate on fields that are in the SHAPE statement. In this example, the SUM is passing OrderProductDetails .'Quantity'. OrderProductDetails is the alias name assigned to the child recordset in the SHAPE statement, and Quantity is a field in the OrderProductDetails recordset.

Occasionally, a hierarchical recordset might suddenly seem to stop working. This problem usually occurs when you have been in and out of a data command's property pages. I was puzzled when I ran into this problem, because I hadn't made changes to the recordsets. Out of curiosity, I displayed the Hierarchy Information dialog box for the parent recordset, and to my surprise, it contained nested SHAPE statements. Although you can use nested SHAPE statements, I was not trying to. The problem stemmed from my adding aggregate information to the recordset. To fix it, I deleted the child command and recreated it.

In building applications with VB 6.0, the developers at my company have found occasions when the Data Environment doesn't seem to work correctly. For example, it's easy to programmatically walk through hierarchical recordsets using ADO but not with the Data Environment. So, they now test the Data Environment with complex SQL and SHAPE statements that match the application before building a complex application that is tied to the Data Environment.

The Data Environment designer is quite helpful for building queries, even queries to use with ADO. So use the Data Environment to build powerful SHAPE statements, then place the SHAPE statement in your ADO code and execute it.

In future columns, I will explore more uses of the Data Environment designer. Specifically, I will explore how to use the designer with VB, ADO, MTS, and other services.