ADO is AOK (a simple ADO tutorial)

Environment: Demo written in Visual C++ 6.0

We are all familiar with the CDatabase classes, and the
CDaoDatabase classes. They are relitivly simple to use, they mesh
well with our applicaiton and they are easily distributed. Using
something like the GenericRecordset class (found here ar code
guru) makes it even easier to create powerful database aplication
with little effort on your part.

Now just as you felt safe to start doing database development
in C++, Microsoft pushes something called UDA (Universal Data
Access) and a relitivly simple but mostly undocumented object
called ADO (ActiveX Data Object). Unfortunatly they felt that
there is little need to show any kind of documentation for us
Visual C developers. If you try to search around you will be hard
pressed to find any good examples. Even here at code guru there
is only 1 other article about ADO.

So after several months of playing around with ADO, I finally
feel good about it, and will probably never use anything but ADO
for all huturd development. When you see how easy it is to use
ADO, and how powerfull it is (can we say thread safe?) you will
probably never go back either. When I wrote the GenericRecordset
for DAO, it was to provide a simple way to use dynamic data
binding. That is, to allow me to base a recordset on a query
rather than a table. One of the things I liked about ADO from the
start was that this, dynamic data binding, is the default way ADO
works.

Getting Started:
Before you can use ADO, you have to let your application know you
are going to use it. In your stdfx.h file you need to add the
following code:

#import "c:program filescommon filessystemadomsdao15.dll" no_namespaces rename("EOF" adoEOF")

This line basicly says, use ADO, but do not use name spaces,
and change EOF to adoEOF. You have to rename EOF because of a
nasty conflict of constants. Once you have done this then there
are no include files, and nothing else you have to link to your
application.

Look at the pieces, _ConnectionPtr, _CommandPtr, and
_RecordsetPtr (no _CommandPtr in this article)
ADO, much like its counterparts DAO and CDatabase, comes
in several pieces. Unlike its counterparts, ADO is COM based. An
interesting thing about learning about ADO is that you are forced
to learn a little about COM. (This is a good thing because as a
windows developer you will be headed down that road sooner or
later.) The three pieces of ADO are the Connection, the Command,
and the Recordset.

The Connection returns a recordset or a NULL. You generally
use this to connect to the database, and/or to run SQL statements
that do not return anythng. While the Conection object can return
a recordset, it is not the best way to use it. Much like
CDaoDatabase, you (for the most part) make this your connection
to the database, then use other objects to perform your data IO.

The Command returns a recordset, and allows you a simple way
to run stored procedures, or any SQL statement that returns a
recordset. You can either use your global Connection to the
database with this, or you can tell it the connectionstring when
you open your command. This is a nice feature if you are doing a
one time connection. But if you are going to be create a lot of
recordsets, then you will want to use the Connection Object to
connect to the databse, and this object to run Stored procedures.

And the Recordset is, well, a recordset. It provides a little
more control over the recordset (like locking, cursors etc…)
than the other 2 methods do. As with the Command object, you do
not need to have an open conneciton. If you include the
connection string rather than a pointer to the conection object,
you can use the Recordset object by it’s self. The best way, if
you are using multiple recordets is to use the Connection object
to connect to a database, then the recordet object to make your
recordsets. Two pieces I will talk about in this article are the
Connection and the Recordset.

_ConnectionPtr, this is the connection
interface. It is similar to CDatabase or CDaoDatabase. It basicly
works the same way. You create an instance of it, point it to a
database either through ODBC or a provider, and you open it. Look
how similar it is to CDaoDatabase:

CDaoDatabase MyDb = new CDaoDatabase();
m_DaoServerDB.Open(NULL,FALSE,FALSE,"ODBC;DSN=SAMS_SVR;UID=admin;PWD=admin");
Now using ADO:
_ConnectionPtr MyDb;
MyDb.CreateInstance(__uuidof(Connection));
MyDb->Open("DSN=SAMS_SVR;UID=admin;PWD=admin","","",-1);

_RecordsetPtr, This is the recordset
interface. It is similar to CDaoRecordset. Again when you see how
similar it is to CDaoRecordset you will wonder why you did not
use it sooner. Lets see how they work: (We will use the database
and the connection above in our example.)

CDaoRecordset MySet = new CDaoRecordset(MyDb);
MySet->Open(AFX_DAO_USE_DEFAULT_TYPE,"SELECT * FROM some_table");

