OLEDB - Objects via ADO

OLEDB, Reprints, Source Code, Way back

ADO seems to be the ideal way to expose tabular data from your own COM objects and the ATL OLE DB Provider templates can help!

The problem

If you already have a COM object that manages data that is naturally tabular, or, if you have a COM object that has data which is often displayed in a tabular form then it would seem sensible to leverage the work being put into ADO by third party data control manufacturers. Why craft a custom control to display your data when you could use any number of ADO aware controls, if only your COM object could provide an ADO view of itself. Also, by presenting a standard interface to your data object it becomes easy for others to use your object, and you don’t have to write the documentation for the interface because it’s ADO!

It’s quite common to find a legacy data object that would benefit from being accessed via ADO. The problem is that providing ADO access is a non-trivial thing to do. The ATL OLE DB Provider templates are useful for simple situations but appear to fall down when you want your own object to own the data rather than simply copying it all into an array inside the rowset object. Also, getting access to your data isn’t that easy, you have to hook yourself up to a data provider, then get a rowset from it, etc.

However, it is possible to extend the ATL templates to allow your object to retain ownership of its data, and it’s also possible to hide all of the hoops that you have to jump through to wire up your object to its ADO recordset view.

This article will first cover the mechanics of providing an ADO interface onto a simple data object and then deal with writing a reusable OLE DB rowset implementation that can provide in-place access to the simple data object’s data but still be used in an ATL OLE DB provider framework.

A simple data object

Suppose we have a simple data object that implements the following interface and internally represents its data in a tabular form.

interface IMyDataObject : IUnknown
{
   HRESULT SetColumnSize([in] long columnSize);

   HRESULT AddColumn(
      [in] BSTR columnName,
      [out, retval] long *index);
  		
   HRESULT GetColumnName(
      [in] long index,
      [out, retval] BSTR *columnName);

   HRESULT AddRow([out, retval] long *index);
  		
   HRESULT RemoveRow([in] long index);
  		
   HRESULT Depth([out, retval] long *depth);

   HRESULT Width([out, retval] long *width);

   HRESULT SetAt(
      [in] long rowIndex,
      [in] long columnIndex,
      [in] BSTR value);

   HRESULT GetAt(
      [in] long rowIndex,
      [in] long columnIndex,
      [out, retval] BSTR *value);
};

The object can be used to store a table of strings. It’s easy enough to use this object from Visual Basic but the interface isn’t ideal. You can download the simple data object and a Visual Basic test program from here.

Obtaining an ADO recordset

The ADO interface that we will use will access our data object via a custom OLE DB provider. We can add a provider to the simple data object’s DLL by selecting Insert New ATL Object and choosing a Provider from the Data Access section of the object wizard. Once we’ve done this we can add an interface to the simple data object that will allow it to return an ADO recordset view of itself. This interface will use ADO to access the OLE DB provider that we’ve added and construct a recordset to return to our Visual Basic client code.

The interface that we’ll add will look like this:

interface IGetAsADORecordset : IDispatch
{
   [id(1), helpstring("method GetAsRecordset")]
   HRESULT GetAsRecordset(
      [in] CursorLocationEnum CursorLocation,
      [in] LockTypeEnum LockType,
      [in] CursorTypeEnum CursorType,
      [out, retval] VARIANT *pvRecordset);
};

We’ve chosen a variant to return the recordset as it means we don’t have to worry about using importlib to pull in the ADO type library. It results in one extra QI call for Visual Basic to get the correct interface from the IDispatch pointer stored in the variant but it frees us from a run-time binding to the location of the ADO type library (see knowledge base article Q186387).

The Visual Basic client code can then do something like the following:

   Dim dataObject as New MyDataObject
   Dim asRs as IGetAsADORecordset
   Set asRs = dataObject
   Dim rs as ADODB.Recordset
   Set rs = asRs.GetAsRecordset( _
               adUseClient, _
               adLockOptimistic, _
               adOpenStatic)

   ' now do something with the recordset we have!

We should probably make the cursor and locking flags optional and have them default to standard values which would make the code less above less complex in most cases.

We can add this interface to our simple data object’s IDL and, in keeping with the ATL way of doing things, we can write a template that implements the interface for us.

The resulting implementation template looks something like this:

template <
   class T,
   const GUID* plibid = &CComModule::m_libid,
   WORD wMajor = 1,
   WORD wMinor = 0,
   tihclass = CComTypeInfoHolder>
class ATL_NO_VTABLE IGetAsADORecordsetImpl
   :  public IDispatchImpl<
         IGetAsADORecordset,
         &IID_IGetAsADORecordset,
         plibid,
         wMajor,
         wMinor,
         tihclass>
{
   public:

      STDMETHOD(GetAsRecordset)(
         CursorLocationEnum cursorLocation,        // [in]
         LockTypeEnum lockType,                    // [in]
         CursorTypeEnum cursorType,                // [in]
         VARIANT *pvRecordset)                     // [out]
      {
         if (!pvRecordset)
         {
            return E_POINTER;
         }
         return E_NOTIMPL;
      }
};

It’s fairly simple, but as the interface is a dispatch based and we inherit from IDispatchImpl we take extra template parameters and default them so that our users can adjust the functionality of the IDispatch implementation if they need to.

Of course we now have to connect our data object to the OLE DB provider so that it can access our tabular data and present it via ADO. As we’ll be using ADO to connect to our OLE DB provider we must somehow pass the data object through the ADO call into the OLE DB provider. Luckily ADO providers a method to do this kind of thing in the form of a Command that takes a parameter, which can be anything that fits in a variant. We’ll simply pass the IUnknown pointer to our data object as the parameter to our ADO Command.

