Managing database operations using ADO and C++ Part 4: Creating the ADO manager class

By Patrick Mancier

Now that we have gone over the basics, described a way to design and create ADO wrapper classes, it is now time for the final phase of creating an ADO manager class that will work on all the concepts we have discussed. Our final goal with this design is to use as little code as possible in running any of the desired SQL operations against the database. Without this ADO manager class, every time we needed to perform some kind of operation we would have to set up the correct ADO wrapper classes, initialize them, set up our list of desired parameters, queries, etc and then execute the SQL command, check its status and perform error handling if something went wrong. We would then have to look at the returned recordset and get the data from it. This would have to be done repeatedly for every time we wanted to access any part of the database. It would result in an enormous amount of repetitious code throughout our application. If we can design this ADO manager class correctly it will be much easier to do ADO database operations and will make any code changes for future database changes much easier. Things like adding or deleting columns and tables or any time a procedure changes in our database schema will require fewer code changes.

In designing the ADO manager class, we need to enumerate what the ADO manager class should do. Here is a list of some of the things we need:

Seamlessly use the ADO wrapper classes to perform ADO operations.
Execute the basic SQL operations of DELETE, INSERT, SELECT and UPDATE on a given table in the database.
Handle the various differences of the parameter signatures that exist from procedure to procedure.
Execute 'user defined' SQL queries and stored procedures.
Iterate through a returned set of records.

To address the seamless use of the ADO wrapper classes is fairly easy. We will declare a set of pointers that point to each one of the ADO wrapper classes that we need. During normal operation of calls to the ADO manager class, the use of any one of these classes will be hidden from the caller. If these classes need to be persistent, they will either be assigned at instantiation or will be dynamically allocated at the ADO manager class instantiation. In other cases, the class will simply be initialized and deleted as needed. For example, the CADOConnection class will have been previously initialized. The pointer to this initialized class will be passed into the initialization of the ADO manager class. As another example, the CADOParameter class will need a new allocation that needs to be passed into ADO for each parameter that is needed for the procedure, so this will be a class member that will be allocated and deleted dynamically as needed.

To execute the basic DELETE, INSERT, SELECT and UPDATE procedures this will simply be a set of public functions that the caller will call named after the operation that is needed. These functions will operate only on tables in the database; USERQUERY will operate only on user defined queries or stored procedures. Calling any of these functions will automatically configure our ADO manager class to operate in the manner that is necessary to accomplish the database operation.

void ADO_USERQUERY();

void ADO_DELETE();

void ADO_INSERT();

void ADO_SELECT();

void ADO_UPDATE();

The following is a short list of available 'name mangled' parameter and value functions. This list does not cover every available column type that exists in a database table. These are the functions that are used in our inherited class that publicly inherits CADOManager. These are helper functions that make it easier to manage our ability to perform the database operations on the various queries and stored procedures we need to call.

The following functions are used to set up the parameters to our calls to each of our procedures.

void AddInputParameter(CString szParameterName, int iType, int iSize, int iValue);

void AddInputParameter(CString szParameterName, int iType, int iSize, CString szValue);

void AddInputParameter(CString szParameterName, int iType, int iSize, FILETIME ftValue);

void AddOutputParameter(CString szParameterName, int iType, int iSize);

The following functions are using to retrieve the column values from the ADO recordset object and populate the class member variables in our inherited class.

void GetValue(CString szColumnName,int& iValue);

void GetValue(CString szColumnName,CString& szValue);

void GetValue(CString szColumnName,FILETIME& ftValue);

In order to handle the variance in the number of parameters and parameter types for the table or user defined queries or procedures that the ADO manager class will call, we need to use some type of mechanism that can be called from the base ADO manager class but operates from the inherited class. We need a way to set up the input and output parameters for a given procedure execute the procedure and check its return status. We cannot just set up all the parameters needed in the base ADO manager class; this would be very unwieldy to continue adding functions to do this for every new procedure. We need to somehow separate this functionality from the base ADO manager class but still be able to actually call a function that does this from the base ADO manager class. This sounds like a chicken and egg problem unless you consider the idea of a virtual function. Virtual functions are functions that by design are overridden by a class that inherits the base class. This overridden function can be called from the base class but its implementation exists in the inherited class. So what will happen when a query is run, depending on the type of operation that takes place a virtual function will be called that will set up the parameters for that particular operation. The inherited class will have the implementation needed to set up the parameters for the operation.