Now using ADO:

_RecordsetPtr MySet;
MySet.CreateInstance(__uuidof(Recordset));
MySet->Open("SELECT * FROM some_table",
	MyDb.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);

ADO is slightly more involved. But with
some of the added benifits you get with ADO (most of which are
beyond the scope of this article) ADO is worth the extra effort
here.

Now that we have a Connection and a
Recordset, lets get some data out of it. In both cases we are
going to fill a list box with information in the recordset.
(Assuming we have a listbox called m_List)

DAO:
VARIANT *vFieldValue;
COleVariant covFieldValue;
CString Holder;
while(!MySet->IsEOF())
{
MySet->GetFieldValue("FIELD_1", covFieldValue);
vFieldValue = (LPVARIANT)covFieldValue;
if(vFieldValue->vt!-VT_NULL)
{
Holder.Format("%s",vFieldValue->pbVal);
m_List.AddString(Holder);
}
MySet.MoveNext();
}

ADO:
_variant_t Holder
while(!MySet->adoEOF)
{
Holder = MySet->GetCollect("FIELD_1");
if(Holder.vt!=VT_NULL)
m_List.AddString((char*)_bstr_t(Holder));
MySet->MoveNext();
}

A special note. There is NO documentation for the GetCollect
method. I have searched everywhere, and no one mentioned it. the
other method of retrieving data would be:

Holder = MySet->GetFields->Field->(_variant_t(FieldNumber))->Value;
I like the GetCollect better.

Dynamic Binding vs DFX:
Dynamic binding allows you to create a recordset on any
legal SQL statments that returns something. For example, imagine
trying create a recordset using DFX that will allow you to sum
several fields:

SELECT (SUM(field_1) + SUM(field_2)) AS answer FROM some_table

Not an easy task (if do’able at all)
This is one of the advantages of dynamic data binding. Another is
the decrease in code. This makes your aplication smaller and
easier to maintain. And lastly, according to Microsoft, it is the
prefered way of retreiving data from a data source. So dynamic
binding is more flexable, faster, and easier to maintain, could
we ask for amything more?

With most application you can have a
single (global) connection to a database, then create recorsets
to your hearts content. And if you have written applications with
a lot of recordsets, then you know the amount of added code that
DFX adds to your application. All of this code is gone with
dynamic binding.

What the heck is _variant_t and
_bstr_t ?
Unfortunatly our CString class is gone with COM. (sigh,
so is CStringEx). Because COM has to cross development platform
it needed a more generic way to handle strings, and other data.
This was answered by the VARIANT data type, and the BSTR data
type. The VARIANT is basicly a huge union of every type of data
you can think of (except char*). The BSTR is basicly a string
with a size attached (a replacement for char*).

These can be a royal pain in the fanny
to work with. So enter _variant_t and _bstr_t.
Basicly, (very basicly) the _variant_t class wraps the VARIANT
data type and allows us to simply cast the data as a type. This
simplifies using a VARIANT. The _bstr_t does the same thing with
a BSTR. In the example you can see how I get a VARIANT from the
Recordset GetCollect method, then put it ito a _bstr_t, then cast
it to a char*, or I cast the _variant_t as a long, or a double,
or whatever….

_variant_t Holder;
// first get the VARIANT and put it into the _variant_t
Holder = MySet->GetCollect("FIELD_1");
// now put it into a _bstr_t and cast it to a char*
m_List.AddString((char*)_bstr_t(Holder));

Compare that with what you have to do
with out the _variant_t and _bstr_t

COleVariant covFieldValuel
VARIANT vFieldValue
CString Holder;
MySet->GetFieldValue("FIELD_1", covFieldValue);
vFieldValue = (LPVARIANT)covFieldValue;
Holder.Format("%s",vFieldValue->pbVal);
m_List.AddString(Holder);

Quite a difference.

Update, Insert, and Delete:
When I perform an update, insert or delets I usually
like to use the Connection object or the Command object. The
reason for this is that is seems simpler to create the SQL
statement in a CString, then use the Execute method. But you can
to all three with the Recordset object.

Update Method can update based on one of
these three parameters:
1: Assign values to a Field object’s Value property and call the Update
method.
2: Pass a field name and a value as arguments with the Update
call.
3: Pass an array of field names and an array of values with the Update
call.

