How to Return Disconnected ADO Recordset from COM

• Download demo project - 42.7 KB

Introduction

Some business objects return ADO recordsets to their client. This is a common practice when you are developing a 3-tier web application. You need to process your business logic in your object or stored procedure and then return the results/resultset to the presentation layer. Very often, you need to return the resultset to the client so that the presentation layer can process the resultset (e.g.: converting to XML, displaying the results). While the presentation layer is doing the processing, it is good to disconnect it from SQL Server to conserve database resource (e.g.: disconnected recordset).

Implementation Details

First, you have to configure Visual C++ to use platform SDK. Select Options from the Tools Menu. Click the Directories tab.

• Add an entry for the Platform SDK include directory (e.g: c:\platform sdk\include)
• Add an entry for the ATL 3.0 include directory (e.g: c:\platform sdk\include\atl30)
• Add an entry for the library directory of the Platform SDK (e.g: c:\platform sdk\lib)
• Add an entry for the Bin directory of the Platform SDK (e.g: c:\platform sdk\bin) (Make sure that these entries are first in the list)

Next, you have to configure your project to work with ADO. The easiest way to work with ADO in Visual C++ is to use native COM support to import the ADO type library and generate smart pointer wrapper classes for the objects in the ADO library: Recordset, Connection, Command and so on. Add the following #import statement to the stdafx.h file in your project:

#import "c:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF", "adoEOF") 

This assumes that your msado15.dll resides in c:\Program Files\Common Files\System\ADO\msado15.dll. Create a method to the Interface.

Method name: GetEmployeeList.

Parameters: [out, retval] _Recordset **returnvalue

STDMETHODIMP CAdoRec::GetEmployeeList(_Recordset **returnvalue)
  {
  _ConnectionPtr pConnection = NULL;
  _CommandPtr pCommand = NULL;
  _RecordsetPtr pRecordset = NULL;
 _bstr_t sql("select lastname,firstname,employeeid from employees");

  try
  {
  pConnection.CreateInstance(__uuidof(Connection));
  pConnection->Open(CONNECTION_STRING,"","",-1);
  pRecordset.CreateInstance(__uuidof(Recordset));
  pRecordset->CursorLocation=adUseClient;
  pRecordset->Open(sql,pConnection.GetInterfacePtr(), 
    adOpenStatic,adLockBatchOptimistic,-1);  
  pRecordset->AddRef();
  *returnvalue = pRecordset;
  pRecordset->putref_ActiveConnection(NULL);
  }
  catch(_com_error err)
  {
    pConnection->Close();
  }  

   pConnection->Close();
   return S_OK;
}

You have to add reference to the recordset and then return it. After that, you disconnect the recordset from the database connection. You can modify the code to call a stored procedure instead.

You also have to import the ADO type library into your IDL file. Use importlib as shown below:

 import "oaidl.idl";
 import "ocidl.idl";
 [
 uuid(AAC8075D-21AD-46B2-A1BE-9777DE78B4DC),
 version(1.0),
 helpstring("ADOrecordset 1.0 Type Library")
 ]
 library ADORECORDSETLib
 {
 importlib("stdole32.tlb");
 importlib("stdole2.tlb");
 importlib("c:\Program Files\Common Files\System\ADO\msado15.dll"); 
[
 uuid(DB761CAF-9C4A-42ED-9654-F963552F3FA4),
 helpstring("AdoRec Class")
 ]
 coclass AdoRec
 {
 [default] interface IAdoRec;
 };
 [
 object,
 uuid(E086C0B9-52EC-4F45-AE9D-03BB9CA5FE84),
 dual,
 helpstring("IAdoRec Interface"),
 pointer_default(unique)
 ]
 interface IAdoRec : IDispatch
 {
 [id(1), helpstring("method GetEmployeeList")]
   HRESULT GetEmployeeList([out,
   retval] _Recordset **returnvalue);
 };
 };

This is how you can call the GetEmployeeList method to return a disconnected recordset to ASP. Note that objPagingRS is the disconnected recordset. You can manipulate it just like a normal recordset. For illustration purposes, I am just rendering the first field of the recordset on the ASP page.

<%
  set fbn=server.CreateObject("adorecordset.adorec")
  set objPagingRS=fbn.GetEmployeeList()
  set fbn=nothing
  do while Not objPagingRS.EOF
  Response.Write "<BR>" + cstr(objPagingRS.Fields(1)) + vbCrLf
  objPagingRS.MoveNext
  loop
  objPagingRS.Close
  Set objPagingRS = Nothing
  %>