ADO at a glance

• Download demo project - 13 Kb
• Download sample database - 13 Kb

Introduction

Microsoft ActiveX Data Objects (ADO) allow you to manipulate and store your data in a database server through an OLEDB provider, and as it provides dual interface you are able to use it from both scripting languages such as VBScript and JavaScript as well as C++. It has so many advantages such as high speed, low memory overhead and the most important one, ease of use.

In this brief article I'm going to describe you how to start working with ADO, for instance, how to open a recordset, query the database or execute a stored procedure.

First of all let me inform you about the requirements. In this article, I used ADOTestDB.MDB as a sample database in conjunction with VC++ 6 (SP3) and ADO 2.1.In the database file I implemented two simple tables, named Student and Dept with different fields of different types and some sample data (You're able to download it from the above link). One more think that i should mention here is that I'm using import directive and gain features of smart pointers in this article.

By typing the following lines in your stdafx.h you simply can do the same too.

  	#import "msado15.dll" \
	no_namespace \
  	rename( "EOF", "adoEOF" )

If you mention above code you'll get that I renamed the EOF to adoEOF, doing so will prevent your application from some nasty conflicts in future which will waste your time to find out the reason.

After doing so you need to initialize the COM before doing anything. In order to do that, call the CoInitialize(NULL) function somewhere at the beginning of your program and call CoUninitialize() after doing your job with ADO.

Now we are going to the main part of using ADO:

Making a connection to the database server

The first step to access a data source in ADO is connecting to the data source. In order to connect to a data source you can use ADO's Connection object. The main way ADO establishes a connection is through the Connections Open member function. Take a look at the following code (Error handling omitted because of code brevity):

	_ConnectionPtr m_pConn;
	
	m_pConn.CreateInstance (__uuidof(Connection));
	m_pConn->Open ( 
	_bstr_t ( "Provider=Microsoft.Jet.OLEDB.4.0;
		Data Source = ADOTestDB.MDB" ),
	_bstr_t ( "" ),
	_bstr_t ( "" ),
	adModeUnknown );

That is a sample code snipped to establish a connection to the ADOTestDB.MDB via Microsoft.Jet.OLEDB.4 provider. First of all you have to define an object of type _ConnectionPtr (ADO Connection) then you should instantiate it and last, issue the Open function. The first parameter of Open member function is connection string. This parameter specifies the data source you are trying to connect as well as the OLEDB provider for it. Next two parameters specify the UserName and Password for logging on to the database. Our sample database doesn't have any users defined so they can be empty strings, and last parameter is Options which determines if the open must work as a synchronous or asynchronous function. You can simply type adModeUnknown if you don't know much about this feature as I did.

It's not bad to mention that you can also use ODBC drivers to connect to a data source, the following code will show you how to do it :

	m_pConn->Open (_bstr_t ("DSN=pubs;uid=sa;pwd=;"),
		_bstr_t (""),
		_bstr_t (""),
		adCmdUnknown );

Representing an SQL statement

There is different ways to represent an SQL statement in ADO. The most regular way is using a Command object. Command is an instruction understood by data provider to modify, manage and manipulate data source which is typically written in SQL. Now we are going to query our sample data source for all students in the Student table. The code looks like the following:

	_CommandPtr pCommand;
	pCommand.CreateInstance (__uuidof (Command));
	pCommand->ActiveConnection = m_pConn; // Formerly opened connection

                                              // pointer

	pCommand->CommandText = "Select * From Student";

As you see above, at first, we declared a Command object then instantiated it. As a next step, we set the ActiveConnection property of the command object by previously opened connection object, m_pConn. The CommandText property of command object represent the query, SQL statement or stored procedure name, which will be issued against the provider, in our case it's the simple query statement, "Select * From Student".

As our command text is a query, by issuing, it would return a set of rows which we should store somewhere. For this purpose we will define a Recordset object which will store the returned rows and in addition will let you manipulate these rows.

Now, we are going to execute this command, but let me mention something as before. There is two ways to execute a command in ADO, first is through the Command objects Execute member function and the other is using Open member function of Recordset object. Here we do it via Recordset's Open, as below:

	_RecordsetPtr pRecordset;
	pRecordset.CreateInstance (__uuidof (Recordset));
	pRecordset->CursorLocation = adUseClient;
	pRecordset->Open ( (IDispatch *) pCommand, vtMissing, adOpenStatic,
	adLockBatchOptimistic, adCmdUnknown);

In the snipped code we first defined an object of type Recordset and then instantiated it. Recordset object in ADO has a dozen of valuable properties for different purposes, the one we used here is CursorLocation, which let you specify the cursor's location usually between client side and server side one. After doing these you can call the Open function in order to issue the command against the data source. First parameter of Open member function specifies a variant evaluated to a Command object, an SQL statement, a Table name, a Stored procedure call or a URL known as a Source. Second parameter is an active connection and is optional. As we specified our active connection in Command object, it doesn't require to specify it again, just simply miss the parameter (In VC++ whenever you need to specify a missing parameter whose type is Variant, specify a _variant_t with a value of DISP_E_PARAMNOTFOUND and a type of VT_ERROR. Alternatively, specify the equivalent _variant_t constant, vtMissing, which is supplied by the #import directive). Third and forth parameters are cursor type and lock type.The lock type of adLockBatchOptimistic is specified because of batch processing we'll use here, and because this processing requires cursor services, we specified the cursor location before. The last parameter indicates how the provider should evaluate the Source argument if its something other than a Command object, but our source is exactly a command object here, so simply type adCmdUnknown as the last parameter. Now, after issuing the Open function, you'll have all students specification in your Recordset object.

Manipulating the Data

At this point, we are going to make some changes to the data. One of the fields in Student table is SocialSecNo which shows each students social security number, consider by some problem the government faced, it has to change the social security numbers starting with '45' to something like '77'. So, we have to change all the SocialSecNo starting with '45' to '77'.In order to do this we filter the current recordset for all SocialSecNo starting with '45'. Moreover, we'll set StudentNo field as an index in the recordset so as to increase the sorting and filtering performance.

Here you might think that its not really efficient to do something such as the following, and it's somehow correct depending on the situation but my most important purpose here is, introducing different capabilities of ADO, so don't think about efficiency and injoy using ADO's features.

The code for manipulating data looks like this:

	pRecordset->Fields->GetItem ("StudentNo")->Properties->
			GetItem ("Optimize")->Value = VARIANT_TRUE;
	
	pRecordset->Sort = "Name";
	pRecordset->Filter = "SocialSecNo LIKE '45*'";
	
	while (!pRecordset->GetadoEOF())
	{
		CString str = (char *) (_bstr_t) pRecordset->Fields->
			GetItem("SocialSecNo")->Value;
	
		str = "77" + str.Right(str.GetLength() - 2);
		pRecordset->Fields->GetItem("SocialSecNo")->Value = 
                     (_bstr_t) str;
		pRecordset->MoveNext();
	}
	
	pRecordset->Filter = (long) adFilterNone;

In the above code at first we set the Optimize property of StudentNo field to make it an index in the recordset, then we've sorted the recordset by Name filed and filtered it for records which SocialSecNo starts with '45'. In the while loop we just simply changed the SocialSecNo value to its new one and moved the current position to next record in the recordset.As you change the SocialSecNo to its new value it no longer matches the filter criteria so its invisible in the recordset.For reappearance of records we should remove the filter at the end, and its precisely what I did at the last line of the code.

Updating the data

There are generally two ways of updating the data in ADO. The first method is immediate update, it does mean that you make changes directly to the recordset and therefore data source as soon as you issue the Update function. But the second method is known as the Batch mode update. If you open your recordset by adLockBatchOptimistic lock type, ADO lets you update your changes in Batch mode. In Batch mode, every navigation over a record or call to Update function on the current record saves changes on a copy buffer and it's just after calling BatchUpdate function that changes propagates to the data source.

In our example, we're using Batch mode so you can propagate changes with the following simple line of code:

    pRecordset->BatchUpdate (adAffectAll );

Now you learnt how to open and query for some data and then manipulation and update them. But there are really many more things in ADO which you must know about, one of the most important one among them is called stored procedures and parameterized commands.

Executing an Stored Procedure with input parameters

Calling an stored procedure is as easy as opening a new recordset such as the one we did in this article formerly. There is just some minor additions for creating input parameters and assigning their values, which we're going to discuss in the following lines. There are two general ways of passing parameters in ADO, first one is through Parameter object and the second one is via Refresh method, but I always prefer the first method because of the performance privileges it has over the second one.

Here, I'll describe first methods for you. The following lines of code shows you how to set the input parameters and execute the Query1 stored procedure in our sample database using Parameter object.

	_CommandPtr pCommand;
	_ParameterPtr pParam1, pParam2;
	CString str("Ma%");

	pCommand.CreateInstance(__uuidof(Command));
	pCommand->ActiveConnection = m_pConn; //Its the connection pointer

                                              //we opened formerly

	pCommand->CommandText = "Query1";
	pCommand->CommandType = adCmdStoredProc;
	pParam1 = pCommand->CreateParameter ( _bstr_t ("DeptID"), adTinyInt, 
				adParamInput, sizeof (BYTE), 
                                _variant_t ( long (11)));

	pCommand->Parameters->Append ( pParam1);
	pParam2 = pCommand->CreateParameter ( _bstr_t ("Name"), adVarChar, 
				adParamInput, str.GetLength (),
                                (_bstr_t) str);

	pCommand->Parameters->Append ( pParam2);
	_RecordsetPtr pRecordset;
	pRecordset.CreateInstance(__uuidof(Recordset));
	pRecordset = pCommand->Execute(NULL, NULL, adCmdStoredProc);

At the beginning, create a Command object then set the ActiveConnection property to an already opened connection, next, specify your stored procedure name in CommandText and assign the adCmdStoredProc value to the CommandType property of your Command Object. As your stored procedure has two input parameters, so declare two Parameter objects and then, create them by calling CreateParameter method of Command object. The first argument of CreateParameter refers to an optional name for your parameter and the second specifies type of the parameter, the third one determines the direction of the parameter which is input in our case. Forth argument stands for the length of the variable and the fifth one specifies the value of the parameter.

After specifying arguments, the created parameters respectively will be assigned to the pParam1 and pParam2.The created parameters then should be added (Appended) to the Parameters collection of our Command object, so as to do that we call the Append method for each Parameter object over Parameters collection. Now, your command is ready for execution.

If you mention to the Query1 stored procedure in our sample database, you'll get that its a simple query statement which will return a rowset (recordset), so after execution of the command object it'll probably return a recordset that we can store in a Recordset object easily, as we did above.

Error handling mechanism

In all codes snipped in this article so far, I didn't care about the error handling because of code brerity.At this point, I'm going to mention one simple example of error handling that you can use in all over your program wherever you use some ADO's critical functions. Generally, becuase ADO made of COM objects so it produces exceptions of type _com_error whenever an error occurs at runtime. You, by putting your ADO function calls in a try - catch block are able to handle the generated exceptions. Take a look at the following code :

	try
	{
		HRESULT hr = m_pConn.CreateInstance(__uuidof(Connection));
		if (FAILED( hr ))
			AfxMessageBox(
                            "Can't create an intance of ADO.Connection" );
 
		if (FAILED( m_pConn->Open( 
			_bstr_t(
                            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = 
			ADOTestDB.MDB"), _bstr_t( "" ), _bstr_t( "" ), 
			adModeUnknown )))
			
			AfxMessageBox( "Can't open datasource" );
 		...
		...
		...

		m_pConn->Close();
	}
	catch( _com_error &e )
	{
		_bstr_t bstrSource(e.Source());
		_bstr_t bstrDescription(e.Description());
		TRACE( "Exception thrown for classes generated by #import" );
		TRACE( "\tCode = %08lx\n", e.Error());
		TRACE( "\tCode meaning = %s\n", e.ErrorMessage());
		TRACE( "\tSource = %s\n", (LPCTSTR) bstrSource);
		TRACE( "\tDescription = %s\n", (LPCTSTR) bstrDescription);
	}
	catch (...)
	{
		TRACE ( "*** Unhandled Exception ***" );
	}

Consider in the above code the Open function call can't find the specified mdb file in the folder, so it'll generate an exception determining the kind of error and error code, in addition to a brief description about the error.

Comments

It was a brief explanation of some ADO features. There are really a plenty of other things in ADO which you should learn if you want to become an expert in this subject. Here, I tried to give you some clues to start learning ADO and developing useful applications using it, I'm also working to issue more articles in near future about this subject, discussing more interesting and unique features.

If you have any suggestions ,recommendation or questions please feel free to ask it. I would be glad to hear the article's weaknesses as well as usefulness. You can reach me using email at shokuie@hotmail.com.