ARSdb - Abstracted Database and Recordset Classes

Overview

This article presents a set of base classes which can be used to abstract your applications from the underlying database access technology.

When planning the development of a new application, many design decisions must be made. For a database application, the database access method is one of those. However, as were all aware, things change. Thanks to the frantic pace Microsoft has set, an application initially written just a few years ago may now have gone through ODBC, DAO, RDO and now ADO. If youve been through a conversion, you know it can be a painful process.

We decided to insulate ourselves from this process by creating our own database classes to wrap the underlying database access technology. This allows our application and its business objects to be independent of the underlying database access method used by the classes. The current implementation uses ADO, but could be updated to use another technology without affecting the application itself.

The classes provide features to simplify the creation of database object classes. It should be possible to write a wizard to create the class code for the tables in a project, although we havent taken the time to do this yet.

The class structure is as follows:

CARSObject - derived from CObject. This is the base class for all ARSdb classes.

CARSConnection - derived from CARSObject.


// CARSConnection - class which encapsulates database connection functionality
class ARSDB_API CARSConnection : public CARSObject {

    friend class CARSRecordset;

    // Must to give us IsKindOf ability
    DECLARE_DYNAMIC(CARSConnection);

    ADOConnection *pConn;
    static DWORD dwNumConnections;
public:

    // Open /close Create/Destroy Connection
    CARSConnection();
    CARSConnection(LPCSTR pcsConnectString, LPCSTR pcsUID, LPCSTR pcsPWD, long lFlags = adOpenUnspecified);
    ~CARSConnection();
    void Open(LPCSTR pcsConnectString, LPCSTR pcsUID, LPCSTR pcsPWD, long lFlags = adOpenUnspecified);
    void Close();

    // Queries
    BOOL IsOpen();
    BOOL IsInitialized();    // If all of the underlying Data Access Components are loaded

    // Transaction Management
    void BeginTrans(long *plNestingLevel);
    void CommitTrans();
    void RollbackTrans();

    // Schema Definitions  
    CARSRecordset *OpenSchema(SchemaEnum Schema, VARIANT *Criteria, VARIANT *SchemaID);

    // Database access
    CARSRecordset *Execute(LPCSTR pcsCommand, VARIANT  *RecordsAffected = NULL, long lOptions = adCmdUnknown);
};

CARSRecordSet - class which incorporates recordset functionality


class ARSDB_API CARSRecordset : public CARSObject {

    // Give access to CARSConnection(ADORecordset) member to CARSConnection
    friend class CARSConnection;

    // Must to give us IsKindOf ability
    DECLARE_DYNAMIC(CARSRecordset);

    ADORecordset *pSet;
    ADOConnection *pConnection;
    CARSRecordset(ADORecordset *_pSet);
public:
    // Open/Close/Create/Delete record set
    CARSRecordset();
    CARSRecordset(LPCSTR pcsSource, CARSConnection *pConn, CursorTypeEnum cType = adOpenDynamic, LockTypeEnum lType = adLockOptimistic, long lOptions = adCmdUnknown);
    ~CARSRecordset();
    void Open(LPCSTR pcsSource, CARSConnection *pConn, CursorTypeEnum cType = adOpenDynamic, LockTypeEnum lType = adLockOptimistic, long lOptions = adCmdUnknown);
    void Close();

    // Queries
    BOOL IsOpen();
    BOOL IsInitialized();    // If all of the underlying Data Access Components are loaded
    BOOL IsBOF();
    BOOL IsEOF();

    // Move operations
    void MoveFirst();
    void MoveNext();
    void MoveLast();
    void MovePrevious();
    // This virtual function depends upon your database standards.  For our system, every
    // table must have a primary key named pk_id.  This can be overridden in your derived 
    // classes, or if you have a similar standard, change the field name in the filter 
    // condition of the implentation of this in arsdb.cpp.
    virtual BOOL PositionToPK(long _pk_id);

    // Filter
    void SetFilter(LPCSTR filter);
    CString GetFilter();

    // Edit Mode 
    EditModeEnum GetEditMode();

    // Updates / Deletes
    void Delete(AffectEnum affectRecords = adAffectCurrent);
    void Update();
    void CancelUpdate();
    
