WrapSP - A Wrapper Class Generator for SQL Stored Procedures

• Download EXE - 23.3 KB
• Download source code - 44.2 KB
• Download AdoDatabase classes - 3.86 KB

Introduction

WrapSP (Wrapper for Stored Procedures) is a tool that simplifies the creation of wrapper classes for calling SQL Stored Procedures (SP). Just a few clicks and you get a class for each SP, simply select the SP, and let WrapSP do the work for you.

WrapSP analyzes the input/output parameters and the result set of the selected Stored Procedure and composes a perfectly tailored class for calling it. If you have to redesign the Stored Procedure, then you can recreate the wrapper class only in seconds. In addition to the class, you always get a working example of how to use it, and just copy and paste it into your project.

If you have to call dozens of Stored Procedures or only one, WrapSP will always save you work.

Background

As you know, a Stored Procedure is a script that is written using the SQL language. It is stored in the database, and it can be called either from another SQL script or from any program having a connection to the database. Stored Procedures are often used as an interface between a program and the database because they decouple the database logic written in SQL language from a program written in, say, C++.

From the calling program point of view, a Stored Procedure can have the following items or any combination of them:

• RETURN_VALUE
• INPUT - parameters
• OUTPUT - parameters
• Result Set

The RETURN_VALUE is an integer number and will be used like in C programs to pass some results (mostly Error Codes).

The INPUT and OUTPUT parameters are optional. They will be used to pass single values of any type to the Stored Procedure or to get any single value from the Stored Procedure.

The Result Set is a structure in the form of a database table containing columns and rows of any type. It will be used to get any number of any information from the database. Mostly, it is the result of a SELECT statement with some JOINs etc.

All the data in a C++ program passed to the database or received from it must be stored in a VARIANT record. This makes the database programming a little bit complicated. For calling a Stored Procedure, you must first find out what parameter must be passed. Then, you have to find out what results should be expected. If the Stored Procedure is complicated, then it can be difficult to pass the correct parameters and to receive the correct sets of values. Very often, it is necessary to change the Stored Procedure, and then you have to start your work from the beginning.

All those routine stuff can be automated and simplified using WrapSP. In my projects, I have saved a lot of time using it. Any changes on existing Stored Procedures aren't a nightmare any more.

How to use WrapSP to generate a Stored Procedure wrapper class

Let me describe step by step how to use WrapSP. You call the program (WrapSP.exe) and you get the following window:

1. First, you have to log into the desired database. You choose the appropriate database from the Data Base combo box. In the fields Log ID and Password, you enter the log-in data. Then, you press the Connect To DB button. The connection to the database will be established. If you enter wrong log-in data or the ODBC connection is not predefined, then you get an error message.
2. After successful connection to the database, the combo box Stored Procedure becomes enabled and you can select the Stored Procedure for which you want to create the wrapper class.

Information about all the parameters of the selected Stored Procedure will be displayed in the list SP Parameter. If this Stored Procedure does not have any parameters, then only the default parameter RETURN_VALUE will be displayed in the list.

In the second list Returned Recordset, the structure of the returned Recordset will be displayed. If the Stored Procedure does not deliver any result set, then this list will be empty.

3. Now, you are ready to create the wrapper class for the selected Stored Procedure. You press the button Create Wrapper Class and the following dialog will appear:



As you can see, you can specify the destination path for the generated class. The class name and the names for the resulting header file and the source file will be generated automatically using the name of the selected Stored Procedure.

In the bottom, there are two options that I have called Parameter by Index and Parameter by Name. Those options specify how the parameter for the Stored Procedure will be applied in the wrapper class.

If you select By Index, then only one function SetParam will be generated and the parameters will be applied using the name of the Stored Procedure parameter as the index (example: "Beginning_Date"). This is the recommended selection because it results in less generated code.

If you select By Name, then for each INPUT and OUTPUT parameter, a separate function having the name of this parameter (example: "Set_Beginning_Date") will be generated.

4. Now, you press the Create Class button, and the header and the source file will be generated in the specified destination directory. If those files are already existing, you will be asked if you want to overwrite them.

A good practice is to have a separate test project in which you can first test the generated classes before you get them in your own project. For this purpose, I have created a sub-project TestIt, so my destination path is set to this project.

Using the generated wrapper class

To use the generated classes in your projects, you have to follow these steps:

1. For testing the classes, it is recommended to make a simple test project.
2. You generate a wrapper class for each Stored Procedure that you want to call.
3. You insert the generated header and source files of your wrapper classes into your test project.
4. You test the call of the Stored Procedure using the generated test function.
5. If it is necessary to change the code of your Stored Procedure, you do this, and then you generate the wrapper class for the Stored Procedure again. This you can repeat until you are satisfied with the results.
6. If you are satisfied with the results, you can take over the tested classes into your final project.

Let us see the following practical example:

For testing purposes, I have generated a simple Command Line based project (TestIt). You can use it for your purposes too. Because the generated classes are ADO based, you have to insert a reference to ADO. A good place for this is the STDAFX.H file in your project directory. The insertion should look like this:

//ADO Stuff manually inserted
#pragma warning(disable:4146)
#import "c:\program files\common files\system\ado\msado15.dll" 
        named_guids rename("EOF", "bEOF") rename("BOF", "bBOF")
#pragma warning(default:4146)
using namespace ADODB;  
inline void TESTHR(HRESULT x) {if FAILED(x) _com_issue_error(x);};
//ADO Stuff manually inserted

As you can see, there is a function TESTHR additionally defined which will be used in some places in the generated class. The place on your PC where the ADO DLL is stored might differ from the above one, so maybe you have to adjust it in your case.

In your project file, you have to insert the following headers:

