Home > Articles > Programming > Visual Basic

  • Print
  • + Share This

Disconnected Recordsets

Disconnected recordsets have the following characteristics:

  • Performance: Good

  • Object-Oriented: Yes

  • Self-Defined: Yes

Disconnected recordsets are a staple of distributed applications. They are a strong choice for a transport and offer a nice compromise between performance, object orientation, and self-definition. This is true because Microsoft has specifically designed the ADO Recordset object to function as a transport.

Based on my discussion of variant arrays, you might initially conclude that a recordset would be a poor performer. After all, it's an object and should therefore suffer from the same marshaling overhead endured by all objects. This, however, isn't the case. Microsoft has designed the ADO Recordset object so that its transport between processes is fundamentally different. Unlike standard Visual Basic objects, the Recordset isn't marshaled when it's moved between processes on different computers. Instead, the object and all its data are persisted and then streamed to the other process.

Moving the Recordset object and its data is accomplished by the OLEDB Persistence Provider (MSPersist). When a Recordset is moved between processes, the object and the records it contains are converted into a special format known as Advance Data Tablegram (ADTG). This format creates a high-performance data stream that will move rapidly from one process to another. When the ADTG arrives at the destination process, MSPersist re-creates the original Recordset object automatically.

The performance provided by MSPersist, the OO nature of the Recordset, and the schema information provided by the Fields collection make this transport very attractive. The drawback of this transport, however, is that it relies on ADO being present at each tier in the hierarchy. This means that your entire system will be affected if Microsoft comes out with new data access technology.

After you create the disconnected recordset and run a query, you must disassociate the database connection. This is accomplished by setting the recordset's ActiveConnection property to Nothing. As soon as it's disassociated, you can freely pass the recordset as a return value from a function. Listing 3 shows how you might run a query in a COM+ component and return a disconnected recordset.

Listing 3: Returning a Disconnected Recordset

Public Function Query () As ADODB.Recordset

    Set Query = Nothing

    'Variables
    Dim objRecordset As ADODB.Recordset
    Set objRecordset = New ADODB.Recordset

    'Run Query
    objRecordset.ActiveConnection = _
        "Provider=SQLOLEDB;Data Source=(local);Database=pubs;UID=sa;PWD=;"
    objRecordset.CursorLocation = adUseClient
    objRecordset.CursorType = adOpenStatic
    objRecordset.LockType = adLockBatchOptimistic
    objRecordset.Source = "SELECT * FROM Publishers

    'Get Data
    objRecordset.Open

    'Disconnect Recordset
    Set objRecordset.ActiveConnection = Nothing

    'Return the Records
    Set Query = objRecordset
End Function

When the client receives the disconnected recordset, it behaves just like any other recordset. You can navigate the recordset with MoveFirst, MoveLast, MoveNext, and MovePrevious. You can edit the data and save the changes locally. You can even add and delete records. The changes simply aren't permanent until you reconnect the recordset to the parent database and commit the changes.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.