    // Read/Write Access to data members
    void GetFieldValue(LPCSTR fieldName, VARIANT *value);
    void SetFieldValue(LPCSTR fieldName, VARIANT *value); 
    CString ConvertVariantToString(VARIANT *value);

    BOOL operator()(LPCSTR fieldName, CString &fd, BOOL doWrite = FALSE, BOOL isNULL = FALSE);
    BOOL operator()(LPCSTR fieldName, long &fd, BOOL doWrite = FALSE, BOOL isNULL = FALSE);
    BOOL operator()(LPCSTR fieldName, short &fd, BOOL doWrite = FALSE, BOOL isNULL = FALSE);
    BOOL operator()(LPCSTR fieldName, BOOL &fd, BOOL doWrite = FALSE, BOOL isNULL = FALSE);
    BOOL operator()(LPCSTR fieldName, double &fd, BOOL doWrite = FALSE, BOOL isNULL = FALSE);
    BOOL operator()(LPCSTR fieldName, float &fd, BOOL doWrite = FALSE, BOOL isNULL = FALSE);
    BOOL operator()(LPCSTR fieldName, COleDateTime &fd, BOOL doWrite = FALSE, BOOL isNULL = FALSE);
    BOOL operator()(LPCSTR fieldName, CY &fd, BOOL doWrite = FALSE, BOOL isNULL = FALSE);
    void AddNew();
protected:
    // Overwritables in order to support easy Subclassing
    CARSRecordset(CARSConnection *pConn, CursorTypeEnum cType = adOpenDynamic, LockTypeEnum lType = adLockOptimistic, long lOptions = adCmdUnknown);
    void Open(CARSConnection *pConn, CursorTypeEnum cType = adOpenDynamic, LockTypeEnum lType = adLockOptimistic, long lOptions = adCmdUnknown);

    // Called upon Open(pConn, CursorType, LockMode, Options)
    // must return source (either table name or query for extracting data)
    virtual LPCSTR GetSource();
    // Called from MoveFirst, Next() and so on to copy data from Database to internal variables
    virtual void CopyDataFromDatabase();
    // Called from Update(), AddNew() to copy data from internal variables to Database
    virtual void CopyDataToDatabase();

};

CARSException - our exception class. This uses a resource dll, arsmsg.dll to store Exception strings so that they can be easily translated to other languages.


class ARSDB_API CARSException : public CException {
    // Must to give us IsKindOf ability
    DECLARE_DYNAMIC(CARSException);

    UINT    m_cause;    // Cause of exception (ID of the message in arsmsg.dll)
    UINT    m_detailed; // Additional explanation (ID of the message in arsmsg.dll)
    UINT    m_helpID;   // Help ID of the exception
    CString m_ErrorStr; // Error String from ADO Connection
public:
    CARSException(UINT _m_cause, UINT _m_detailed = 0, ADOConnection *pConn = NULL, UINT _m_helpID = 0);
    UINT GetCause();
    UINT GetDetailed();
    UINT GetHelpID();
    virtual BOOL GetErrorMessage(LPTSTR lpszError, UINT nMaxError, PUINT pnHelpContext = NULL );
    virtual int ReportError( UINT nType = MB_OK, UINT nMessageID = 0 );
private:
    void ExtractConnError(ADOConnection *pConn);
};

Using the database classes is fairly straight forward. As with CRecordset or similar classes, you normally create a class per table. A sample class for a customer table is shown here. You will notice a number of macro definitions used in the table classes. These are defined in arsdb.h.


#define ARSDB_GETFIELD(fd) (fd##_isNULL = !(*this)("" #fd "", fd))
#define ARSDB_SETFIELD(fd) ((*this)("" #fd "", fd, TRUE, fd##_isNULL)) 
#define ARSDB_CHKSTRFIELD(fd) if (!fd.IsEmpty()) fd##_isNULL = FALSE;
#define ARSDB_CHKDATEFIELD(fd) if (fd.GetYear() != 1900) fd##_isNULL = FALSE;
#define ARSDB_CHKNUMFIELD(fd) if (fd > 0) fd##_isNULL = FALSE;
#define ARSDB_GETCYFIELD(fd)                                          \
    {                                                                 \
        CY fd;                                                        \
        fd.int64 = 0;                                                 \
        fd##_isNULL = !(*this)("" #fd "", fd);                        \
        this->fd = (float )(((double )fd.int64) / ((double )10000));  \
    }

