CMyADO'�Making Life Easy for the ADO Coder

Environment: Visual C++ 6.0

Introduction

Welcome to the MyADO Wrapper article written for CodeGuru. The article contents and the knowledge gained were mostly due to reading other articles on this site, including Bob Place's ADO is AOK article and a few other ADO/Misc DB contributions. I will not explain everything that has already been covered in these articles, but I will demonstrate a class I created for use with ADO and stored procedures which I find very handy. I will also not explain how to set up an SQL database to use stored procedures, as that has also been covered elsewhere on CodeGuru. I will, however, provide all resources required for this article.

Database Setup

This article uses a DSN and table named TestMyADO, and the following can be used to create the following table:

Create Table TestMyADO
(
  ID Int Primary Key Identity,
  Name VarChar(15) Not Null,
  Value VarChar(15) Not Null
)
Go

with the following stored procedures:

Create Procedure InsertMyADO
  @ID Int Out,
  @Name VarChar(15),
  @Value VarChar(15)
As
  Insert Into TestMyADO (Name, Value) Values (@Name, @Value)
  Select @ID = @@Identity
  Return @@Identity
Go

Create Procedure SelectMyADO
As
  Select ID, Name, Value From TestMyADO
Go

The InsertMyADO stored procedure is used to insert a record into TestMyADO and uses a return value, along with an output variable (ID) and two input variables (Name/Value). The SelectMyADO stored procedure creates a recordset that selects all records from TestMyADO.

CMyADO

CMyADO is a class created to make life easy when using ADO. The basic usage is that you open a connection, initialize a stored procedure, add parameters, execute the stored procedure, and get the results. Integrating this class into your code is simple. Merely #include "MyADO.h", initialize COM, and instantiate an object.

// Instantiate the Object
CMyADO MyADOObject;

Next, open the connection and initialize the stored procedure. Always be sure to validate against the return value.

// Open( ConnectionString, UserID, Password ) the Connection
if( MyADOObject.Open( "TestMyADO", "", "" ) == S_OK )
{
  // Initialize( StoredProcedureName ) the Stored Procedure
  if( MyADOObject.Initialize( "InsertMyADO" ) == S_OK )
  {

After the stored procedure is initialized, you can add parameters.

    // Add the Return Value Parameter
    if( MyADOObject.AddParameterReturnValue() == S_OK )
    {
      // Add the Output Long Parameter
      if( MyADOObject.AddParameterOutputLong( "ID" ) == S_OK )
      {
        // Add the Input Text Parameters
        if( MyADOObject.AddParameterInputText(
            "Name", "Test Name" ) == S_OK &&
            MyADOObject.AddParameterInputText(
            "Value", "Test Value" ) == S_OK )
        {

Next, you'll want to execute the stored procedure and retrieve results (Return Value, Output Paramters, Recordset Fields).

          // Execute the Stored Procedure
          if( MyADOObject.Execute() == S_OK )
          {
            long lReturnValue = 0;
            long lID = 0;

            // Retrieve the Return Value and the Output Paramter
            // set up above
            if( MyADOObject.GetParameterReturnValue( &lReturnValue )
               == S_OK &&
                MyADOObject.GetParameterLong( "ID", &lID ) == S_OK )
            {
              // Sanity check that does nothing : )
              if( lReturnValue == lID )
                printf( "Inserted Record with ID: %2d, Name: %15s,
                         Value: %15s\n", lID, pNames[dwIndex],
                         pValues[dwIndex] );
            }
          }
        }
      }
    }
  }
}

And there you have it; easy as pie. Using the SelectMyADO stored procedure is even easier; there are no parameters. It does return a recordset, though, and the fields can be retrieved in a similar and consistent manner.

First, we re-initialize and execute the same ADO object.

// Initialize( StoredProcedureName ) the Stored Procedure
if( MyADOObject.Initialize( "SelectMyADO" ) == S_OK )
{
  // Execute the Stored Procedure
  if( MyADOObject.Execute() == S_OK )
  {

Then, we loop through each record and retrieve the recordset fields.

    // Ensure there are more Records to Retrieve
    while( !MyADOObject.IsEOF())
    {
      long lID = 0;
      char szName[15];
      char szValue[15];

      // Retrieve the Record Fields
      if( MyADOObject.GetFieldLong( "ID", &lID ) == S_OK &&
          MyADOObject.GetFieldText( "Name", szName, 
                                     sizeof( szName )) == S_OK &&
          MyADOObject.GetFieldText( "Value", szValue,
                                     sizeof( szValue )) == S_OK )
        printf( "Selected Record with ID: %2d, Name: %15s, Value:
                 %15s\n", lID, szName, szValue );

      // Move to the Next Record
      MyADOObject.MoveNext();
    }
  }
}

I hope you have found this article and source useful and you take the time to delve deeper into the class to understand how it works. I leave up to you the task of adding functionality to this class for other variable types, as you need them.

Issues

If you use this class and get an IDispatch #3092 error, there's a good chance you either added parameters incorrectly (for example, return values need to be added first in many cases), or you did not create the table or stored procedures in the correct database.

Downloads

Download demo project - 32 Kb


Comments

  • Will not compile on VC 7.0 or 8.0. What am I missing?

    Posted by Mike Pliam on 04/03/2008 02:33pm

    This code will not compile on VC 7.0 or VC 8.0.  I did change the import library to #import "c:\Program Files\Common Files\System\ADO\msado15.dll" rename("EOF", "EndOfFile"), and that is working because it works in other applications and the type libraries show up in the Release and Debug directories.
    
    Get 69 errors, mostly related to undeclared identifers, i.e., _ParameterPtr,  adCmdStoredProc, adInteger, adParamInput, etc, ... and syntax errors related to DataTypeEnum.
    
    Perhaps I am missing some typedefs or a header file?
    
    Can't believe there are not more comments on this code.

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

Top White Papers and Webcasts

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • With the average hard drive now averaging one terabyte in size, the fallout from the explosion of user-created data has become an overwhelming volume of potential evidence that law-enforcement and corporate investigators spend countless hours examining. Join Us and SANS' Rob Lee for our 45-minute webinar, A Triage and Collection Strategy for Time-Sensitive Investigations, will demonstrate how to: Identify the folders and files that often contain key insights Reduce the time spent sifting through content by …

Most Popular Programming Stories

More for Developers

RSS Feeds