MFC Database Access

Analogy categoryAnalogies

Two Parallel Frameworks: ODBC and DAO

MFC ships two independent, largely non-interoperable database class hierarchies: the ODBC-based CDatabase/CRecordset/CFieldExchange stack, which talks to any ODBC data source (SQL Server, Oracle, Access via the ODBC driver) through the standard ODBC API, and the older DAO-based CDaoDatabase/CDaoRecordset/CDaoFieldExchange stack, which is optimized specifically for Access .mdb files via the Jet engine and offers richer Access-specific features like direct access to Jet's table-level indexes. The App Wizard historically let a developer pick 'Database support' and choose an ODBC or DAO data source at project-creation time, generating a CRecordset- or CDaoRecordset-derived class pre-wired to the chosen table or query. Mixing the two stacks within the same recordset class is not supported - a class derives from exactly one hierarchy - though a single application can freely use both if it needs to talk to, say, SQL Server via ODBC and a local Access file via DAO simultaneously.

🏏

Cricket analogy: Having two non-interoperable database stacks is like a cricket board maintaining two entirely separate scoring systems - one for international matches following ICC standards (ODBC) and one for domestic league matches with its own local rules (DAO) - both valid but not directly compatible.

Record Field Exchange

A CRecordset-derived class binds its columns to C++ member variables through an override of DoFieldExchange, containing a series of RFX_Text, RFX_Long, RFX_Date, RFX_Bool, and similar calls (the DAO equivalent uses DFX_Text, DFX_Long, etc. in an overridden DoFieldExchange as well) - this is conceptually the same code-generation pattern as DDX for dialog controls, just aimed at database columns instead of window controls, and it's exactly what the App Wizard/Class Wizard generates automatically when you drag a table into the New Class dialog. Because RFX calls run in both directions - reading a fetched row into member variables and writing edited member variables back out on Update() - a recordset class doubles as both the query's result buffer and the vehicle for inserts and edits, so calling AddNew(), setting the member variables, then calling Update() is the idiomatic way to insert a new row without writing raw INSERT SQL. The order and count of RFX_ calls in DoFieldExchange must exactly match the column order of the SELECT statement (typically built from GetDefaultSQL() or overridden explicitly), and a mismatch produces silently wrong data rather than a compile error.

🏏

Cricket analogy: RFX field exchange running in both directions - populating member variables from a fetched row, then writing them back on Update() - is like a scorer's ledger both recording each ball bowled and being the same document umpires reference to make correction entries.

Dynasets, Snapshots, and Performance

CRecordset::Open takes a type parameter - dynaset, snapshot, or forward-only - that fundamentally changes how the data behaves: a dynaset keeps a live, keyset-driven cursor where edits made by other users become visible as you scroll (at the cost of a round-trip per row to check for changes), a snapshot takes a static, point-in-time copy of the result set that's cheaper to scroll through but never reflects later changes, and a forward-only recordset (the cheapest of all) can only be read once from top to bottom, which is ideal for populating a report or CListCtrl and then discarding the recordset. Fetching large result sets row-by-row through CRecordset::MoveNext is dramatically slower than necessary if a developer forgets to set an appropriate SQL WHERE clause or LIMIT/TOP equivalent before Open(), since MFC's ODBC layer, by default, does not silently cap how much data a query returns. CDBException is the exception type thrown for most ODBC-level errors (connection failures, constraint violations, timeout), and production MFC database code wraps CDatabase::Open, CRecordset::Open, and Update() calls in try/catch(CDBException* e) blocks, calling e->Delete() in the catch block since CDBException objects are heap-allocated by the framework rather than being stack-based like standard C++ exceptions.

🏏

Cricket analogy: A dynaset behaving like a live cursor reflecting other users' edits is like a live scoreboard updating in real time as other officials enter data, whereas a snapshot is like a printed scorecard handed out at the tea break that never updates again regardless of what happens afterward.

cpp

class CCustomerSet : public CRecordset
{
public:
    CCustomerSet(CDatabase* pDB = NULL) : CRecordset(pDB)
    {
        m_nCustomerID = 0;
        m_strName.Empty();
        m_nFields = 2;
    }

    long    m_nCustomerID;
    CString m_strName;

    CString GetDefaultSQL() override { return _T("[Customers]"); }

    void DoFieldExchange(CFieldExchange* pFX) override
    {
        pFX->SetFieldType(CFieldExchange::outputColumn);
        RFX_Long(pFX, _T("[CustomerID]"), m_nCustomerID);
        RFX_Text(pFX, _T("[Name]"), m_strName);
    }
};

// Usage with proper exception handling
try
{
    CDatabase db;
    db.OpenEx(_T("DSN=SalesDB"), CDatabase::noOdbcDialog);

    CCustomerSet rs(&db);
    rs.Open(CRecordset::snapshot, _T("SELECT CustomerID, Name FROM Customers WHERE Region = 'West'"));

    while (!rs.IsEOF())
    {
        TRACE(_T("%ld: %s\n"), rs.m_nCustomerID, rs.m_strName);
        rs.MoveNext();
    }
    rs.Close();
    db.Close();
}
catch (CDBException* e)
{
    AfxMessageBox(e->m_strError);
    e->Delete();
}

A forward-only recordset is typically the fastest choice for populating a read-only report or CListCtrl because it never allocates the bookmarking/keyset structures that dynasets and even scrollable snapshots require.

A CDBException pointer must always be released with e->Delete() in the catch block; unlike standard C++ exceptions caught by value or reference, MFC's CException hierarchy (which CDBException derives from) allocates exception objects on the heap, and forgetting Delete() leaks memory on every caught error.

MFC provides two separate, non-interoperable database stacks: ODBC-based CDatabase/CRecordset and DAO-based CDaoDatabase/CDaoRecordset.
DoFieldExchange with RFX_ (or DFX_ for DAO) calls binds recordset columns to member variables in both the read and write direction.
The order and count of RFX_ calls must exactly match the SELECT statement's column order, or data is silently misassigned.
AddNew() followed by setting member variables and calling Update() is the idiomatic way to insert a row without raw INSERT SQL.
CRecordset::Open's type parameter (dynaset, snapshot, forward-only) trades off live-update visibility against fetch performance.
Forward-only recordsets are the cheapest option for a single top-to-bottom read, ideal for reports or list population.
CDBException objects are heap-allocated and must be released with e->Delete() in every catch block to avoid memory leaks.