ATL COM and ADO

Recently I did a project on online banking at NIIT, Bangalore. The project
was coded mostly using VB with few ATL components thrown in, if only to
teach us programming distributed applications. One middle tier component that
I programmed was built with ATL and uses ADO to query the backend
(SQL Server). Parts of that code appears here.

I assume the reader knows or at least has a fair idea about COM
programming using ATL as well as ADO programming with VB.

What is ADO?

ADO standa for ActiveX Data Object.ADO provides an object-oriented
programming interface for accessing a data source using the OLEDB data provider.
It is the succesor to DAO and RDO object models and combines the best features
DAO and RDO.

Programming OLEDB in C++ is easy. However, for languages like Visual Basic, that do not support
pointers and other C++ features, implementing OLEDB is difficult.

This is where ADO really shines. ADO is an high level interface to OLEDB that is based on COM
interfaces. Thus any application that supports COM can implement ADO.

ADO Features

• Allows acess to all types of data
  
• Supports free threading
  
• Supports asynchronous queries
  
• Supports client-side and server-side cursors
  
• Supports disconnected recordsets

ADO Architecture

In the ADO model, we’ll be using three main types of objects-

• Connection
  
• Command
  
• Recordset

The Connection object sets up a connection to the data source. First, the data source name, its location, user
ID, password etc is stored in a ConnectionString object, which is passed
to the Connection object.to establish a connection to the
data source.

The Command object is used to execute SQL commands, queries and stored
procedures.

When a query is executed, it returns results that are
stored in the Recordset object. Data in a recordset can be manipulated and then
updated to the database.

Using ADO

First, we’ll be building an ATL DLL component. This component has a
method that takes one input parameter (customer ID in the project) and returns a reference
to the corresponding Recordset object to the VB client. The client then
displays the data in a form.

To create the DLL, use the ATL COM AppWizard to generate
the framework for the application. Name the project FindCust
and choose the server type as Dynamic Link
Library.Also choose the option to
support MFC library.

Insert a New ATL Object of type
Simple Object to the project. Use the name Search
in the Short Name textbox of the ATL Object
Wizard Properties and click OK to add the object.

In classview, right click the interface name and add a
method. Name the method SearchCust and type the following in
the Parameters
textbox :

[in] BSTR bstrcustid,[out,retval] _Recordset **ptr

Click the OK button to add the method.

Since the SearchCust method returns a reference to a
Recordset object, we need to import the ADO library. To do this, open the file,
StdAfx.h and add the following code :

#import "C:Program FilesCommon FilesSystemADOMSADO15.DLL" rename_namespace("ADOCust") rename("EOF","EndOfFile")

using namespace ADOCust;

This step will help the Visual C++ compiler to
understand the ADO objects defined in the type library,
MSADO15.DLL. The rename_namespace
function renames the namespace into which the DLL has been imported to the
specified name. The rename option has been used to rename
the EOF keyword to EndOfFile, because EOF is already defined in the standard header
files.

Also the .idl file contains the method
SearchCust which returns a reference to a Recordset
object. To make the MIDL compiler understand the ADO objects, import the type
library in the .idl file using the importlib statement in the library
section (after importlib “stdole2.tlb”) like
:

importlib("C:Program FilesCommon FilesSystemADOMSADO15.DLL");

Also move the interface definition in the .idl file to just
after the importlib statement to make the MIDL
compiler.understand ADO objects.

To do that, cut the interface definition block and paste it after the
imporlib statement that was
added. My interface definition block looks
like:

[
object,
uuid(EB78D558-E071-4D25-80DD-41FD3519934E),
dual,
helpstring("ISearch Interface"),
pointer_default(unique)
]
interface ISearch : IDispatch
{
 [id(1), helpstring("method SearchCust")]
 HRESULT SearchCust([in] BSTR rcustid,
 [out,retval] _Recordset **ptr);
};

Building the ATL Component

Now we are ready to code the SearchCust method to retrieve
the corresponding information.What we need to do is :

• Initialize the COM library


• connect to the data source


• execute the SQL commands


• return the Recordset object


• Uninitialize the COM library

Initialize the COM library :

CoInitialize(NULL);

To connect to a data source, first declare a Connection object
pointer by passing the ID of the coclass.

_ConnectionPtr conptr(__uuidof(Connection));

Now call the Open function to establish a connection to the data
source.

conptr->Open(_T("Provider=SQLOLEDB.1;
                Data Source=SQLServer;
                Initial Catalog=Customer"),
             _T("user1"),
             _T(""),
             adOpenUnspecified);

The Open function takes four parameter. The first one is the
connection string, which contains the name of the provider and name of SQL
Server for connection. The second and third parameters are the user name and the
password to establish the connection. The fourth parameter is the type of
cursor to be used. The _T macro ensures UNICODE compatibility of the
strings.

To pass the SQL command, create a command object pointer by
passing the CLSID of the Command object.

_CommandPtr cmd(__uuidof(Command));

Set the ActiveConnection property of the Command
object to the open Connection object pointer

cmd->ActiveConnection=conptr;

Now store the SQL statement to be executed in the
CommandText property of the Command object.

cmd->CommandText="<Your SQL statement goes here>"

Create a Recordset object and specify the Command
object as the source of the records as follows:

_RecordsetPtr rst(__uuidof(Recordset));
rst->PutRefSource(cmd);

Now open the Recordset using the Open method of the Recordset
object as :

_variant_v vNull;

rst->Open(vNull,
               vNull,
               adOpenDynamic,
               adLockOptimistic,
               adCmdText);

The Open method takes five parameters. The
first and the second parameter is the data source name and the active connection
to use respectively.Since the data source has already been specified in the
Connection object and the ActiveConnection
property is also set in the Command object, the first and the
second parameter is passed as NULL variant values. The third parameter specifies
the cursor type to use followed by the locking parameter. The fifth parameter
specifies how the database should evaluate the command being sent.

Now the Recordset object pointer created will
have a reference to the records returned by the SQL statement. We need to return
this recordset to the client. Use code like :

rst->QueryInterface(__uuidof(_Recordset),
                    (void **) ptr);

The QueryInterface function
takes the IID of the Recordset object and returns a reference to the records
returned by the SQL statement. When the client calls SearchCust
method, this pointer will be returned to the client.

Uninitialize the COM library :

::CoUninitialize();

Now build the component. This will register the DLL in the registry.

Building the Client

Open VB and create a new Standard EXE project.