ATL COM and ADO

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") using:

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 
bstrcustid, [out,retval] _Recordset **ptr);
};

Building the ATL Component

Now we are ready to code the component with 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 parameters. 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.

Note that your connection string will be different than the one that I'm using here. You may need to use other providers as well as connect to a different datasource. Obviously, your username and password will be different. The Initial Catalog parameter defines the database table to be used. For SQL Server, the OLEDB provider is SQLOLEDB. You can use MSDASQL or Microsoft Jet.OLEDB provider to connect to a MS Access database (.mdb) as well.

Execute the SQL commands

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>"

Return the Recordset object

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 VB Client

Open VB and create a new Standard EXE project. Set a reference to the Microsoft ActiveX Data Objects 2.1 Library and FindCust 1.0 Type Library through Project->References.

The following VB code can be used to test the DLL component created :

'declare a variable of the Object type
Dim objCust as Object
'declare a variable of the type Recordset to store the value 
returned 'by the DLL
Dim rst as ADODB.Recordset
'create an instance of the DLL i.e. FindCust.Search
Set objCust =     CreateObject("FindCust.Search")
'call the SearchCust method by passing the Customer ID
'Store the returned Recordset object
Set rst = objCust.SearchCust(1)
'display information from the recordset  in a message box
MsgBox  rst.Fields(1) & " " & rst.Fields(2)

Field Lookup

A recordset object consists of a collection of Field objects that form a Fields collection. Field objects are used to access fields of each record within a recordset. They contain information about the name, the type and the value of the fields in a table.

To illustrate a field lookup, let's consider the online banking scenerio where we have to generate a unique account number for each new customer. The database's Customer table has an account number field, iAccountNumber, which for the sake of simplicity, we'll consider as an integer datatype field.

The SQL command is

select max(iAccountNumber)+1 from customer

Once this command is executed, we'll look up the value from the resultant recordset.

Add a new method GetMaxValue that has a single parameter [out,retval] VARIANT *Val. The implementation looks like:

CoInitialize(NULL);

...//same as code previuosly

...//presented in article


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

VARIANT index;
VariantInit(&index);
index.vt=VT_I4;
index.lVal=0;

FieldsPtr fields;
FieldPtr field;
	
HRESULT hr=rst->get_Fields(&fields);

if(SUCCEEDED(hr))
{
    hr=fields->get_Item (index,&field);
}
if(SUCCEEDED(hr))
{
    hr=field->get_Value (Val);
}

This recordset has a single field (index value is 0) representing the max account number. This is the value that we are looking up and which is now stored in Val.

That's it, guys. Now you are on your way to ADO fame and fortune. :-)

Happy programming!