SQL Server Magazine

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.

   Prev. page   [1] 2     next page