We’ll use the #import feature of VC++ to make the ADO coding easier. The resulting code is something like this:

ADODB::_ConnectionPtr Connection("ADODB.Connection");

Connection->Open(
   _bstr_t( L"Provider=SimpleDataObject.ConversionProvider.1"),
   _bstr_t(""),
   _bstr_t(""),
   -1);

ADODB::_CommandPtr Command("ADODB.Command");

Command->CommandText = _bstr_t("CONVERT");

pUnknown->AddRef();

ADODB::_ParameterPtr Param1 = Command->CreateParameter(
   _bstr_t("IUnknown"),
   ADODB::adIUnknown,
   ADODB::adParamInput,
   -1,
   _variant_t(pUnknown));

Command->Parameters->Append( Param1 );
Command->ActiveConnection = Connection;

CComQIPtr<IDispatch> spCommand = Command;

ADODB::_RecordsetPtr Rs1("ADODB.Recordset");

_variant_t vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);

Rs1->CursorLocation = (ADODB::CursorLocationEnum)cusorLocation;
Rs1->CursorType = (ADODB::CursorTypeEnum)cursorType;
Rs1->LockType = (ADODB::LockTypeEnum)lockType;
Rs1->Open(
   _variant_t(spCommand),
   vtEmpty,
   ADODB::adOpenUnspecified,
   ADODB::adLockUnspecified,
   -1);

// Return the recordset in a variant...

pvRecordset->vt = VT_DISPATCH;
pvRecordset->pdispVal = (IDispatch*)Rs1.Detach();

Assuming our OLE DB Provider does its part then that’s all we need to do from an ADO point of view.

Getting something to work…

We can get the code above working to the point where it will return the standard “view of a file system” ADO recordset that the default, wizard-generated, OLE DB provider returns by adjusting the wizard-generated code very slightly.

First we need to add support for ICommandWithParameters as our command object incorporates a parameter. The implementation of this interface is very straight forward since our command is so simple. Of the three methods in ICommandWithParameters, only SetParameterInfo will ever get called for the command we use above - and this can simply return S_OK.

Once we support ICommandWithParameters our ADO calls will get all the way through to the OLE DB provider’s rowset implementation of the Execute method. This needs to be adjusted to make it work with our command. The wizard-generated code is expecting the command text to be a file specification, it then returns a director listing of all files that match the specification. Later we will replace the entire rowset implementation with one of our own, but for now simply hacking the szDir variable to always be set to “.” will suffice.

Although this recordset does not represent the data stored in our simple data object in any way, it does at least provide a quick and dirty way of testing that the framework that we’re building actually works.

Exposing the recordset interface from QI

Now that we have all the code required to expose a recordset it would be nice to make it a ‘real’ interface on our data object rather than so obviously a separate view on the data. From a Visual Basic point of view it would be nicer if all we had to do was this:

   Dim dataObject as New MyDataObject
   Dim rs as ADODB.Recordset
   Set rs = dataObject

   ' now do something with the recordset we have!

This is actually reasonably easy. We need to create the ADO recordset as a tear-off interface when we’re first asked for it and aggregate it into our data object. We can then add some COM_INTERFACE_ENTRY_FUNC macros to our COM Map to handle the various flavours of ADO recordset interfaces that we might be asked for and also to handle any other interfaces that the ADO recordset object is normally expected to expose… This can all be done inside our implementation of IGetAsADORecordset which leaves us only having to chain our object’s interface map to the IGetAsADORecordsetImpl one…

Download

The complete implementation of our data object with support for returning an ADO recordset (full of unrelated file system information!) via IGetAsADORecordset and also via QI, can be found here.

The following source built using Visual Studio 6.0 SP3. Using the July 2000 edition of the Platform SDK. If you don’t have the Platform SDK installed then you may find that the compile will fail looking for “msado15.h”. You can fix this problem by creating a file of that name that includes “adoint.h”.

Revision History

  • 15th September 1999 - Initial revision at www.jetbyte.com.
  • 17th September 1999 - Hacked in a simple solution to a stupid cyclical reference counting problem.
  • 22nd July 2000 - Bug fix, IGetAsADORecordsetImpl.h, line 77, _Recordset15 should be Recordset15. Thanks to Nie Jiantao for reporting this.
  • 2nd October 2000 - Fixed some build configuration errors. Thanks to Charles Finley for reporting these.
  • 12th October 2005 - reprinted at www.lenholgate.com.

Other articles in the series

  • Objects via ADO - ADO seems to be the ideal way to expose tabular data from your own COM objects and the ATL OLE DB Provider templates can help!
  • Custom Rowsets - The ATL OLE DB Provider templates appear to rely on the fact that your data is kept in a simple array, but that’s not really the case at all!
  • IRowsetLocate and Bookmarks - Adding bookmark functionality is relatively easy and it enables our ADO recordset to be used with a greater number of data bound controls.
  • Updating data through an ADO recordset - The ATL OLE DB Provider templates only seem to support read-only rowsets, and making them support updating of data isn’t as easy as you’d expect!
  • Client Cursor Engine updates - Making the ADO Client Cursor Engine believe that your rowset is updateable involves jumping through a few extra hoops…
  • Disconnected Recordsets - If you are going to use the client cursor engine then often it’s a good idea to disconnect your recordset…