#define ARSDB_SETCYFIELD(fd)                                          \
    {                                                                 \
        CY fd;                                                        \
        if (!fd##_isNULL)                                             \
            fd.int64 = (__int64 )(this->fd * 10000.0);                \
        else                                                          \
            fd.int64 = 0;                                             \
        (*this)("" #fd "", fd, TRUE, fd##_isNULL);                    \
    }
        
#define ARSDB_CHKCYFIELD(fd)                                          \
    {                                                                 \
        if ((fd >= 0.01) || (fd <= -0.01))                            \
            fd##_isNULL = FALSE;                                      \
    }

#define ARSDB_FIXDATEFIELD(fd)                                        \
    {                                                                 \
        if (fd.GetStatus() == COleDateTime::invalid)                  \
            fd.SetDate(1900,1,1);                                     \
    }                                                                 


The header file for this table looks like this:


#if !defined(TBLCUST_DEFINED)
#define TBLCUST_DEFINED

#include "arsdb.h"
#include "fdndbcmn.h"


class FDNDB_API CTblCustomers : public CARSRecordset {
public:
    // Data members
    long pk_id;                BOOL pk_id_isNULL;
    COleDateTime CreationDate; BOOL CreationDate_isNULL;
    CString ContactFirstName;  BOOL ContactFirstName_isNULL;
    CString ContactLastName;   BOOL ContactLastName_isNULL;
    CString AddressLineOne;    BOOL AddressLineOne_isNULL;
    CString AddressLineTwo;    BOOL AddressLineTwo_isNULL;
    CString City;              BOOL City_isNULL;
    CString StateOrProvince;   BOOL StateOrProvince_isNULL;
    CString PostalCode;        BOOL PostalCode_isNULL;
    CString Country;           BOOL Country_isNULL;
    CString ContactTitle;      BOOL ContactTitle_isNULL;
    CString PhoneNumber;       BOOL PhoneNumber_isNULL;
    CString Extension;         BOOL Extension_isNULL;
    CString FaxNumber;         BOOL FaxNumber_isNULL;
    CString EmailAddress;      BOOL EmailAddress_isNULL;
    CString Notes;             BOOL Notes_isNULL;
    long OrderCount;           BOOL OrderCount_isNULL;
    float OrderTotal;          BOOL OrderTotal_isNULL;
    COleDateTime LastOrder;    BOOL LastOrder_isNULL;
    short Active;              BOOL Active_isNULL;


    CTblCustomers();
    CTblCustomers(CARSConnection *pConn, CursorTypeEnum cType = adOpenDynamic, LockTypeEnum lType = adLockOptimistic, long lOptions = adCmdText);
    
    void ClearVariables();
    virtual LPCSTR GetSource();
    virtual void CopyDataFromDatabase();
    virtual void CopyDataToDatabase();

    BOOL GetOrderTotals(CTblOrderTotals &tbl);

};

The implementation:


// tblcust.cpp - Implementation of customer table
//-----------------------------------------------

#include "stdafx.h"
#include "tblcust.h"
#include "tblorderttls.h"

CTblCustomers::CTblCustomers()
{
    ClearVariables();
}

CTblCustomers::CTblCustomers(CARSConnection *pConn, CursorTypeEnum cType /* adOpenDynamic */, LockTypeEnum lType /* adLockOptimistic */, long lOptions /* adCmdText */)
{
    ClearVariables();
    Open(pConn, cType, lType, lOptions);
}

void CTblCustomers::ClearVariables() {
    pk_id = 0;              pk_id_isNULL = TRUE;
    CreationDate.SetDate(1900,1,1); CreationDate_isNULL = TRUE;
    ContactFirstName = "";  ContactFirstName_isNULL = TRUE;
    ContactLastName = "";   ContactLastName_isNULL = TRUE;
    AddressLineOne = "";    AddressLineOne_isNULL = TRUE;
    AddressLineTwo = "";    AddressLineTwo_isNULL = TRUE;
    City = "";              City_isNULL = TRUE;
    StateOrProvince = "";   StateOrProvince_isNULL = TRUE;
    PostalCode = "";        PostalCode_isNULL = TRUE;
    Country = "";           Country_isNULL = TRUE;
    ContactTitle = "";      ContactTitle_isNULL = TRUE;
    PhoneNumber = "";       PhoneNumber_isNULL = TRUE;
    Extension = "";         Extension_isNULL = TRUE;
    FaxNumber = "";         FaxNumber_isNULL = TRUE;
    EmailAddress = "";      EmailAddress_isNULL = TRUE;
    Notes = "";             Notes_isNULL = TRUE;
    OrderCount = 0;         OrderCount_isNULL = TRUE;
    OrderTotal = 0;         OrderTotal_isNULL = TRUE;
    LastOrder.SetDate(1900,1,1); LastOrder_isNULL = TRUE;
    Active = FALSE;         Active_isNULL = TRUE;
    Birthday.SetDate(1900, 0, 0); Birthday_isNULL = TRUE;
}

LPCSTR CTblCustomers::GetSource() {
    return "select * from custom.tblCustomers";
}

void CTblCustomers::CopyDataFromDatabase() {
    if (!IsEOF() && !IsBOF()) {
        ARSDB_GETFIELD(pk_id);
        ARSDB_GETFIELD(CreationDate);
        ARSDB_GETFIELD(ContactFirstName);
        ARSDB_GETFIELD(ContactLastName); 
        ARSDB_GETFIELD(AddressLineOne); 
        ARSDB_GETFIELD(AddressLineTwo);
        ARSDB_GETFIELD(City);
        ARSDB_GETFIELD(StateOrProvince);
        ARSDB_GETFIELD(PostalCode);
        ARSDB_GETFIELD(Country);
        ARSDB_GETFIELD(ContactTitle);
        ARSDB_GETFIELD(PhoneNumber);
        ARSDB_GETFIELD(Extension);
        ARSDB_GETFIELD(FaxNumber);
        ARSDB_GETFIELD(EmailAddress);
        ARSDB_GETFIELD(OrderCount);
        ARSDB_GETCYFIELD(OrderTotal);
        ARSDB_GETFIELD(LastOrder);
        ARSDB_GETFIELD(Active); 
        ARSDB_GETFIELD(Notes);       
        ARSDB_GETFIELD(Birthday);
        
        ARSDB_FIXDATEFIELD(LastOrder);
        ARSDB_FIXDATEFIELD(CreationDate);
        ARSDB_FIXDATEFIELD(Birthday);
    }
    else
        ClearVariables();
}

void CTblCustomers::CopyDataToDatabase() {

    ARSDB_CHKDATEFIELD(CreationDate);
    ARSDB_CHKSTRFIELD(ContactFirstName);
    ARSDB_CHKSTRFIELD(ContactLastName); 
    ARSDB_CHKSTRFIELD(AddressLineOne); 
    ARSDB_CHKSTRFIELD(AddressLineTwo);
    ARSDB_CHKSTRFIELD(City);
    ARSDB_CHKSTRFIELD(StateOrProvince);
    ARSDB_CHKSTRFIELD(PostalCode);
    ARSDB_CHKSTRFIELD(Country);
    ARSDB_CHKSTRFIELD(ContactTitle);
    ARSDB_CHKSTRFIELD(PhoneNumber);
    ARSDB_CHKSTRFIELD(Extension);
    ARSDB_CHKSTRFIELD(FaxNumber);
    ARSDB_CHKSTRFIELD(EmailAddress);
    ARSDB_CHKSTRFIELD(Notes);
    ARSDB_CHKCYFIELD(OrderTotal);
    ARSDB_CHKNUMFIELD(OrderCount);
    ARSDB_CHKDATEFIELD(LastOrder);
    ARSDB_CHKDATEFIELD(Birthday);
    
    Active_isNULL = FALSE;
    
    
    if (!IsEOF() && !IsBOF()) {
        ARSDB_SETFIELD(CreationDate);
        ARSDB_SETFIELD(ContactFirstName);
        ARSDB_SETFIELD(ContactLastName); 
        ARSDB_SETFIELD(AddressLineOne); 
        ARSDB_SETFIELD(AddressLineTwo);
        ARSDB_SETFIELD(City);
        ARSDB_SETFIELD(StateOrProvince);
        ARSDB_SETFIELD(PostalCode);
        ARSDB_SETFIELD(Country);
        ARSDB_SETFIELD(ContactTitle);
        ARSDB_SETFIELD(PhoneNumber);
        ARSDB_SETFIELD(Extension);
        ARSDB_SETFIELD(FaxNumber);
        ARSDB_SETFIELD(EmailAddress);
        ARSDB_SETFIELD(OrderCount);
        ARSDB_SETCYFIELD(OrderTotal);
        ARSDB_SETFIELD(LastOrder);
        ARSDB_SETFIELD(Active); 
	ARSDB_SETFIELD(Notes);
        ARSDB_SETFIELD(Birthday);
    }
    else
        ClearVariables();
}

// This function returns the records from a table related by a foreign key to this customer
// using the filter function
BOOL CTblCustomers::GetOrderTotals(CTblOrderTotals &tbl)
{
    if (pk_id) {
        CString filter, id;
        id.Format("%d", pk_id);
        filter = CString("fk_customer_id = ") + id;
        tbl.SetFilter(filter);
        if (!tbl.IsBOF()) {
           tbl.MoveFirst();
           return TRUE;
        }
        else
           return FALSE;
    }
    return FALSE;
}

Using the classes in an application should be fairly straight forward for anyone who has used the standard database classes. For example:


        try {
	    CARSConnection conn("Provider=MSDASQL;DSN=microsoft", "support", "support");           
            CTblCustomers rs(&conn);
            
            rs.MoveFirst();
            while (!rs.IsEOF()) {                
                cout << (LPCSTR )rs.ContactFirstName << " " << (LPCSTR )rs.ContactLastName << endl;
                rs.MoveNext();
            }
        }
        catch (CARSException *e) {
            e->ReportError();
        }


These classes were developed for a specific project, and have been refined as needed. They have been stable for some time now, and have proven sufficient for us to develop a commercial application based on them, but may be missing functionality required for your specific needs.

Known drawbacks and limitations

There seems to be a bug in some ADO providers that affects the SetFilter function. For some reason, more than two filter conditions cause the filter to fail and return all records in the recordset.

If you decide to use this code

You are free to use or modify this code subject to the following restrictions:

You must acknowledge us somewhere in your about box or documentation, simple "Parts of code by.." will be enough. If you cannot (or don't want to) mention us, contact one of us personally.

Do not remove copyright notices from the source and header files.

Do not publish any part of this code or article on other websites.

We reserve exclusive right to update this page, as well as provided source code. Please publish your modification and additions on adjacent pages. We may add your modifications and/or additions to a future update to the article and source with proper credit for your work.

Downloads

Download source - 19 Kb


Comments

  • Too complex

    Posted by Legacy on 06/24/2003 12:00am

    Originally posted by: Malan

    The Idea is nice but your implementation could be simpler.

    Reply
  • Awful !

    Posted by Legacy on 06/11/2001 12:00am

    Originally posted by: thE revieweR

    If you try to read the coding above, you may become confused about database handling !

    Reply
  • British...

    Posted by Legacy on 08/10/1999 12:00am

    Originally posted by: PM

    Some of my British colleagues think this class is great...

    "I'm having trouble with my ARSObject!"

    "This ARSDatabase is a pain in the arse"

    These would be the same people that got a kick out of the name of some bug tracking software we used - "Buggery" (Where else would you keep bugs?)

    -pm

    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 …

  • CentreCorp is a fully integrated and diversified property management and real estate service company, specializing in the "shopping center" segment, and is one of the premier retail service providers in North America. Company executives travel a great deal, carrying a number of traveling laptops with critical current business data, and no easy way to back up to the network outside the office. Read this case study to learn how CentreCorp implemented a suite of business continuity services that included …

Most Popular Programming Stories

More for Developers

RSS Feeds