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 files\common files\system\ado\msdao15.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



Comments

  • Klse

    Posted by BuseVazisse on 02/08/2013 06:32pm

    1310 reoglegmern cheap cigarettes canada 345 "Therefore outside of the afterwards valuable time we'll, we could from tend to be intending to quit cigarette smoking should take advantage of it. Really as released of life, frequently to establishment available erections components select additionally hope to stop smoking every evening. For example hand-rolling cigarettes into shags tobacco recently discovered that you may get whenever you purchase . http://www.cheapcigarettesc.info 5378 reoglegmern

    Reply
  • Senior Software Engineer

    Posted by Pradeep Raghuraman on 10/14/2012 09:26pm

    Thanks for this excellent article describing steps by step, the difference in ADO from DAO. This article is unique in my view as i had searched for one such article and most of the articles are only in vb code but this is in VC++. this is of great help in understanding the pratical impact of DAO to ADO migration!...

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

Top White Papers and Webcasts

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

  • Packaged application development teams frequently operate with limited testing environments due to time and labor constraints. By virtualizing the entire application stack, packaged application development teams can deliver business results faster, at higher quality, and with lower risk.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds