Click here to Skip to main content
11,583,859 members (62,125 online)
Click here to Skip to main content

ATL COM and ADO

, 3 Apr 2001 237K 1.4K 64
Rate this:
Please Sign up or sign in to vote.
An ATL Component that uses ADO for data.
<!-- Main HTML starts here -->

Introduction

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.

ADO Features

  • 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.

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") 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)
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 MoveFirst , MoveNext, MovePrevious and MoveLast to navigate through the recordset and Update to update the data to the database.

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. Smile | :)

Hope ya all find this article useful.

Happy programming!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Amit Dey
Web Developer
India India
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 visualcdev@hotmail.com


You may also be interested in...

Comments and Discussions

 
QuestionHow to concatinate two recordsets in Visual c++ 6 Pin
LogicHead11-May-07 20:10
memberLogicHead11-May-07 20:10 
GeneralAccepting ADO Recordset in vc++ Client Pin
Ashwin kumar Gurujala24-Oct-05 20:58
memberAshwin kumar Gurujala24-Oct-05 20:58 
GeneralCompile time error in .tlh &amp; .tli Pin
gajesh21-Oct-05 19:59
membergajesh21-Oct-05 19:59 
Generalbatch sql Pin
asmotritsky26-May-04 11:32
memberasmotritsky26-May-04 11:32 
GeneralArticle copy of NIIT's (India) education material or Vice-Versa Pin
kalyankrishna122-Apr-04 0:33
memberkalyankrishna122-Apr-04 0:33 
GeneralGreeting Pin
anonymous27-Mar-04 3:24
sussanonymous27-Mar-04 3:24 
Generalwarning C4146 Pin
khamis18-Feb-04 3:15
memberkhamis18-Feb-04 3:15 
GeneralErrors and Exceptions Handling using ATL Pin
gertano1-May-03 13:41
membergertano1-May-03 13:41 
GeneralFirebird, C++ and ADO Pin
Anonymous1-Apr-03 0:35
sussAnonymous1-Apr-03 0:35 
GeneralAdding Write recordset function Pin
harryn448418-Mar-03 10:30
memberharryn448418-Mar-03 10:30 
GeneralRe: Adding Write recordset function Pin
Amit Dey21-Mar-03 12:36
memberAmit Dey21-Mar-03 12:36 
GeneralHelp with input param Pin
dmyoung13-Dec-02 3:58
memberdmyoung13-Dec-02 3:58 
GeneralGet error in VB Pin
robbery24-Oct-02 21:40
sussrobbery24-Oct-02 21:40 
GeneralRe: Get error in VB Pin
duyanning19-Jan-03 17:08
sussduyanning19-Jan-03 17:08 
GeneralRe: Get error in VB Pin
rewaccco2-Dec-03 5:00
memberrewaccco2-Dec-03 5:00 
GeneralLink Error Pin
Harvail20-May-02 6:45
memberHarvail20-May-02 6:45 
GeneralRe: Link Error Pin
Amit Dey7-Jun-02 10:58
memberAmit Dey7-Jun-02 10:58 
GeneralATL DCOM SERVICE Pin
RAMESH_egate21-Feb-02 20:36
memberRAMESH_egate21-Feb-02 20:36 
GeneralBuild errors Pin
Anonymous20-Feb-02 5:31
memberAnonymous20-Feb-02 5:31 
GeneralRe: Build errors Pin
jonsi15-Mar-02 6:01
memberjonsi15-Mar-02 6:01 
GeneralRe: Build errors Pin
jonsi15-Mar-02 6:06
memberjonsi15-Mar-02 6:06 
GeneralRe: Build errors - solution Pin
marcinj25-Nov-03 4:24
membermarcinj25-Nov-03 4:24 
GeneralOpen recordset statement Pin
Mahmood Rahman7-Feb-02 23:54
memberMahmood Rahman7-Feb-02 23:54 
Generalcode help Pin
gpshady16-Jan-02 4:14
membergpshady16-Jan-02 4:14 
GeneralRe: code help Pin
Amit Dey19-Jan-02 4:13
memberAmit Dey19-Jan-02 4:13 
QuestionWhy do you select support MFC library in your FindCust ATL sample? Pin
Angus Comber5-Jan-02 5:31
memberAngus Comber5-Jan-02 5:31 
AnswerRe: Why do you select support MFC library in your FindCust ATL sample? Pin
Amit Dey19-Jan-02 4:09
memberAmit Dey19-Jan-02 4:09 
GeneralUrgent :Help to find Book Pin
Ravikkatira14-Dec-01 0:46
memberRavikkatira14-Dec-01 0:46 
GeneralRe: Urgent :Help to find Book Pin
Jon Sagara14-Dec-01 3:32
memberJon Sagara14-Dec-01 3:32 
QuestionMempry Leak in ADO? Pin
yogz21-Nov-01 14:38
memberyogz21-Nov-01 14:38 
AnswerRe: Mempry Leak in ADO? Pin
Anonymous23-Oct-02 15:58
sussAnonymous23-Oct-02 15:58 
GeneralIt Urgent ... Help Me Pin
Anonymous12-Nov-01 19:20
memberAnonymous12-Nov-01 19:20 
GeneralRe: It Urgent ... Help Me Pin
WhyIsThisSo10-Dec-01 12:25
memberWhyIsThisSo10-Dec-01 12:25 
GeneralRe: It Urgent ... Help Me Pin
Anonymous21-Feb-02 12:28
memberAnonymous21-Feb-02 12:28 
GeneralRe: It Urgent ... Help Me Pin
Anonymous15-May-02 11:43
memberAnonymous15-May-02 11:43 
GeneralRe COM & ADO Pin
Harsh Parikh3-Aug-01 10:40
memberHarsh Parikh3-Aug-01 10:40 
GeneralRe: Re COM & ADO Pin
Amit Dey15-Feb-02 8:44
memberAmit Dey15-Feb-02 8:44 
GeneralRe: Re COM & ADO Pin
bibilm25-Sep-02 4:46
memberbibilm25-Sep-02 4:46 
GeneralRe: Re COM & ADO Pin
Amit Dey26-Sep-02 5:58
memberAmit Dey26-Sep-02 5:58 
GeneralRe: Re COM & ADO Pin
bibilm26-Sep-02 6:15
memberbibilm26-Sep-02 6:15 
General2 EXE and 1 COM between Pin
Anonymous4-Jun-01 2:42
memberAnonymous4-Jun-01 2:42 
GeneralRe: 2 EXE and 1 COM between Pin
Kopi11-Jun-01 11:15
memberKopi11-Jun-01 11:15 
Generalvariant_t : the correct way Pin
Amit Dey2-Jun-01 1:38
memberAmit Dey2-Jun-01 1:38 
Generaldll error Pin
murali31-May-01 6:10
membermurali31-May-01 6:10 
mr amit
iam a programmer and into atl programming only few months ago, i saw your tutorial for atl with ado, i was able to build the dll with out error or warnings but when i use the dll in vb project, is is saying dll load error, why is this happening, iam using mdb database, and the only change i made is to data source and the provider.so kindly let me know the possible reasons for error as soon as possible.



Generalole db in vc6 Pin
Ivgeny Broitman22-May-01 22:09
memberIvgeny Broitman22-May-01 22:09 
Generalerror LNK2001: unresolved external symbol Pin
Anonymous11-May-01 4:22
memberAnonymous11-May-01 4:22 
GeneralRe: error LNK2001: unresolved external symbol Pin
ganeshwalavalkar1-Jun-01 4:13
memberganeshwalavalkar1-Jun-01 4:13 
QuestionHow does the .idl file should be look like? Pin
Itsik saban10-Apr-01 22:16
memberItsik saban10-Apr-01 22:16 
AnswerRe: How does the .idl file should be look like? Pin
Amit Dey10-Apr-01 23:07
memberAmit Dey10-Apr-01 23:07 
AnswerRe: How does the .idl file should be look like? Pin
ganeshwalavalkar1-Jun-01 4:15
memberganeshwalavalkar1-Jun-01 4:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150603.1 | Last Updated 4 Apr 2001
Article Copyright 2000 by Amit Dey
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid