The MFC ODBC Classes—CRecordset and CDatabase

With the MFC classes for Windows, you use C++ objects instead of window handles and device context handles; with the MFC ODBC classes, you use objects instead of connection handles and statement handles. The environment handle is stored in a global variable and is not represented by a C++ object. The two principal ODBC classes are CDatabase and CRecordset. Objects of class CDatabase represent ODBC connections to data sources, and objects of class CRecordset represent scrollable rowsets. The Visual C++ documentation uses the term "recordset" instead of "rowset" to be consistent with Microsoft Visual Basic and Microsoft Access. You seldom derive classes from CDatabase, but you generally derive classes from CRecordset to match the columns in your database tables.

For the author query in the previous section, you would derive (with the help of ClassWizard) a CAuthorSet class from CRecordset that had data members for first name, last name, city, state, and zip code. Your program would construct a CAuthorSet object (typically embedded in the document) and call its inherited Open member function. Using the values of parameters and data members, CRecordset::Open constructs and opens a CDatabase object; this function issues an SQL SELECT statement and then moves to the first record. Your program would then call other CRecordset member functions to position the ODBC cursor and exchange data between the database fields and the CAuthorSet data members. When the CAuthorSet object is deleted, the recordset is closed and, under certain conditions, the database is closed and deleted. Figure 31-2 shows the relationships between the C++ objects and the ODBC components.

Figure 31-2. MFC ODBC class database relationships.

It's important to recognize that the CAuthorSet object contains data members that represent only one row in a table, the so-called "current record." The CRecordset class, together with the underlying ODBC rowset code, manages the database dynaset or snapshot.

The important CRecordset member functions discussed in this chapter are summarized in the table below.

Function	Description
Open	Opens the recordset
AddNew	Prepares to add a new record to the table
Update	Completes an AddNew or Edit operation by saving the new or edited data in the data source
Delete	Deletes the current record from the recordset
Edit	Prepares to implement changes on the current record
IsBOF	Determines whether the recordset has been positioned before the first record
IsEOF	Determines whether the recordset has been positioned after the last record
MoveNext	Sets the current record to the next record or to the next rowset
MoveFirst	Sets the current record to the first record in the recordset
MoveLast	Sets the current record to the last record or to the last rowset
MovePrev	Sets the current record to the previous record or to the previous rowset
GetDefaultConnect	Gets the default connect string for the data source on which the recordset is based
GetDefaultSQL	Gets the default SQL string
DoFieldExchange	Exchanges data between the recordset data fields and the corresponding record on the data source
GetStatus	Gets the index of the current record in the recordset and the final count status
GetRecordCount	Determines the highest-numbered record yet encountered as the user moves through the records
GetODBCFieldCount	Gets the number of fields in the recordset object
GetODBCFieldInfo	Gets information about the fields in the recordset

Counting the Rows in a Recordset

It's difficult to know how many records are contained in an ODBC recordset. ODBC doesn't provide an accurate count of the rows in a recordset until you've read past the end. Until that time, the count returned from the CRecordset::GetRecordCount member function is a "high-water mark" that returns only the last row accessed by CRecordset::MoveNext. The CRecordset::GetStatus function returns a CRecordsetStatus object, which has a member m_bRecordCountFinal that indicates whether the count is final.

The CRecordset::MoveLast function does not register the record count for you, even for dynasets. If you want to know how many records are included in a recordset, loop through the whole table with MoveNext calls. (A faster alternative is to use the COUNT function.) If your program adds or deletes a record or if another user adds or deletes a record, the record count is not adjusted.

Processing ODBC Exceptions

Many MFC ODBC calls don't return an error code but instead throw a CDBException object, which contains a string describing the error. Suppose you are trying to delete a record from a table in an Access database. Access might be enforcing referential integrity rules, which means that you're not allowed to delete that row because a row in another table depends on it. If you call CRecordset::Delete, you'll see an ODBC error message box that came from the MFC base classes.

You certainly appreciate the error message, but now ODBC has "lost its place" in the recordset, and there is no longer a current record. Your program needs to detect the error so that it won't call functions that depend on a current record, such as CRecordset::MoveNext. You must handle the exception in this way:

try {
    m_pSet->Delete();
}
catch(CDBException* e) {
    AfxMessageBox(e->m_strError);
    e->Delete();
    m_pSet->MoveFirst(); // lost our place!
    UpdateData(FALSE);
    return;
}
m_pSet->MoveNext();