Managing database operations using ADO and C++, Part 5: Using the CADOManager in practice

By Patrick Mancier

To use the CADOManager class as a base class to an inherited management class we should adopt some convention to name these inherited classes. It can be anything really but it seems easy enough to come up with something like CTbl<TableName> for clarity. For example, in our database schema we want to write a table class for the table called 'People' and that inherits from CADOManager. We would call this table class CTblPeople. In the attached project files, there is a special template module called TemplateCode.cpp and TemplateCode.h. In here is a template of how the table class would look. All one needs to do is the do a search and replace operation on <Table Name> and substitute whatever the name of the table is that the class is to operate on. This should make it very easy to continue added tables to list of modules.

Here is a description of what we want the inherited class to do when performing some SQL operation on a table in the database:

At program initialize, establish a connection using a globally declared CADOConnection class. This class will be instantiated for the lifetime of the application.
Initialize the table class using the previously established CADOConnection class. This will tell the class which database connection to use.
Set up any parameters that are needed for the operation by populating the necessary class member variables.
Call the desired SQL operation function.
Check the return to make sure that it was OK.
If not OK, check to see what the error was and perform error handling.

That's pretty much it. Here is a code snippet of what we want it to look like in practice when using a real inherited table class and performing an INSERT operation on the table.

// pdbc is a previously established and initialized CADOConnection class
extern CADOConnection *pdbc;
#define SQL_OK 0
int InsertPeopleTableExample()
{
        CTblPeople *ptblPeople;
        int iErrorCode;

        iErrorCode = SQL_OK;

        ptblPeople = new CTblPeople(pdbc);
        if(ptblPeople)
        {S
                ptblPeople->m_csPersonName = "Tom Smith";
                ptblPeople->m_iPersonAge = 28;
                if(!ptblPeople-> ADO_INSERT())
                {
                        iErrorCode = ptblPeople->LastError();
                        // Perform Error handling
                }

                delete ptblPeople;
        }
        
        return iErrorCode;
}

Now even though it doesn't look like it takes a lot of code to perform this SQL insert, there are many things happening. What is not obvious from looking at this code is what code is executing. We don't see anything other than the basic ADO manager class functions.

Here is the header of CTblPeople:

class CTblPeople : public CADOManager
{
private:
        void Initialize();

public:
        int mp_iRecordID;                       //      Parameter: RecordID to operate on
        CString m_szPersonName;         //      Persons full name
        int m_iPersonAge;                       //      Persons age

        void OnSetDELETEParameters();
        void OnSetINSERTParameters();
        void OnSetSELECTParameters();
        void OnSetUPDATEParameters();

        void OnGetSELECTValues();

        CTblPeople(CADOConnection& dbc);
        CTblPeople(CADOConnection *pdbc);
        CTblPeople(void);
        ~CTblPeople(void);
};

The first thing you see is that CTblPeople publicly inherits CADOManager. As explained in the previous part of the article the virtual functions of CADOManager are overridden here in order to perform the necessary database operations properly. We also know that in the Initialize() function we have made a call to SetTable("People") in order to tell the underlying CADOManager that this is a table class. When the table class is now set up, let's as an example perform an insert operation. Before the caller calls CADOManager::INSERT(), it sets the member variables m_szPersonName and m_iPersonAge to the desired values. Then the caller calls CADOManager::INSERT() function and the operation of our query is set to an insert operation. Since the ADO manager class knows the name of the table and it knows now knows that we want to do an insert operation, it names the internal procedure to call People_Insert. Next it calls the virtual function OnSetINSERTParameters() in the CADOManager::RunQuery() function. It knows to do this because we told the ADO manager class we are doing an INSERT() operation. The CTblPeople class has overridden this virtual function and has defined the function to set up our parameters for the procedure in the database. When this function is called, the columns that represent the columns to insert are set up as parameters to the query operation and the query, which is actually a stored procedure call, is executed. The status can be checked and if something went wrong perform some error handling. Since an INSERT() operation does not result in a recordset, as long as everything went smoothly, we are done.

This code details what is happening on the call to CtblPeople::OnSetINSERTParameters(). As you can see each member of the class has become an input parameter. The caller sets these class variables before calling CADOManager::INSERT() and this gets called before the procedure actually runs. If in the future, say the design of the table called for PersonBirthDate, only a few lines of code would have to be added to the classes various functions to accommodate the new column in the table.

void CTblPeople::OnSetINSERTParameters()
{
        AddParameter("PersonName",Parameter_string,50,m_szPersonName);
        AddParameter("PersonAge",Parameter_int,PARAMETER_INT_SIZE,m_iPersonAge);
}

Using the CTblPeople class here are some other examples of how to use the CADOManager. Assume that there are records already in the table.