These virtual functions set up the parameters BEFORE an SQL procedure is called. In this case, the caller must set the class members values and then run the operation.

virtual void SetUserParameters();

virtual void SetDELETEParameters();

virtual void SetINSERTParameters();

virtual void SetSELECTParameters();

virtual void SetUPDATEParameters();

These virtual functions will populate the member variables AFTER an SQL procedure is called. These functions will be called during recordset operations.

virtual void GetUserValues();

virtual void GetSELECTValues();

For this example, we create a class called CTblPeople that publicly inherits CADOManager in order to implement our database operations we want to perform on the table 'People' in our database schema.

This is not the actual code in our ADO manager class but serves as an example of how the basic operation of the class will work. This is written in pseudo-code form for simplicity. The actual call will vary depending on the table operation to perform.

BOOL ADO_<Table Operation, DELETE,INSERT,SELECT,UPDATE>()
{
    Set<Table Operation>Parameters();     //  Overridden function in inherited class CTblPeople
    return PerformTableOperation(SQLOperation); //  SQLOperation = DELETE, INSERT, SELECT or UPDATE
}

BOOL PerformTableOperation(ADOOperation)
{
    Set<Table Operation>Parameters();     //  Overridden function in inherited class CTblPeople
    SetUpCommandObject();                       //  Series of call that properly set up CADOCommand object 
    SetQueryType(QueryType_Table);              //  Tell CADOManager what query type we are running, this is a table operation
    SetOperation(ADOOperation);         //  Set the operation we are running, DELETE, INSERT, SELECT or UPDATE
    szProcName = GetProcedureName();    //  Based on the fact we are running table operation and which operation is being run, derive the procedure name        
    ExecuteSQLOperation();                      //  Run the table operation we need by calling the procedure <TableName>_<Table Operation>
    if(Operation == SELECT)
    {
        //      Create a new CADORecordset object and attach the recordset that was generated from the ExecuteSQLOperation call
       CreateCADORecordSetObject(GetRecordsetPtrFromCommandObject());
    }

    return TRUE on success;
    return FALSE on error;              
}

CTblPeople code: (publicly inherits CADOManager class)

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);
};

void CTblPeople::Initialize()
{
        mp_iidRecord = 0;
        m_szPersonName = _T("");
        m_iPersonAge = 0;

        SetTableName("People");
}

void CTblPeople::SetSELECTParameters()
{
        if(mp_iRecordID)
        {
                AddInputParameter("idRecord",Parameter_int,PARAMETER_INT_SIZE,mp_iidRecordID);
        }
}

void CTblPeople::SetINSERTParameters()
{
        AddInputParemater("PersonName",Parameter_int,m_szPersonAge.GetLength(),m_szPersonAge);
        AddInputParemater("PersonAge",Parameter_string,PARAMETER_INT_SIZE,m_iPersonAge);
}

void CTblPeople::GetSELECTValues()
{
        GetValue("idRecord",mp_iidRecord);
        GetValue("PersonName",szPersonName);
        GetValue("PersonAge",m_iPersonAge);
}

Notice in CTblPeople that the public member variables mp_iidRecord, m_szPersonName and m_iPersonAge map directly to the columns in our 'People' table in the database schema. This will be useful in our final design when we need to scroll through the recordset or set up the parameters to one of the database operations. In viewing the code above one can see that even though the virtual functions are being called from CADOManager the overridden function is implemented on the inherited class CTblPeople. We see that the Set<database operation>Parameters() function is used to initialize the parameters we need for the SQL procedure/query that is being called and that afterwards, depending on if it is a SELECT or a user query we populate the inherited class member variables that represent columns in the table by calling the GetSELECTValues() or GetUserValues() function. These two functions will also be used when scrolling through the returned recordset.

