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 Files\Common Files\System\ADO\MSADO15.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 Files\Common Files\System\ADO\MSADO15.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.


Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: August 14, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Data protection has long been considered "overhead" by many organizations in the past, many chalking it up to an insurance policy or an extended warranty you may never use. The realities of today makes data protection a must-have, as we live in a data-driven society -- the digital assets we create, share, and collaborate with others on must be managed and protected for many purposes. Check out this upcoming eSeminar and join Seagate Cloud …

  • Agile development principles have gone from something used only by cutting-edge teams to a mainstream approach used by teams large and small. If you're not using agile methods already though, or if you've only been exposed to agile on small projects here and there, you may wonder if agile can ever work in your environment. Read this eBook to learn the fundamentals of agile and how to increase the productivity of your software teams while enabling them to produce higher-quality solutions that better fulfill …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds