ADO Recordset Persistence

Recordset persistence is implemented in ADO with the Microsoft OLE DB Persistence Provider. This provider generates a forward-only, read-only rowset from a saved XML file or stream that contains the schema information generated by ADO. Similarly, it can take an ADO Recordset, generate XML, and save it to a file or any object that implements the IStream interface. (In fact, a file is just another example of an object that supports IStream.) For versions 2.5 and later, ADO relies on the Microsoft XML parser to load the XML into the Recordset; therefore msxml.dll is required. For version 2.5, msxml shipped with Internet Explorer 5. For version 2.6, msxml shipped with SQL Server 2000.

ADO 2.0 and 2.1 allowed Recordset persistence into flat files only. With ADO 2.5 and later, Recordset objects can be persisted into any object that implements the IStream interface. One such object is the ADO Stream object. If you write an IStream implementation to persist and load from, you do not need to support asynchronous fetching or be free threaded. Recordset objects cannot be loaded in asynchronous mode when loading from an IStream object.

The easiest way to persist data into XML and load it back again is through ADO. The following ADO code example demonstrates saving the data in the Titles table to a file named Titles.sav.

Dim rs as new Recordset
Dim rs2 as new Recordset
Dim c as new Connection
Dim s as new Stream

' Query the Titles table.
c.Open "provider=sqloledb;data source=mydb;initial catalog=pubs;user id=sa;password="
rs.cursorlocation = adUseClient
rs.Open "select * from titles", c, adOpenStatic

' Save to the file in the XML format. Note that if you don't specify 
' adPersistXML, a binary format (ADTG) will be used by default.
rs.Save "titles.sav", adPersistXML

' Save the Recordset into the ADO Stream object.
rs.save s, adPersistXML
rs.Close
c.Close

set rs = nothing

' Reopen the file.
rs.Open "titles.sav",,,,adCmdFile
' Open the Stream back into a Recordset.
rs2.open s

ADO always persists the entire Recordset object. If you wish to only persist a subset of rows of the Recordset object, use the Filter method to narrow down the rows or change your selection clause. However, you must open a Recordset object with client-side cursors (cursorlocation = adUseClient) to use the Filter method for saving a subset of rows. For example, to retrieve titles that start with the letter "b," you can apply a filter to an open Recordset object:

rs.Filter "title_id like 'B*'"
rs.Save "btitles.sav", adPersistXML

Note that you can open a Recordset object from a URL as well as a filename:

rs.open http://MyWebServer/XMLSamples/titles.sav,,,,adCmdFile

ADO always loads the XML into a Cursor Engine rowset on a call to the Open method (that is, when the CursorLocation property on the Recordset is set to adUseClient). This allows ADO to surface a scrollable, bookmarkable Recordset object on top of the forward-only rowset generated by the Persistence Provider.

Saving an OLE DB Rowset from C/C++

Recordset persistence is only exposed through ADO, so you cannot call the Save method directly on an OLE DB rowset. Similarly, you cannot open an OLE DB rowset by loading it from a file or stream. The IADORecordsetConstruction interface makes it easy to wrap a rowset with an ADO Recordset object or to get to the rowset from an ADO Recordset object.

To save a rowset, the following function takes a rowset and transfers it to an ADO Recordset object. Then it uses the Save method on the Recordset object to save the rowset to a file.

HRESULT SaveMyRowset(IRowset *pRowset, BSTR bstrFileName)
{
    // Get the Recordset interface.
    IADORecordset * pRs = NULL;
    IADORecordsetConstruction * pADORsCt = NULL;
    HRESULT hr;

    // Get an ADO Recordset and ask for the Recordset construction
    // interface.
    if (FAILED (hr = ::CoCreateInstance(
                            CLSID_ADORecordset, 
                            NULL,
                            CLSCTX_INPROC,
                            IID_IADORecordsetConstruction, 
                            (LPVOID *) &pADORsCt)))
        return hr;

    // Give the rowset to ADO.
    if (SUCCEEDED (hr = pADORsCt->put_Rowset(pRowset))
    {
        hr = pADORsCt->QueryInterface(IID_IADORecordset, (void **)&pRs);
        // Note that you can pass Save() a variant containing a BSTR 
        // (filename), or an object VT_UNKNOWN that supports IStream.
        hr = pRs->Save(vDest, adPersistXML);
        }

    // Cleanup: (or use ATL CComPtr class).
    pRs->Release();
    pADORsCt->Release();

    return hr;
}

Loading OLE DB Rowsets from C/C++

To load a rowset, the following function creates a Recordset object and uses the Open method to open the file. Then it retrieves the rowset out of the Recordset object using the IADORecordsetConstruction interface and returns it to the user.

HRESULT LoadMyFile(BSTR bstrFileName, IRowset **ppRs)
{
    IADORecordsetConstruction * pRsCt = NULL;
    IADORecordset * pADORs = NULL;
    HRESULT hr;

    // Create the ADO Recordset.
    if (SUCCEEDED (hr = ::CoCreateInstance(CLSID_ADORecordset, 
                    NULL,
                    CLSCTX_INPROC,
                    IID_IADORecordset,
                    (LPVOID *) &pADORs)))
    {
        VARIANT varEmpty;

        varEmpty.vt = VT_ERROR;
        varEmpty.scode = DISP_E_PARAMNOTFOUND;

        hr = pADORs->Open(bstrFilename, varEmpty, -1,-1,-1);
        hr = pADORs->QueryInterface(IID_IADORecordsetConstruction, 
                                (void **)&pRsCt);
        hr = pRsCt->get_Rowset (ppRs);
    }
    pADORs->Release();
    pRsCt->Release();

    return hr;
}

C++ users also have the option of using ADO directly in their programs instead of OLE DB. The "#import" utility in Microsoft Visual C++ 6.0 makes it easy to do that. Refer to the Visual Studio or ADO documentation for more information about #import.