Creating Custom Hierarchical Recordsets

Before you Begin this Article...

This article covers two advanced ADO topics: creating hierarchical Recordsets (via data shaping) and creating custom Recordsets. Specifically, this article looks at how to create custom hierarchical Recordsets. Therefore, it is essential that the reader have a fluid understanding of both data shaping and custom Recordsets, topics that this article doesn't aim at teaching. To learn about these topics, be sure to read the following articles: - continued - Data Shaping Using Custom Recordsets

Introduction to Data Shaping
This article examines how to create custom hierarchical Recordsets. Starting in ADO 2.1, Microsoft introduced the concept of "data shaping," which is the process of reshaping data. The typical Recordset object can be thought of as a two-dimensional matrix, with the columns explaining the attributes of a piece of data, and each row representing a distinct piece of data. While this abstration works well when dealing with scalar data, it quickly falls apart when working with relational data.

For example, if you wish to represent an Employee in a data model, you would create a table named, perhaps, tblEmployee, with a column for each descriptive attribute of the employee: FirstName, LastName, BirthDate, Title, etc. While this scalar information could be easily stored in a conventional Recordset, imagine that you wanted to also store a list of projects each Employee was working on (assuming that there was a table containing information on each project, tblProject). Clearly, a Recordset, in its classical form, could not convey such information. To accomodate this, you would have to use a many-to-many table, linking multiple employees to multiple projects.

However, with data shaping, you could simply "reshape" the Recordset into a hierarchical Recordset, one that is capable of conveying a list of projects for each separate employee. In essence, reshaping envolves making one (or more) of the columns in a Recordset a Recordset itself (Recordsets of Recordsets, if you will). I understand this may sound confusing, and it is at first. Rather than delve into the specifics of data shaping, I will refer you to two previous 4Guys articles: Data Shaping and Advanced Data Shaping Techniques. The first article provides a good introduction to data shaping and should be thoroughly read and understood before tackling this article.

Introduction to Custom Recordsets
When reading database information into a Recordset, the Field objects in the Recordset's Fields collection are determined by the data being read into the Recordset. (Each Field object in the Recordset object represents a column describing the database data. The Fields collection contains all of these Field objects for the Recordset.) That is, if you Open a Recordset object by performing the following SQL query:

SELECT EmployeeID, LastName, FirstName FROM tblEmployee

the Recordset will have three Field objects in its Fields collection, with the names EmployeeID, LastName, and FirstName. These Field objects will contain information on what type of data is expected as well as other properties, such as if the columns are NULLable, if they are AutoNumber fields, etc.

Since the structure of a Recordset object can be determined on the fly, it would make sense to assume that we could craft our own Recordsets. Well, we can. Such hand-crafted Recordsets are referred to as custom Recordsets. Custom Recordsets are useful since they can store a richer set of data than other collection-type objects (such as the Scripting.Dictionary object). They become really useful when used in application-scope; that is, when a single custom Recordset is created in Global.asa to perform some data collection or reporting task.

There are a number of articles on 4Guys that illustrate how to create and use custom Recordsets, so I am not going to go into detail on how to create or use these nifty custom Recordsets. Rather, be sure to read Using Custom Recordsets for more information.

Creating Custom Hierarchical Recordsets
Since ADO can support both data shaping and custom Recordsets, it would follow that ADO is capable of allowing developers to create custom hierarchical Recordsets. As with creating regular custom Recordsets, the true power of creating custom hierarchical Recordsets comes when you create an application-wide hierarchical Recordset. In this article we won't examine any real-world applications, I leave that to your imagination (if you come up with some nifty ideas, be sure to let me know!). Rather, in this article, we will focus on the syntax and semantics for creating such custom Recordsets. (There is a live demo accompanying this article that you can try out...)

We must begin by creating and opening a Connection object that uses the MSDataShape provider. The MSDataShape provider is a special provider required when using data shaping (i.e, when creating hierarchical Recordsets). Note that we specify the Data Provider setting as NONE; since we are creating a custom hierarchical Recordset, we want to tell ADO not to worry about tying this Connection object to a specific data provider:

Dim strConnString, objConn 'We want the MSDataShape provider and no Data Provider strConnString = "Data Provider=NONE; Provider=MSDataShape" objConn.Open strConnString 'Open the connection

Next, we need to create our custom hierarchical Recordset. When working with hierarchical Recordsets you can think of the data in terms of parent-child relationships. Touching back on our earlier example, we will create a custom hierarchical Recordset with Employee information, the hierarchical shape coming in listing each employee's projects. Our parent data, then, is the individual employee's information; the child data is the list of projects for the current employee.

In Part 2 we'll look at the specific syntax required to create custom hierarchical Recordsets.