AddNew method takes an array of fields, and a
mathcing array of values.

Delete MethodWill delete either the current
record, or the records based on the curent filter.

In all three methods you may need to ReQuery to see the
results.

Sample Code:
The sample code I have included with this article is a
simple MFC application. In CWinApp, I declare the _connectionPtr,
_CommandPtr, and _RecordsetPtr interfaces.

// Global ADO Objects
// connection
_ConnectionPtr	m_pConnection;
_CommandPtr	m_pCommand;
_RecordsetPtr	m_pRecordset;

It is interesting to note that (in
VC6.0) if you type "
m_pConnection." you will get a list of functions and
members, if you type "
m_pConnection->" you get a totally different set of
functions and members. This is because you are actually pointing
to 2 different things. If you are looking at the "."
methods you are looking at the smart pointer methods. If you are
looking at the "->" methods then you are looking at
the ones from what ever you created (_ConnectionPtr etc…). This
is also why you see 2 line, one using the "." and one
using the "-> right after eachother.

_ConnectionPtr MyDb;
MyDb.CreateInstance(__uuidof(Connection));
MyDb->Open("DSN=SAMS_SVR;UID=admin;PWD=admin","","",-1);

Back to the sample code. In the init
instance of the application I open the connection. It is pointing
to a database on my system. You will have to change this to a
database (ODBC) on your system or use one of the UDA providers to
a database.

// When we open the application we will open the ADO connection
m_pConnection.CreateInstance(__uuidof(Connection));
m_pConnection->Open("DSN=ADOTest","","",-1);

If you open the about dialog you will
see a listbox. You will see a button called button 1, this is
where the meat of the ADO is located. I create instances of the
recordset interface, open the recordset based on the query I want
to use, then loop through the records:

_variant_t TheValue;
theApp.m_pRecordset.CreateInstance(__uuidof(Recordset));
try
{
	theApp.m_pRecordset->Open("SELECT DISTINCT FLDESC FROM tblFALines",
		theApp.m_pConnection.GetInterfacePtr(),
		adOpenDynamic,
		adLockOptimistic,
		adCmdText);

	while(!theApp.m_pRecordset->adoEOF)
	{
		TheValue = theApp.m_pRecordset->GetCollect("FLDESC");
		if(TheValue.vt!=VT_NULL)
		m_List.AddString((char*)_bstr_t(TheValue));
		theApp.m_pRecordset->MoveNext();
	}
	theApp.m_pRecordset->Close();
}
catch(_com_error *e)
{
	CString Error = e->ErrorMessage();
	AfxMessageBox(e->ErrorMessage());
}
catch(...)
{
	MessageBox("Whoa this is bad");
}

Remember to use try and catch, ADO will
crash your application is a heart beat if you don’t! You will
need to catch _com_error and … all the time.

I have tried to keep this as simple as I
can. I have omitted many details, over simplified things, and
ignored some good coding practices. (like checking the HRESULT
that most COM methods return) The goal of this article is to show
you that ADO (and COM in general) is nothing to fear, not to show
you everything ADO can do. I have not even scratched what ADO can
do for you. It is fast, easy to use, and much more powerful than
DAO. Take a look at the article here at code guru about how easy
it is to use ADO to access stored procedures.

There are 2 good books out there that
will get you started. One of them you can read the entire book
for FREE (thats FREE) at
www.mcp.com. The
other you have to buy. (I would buy both books unless you have a
computer in your bathroom, and next to your bed.)

The books are ADO 2.0 by WROC press.
This book, like so many others, has very FEW C++ examples. But it
is a great reference for all the methods and properties. After
you get use to using ADO porting the examples to C++ is relitivly
simple. The second book is Learn Database Programming with Visual
C++ in 21 days. Yes, I know those "in 21 days" books
usually stink, and are really embarasing to buy when there are
other developers around you, and you try to hide them on your
book shelf. But this one is an exception to the rule! The
information in it is great. Also you can read the ENTIRE BOOK on
line at
www.mcp.com. Both are worth the price. The 1st half of the
"21 Days" book deals directly with ADO and how to use
it (good stuff). The last half gets into database design and such
(ok, material).

Bob Place

Download demo project – 39 KB

Date Last Updated: April 4, 1999

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read