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

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read