INFO: Using Disconnected Hierarchical Recordsets

Hierarchical recordsets can be used as an alternative to JOIN and GROUP BY syntax when you need to access parent-child and summary data. These recordsets can be created by using the SHAPE provider, MSDataShape. Many applications now use a 3-tier architecture and might find it useful to create these recordsets, disconnect them from their data source, and then pass them back to the client. This article discusses what is necessary to pass disconnected hierarchical recordsets out-of-process.

This article assumes that you are already familiar with 3-tiered applications, hierarchical recordsets, and passing ADO recordsets as parameters to and from functions. For more information on these topics, please see the following articles on the Microsoft Knowledge Base: Working with recordsets based on SHAPE queries in 3-tier applications is similar to working with regular recordsets. However, there are some differences. Below are two of these differences that are specific to working with disconnect hierarchical recordsets:

1. Recordsets based on parameterized SHAPE queries cannot be passed out-of-process. Here is an example of a parameterized SHAPE query:

      SHAPE {SELECT * FROM Employees WHERE LastName='Davolio'}
      APPEND ({SELECT * FROM Orders WHERE EmployeeID = ?} AS EmpOrders
         RELATE EmployeeID TO PARAMETER 0)
   						

   The reason these recordsets cannot be passed out-of-process is that parameterized SHAPE commands do not retrieve all the rows at creation-time. The child records are fetched on demand from the data source as the user moves through the parent records. If these recordsets were marshalled out-of-process there would not be enough information marshalled for the child records to be retrieved.
2. When sending hierarchical recordsets back to the server for updating, you need to manually loop through all of the child recordsets and issue an UpdateBatch for each one. While looping through the child recordsets, be sure to set the ActiveConnection property of each of the children recodsets to a valid connection object before issuing the UpdateBatch method. If you fail to set the ActiveConnection to a valid connection before issuing the UpdateBatch, no updates are sent to the database.
   
   Below is an example of a function that can be used to update hierarchical recordsets on the server side. This function accepts a hierarchical recordset as an incoming parameter and uses recursive calls to loop through all of the child recordsets and send the updates to the server.

Steps to Reproduce Behavior

1. Start Visual Basic.
2. Choose the default project type.
3. Set a project reference to the Microsoft ActiveX Data Objects 2.x Library.
4. Place a CommandButton on the form.
5. Paste the following code into the Code Window:
   
   Note You must change Server=<ServerName>, Uid=<username>, and Pwd=<strong password> to the appropriate server name, username, and password before you run this code. Make sure that the changed uid has the appropriate permissions to perform the required operations on the specified database.

   Dim m_cn As ADODB.Connection
   
   Private Sub Command1_Click()
           
           Dim cnn As New ADODB.Connection
           Dim rst As New ADODB.Recordset
           Dim rsChapter As Recordset
                   
           cnn.CursorLocation = adUseClient
           cnn.Open "Provider=MSDataShape;Data Provider=sqloledb;" _
            & "Server=<ServerName>;uid=<username>;pwd=<strong password>;Database=pubs;"
           rst.Open "SHAPE  {select * from authors}APPEND " & _ 
           "({select * from titleauthor} AS chapter RELATE au_id TO au_id)", _ 
              cnn, adOpenStatic, adLockBatchOptimistic
           Set rst.ActiveConnection = Nothing
           rst.MoveFirst
           rst("au_lname") = "Smith"
           rst.Update
                   
           If (UpdateBatchX_ALLChildren("Provider=MSDataShape; " _ 
              & "Data Provider=sqloledb;Server=<ServerName>;" _ 
              & "uid=<username>;pwd=<strong password>;Database=pubs;", rst) <> 0) Then _
   
               Debug.Print Err.Number & " -- " & Err.Description
               Exit Sub
           End If
             
   End Sub
   Public Function UpdateBatchX_ALLChildren(ByVal szConnect As String,  _ 
        ByVal rs As ADODB.Recordset) As Long
   
       'On Error GoTo errhandler
       
       Dim rsChild As ADODB.Recordset
       Dim i As Integer
       
       'Connect to the database .. m_cn is a module level ADODB connection
       if Not (szConnect = "") Then
           Set m_cn = CreateObject("ADODB.Connection")
           With m_cn
               .ConnectionString = szConnect
               .ConnectionTimeout = 15
               .CursorLocation = adUseServer
               .Open
               
           End With
       End If
           
       'Update each of the child recordsets ... call recursively to
       'handle all of the children
       With rs
           .ActiveConnection = m_cn
           .UpdateBatch adAffectAllChapters
           For i = 0 To rs.Fields.Count - 1
               If (rs.Fields(i).Type = adChapter) Then
                   Set rsChild = New ADODB.Recordset
                   Set rsChild = rs.Fields(i).Value
                   UpdateBatchX_ALLChildren "", rsChild
                   rsChild.Close
                   Set rsChild = Nothing
               End If
           Next i
                   
       End With
      
       'Only close the connection on the original call
       If Not (szConnect = "") Then
           m_cn.Close
           Set m_cn = Nothing
       End If
       
       UpdateBatchX_ALLChildren = 0
       Exit Function
   
   errhandler:
       UpdateBatchX_ALLChildren = Err.Number
       Err.Raise Err.Number, " UpdateBatchX ", Err.Description
           
   End Function
   					

For more information, please see the following articles in the Microsoft Knowledge Base: