Recently I did a project
on online banking at NIIT, Bangalore. The project was coded mostly using VB with a 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 stands for ActiveX Data Objects. 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.
- Access to all types of data - Various data sources include e-mail, text files, RDBMSs, ISAM/VSAM databases and all ODBC data sources.
- Supports free threading - ADO supports multiple client connections through multiple threads in such a way that these threads do not interfere with each other.
- Supports asynchronous queries - This basically means that after an SQL query is submitted to the database server, the control returns immediately to the calling application, allowing the user to continue working while the query is being processed. On completion of the query, the results are sent to the client.
- Supports client-side and server-side cursors - Cursor is a mechanism that allows access and navigation of data in a recordset. They are implemented as client-side or server-side. Traditionally, frequently updated recordsets are implemented as server-side while read-only recordsets are implemented as client-side.
- Supports disconnected recordsets - After a recordset is returned on execution of a query, it is stored as a client-side cursor and the active connection is closed. After changes have been commited to the recordset, the connection is reestablished and all updates are sent in a batch to the data store. This helps in reducing network traffic to a great extent.
- Supports Commands as connection methods - An unique feature of ADO is that when a command is executed, a connection is first established internally before that command gets submitted for execution. Compare this to traditional object models like DAO/RDO where a connection has to be established explicitly before a command can be submitted.
In the ADO model, we'll be using three main types of objects-
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
The Command object is used to execute SQL commands, queries and stored
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.
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
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
[in] BSTR bstrcustid,[out,retval] _Recordset **ptr
Click the OK button to add the method.
#import "C:\Program Files\Common Files\System\ADO\MSADO15.DLL" rename_namespace("ADOCust") rename("EOF","EndOfFile")
using namespace ADOCust;
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 :
This step will help the Visual C++ compiler to
understand the ADO objects defined in the type library,
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
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
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
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
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
To connect to a data source
First declare a Connection object
pointer by passing the ID of the coclass.
Now call the Open function to establish a connection to the data
conptr->Open(_T("Provider=SQLOLEDB.1; Data Source=SQLServer;Initial Catalog=Customer"),
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
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
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.
ActiveConnection property of the Command
object to the open Connection object pointer
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:
Now open the Recordset using the Open method of the Recordset
object as :
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
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);
takes the IID of the Recordset object and returns a reference to the records
returned by the SQL statement. When the client calls
method, this pointer will be returned to the client.
Uninitialize the COM library
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 :
Dim objCust as Object
returned Dim rst as ADODB.Recordset
Set objCust = CreateObject("FindCust.Search")
Set rst = objCust.SearchCust(1)
MsgBox rst.Fields(1) & " " & rst.Fields(2)
Once you have got the Recordset object, you can manipulate and update the data
it holds in any way that you want. For example, you can use
MoveLast to navigate through the recordset and
to update the data to the database.
A recordset object consists of a collection of
Field objects that form
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
Add a new method
GetMaxValue that has a single parameter
VARIANT *Val. The implementation looks like:
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
That's it, guys. Now you are on your way to ADO fame and fortune. :-)
Hope ya all find this article useful.
Amit Dey is a freelance programmer from Bangalore,India. Chiefly programming VC++/MFC, ATL/COM and PocketPC and Palm platforms. Apart from programming and CP, he is a self-taught guitar and keyboard player.
He can be contacted at firstname.lastname@example.org