A function in the base ADO manager class called SetTable() is used to inform the ADO manager class that it should operate on a database table. Remember when we came up with a naming convention for the procedures in our database? Now we see this was a wise thing to do, because the ADO manager class can now derive the procedure name it needs based on the rules of our design. Recall that the general pattern we used for procedure names was <Table Name>_<Database operation>.

As an example, let's say we would like to insert a new record into that table. We start by having the inherited class call SetTable("People") in its initialization function. Since the name of our table is 'People' and we are calling CADOManager::INSERT() the name of the procedure that our ADO manager class will derive from these two pieces of information will be People_Insert. Since this function may have different numbers and types of parameters than the other table functions, we will call the virtual function SetINSERTParameters() inside of CADOManager but the function CTblPeople::SetINSERTParameters() is the one that will be called. This allows the programmer to modify CTblPeople class in order to set up the parameters that are necessary to perform the INSERT() operation on this database table. In the case of INSERT we set up the PersonName and PersonAge columns to pass to the People_Insert procedure.

A user-defined procedure will be handled differently by calling another function that will run user defined queries or procedures. In this case, a 'user defined' query or procedure is one that falls outside of the table model we have set up. As a rule of thumb, if our database operation does not involve one of the four directly defined operations we want the ADO manager class to operate with on a database table, it will then be either be a user defined query or procedure. These type of operations will operate in a very similar way as the process above expect that instead of calling one of the standard function operators, the user must define the query string and call USERQUERY instead. The CADOManager will call SetUserParameters() and GetUserValues() in place of the other four standard operations.

Now that we have discussed the idea of virtual functions in our ADO manager class and we have detailed what those functions are and what they do, we can now discuss how we will handle recordset operations. Select type queries that are performed whether they are direct SQL statements or stored procedures will result in a returned recordset. We have a class wrapper called CADORecordset that will contain this returned recordset if needed. However, we want an easy way to scroll through this record set and pick up the values from it. There will be two ways to obtain a recordset. One way is to operate on a database table and call the SELECT() operation. The other way is to have a user-defined query or stored procedure return a recordset through some type of select operation.

When this select type query is finished, if there were records to be returned, i.e. a SELECT operation, a CADORecordset will be created in the CADOManager object. We can scroll through the recordset using various navigational recordset type functions. These should be self-explanatory.

BOOL MoveFirst();

BOOL MoveLast();

BOOL MoveNext();

BOOL MovePrev();

BOOL MoveTo(int iRecordsToMove);

To obtain the recordset values, it will involve a combination of calling a and CADOManager::GetValue() function and CADOManager::GetNextRecord(). CADOManager::GetNextRecord() does something more than just increment the recordset pointer. It calls GetUserValues() if the operation is a user define query or stored procedure and GetSELECTValues() if the operation is a table based SELECT() operation. Calling these functions allows the inherited ADO manager class to populate the class members that represent each column in the returned recordset. CADOManager::GetNextRecord() will continue to return TRUE until reaching the end of the recordset.

Below is an example of how we can scroll through a recordset:

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

In looking at the above code some of you might be thinking wait! When calling SELECT(), the class members are populated with the first record values and then calling GetNextRecord() will go to the next record in the recordset and we will miss writing the first record values into our flat file! Don't worry, the design of our ADO manager class takes this into account. The very first call to GetNextRecord() does not actually increment past the first record in the recordset it will start at the first record. Only on a subsequent call to GetNextRecord() will the recordset pointer increment. In other words, after a SELECT operation think of the recordset pointing to the 0th record in the record set instead of the 1st and calling GetNextRecord() causes the recordset pointer to point to the first record in the recordset.

In the next part of this article we will conclude with some explanation on how to code an inherited class to use the ADO manager class and show some examples of how to actually use the ADO manager class in practice.

Continue to part 5 of the tutorial
Back to part 3 of the tutorial
Back to the ADO tutorial index page