There are a couple of reasons why this is useful:

• Performance: When used correctly, data shaping can improve performance.
• Convenience: It's extremely easy to map the parent/child relationship in data shaping.

The easiest way to see what data shaping involves is to look at a diagram:

This shows a hierarchy from the pubs databases, showing Publishers, Titles and Sales.

One important point to note is that each child recordset is not a separate recordset on its own. So, in the above diagram there are not six recordsets, but only three. How come? Well, there is a recordset for each level in the hierarchy - which is Publishers, Titles and Sales. When you reference the Titles for a Publisher, you are actually referencing the Titles recordset, but ADO filters the Titles, so only those applicable to the selected Publisher are shown. This gives the impression that you have a separate recordset for each child element.

Using Data Shaping

To use data shaping you have to use the following:

• The MSDataShape OLEDB Provider
• A special Shape language, a superset of SQL, that allows you to construct the hierarchies

The connection string doesn't actually change that much, even though you are using a new provider. This is because you still need to get the data from somewhere. So what you do is this:

Provider=MSDataShape; Data Provider=SQLOLEDB; Data Source = ...

You use the MSDataShape as the Provider, and your normal provider becomes the Data Provider, with the rest of the connection string remaining the same.

The easy way to construct a connection string for data shaping is to start with your normal connection string, and append it to the end of the data shaping bits. For example, consider the following, normal connection string:

strConn = "Provider=SQLOLEDB; Data Source=Kanga; " & _
     " Initial Catalog=pubs; User Id=sa; Password="

You can create a connection string for the data shape provider like so:

strConn = "Provider=MSDataShape; Data" & strConn

This sets the provider to be MSDataShape, and the Data Provider becomes the real source of the data. The original connections string already has the Provider= bits in it, so we only need to put Data in front of this to get the correct connection details.

The Shape Language

The shape language has its own grammar, but we won't go into the formal construction of it here - it's included in the ADO documentation. In most situations you'll be using the following command:

SHAPE {parent command} [AS parent alias]
APPEND ({child command} [AS child alias]
RELATE parent_column TO child_column) [AS parent_column_name]

The easiest way to understand this is to see an actual example, so we'll start with just Publishers and Titles:

SHAPE {SELECT * FROM Publishers}
APPEND ({SELECT * FROM Titles}
RELATE Pub_ID TO Pub_ID) AS rsTitles

The parent recordset is the first line, and the child recordset the second line. The third line indicates the two fields that provide the relationship between the parent and child. In this case both tables have a field called Pub_ID (the Publisher ID field). This command returns a recordset containing the publishers, and onto the end of that recordset it APPENDs a new column (of type adChapter), which contains the child recordset. The name of this column is given by the AS clause - in this case it will be rsTitles.

The type adChapter just indicates that the field contains a child recordset. Personally I think adChild or adRecordset would have been better.

You can easily see what the fields of the parent recordset look like, by just looping through the Fields collection. Using the SHAPE command above, we get:

Continued...