Enhanced Database Classes

Max Fomitchev

MFC has great support for database application development. CDatabase and CRecordset are used in virtually every database application, and the Class Wizard provides a fast and easy way to use them. But if your application uses many database tables, Class Wizard will generate a lot of recordset files, and your project will be cluttered with patterns of similar code. Max Fomitchev shows how a template recordset class can reduce the number of recordset files and enhance CRecordset functionality at the same time.

This article presents a new database template class-CDataSet. The main purpose of this class is to reduce the amount of source code and to provide an interface for arrays of data objects. The CDataSet template class is defined in Listing 1.

Listing 1. The CDataSet template.

template<class T, int M> class CDataSet : public CRecordset
{
public:
   CDataSet(LPCSTR Table, CDatabase* pdb);
   T               m_Data;          // Attached object
   CString         m_DefaultSQL;    // Default SQL SELECT statement
   CString         m_DefaultSort;   // Default SQL ORDER BY clause
   CString         m_DefaultFilter; // Default SQL WHERE clause

// Operations
public:
   virtual BOOL Search(LPCSTR Filter, LPCSTR Sort = NULL, BOOL bFail = FALSE);
   virtual BOOL DirectSearch(LPCSTR Filter, LPCSTR Sort = NULL, BOOL bFail = FALSE);
   virtual void LoadAll(CArray<T, T>& A, int N = 0);
   virtual void SaveAll(CArray<T, T>& A);
   virtual void Load(T& Data) { Data = m_Data; }
   virtual void Store(T& Data);

// Implementation
protected:
   virtual CString GetDefaultSQL() { return m_DefaultSQL; }
   virtual void DoFieldExchange(CFieldExchange* pFX);
};

This template takes two parameters: a data object class and the number of bound fields. The data object class typically consists of a set of member variables corresponding to different database table fields, like this:

struct DataObj {
   CString   Var1;
   CString   Var2;
   int      Var3;
   float      Var4;
   // Etc.
   // Member functions
};

If your data object is defined as a class rather than a struct, it has to have a default constructor and operator=. The template uses them, and you'll get compiler error messages if they're missing.

The m_DefaultSQL member variable of the CDataSet gives convenient and flexible control over the SQL data source. m_DefaultSQL can contain simply a table name, a list of joint tables separated by commas, or a complex SQL SELECT statement. Also, since m_DefaultSQL can be changed at runtime, it's possible to use the same class to access different tables (providing that these tables have the same structure). The recordset must be reopened in order for Default SQL changes to take place.

The Load(), Store(), LoadAll(), and StoreAll() methods perform single or multiple data object load and store operations. The final parameter, N, of LoadAll() specifies the maximum number of records to load.

Improved search capabilities are implemented in the Search() and DirectSearch() methods. Search() uses the m_DefaultSort and m_DefaultFilter member variables and throws an exception if the desired record isn't found and the bFail parameter is set to TRUE. DirectSearch() accepts an explicitly specified SQL WHERE clause.

The template class implementation is straightforward and is shown in Listing 2.

Listing 2. Implementation of CDataSet.

template<class T, int M> CDataSet<T, M>::CDataSet(LPCSTR Table, CDatabase* pdb) : 
                                                                    CRecordset(pdb)
{
   m_nFields = M;
   m_DefaultSQL = Table;
   m_DefaultFilter = "%s";
}

template<class T, int M> BOOL CDataSet<T, M>::Search(LPCSTR Filter,LPCSTR Sort, BOOL bFail)
{
   if ( IsOpen() ) 
      Close();

   SetStatus("Opening " + m_DefaultSQL + " ...");

   if ( Filter )
      m_strFilter.Format(m_DefaultFilter, Filter);
   else
      m_strFilter = "";
   m_strSort = Sort;

   Open();

   // Throw exception if record not found
   if ( bFail && IsEOF() )
      THROW(new CMyException(m_DefaultSQL + " record not found!"));
   return !IsEOF();
}

template<class T, int M> BOOL CDataSet<T, M>::DirectSearch(LPCSTR Filter, LPCSTR Sort, BOOL bFail)
{
   if ( IsOpen() ) 
      Close();

   SetStatus("Opening " + m_DefaultSQL + " ...");

   m_strFilter = Filter;
   m_strSort = Sort;
   Open();
   
   // Throw exception if record not found
   if ( bFail && IsEOF() )
      THROW(new CMyException(m_DefaultSQL + " record not found!"));
   return !IsEOF();
 }