extern CADOConnection *pdbc;
#define SQL_OK 0
int main()
{

        // DELETE:  DELETE FROM People WHERE idRecord = 1
        CTblPeople *ptblPeople;
        int iErrorCode;
        int iRecordID;
        CString szName;
        Int iAge;
        iErrorCode = SQL_OK;

        ptblPeople = new CTblPeople(pdbc);
        if(ptblPeople)
        {

                // Select all the records in the table
if(!ptblPeople->ADO_SELECT())  // Class populates the class variables from the first row of the recordset automatically
                {
                        iErrorCode = ptblPeople->LastError();
                        // Perform Error handling
                }

                // Loop through the returned recordset
                while(ptblPeople->GetNextRecord())
                {
                        szName = ptblPeople->m_szPersonName;
                        iAge   = ptblPeople->m_szPersonAge;
                        // TODO: Write szName and iAge to a flat file
                }

                // Delete a record from the table
ptblPeople->mpiidRecord = 1;     // Set the record ID to be deleted
                if(!ptblPeople->ADO_DELETE())
                {
                        iErrorCode = ptblPeople->LastError();
                        // Perform Error handling
                }

// Insert a new record
        ptblPeople->m_csPersonName = "Tom Smith";
                ptblPeople->m_iPersonAge   = 28;
                if(!ptblPeople->ADO_INSERT())
                {
                        iErrorCode = ptblPeople->LastError();
                        // Perform Error handling
                }
                else
                {
                        iRecordID = ptblPeople->GetReturnParameter();
                }

// Update the last inserted record, we don't have to change m_szPersonName, already set from INSERT()
        ptblPeople->mpiidRecord  = iRecordID;     // Set the record ID to the previously inserted record ID
        ptblPeople->m_iPersonAge = 30;          //  Change the persons age to 30
                if(!ptblPeople->ADO_UPDATE())
                {
                        iErrorCode = ptblPeople->LastError();
                        // Perform Error handling
                }

                delete ptblPeople;
        }
        
        return iErrorCode;
}

Calling a user defined query and procedure

What if we need to call a custom query or procedure? Basic operations on tables are fine for simple things, but in many situations database operations are anything but simple. We have designed our CADOManager class in such a way as to handle more complex situations as well as simple table operations.

USERQUERY() is the function that we need to use to perform these 'user defined' operations. Let's say we want to create a quick on the fly query that does something to our People table.

// pdbc is a previously established and initialized CADOConnection class
extern CADOConnection *pdbc;
#define SQL_OK 0
void UserDefineQueryExample()
{
        CADOManager *pADO;
        int iAge;

        pADO = new CADOManager(pdbc);
        if(pADO)
        {
                pADO->ADO_USERQUERY("SELECT PersonAge as Age FROM People");
                while(pADO->GetNextRecord())
                {
                        pADO->GetValue("Age",iAge);
                        //TODO: Write the value iAge to some file
                }

                delete pADO;
        }
        
}

As you can see, this could easily be done with our 'standard' set of operations but this example shows how one can directly call a query using our CADOManager class. In this example, only the alias 'Age' for the column PersonAge is returned from the query. We can scroll through the recordset just like any of our other table operations and obtain the list of ages of the people in our table.

Calling a stored procedure is very similar, with the one difference of potentially having to set up parameters first. In the following example, we will manually set up and call one of our normal 'basic operation' procedures. This example can apply to any procedure that is written in the database, it is not limited to the ones we wrote to manage the table operations. You can see from studying the code that calling a user defined stored procedure is very similar to calling query. You just need to be sure that any parameters to the stored procedure are set up in the order that they appear in the procedure.

//      pdbc is a previously established and initialized CADOConnection class
extern CADOConnection *pdbc;
#define SQL_OK 0
void UserDefineProcedureExample()
{
        CADOManager *pADO;
        int idRecord;
        int iPersonAge;
        CString szPersonName;

        pADO = new CADOManager(pdbc);
        if(pADO)
        {
                idRecord = 1;
                pADO->AddInputParameter("idRecord",Parameter_int,PARAMETER_INT_SIZE,idRecord);
                pADO->ADO_USERQUERY("People_Select");
                while(pADO->GetNextRecord())
                {
                        pADO->GetValue("PersonName",szPersonName);
                        pADO->GetValue("PersonAge",iPersonAge);
                        //TODO: Write the value of szPersonName and iPersonAge to some file
                }

                delete pADO;
        }
}

So in conclusion, hopefully the reader can tell that managing database operations with ADO does not have to be difficult. With enough design and planning it can be a very easy task to set up a framework to manage just about any possible operation needed. The attached project will provide a starting point for the reader to further develop a more robust and production quality framework.

Back to part 4 of the tutorial
Back to the ADO tutorial index page