Creating Custom Hierarchical RecordsetsBy Scott Mitchell
|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:|
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
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
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
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
object in the Recordset object represents a column describing the database data. The
contains all of these
Field objects for the Recordset.) That is, if you Open a Recordset
object by performing the following SQL query:
the Recordset will have three
Field objects in its
Fields collection, with the names
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:
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.