This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

October 2000

Create a Single Parent/Child Recordset with ADO's Data Shapes

by Asli Bilgin

Since its introduction in ADO 2.0, data shaping has remained largely on the fringes of Visual Basic lore. Relegated to the back pages of musty manuals, you may have overlooked this useful aspect of ADO. If you're not familiar with data shaping, in essence, it lets you create recordsets within recordsets--or parent/child relationships--all with a single ADO object. This means no messy joins, no complicated filtering, and no need for spaghetti code in presentation logic. Data shaping reduces the amount of traffic crossing a network, provides more flexibility when using aggregate functions, and reduces overhead when interfacing with leading-edge tools like XML.

In this article, we'll explain how you can use hierarchical recordsets and point out their advantages. We'll provide concrete examples of ADO's hierarchical data engine and use it to quickly fill a treeview control, as seen in Figure A. In addition, we'll give you tips and tricks you can use with data shaping. Finally, we'll point out promising possibilities for using the more advanced features of hierarchical recordsets.

Figure A: Hierarchical recordsets reduce network traffic and remove the need for custom linking code between parent/child records.

The data shape structure

The term data shaping is somewhat of a misnomer--you don't actually have free reign to shape the ADO data as you please. It must remain within a structured format of parent/child relationships. If you've worked with a treeview before, you're no doubt familiar with this concept. As long as you adhere to the parent/child context, there's no limit to the hierarchical shape of your recordset.

So, what benefit do you gain from this arcane ADO structure? Why not simply combine everything with a single join? Or perhaps create two separate recordsets, one with parent information, and the other with child information. Well, hierarchical recordsets let you treat parent and child data as separate recordsets, within a single object. However, hierarchical recordsets maintain the relationship between the parent and child records. This structure frees you from having to create presentation logic to combine the data, since the system maintains the hierarchy, not the developer.

What good does it do?

In order to understand the benefits of using hierarchical recordsets, let's compare it to the alternatives. Consider a scenario where you need to determine annual employee bonuses. To gauge employee performance, you want to look at how many product orders each employee has generated. We'll use the Northwind database, which ships with SQL Server 7.0. To view these measurements, we need to work with three tables, Employees, Orders and Order Details, which represent a natural hierarchy. The Employees table exists as a parent at the hierarchy's highest level; the Orders table exists as a child; while the Order Details table is the grandchild, at the lowest end of the hierarchy. If you want to show all order detail records for each employee, you need to relate three tables together. To do so before ADO 2.0, you had two choices:

• Use joins. Join all three tables into a single recordset using back-end SQL code.
• Use independent recordsets. Create two separate recordsets and relate them together with complicated presentation logic.

If you've ever tried, it's not much fun to code multiple joins, nor does it look very elegant in its flat structure. In addition, you end up with a lot of redundant data crossing the network--extra data you don't need. Consider the scenario mentioned previously. If you created a single, joined recordset for the order details for each customer, you'd end up with a recordset that looked something like Table A.

Table A: Joining parent/child records

Employee.Name	Order.ShipName	Order.OrderID	Order Detail.Quantity
Laura Callahan	Hanari Carnes	10250	35
Laura Callahan	Hanari Carnes	10250	10
Laura Callahan	Hanari Carnes	10250	15
Nancy Davolio	La maison d'Asie	10350	15
Nancy Davolio	La maison d'Asie	10350	18

Look at all the redundant data! With just two separate orders, the code has brought back three times the number of rows in a flat, hard-to-read structure. With data shaping, you can get these results, without the redundancy, in an easy-to-read, treeview-like structure.

Using two separate recordsets

As another alternative, you can extract the same data in two different recordsets, one containing employee orders, and the other the order details. Then, you can create a front end that combines the two recordsets, perhaps in a click-here-for-more-detail style, but that's still not ideal. Using hierarchical recordsets, you can pump your data into a treeview, a grid or XML format. All are a natural fit for the hierarchical structure. So how do hierarchical recordsets eliminate the problems of repeating data, large recordset sizes and necessitating the use of coding filters? First, this structure doesn't contain repeating data because it embeds child recordsets within the parent recordset. This has the added benefit of reducing recordset size. There's no need to use two separate recordsets in lieu of a SQL Join. Thus, you won't need to code filters, since the data shape doesn't contain repeating data or separate recordsets to relate. The shape automatically pre-filters the data for you.