template<class T, int M> void CDataSet<T, M>::LoadAll(CArray<T, T>& A, int N)
{
   SetStatus("Loading " + m_DefaultSQL + " ...");
   A.RemoveAll();
      
   while ( !IsEOF() && (N == 0 || A.GetSize() < N) )
   {
      A.Add(m_Data);
      MoveNext();
   }  
}

template<class T, int M> void CDataSet<T, M>::SaveAll(CArray<T, T>& A)
{
   SetStatus("Writing " + m_DefaultSQL + " ...");
      
   for ( int i = 0;i < A.GetSize(); i++ )
   {
      AddNew();
      Store(A[i]);
   }  
}

template<class T, int M> void CDataSet<T, M>::Store(T& Data)
{
   SetStatus("Updating " + m_DefaultSQL + " ...");
   Edit();
   m_Data = Data;
   Update();
}

template<class T, int M> void CDataSet<T, M>::Close()
{
   CRecordset::Close();
   SetStatus("Ready");
}

Many of these CDataSet methods use a SetStatus() function, which displays an hourglass cursor and shows current operation status on the application's status line:

void SetStatus(const CString Msg)
{
   CFrameWnd* pMainFrame = (CFrameWnd*)AfxGetMainWnd();

   if ( pMainFrame )
   {
      pMainFrame->SetMessageText(Msg);
      pMainFrame->UpdateWindow();

      if ( strcmp(Msg, "Ready") == 0 )
         pMainFrame->EndWaitCursor();
      else
         pMainFrame->BeginWaitCursor();
   }
}

This function is particularly useful when processing large amounts of data or executing complex queries. It gives the user an idea why the application is waiting.

The methods throw exceptions if they run into trouble. CMyException is a simple exception class that displays the specified error message:

class CMyException: public CException {
   CString m_ErrorMsg;
public:
   CMyException(int ErrMsgResourceID);
   CMyException(CString ErrMsg);
   BOOL GetErrorMessage(LPTSTR lpszError, 
                        UINT nMaxError,
                        PUINT pnHelpContext = NULL);
};

CMyException::CMyException(int ResourceID)
{
   m_ErrorMsg.LoadString(ResourceID);
}

CMyException::CMyException(CString ErrorMsg)
{
   m_ErrorMsg = ErrorMsg;
}

BOOL CMyException::GetErrorMessage(LPTSTR lpszError,
                                   UINT nMaxError,
                                   PUINT)
{
   strncpy(lpszError, (LPCSTR)m_ErrorMsg, nMaxError);
   return TRUE;
}

In your application, an instance of the CDataSet class can be instantiated like this:

CDataset<DataObj, 4> MySet("Table1", &db);

"Table1" is the name of the database table, and "&db" points to the open database. In most cases (especially when using transactions), it makes sense to create a database object and open the database explicitly before opening recordsets.

Did you notice that there was one implementation missing from Listing 2? DoFieldExchange()must be implemented separately for each data class, to establish a link between the data object members and the database fields. Listing 3 shows how it might be done for DataObj.

Listing 3. The DoFieldExchange() method for DataObj.

void CDataSet<DataObj, 4>::DoFieldExchange(CFieldExchange* pFX)
{
   pFX->SetFieldType(CFieldExchange::outputColumn);
   RFX_Text(pFX, "VAR1", m_Data.Var1);
   RFX_Text(pFX, "VAR2", m_Data.Var2);
   RFX_Int(pFX, "VAR3", m_Data.Var3);
   RFX_Single(pFX, "VAR4", m_Data.Var4);
}

Here's an example that uses CDataSet:

CDatabase db;
CArray<DataObj, DataObj> A;

TRY {
   db.Open("TEST");
      
   // Create recordset object
   CDataSet<DataObj, 4> MySet("Table1", &db);

   // Set default filter to var1
   MySet.m_DefaultFilter = "Var1 = '%s'";
   MySet.Open();

   // Load all of the records
   MySet.LoadAll(A);

   // Find some record
   if ( MySet.Search("Anything") )
   {
      DataObj B;

      // Load, update, and store new record
      MySet.Load(B);
      B.Var1 = "Anything else?";
      MySet.Store(B);
   }

   MySet.Close();
   AfxMessageBox("Data is loaded!");
}
CATCH_ALL(e) {
   e->ReportError();
}
END_CATCH_ALL

To test the sample project that's available in the Subscriber Downloads at www.pinpub.com/vcd, create a system DSN called TEST (Microsoft Access Driver) and point it to Test.mdb. Run DbTest.exe, and select Test | DataSet from the menu.