Efficient data processing with ADO in C++: Method GetRows

• Download demo + library source files - 76.8 Kb
• Download demo binary - 18.2 Kb

Introduction

This library is a way for C++ applications to process ADO recordsets via Safe Arrays supported by ADO. It uses method GetRows of the ADO Recordset interface to retrieve a chunk of data, and allow simple processing of it as a bi-dimensional array, similar to that in Visual Basic.

Advantages offered by the library:

1. Converting ADO data types into C++ native types. The library also implements automatic conversion of binary columns into any ready-to-use format, such as data stream, data array or even an image object ready for displaying: IStream, ISequentialStream, IPicture or HBITMAP.
2. It provides a platform for the fastest possible data processing that an ADO database application can possibly have. It is very efficient for heavy computations in ADO C++ applications that require to run complex statistical analysis or do other data processing. For instance, it is ideal for back-end applications that need to do heavy data analysis via ADO to generate online client documents.
3. The class implements very efficient ADO usage in C++ in terms of memory usage and CPU load via Selective Data Processing, i.e. selective column reading.
4. Substantially fewer steps are required with this library to access data via simple and safe {row, col} logic of addressing the value (there is no need to move through recordsets with commands like MoveNext or to control where the recordset cursor is).
5. Fail-proof mechanism guarantees that any problem occurring during data processing is gracefully handled via C++ events of the class.
6. Hiding away all the complexity of ADO Safe Arrays, so troublesome for C++ developers. Also with this library there is no need to be using COM Smart Pointers to access data from ADO.

Background

In the world of today's C++ developers for Windows, the word database usually associates with one of two things: OLEDB or ADO, don't mind me burying DAO today :) I found it from my extensive practice that most developers choose ADO over OLEDB because most of the database tasks are not as much speed-critical to justify the time stretch that OLEDB usage might imply, compared to ADO. Simplicity and time frame usually dictate the choice. I have been using ADO in many projects over years, and found it the most practical choice, apart from A-hypothetical situation when my application would not be fast enough - I just never came across that. And even if you did, this library will also enable you to expand ADO speed frontiers quite a bit.

In this article, I intend to show there is yet another angle to ADO that with a little help could make ADO look even more attractive to C++ developers that seek speed and agility for their data processing.

ADO offers three ways to access data:

1. Recordset-based method, familiar to most database developers when we access data by moving the recordset cursor into the next position;
2. Safe Arrays, when a chunk of data can be ordered from the recordset for direct processing using array indexes;
3. String method via method GetString.

The first approach is what has always been out there, and as for this article, means no interest to us whatsoever. The third approach is more appropriate for script languages where working with strings is preferable. It is the second approach that I've been dancing around on many occasions, curious to get it to work in my C++ applications the right way.

You can find a good consideration for all three methods of reading data in the following article.

The key to ADO Safe Arrays is in the method GetRows of the ADO Recordset interface. It exposes recordset data via Safe Array presentation so it becomes possible to use simple indexing logic of a bi-dimensional array to access data-containing cells. This Safe Array feature of ADO was created specifically to be used in scripting languages (primarily in Visual Basic). In fact, if we run a search in the internet for what there is out there on method GetRows, only Visual Basic examples will pop up. And just for the fullness of this research, here's a list of some links that you might come across:

• GetRows! ... Don't retrieve data any other way!
• Database GetRows sample.
• The GetRows Method.
• ADO Recordset Object GetRows Method.

If you look up through those examples, you will find mostly pros and no cons to this approach. Now the obvious question should be: Why on earth nobody is using ADO via Safe Arrays in C++? The answer is simple, and comes from the reason this feature was developed in the first place - Visual Basic. That's it, no provision for C++, as long as it complies with COM Automation and works in VB - it is just fine right there:) And to clarify what's all the trouble about, using multidimensional Safe Arrays in C++ is a developer's nightmare, to put it simply.

As I struggled to use ADO Safe Arrays in my C++ applications, I eventually made up my mind and set on a thorny path to change such a situation upside down so to never happen again. This library I'm offering is the result of my quest, fully open to your judgment. The library hides away all the complexity of Safe Arrays, COM Smart Pointers and awkward cursor operations, to make usage of ADO in C++ never as simple, efficient and graceful.

Using the code

The whole library is, basically, just one class CNCQuickADO that does it all. It contains one embedded class CColumns to manage Safe Arrays of columns to be passed into the method GetRows. In this article I tried to use a little different approach to documenting the class, and put all my efforts into the detailed documentation of the header and implementation files. There's truly, very little I could add to those big comments that I put against each declaration entity in the file NCQuickADO.h, so I see little point to even trying to do that. Of course, I might be wrong there, so we will see. For those feeling lazy to open the header file, there is also an HTML version of the class declaration.

You will find very good documentation in the demo application as well. The demo application (see screenshot above) is a simple ADO client that asks for an ADO Connection String, SQL Query, and displays all the data produced by that query in a list control. Code there is simple enough to skip any additional comments on it here, so I'll just underline the main usage concept.

You create an ADO Recordset object, open it and then pass into the class CNCQuickADO for processing like shown in the example below:

