Hierarchical Recordsets in CML/LitCrit

The use of hierarchical recordsets (data shaping) is a feature introduced in ADO 2.0 that allows you to build shaped recordsets. This type of recordset is very much like the master-detail relationship between tables. The title and item tables in the FmLib database are an example of a master-detail relationship. The master (title) has a one-to-many relationship with the detail (item). Windows® Explorer is another example that shows hierarchy. You can think of the directories as a parent recordset, the subdirectories as the links to the child recordsets, and the files as the fields within parent and child recordsets. The following diagram shows what the hierarchical recordset from the AdvancedSearch method looks like.

Before hierarchical recordsets were available, you had two options when accessing multiple tables:

• Use multiple recordsets. This often keeps the SQL more legible but depending on the results you need, multiple recordsets can be unwieldy to work with.
• Perform an SQL join on multiple tables. This SQL was often complex to write and difficult to understand, and joins carry inherent overhead.

Now, hierarchical recordsets can represent information more efficiently. Data shaping allows a column in the parent recordset to contain a child recordset (or chapter) of its own. You can append multiple child recordsets to each parent recordset and the child recordsets can in turn have child recordsets. The shaped recordset used in the AdvancedSearch method has two child recordsets: authors and critique.

Advantages of using hierarchical recordsets:

• You can reduce the number of records transported across the network. In the case of the hierarchical recordset used in the AdvancedSearch method, the parent record (selected from the title table) is only included once, instead of being duplicated for every author record.
• You can create summary information with shapes. For example, the parent recordset can contain summary information on titles, such as the average price by media type, and a child recordset can contain the details about each title.
• You can use hierarchical recordsets with stored procedures. You can improve performance in your application by calling stored procedures in your SHAPE commands. The CML/LitCrit application does not call stored procedures from SHAPE commands.
• Hierarchical recordsets allow you to write queries that are easier to understand and debug.