Say goodbye to GROUP BY

Hierarchical recordsets provide other benefits. They can serve as an alternative to the GROUP BY SQL clause. With the GROUP BY clause, you would only get aggregate values in the recordset. With hierarchical recordsets, you can return aggregate data within the parent recordset, while still maintaining detailed records within the embedded child recordset. Conceptually, you'll probably find it more natural to understand the hierarchy in shaped recordsets than the flat, single recordset returned from joins. This makes it easier to implement parent/child data in controls such as treeviews and hierarchical grids compatible of displaying parent/child relationships.

Hierarchical recordsets provide another advantage over joins. Recordsets created from a SQL JOIN clause present a static, read-only view of data. Since hierarchical recordsets actually mimic the table structure in the data source, you can edit them. As a result, you can perform standard CRUD functions on hierarchical recordsets. The disadvantage is that you can't use disconnected recordsets with data-shaped objects, since ADO needs a persistent connection to the data source to create them.

Putting principle into practice

Now that we've explained all the great benefits of hierarchical recordsets, let's see how to create them. To do so, you use a standard SQL statement, along with three major keywords: SHAPE, APPEND and RELATE. The SHAPE keyword specifies and defines the parent recordset. This recordset provides the base recordset or the parent. This phrase uses the following syntax:

SHAPE {parent-sql-statement} 
[AS parent-recordset-alias] 

So, to extract all the employee records from a table as a parent recordset, we'd use the following SQL statement:

SHAPE {SELECT employeeID,FirstName, 
LastName FROM Employees}

Once you create a parent object, you next append the child recordset. The APPEND clause uses a similar syntax as SHAPE and contains the necessary SQL statement to create the child recordset within the parent. Continuing with our example, to add the orders to the object, you'd add this phrase:

APPEND ({SELECT OrderID, shipname, employeeid 
    FROM orders 
    WHERE shipname ='QUICK-Stop'} 
    AS oRSOrder

You probably noticed that we placed an opening parenthesis in this clause but didn't close it at the end. That's because in addition to the child recordset's SQL statement, you must also indicate how you want the two recordsets to relate. The RELATE clause uses a syntax like this:

RELATE parent-column to child-column) 
[AS chapter-alias]

The RELATE keyword works like a join, relating similar columns to one another. However, it doesn't join the records in the traditional sense. Instead, it simply provides the link between recordsets. Altogether, the completed SQL statement would look like this:

SHAPE {SELECT employeeid,FirstName, LastName 
FROM employees} 
    APPEND ({SELECT OrderID, shipname, employeeid 
    FROM orders WHERE shipname ='QUICK-Stop'} 
    AS oRSOrder 
    RELATE EmployeeID TO EmployeeID);
        

Filling a treeview with ease

For our example, we'll use a data-shaped recordset to fill a treeview control with employee order information. While we'll use SQL Server for the back end, you can use any OLE DB provider. To begin, open a standard Visual Basic project. Next, select Project | Components from the menu bar and choose the Microsoft Windows Common Controls group. Click OK to add the treeview control to IDE's toolbar. Now, drop a treeview control onto the project's default form. Right-click on the control and select Properties from the shortcut menu.

When Visual Basic opens the Properties dialog box, set the LineStyle property to 1-tvwRootLines. Click OK to apply the setting. At this point, the form should look similar to Figure B. Now, select Project | References from the menu bar, and add a reference to the Microsoft ActiveX Data Objects 2.0 Library (or 2.1). Click OK to dismiss the dialog box. Finally, right-click on the form and select View Code from the shortcut menu. Listing A on the next page shows the code we created to fill the treeview. Enter it into the form's Load() event.

Listing A: Filling the treeview

Private Sub Form_Load()
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim oRSChild As ADODB.Recordset
Dim oparNode As Node

Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")

oConn.Provider = "MSDataShape"
oConn.Open "Data Provider=SQLOLEDB; Data Source=ElementKJ;" _
    & "Initial Catalog=Northwind;User Id=sa;Password=;"

oRS.Open "SHAPE {SELECT employeeid,FirstName, " _
    & "LastName FROM employees} " _
    & "APPEND ({SELECT OrderID, shipname, employeeid " _
    & "FROM orders WHERE shipname ='QUICK-Stop'} " _
    & "AS oRSOrder " _
    & "RELATE EmployeeID TO EmployeeID)", oConn

