Opening a Shaped Recordset

A useful feature of ADO is that you can create shaped recordsets. Data shaping enables you to define the columns of a recordset, the relationships between them, and the manner in which the recordset is populated with data. The columns can contain data from a provider such as Access or the SQL Server, references to another recordset, values derived from a calculation on a row, and so on.

Here's a simple example from the NorthwindCS sample database (in the Chapter 19 code download), which has two tables with a parent-child relationship: Orders, which contains the header information for customer orders, and Order Details, which contains the individual line items for each order.

In the past, to populate a list with the details of a select set of orders, you would have created a recordset based on a query much like the following:

SELECT O.OrderlD, O.CustomerlD, O.OrderDate,

D.ProductID, D.UnitPrice, D.Quantity,

D.Discount

FROM Orders As O

INNER JOIN [Order Details] As D ON D.OrderlD

= O.OrderlD

WHERE Year(O.OrderDate) = 1996

AND OrderID BETWEEN 10248 AND 10250

ORDER BY O.OrderDate DESC

This query returns a dataset that contains all the orders in 1996 where the OrderlD is between 10248 and 10250, in descending date order. In this case, the columns from the Orders table (OrderlD, Customer, and OrderDate) are repeated unnecessarily for every row of data returned from the Order Details table. Wouldn't it be nice if you could return only one row of Orders data for each group of related rows from Order Details? Closely examine the following ADO code, paying particular attention to the SQL statement:

Dim cn As New ADODB.Connection Dim rsOrders As New ADODB.Recordset Dim rsDetails As New ADODB.Recordset Dim strSQL As String

' Define and create the connection cn.CursorLocation = adUseClient

' We have to use this provider cn.Provider = "MSDataShape"

' Open a connection to SQL Server cn.Open "Data Provider=SQLOLEDB;" & _

"Integrated Security=SSPI;Database=NorthwindCS"

' Create the SQL statement that does all the work strSQL = "SHAPE {SELECT DISTINCT OrderlD, " & _

"CustomerlD, OrderDate FROM Orders " & _ "WHERE Year(OrderDate) = 1996 " & _ "AND OrderID BETWEEN 10248 AND 10250 " & _ "ORDER BY OrderDate DESC} " &_ "APPEND ({SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " &

"FROM [Order Details]} " &_ "RELATE OrderID TO OrderID)"

' Create the recordset for the orders table rsOrders.Open strSQL, cn

Shaped recordsets such as this are called hierarchical recordsets. They exhibit a parent-child relationship in which the parent is the container recordset and the child is the contained recordset. The Shape statement enables you to create a shaped recordset, which you can then access programmatically or through a visual control. You issue the Shape statement as you do any other ADO command text.

This simple example demonstrates only a fraction of what can be accomplished using the Shape statement. Although an in-depth examination of SQL Shapes is beyond the scope of this book, the Access help has several informative topics on the subject.

0 0

Post a comment