dcsimg

Shaping Data with ADO

By ServerWatch Staff (Send Email)
Posted Feb 7, 2001


by David Faour Master-detail data relationships are a fact of life for a VB-Database developer. Common examples are authors to books written, students to courses, and customers to orders. When you want to get all of the master-detail data, there are two traditional ways of completing the task.

Master-detail data relationships are a fact of life for a VB-Database developer. Common examples are authors to books written, students to courses, and customers to orders. When you want to get all of the master-detail data, there are two traditional ways of completing the task.

Select MasterTable.fieldlist, DetailTable.fieldlist from 
MasterTable join DetailTable on
MasterTable.PrimaryKey=DetailTable.ForeignKeyFromMaster where
[list of conditions]

Alternatively

Select MasterTable.fieldlist from MasterTable where [list of conditions]

Followed by a loop through each master record

Do until MasterRecordset.eof

DetailSQL= Select DetailTable.fieldlist from DetailTable
where DetailTable.ForeignKeyFromMaster=MasterRecordset!PrimaryKey
and [list of remaining conditions]

DetailRecordset.Open DetailSQL, Connection,
cursortype,locktype
MasterRecordset.movenext
Loop

Each of these methods has its flaws. The first method, using a join, may result in a tremendous amount of duplicated data. It will return in each row all of the Master data and then the associated detail data. Memory is wasted in holding this repetitive data.

The second method requires a trip to the database for the initial MasterRecordset, plus one trip for each record in the master to obtain the detail data. Network bandwidth and system speed are sacrificed as a result.

What's a VB-Database developer to do? Shaped recordsets are an alternative. Beginning with ADO 2.0, this method is available. Shaped recordsets allow the developer to retrieve data in a hierarchical fashion. The shaped recordset adds a special "field" that is actually a recordset unto itself. Let's look at the customers-orders example. Suppose you want data on each customer and on the orders made by each customer.

Your query will resemble the following:

SHAPE {SELECT fieldlist FROM Customers} AS Customers 
APPEND ({SELECT fieldlist FROM Orders}
RELATE customer_id TO customer_id) AS Orders

What you get is a recordset that contains the fieldlist you are interested in from Customers with a special field appended to the end. The appended field is actually a recordset unto itself.

This method allows you to eliminate redundant data that would be returned in a join style query, and also does not require multiple trips to the database. You can get the best of both worlds.

What will you need to do this? Well, you must be using
            ADO 2.0 or higher, VB6, and you must specify the shaping provider in
            your connection string.
            

Adding Provider=MSDataShape; to the beginning of your connection string should do the trick.

Shaped recordsets are not a silver bullet, but just another option to consider. In forthcoming articles I intend to discuss the situational appropriateness of their use.

Page 1 of 1


Comment and Contribute

Your name/nickname

Your email

(Maximum characters: 1200). You have characters left.