Do While Not oRS.EOF
    Set oparNode = TreeView1.Nodes.Add(Text:=oRS.Fields(0) _
        & " " & oRS.Fields(1) & " " & oRS.Fields(2))
    Set oRSChild = oRS.Fields("oRSOrder").Value
    Do While Not oRSChild.EOF
        TreeView1.Nodes.Add relative:=oparNode.Index, _
            relationship:=tvwChild, Text:=oRSChild.Fields(0) _
            & "   " & oRSChild.Fields(1)
        oRSChild.MoveNext
    Loop
    oRS.MoveNext
Loop

oRS.Close
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
End Sub

Figure B: We'll use ADO's hierarchical recordset to fill the treeview control.

To see the code in action, press [F5]. When Visual Basic launches the project, it first creates the necessary ADO objects. Notice that in order to take advantage of ADO data-shapes, you must set the Connection object's provider equal to MSDataShape, as we did in this line:

oConn.Provider = "MSDataShape"

The MSSDataShape setting tells ADO to use the Hierarchical Cursor engine to interpret the SQL. Once the code creates the Connection, it generates the hierarchical recordset. The procedure uses the same SQL syntax we discussed earlier, along with a WHERE clause to limit the number of records. Next, the code begins to loop through the parent recordset.

As the loop's second statement, it creates a recordset object variable based on the value in the oRSOrder field. Looking back through the SQL statement in Listing A, you can see that oRSOrder is an alias for the child recordset. To return the object contained therein, you extract the field's value, like so:

Set oRSChild = oRS.Fields("oRSOrder").Value

At this point in the procedure, oRSChild stands for the appended child recordset of orders. And it only contains the related child records, no more. From here, it's a simple matter to fill the treeview control. The loop determines when to stop advancing through the records. Figure A on the cover, shows the completed results.

Some data shaping tips

If you don't look forward to typing out lengthy SQL statements, try using the Data Environment Designer (DED) in VB 6.0 to create the hierarchical recordset. The designer automatically generates the shape syntax for you. In addition, the DED will display a hierarchical recordset in a convenient property sheet.

Table names and the RELATE clause

If you noticed, you don't have to specify table names in the column name portion of the RELATE clause. Just remember that the parent table's column name goes first, followed by the child table's column name.

What's your data type?

Often you may need to test a recordset field to determine if it contains another recordset. The data type of a hierarchical recordset is adChapter. With this in mind, you could create a conditional statement like so:

If oRSEmployees.Fields("FieldName").Type _
    = adChapter Then _

Don't forget the Value property

We mentioned it previously, but it's worth repeating. In order to access the embedded recordset from a field, you must explicitly use the Value property, as shown in the following code:

Set oRSOrders = oRSEmployees.Fields _
    ("oRSOrders").Value

In traditional fields, you never have to indicate the Value property, since it's the Field object's default member. However, you must do so with hierarchical recordsets; otherwise, you won't have access to the child objects contained in the field.

Adding aliases

Consider using aliases for child recordsets. You're bound to refer to the child recordsets later on in your code, and aliases provide easier readability. Take a careful look at the SQL syntax we provide to see where to place the alias names accordingly.

Advanced ideas

Data shaping provides advanced functionality that you'll no doubt find useful. We won't go into too much more detail on implementation specifics. However, we would like to expose you to some high-level features. For instance, ADO data shapes allow multiple nested recordsets within a recordset, which means that multiple recordset columns can contain additional recordsets. You can also store recordsets within recordsets within recordsets, ad infinitum... well, at least until your memory runs out. Also, consider creating a blank hierarchical recordset in memory, rather than using a multi-dimensional array. It's much easier to parse a recordset than a complex array.

Microsoft specifies three kinds of data shapes. In this article, we've mainly discussed relation-based shapes. ADO contains two other shapes, however--parameter-based and group-based. The parameter-based shape increases performance, because it doesn't return any child recordsets until a procedure actually needs them. Of course, this process does require more trips across the network and a persistent connection, but it's useful if you need the initial data to get across the network fast. The second shape, group based, allows you to use aggregation on the children recordsets with such SQL clauses as COUNT, SUM and AVG.

Copyright © 2000 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.