// pRecordset is of type ADODB::_Recordset*
CNCQuickADO rs(pRecordset);
// That's it, all data soaked in, and object pRecordset
// can be released at this point;

There are four optional parameters that also can be passed into the class (see in the class declaration). Let's walk through a few more examples of passing data into the class;

• to get only the first 100 records:

  // pRecordset is of type ADODB::_Recordset*
  CNCQuickADO rs(pRecordset, 100);
  // That's it, all 100 records have been read in,
  // and what happens with any remaining records is up to you;

• to get all records, but only columns "ID" and "Name" from the recordset:

  // pRecordset is of type ADODB::_Recordset*
  CNCQuickADO::CColumns cols(_T("ID"), _T("Name"), NULL);
  CNCQuickADO rs(pRecordset, 0, ADODB::adBookmarkCurrent, cols);
  // That's it, all records have been read in for the
  // selected columns, and object pRecordset
  // can be released at this point;

Now let's consider how data can be accessed from the class.

• to go through all records and read Long from the first column and Text from the fifth column:

  // rs is an initialized object of type CNCQuickADO;
  long nRows = rs.GetNumberRows(); // Get number of rows;
  for(long i = 0;i < nRows;i ++) // Go through all records;
  {
      long lValueID;
      _bstr_t strText;
      rs.GetLong(i, 0, lValue); // Read LONG from the first column;
      rs.GetText(i, 4, strText); // Read Text from the fifth column;
      // Process data here...
  }

• read only column "Photo" for all records, which is a bitmap file:

  // pRecordset is of type ADODB::_Recordset*
  CNCQuickADO::CColumns cols(_T("Photo"), NULL); // Column filter;
  CNCQuickADO rs(pRecordset, 0, ADODB::adBookmarkCurrent, cols);
  long nRows = rs.GetNumberRows(); // Get number of rows;
  for(long i = 0;i < nRows;i ++) // Go through all records;
  {
      HBITMAP hBitmap = (HBITMAP)rs.GetBinary(i, 0, bfBitmap);
      // Get ready-to-display bitmap;
  
      // Process the bitmap here...
  }

Now let's consider a more complex computational example to show how effective it is that we can access values via pair {row, col}, like from a bi-dimensional array. Assume we have a table with lots of columns, and we know that columns with indexes 3, 5 and 9 contain values of type double (since columns via names we already considered).

So what we want is to calculate the sum of average values for those columns.

// pRecordset is of type ADODB::_Recordset*
CNCQuickADO::CColumns cols(3L, 5, 9, -1); // Column filter;
CNCQuickADO rs(pRecordset, 0, ADODB::adBookmarkCurrent, cols);
long nRows = rs.GetNumberRows(); // Get number of rows;
long nCols = rs.GetNumberCols(); // Get number of columns;
double dAverageSum = 0.0; // Target value (sum of average values);
for(long i = 0;i < nRows;i ++) // Go through all records;
{
    double dAverage = 0.0; // Average value;
    for(long k = 0;k < nCols;k ++) // For all selected columns;
    {
        double dValue; // Temporary value;
        if(rs.GetDouble(i, k, dValue))
        // If retrieved the value successfully and it is not (NULL);
            dAverage += dValue;
    }
    dAverage /= nCols; // Row average;
    dAverageSum += dAverage; // Increment the sum of average values;
}

That's all there is to it, and if you ever tried to use the method GetRows in C++, then you will be able to appreciate the simplicity of what needs to be done, thanks to this library.

So, if you feel that I'm missing something in my article, don't hesitate to tell me, and I will update it quickly ;) I will also appreciate a fair rating with comments on my first article. Thank you.

Points of Interest

There were many challenges that I had to face while writing this code. I would like to list a few here that were most interesting to me:

1. Design and implementation of a class that constructs a Safe Array from a dynamic number of method parameters, i.e. class CNCQuickADO::CColumns. It is quite a reusable piece of code now whenever you need a Safe Array of parameters to be passed elsewhere.
2. Getting class CNCQuickADO to correctly process and pass a Safe Array of columns into the method GetRows of ADO. I couldn't find any documentation on specifics of Safe Arrays required by the method GetRows, and there really were such.
3. Correct handling of whatever may happen as a result of calling the method GetRows when it throws an exception. Lack of documentation in this instance made me rely heavily on experimenting.
4. Correct data type conversion from ADO into variant data types as done by method GetRows. No conversion tables available anywhere, so I had to go through all existing ADO data types myself, slowly but surely.
5. Efficient implementation of the method CNCQuickADO::GetBinary. I just always wanted to have this method return an image when the column was an image file, so now I have one.

Copyright Notes

All code published here is courtesy of www.neatcpp.com to be used for demo purposes or to study the subject, and provided as is, without any warranty.

History

I cannot but mention here that this is my first article published on CodeProject. I wanted to publish one for the last five or six years, and now I finally got around it, publishing some of my code. I hope it will be as useful to other developers as it was to me.

• 27/06/2005 - Initial draft.
• 28/06/2005 - Added more code examples, refined English.
• 06/07/2005 - Modified the library to support ADO type adVarNumeric for Oracle databases, as suggested by Walter Reiser.