INFO: Using Disconnected Hierarchical Recordsets

• Microsoft Data Access Components versions 2.0, 2.1

SUMMARY

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:

Q189657 HOWTO: Use the ADO SHAPE Command

Q186342 HOWTO: Create a 3-Tier App using VB, MTS and SQL Server

Q182442 FILE: Adomts.exe Shows Using ADO w/ an MTS Component via DCOM

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.
   
   The following MSDN article explains this in more detail:


http://msdn.microsoft.com/developer/news/devnews/sepoct98/rowset.htm


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 each of the following:
   
   Microsoft ActiveX Data Objects 2.1 Library
   
   -or-
   
   Microsoft ActiveX Data Objects 2.0 Library



4. Place a CommandButton on the From.



5. Paste the following code into the Code Window:




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=<Your Server Name>;uid=<UID>sa;pwd=<PWD>;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=WUTANG;" _ 
           & "uid=sa;pwd=;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
     

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Mike Ruthruff, Microsoft Corporation

REFERENCES

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

Q191744 HOWTO: Extract Child Recordsets from Shaped Recordset

Q196968 PRB: SHAPE Provider Pulls Down All Records in Child Table

Q185423 PRB: Most ADO Recordset Properties Are Not Marshalled

Additional query words:

Keywords          : kbADO200 kbADO201 kbDCOM kbGrpVBDB kbGrpMDAC 
Version           : WINDOWS:2.0,2.1
Platform          : WINDOWS 
Issue type        : kbinfo 

Last Reviewed: May 3, 1999