#include "stdafx.h"
#include "adodatabase.h"              // base class
#include "EmployeeSalesbyCountry.h"   // generated Wrapper Class 1
#include "TenMostExpensiveProducts.h" // generated Wrapper Class 2

The AdoDatabase.h is the base class for all the generated Stored Procedure classes, so it must always be inserted into your project. The corresponding source file AdoDatabase.cpp must also be part of your project.

For each generated wrapper class, you have to insert the header and the source file into your project too (I have done this here for two classes).

You can use the generated test functions, defining them as external:

void extern Test_CEmployeeSalesbyCountry();
void extern Test_CTenMostExpensiveProducts();

Then, you can simply call those functions in the test project, like this:

int _tmain(int argc, TCHAR* argv[], TCHAR* envp[])
{
    int nRetCode = 0;
    if (!AfxWinInit(::GetModuleHandle(NULL), NULL, ::GetCommandLine(), 0))
    {
       // some error handling ...
    }
    else
    {  // call the Test Functions 
            Test_CEmployeeSalesbyCountry();
        Test_CTenMostExpensiveProducts();
    }
    return nRetCode;
}

The test functions will always be inserted at the end of the corresponding source file for their class, so for each generated wrapper class, you will get one specialized test function. Let us see one of those functions. Test_CEmployeeSalesbyCountry looks like this (what you see is exactly what you get from WrapSP):

/*----------------------------------------------------------------*/
/*    Example for using the created class.                        */
/*----------------------------------------------------------------*/

void Test_CEmployeeSalesbyCountry()
{
    CEmployeeSalesbyCountry usp;

    try {

        // Open the DB and initialize evrything

        if (usp.Connect()) {

            // TODO: Add your initialization for the input parameters !!!

            _variant_t vIn;

            usp.SetParam( _T("Beginning_Date"), vIn); // datetime
            usp.SetParam( _T("Ending_Date"),    vIn); // datetime

            // Call the SP and check the correct return code if supplied

            if (0 == usp.Call()) {

                // TODO: Loop through the returned records
                // and do something useful with them !!!

                EmployeeSalesbyCountry_RecordSet_Type rRec;
                _variant_t vRec;

                while (usp.GetNextRecordSet(rRec)) {

                    vRec = rRec.vCountry;     // VarWChar(15)
                    vRec = rRec.vLastName;    // VarWChar(20)
                    vRec = rRec.vFirstName;   // VarWChar(10)
                    vRec = rRec.vShippedDate; // DBTimeStamp
                    vRec = rRec.vOrderID;     // Integer
                    vRec = rRec.vSaleAmount;  // Currency
                }
            }
        }
    }
    catch(_com_error e) {
        CADODatabase::Msg(e.ErrorMessage());
    }
}

As you can see, there will be one instance of the generated class (usp) declared. Then, a connection to the database will be established by calling usp.Connect(). The parameters for the database connection are defined inside the class so they must not be specified here. All input/output parameters of the Stored Procedure will be declared. The input parameters must be set to their proper values. Then, the Stored Procedure call can be done: usp.Call(). After calling the Stored Procedure, either we have to read the output parameter (this will also be generated), or like in this example, we get back a resultset through which we can loop. To simplify the access to the resultset, an appropriate record type will always be generated (here: EmployeeSalesbyCountry_RecordSet_Type).

You can cut and paste the test function into your project. But, if you have to supply some input parameters for your Stored Procedure, you have to replace the generated place holders (vIn) with some real values; otherwise, you get an empty resultset from the Stored Procedure. So, for testing, we replace the empty vIn parameters with something like this:

usp.SetParam( _T("Beginning_Date"), 
    vIn = COleDateTime(1996,8,1,0,0,0));  // datetime
usp.SetParam( _T("Ending_Date"), 
    vIn = COleDateTime(1996,9,1,0,0,0));  // datetime

Now, if you execute the test function and step through it in Debug mode, then you will see that after a successful call of the Stored Procedure, you can loop through the returned recordsets and you can read the elements of the record.

That is what we need! After you have tested the Stored Procedure call, you can copy this piece of code and paste it into your project.

Points of interest

The main point of this article is how to use the WrapSP tool and the generated classes in your own projects. I have not explained the internals of the tool itself; the code is very simple, so you can analyze it yourself.

At the end, I would like to put together some hints that were important for me. Maybe they will help you too if you use WrapSP.

• Be sure that the ODBC connection for the database that you want to use is correctly defined and it works.
• I recommend you to use a test project to examine your classes in a simple environment before you take it over in a more complicated project.
• WrapSP generates a RecordSet type for the returned recordset. So, you can easily access the individual elements in it.
• For each Stored Procedure parameter and for each element of the resultset, a comment describing the data type that you have to supply or that you will receive will be generated. This will simplify the coding for you.
• Maybe some Stored Procedures that you are using are so complicated that WrapSP can't figure out the resulting set. In such a case, you can temporarily comment out the internal code of the Stored Procedure except the final SELECT that you are using to return the values, and run WrapSP again. For WrapSP, it is important to recognize the structure of the resultset and not the returned values themselves.
• If you have Stored Procedures that deliver return sets with different structures depending on the input parameter, you can create a separate wrapper class for each returned structure. But to avoid naming conflicts, you have to rename them manually.
• If you use more then one wrapper class in your code, they can share a single opened database connection. An example for this is also supplied in the TestIt project.
• The program has been written with Visual Studio 6, but it can be easily ported to Visual Studio .NET. It has been tested with MS-SQL 7, but it will also work with newer versions of SQL.

Acknowledgments

The code is based on one of the early versions of AdoDatabase developed by Carlos Antollini.

I have changed it and extended it for my needs.

History

• 2 August 2009, version 2.01 - First published version.