InformIT: Visual C++ 6 Unleashed > The CRecordset Class

Table of Contents

The `CRecordset` Class

You use CRecordset to encapsulate queries of the database, as well as to add, update, or delete rows. The MFC AppWizard generates a derived CRecordset class and instantiates it with a pointer named m_pSet that can be used to invoke any CRecordset functionality. In our example, a new class derived from CRecordSet is defined in the Chap21Set.h header file, as shown in gray in the following code:

// Chap21Set.h : interface of the CChap21Set class
//
/////////////////////////////////////////////////////////////////////////////

#if !defined(AFX_CHAP21SET_H__FCC314EE_AF89_11D3_9447_9FD0F70EAABB__INCLUDED_)
#define AFX_CHAP21SET_H__FCC314EE_AF89_11D3_9447_9FD0F70EAABB__INCLUDED_

#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000


class CChap21Set : public CRecordset


{

You will almost never use CRecordset objects without deriving your own class, because the CRecordset class provides a framework for your own derived classes to use member variables to receive the data from rows returned by the database.

Record Field Exchange

The MFC framework can move data back and forth between the database and the member variables of your CRecordset by using record field exchange, which works very much like the dialog data exchange mechanism you saw in Chapter 5, "Creating and Using Dialog Boxes."

The exchange is set up by implementing the DoFieldExchange() function for your CRecordset class. Like the CDialog::DoDataExchange() function, the bulk of your implementation will use a set of macros that MFC provides for defining record field exchange. Like the DFX_ macros, MFC provides a range of different RFX_ macros for different data types. Table 21.1 shows the available RFX_ macros and the data types for the member variables with which they are used.

Table 21.1. Record Field Exchange Macros

`RFX` Macro	Member Variable Type
`RFX_Binary()`	`CByteArray`
`RFX_Bool()`	`BOOL`
`RFX_Byte()`	`int`
`RFX_Date()`	`CTime`
`RFX_Double()`	`double`
`RFX_Int()`	`int`
`RFX_Long()`	`LONG`
`RFX_LongBinary()`	`CLongBinary`
`RFX_Single()`	`float`
`RFX_Text()`	`CString`

Each of the macros listed in the table takes three parameters: the CFieldExchange pointer passed to DoFieldExchange(), the name of the database field, and the member variable to hold the data for that field. The DoFieldExchange() function from our ex ample (in Chap21Set.cpp) should help illustrate how these are used:

void CChap21Set::DoFieldExchange(CFieldExchange* pFX) {
    //{{AFX_FIELD_MAP(CChap21Set)
    pFX->SetFieldType(CFieldExchange::outputColumn);
    RFX_Long(pFX, _T("[EmpId]"), m_EmpId);
    RFX_Text(pFX, _T("[EmpName]"), m_EmpName);
    RFX_Text(pFX, _T("[Salary]"), m_Salary);
    RFX_Text(pFX, _T("[Dept]"), m_Dept);
    //}}AFX_FIELD_MAP
}

After you set up your DoFieldExchange() function, the member variables of your recordset class are updated automatically to hold the values for the current row when you fetch a new row. The data from the member variables also is used automatically when you add new records or update existing records, as you will see later in the "Changing Data in a Recordset" section.

`GetFieldValue()`

As an alternative to record field exchange, you can always call CRecordset:: GetFieldValue(), which enables you to retrieve the value of any field in the current row, even if you have not defined a member variable for the column or set up record field exchange. You can use this function to retrieve the value for a column based on its index or the column name. You can retrieve values as a CString or a CDBVariant object, which enables you to handle many different types of data using a single data type.

Refreshing the Recordset

If you want to refresh the recordset, you can call CRecordset::Requery(). This goes out and retrieves the data from the database again, including any changes that were made since the recordset was opened. If you are using a snapshot recordset, this is the only way to get at any changes made to the data by other users. Also, a dynaset recordset will not report any INSERTs until a Requery() is called.

Moving About in the Recordset

The member variables you defined in your CRecordset class to hold column data are only intended to hold one value at a time. To access the data for the rows in the record set, move to a specific row in the recordset. The CRecordset class provides several methods that make moving around the recordset quite simple, starting with CRecordset::MoveNext().

Moving Forward

MoveNext() takes no parameters and simply moves on to the next row in the recordset, updating the member variables of the recordset along the way. Note that calling Move() after you have scrolled to the last record throws an exception. To prevent this, you should call IsEOF() to find out whether you are at the end of the recordset before calling Move().

The following example shows the use of MoveNext() to trace through the rows in the recordset. Note that exception handling has been removed to simplify the example, but your applications should be prepared to catch any exceptions that may be thrown:

    do {
        m_pSet->MoveNext();
    }
    while(!m_pSet->IsEOF());

Scrolling

If your recordset uses a forward-only cursor, you'll have to make do with just the MoveNext() function. If your driver supports scrolling, however, you have several other options. You can check whether the recordset supports scrolling by calling CRecordset::CanScroll().

The MoveFirst() and MoveLast() methods move to the first and last record in the recordset. You also can move to any specific row by calling SetAbsolutePosition(), which takes a zero-based index into the recordset.

Additionally, the MovePrev() member moves to the previous row. Calling MovePrev() when you are already at the beginning of the recordset generates an exception, so it is a good idea to call IsBOF() to check whether you are at the beginning of the recordset before calling MovePrev().

Using Bookmarks

You can navigate to specific rows in the recordset by using bookmarks, provided your ODBC driver supports them. You can determine whether bookmarks are supported by calling CanBookmark(). You can call the GetBookmark() member to retrieve a bookmark for the current row in the recordset. You then can return to that specific row later by passing the bookmark value to SetBookmark().

`CRecordset::Move()`

Each of the previous navigation functions is supported by the more generic Move() function, which you can use to move a number of rows forward or backward from the current row. Here is the prototype for Move():

virtual void Move( long nRows, WORD wFetchType = SQL_FETCH_RELATIVE );

The nRows parameter takes the number of rows to move—positive values are used to move forward, and negative values may be used to move backward.

You may omit the wFetchType parameter and simply use the SQL_FETCH_RELATIVE default, or you may specify one of the following values:

SQL_FETCH_ABSOLUTE—Fetches the nRowth row in the recordset.
SQL_FETCH_BOOKMARK—Fetches the row specified by a bookmark value passed in nRows.
SQL_FETCH_FIRST—Fetches the first row in the recordset.
SQL_FETCH_LAST—Fetches the last row in the recordset.
SQL_FETCH_NEXT—Fetches the next row.
SQL_FETCH_PRIOR—Fetches the previous row.
SQL_FETCH_RELATIVE—Fetches the row nRows from the current row.

If an error occurs in the Move() operation, it throws an exception; otherwise, the current row is set to the new row, and the member variables of the recordset are updated to hold the data for the new row.

Changing Data in a Recordset

In most cases, if your application is to do any real work, it eventually modifies the data in the database. This may consist of adding new rows to tables, deleting rows, or updating the data in an existing row. Because deleting a row is the simplest of these operations, let's start with that.

Deleting a Row

To delete a row from a datasource, you simply open the recordset and call Move() or MoveNext() until the row you want to delete is the current row; then call CRecordset::Delete(). This removes the current row from the database and sets the recordset's member variables to NULL values. You call a Move function to move to another valid row.

Adding a New Row

Before adding a new row, you should be certain that you have not opened the recordset as read-only. You can easily check this by calling CRecordset::CanAppend() to see whether you can add records using this recordset.

Adding a new row involves three steps. First, call CRecordset::AddNew() to create a new empty row. Then set the member variables of the recordset to the values you want to place in the new row. Finally, call CRecordset::Update() to add the new row to the database. The following example illustrates this process:

if(m_pSet->CanAppend()) {
    m_pSet->AddNew();
    strcpy(m_pSet->m_EmpName, "George Washington");
    strcpy(m_pSet->m_Dept, "Executive");
    m_pSet->m_Salary = 100000;
    if(m_pSet->Update())
        TRACE("Row Added OK\n");
    else
        TRACE("Row Not Added\n");
} // end if CanAppend()

If you are using a dynaset, the new row appears as the last row in the recordset. If you are using a snapshot, however, you call Requery() to update the recordset to include the new row.

Editing an Existing Row

Updating is one of the most complex database update methods in the MFC ODBC API. Fortunately, the AppWizard generates code that allows updates. However, you need to understand the Update() function for more complex update operations within a recordset.

Like adding a new row, editing an existing row requires three steps. First, call CRecordset::Edit() to begin the update process for the current row. Next, change the values of the member variables of the recordset. When you call CRecordset::Update(), these changes are transferred to the database. If you choose not to update the row after you have called Edit(), you can cancel the update by calling CancelUpdate().

The following example shows how you can use Edit() and Update() to modify the current existing row in the database:

if(m_pSet->CanUpdate()) {
    m_pSet->Edit();
    strcpy(m_pSet->m_EmpName, "George Washington");
    strcpy(m_pSet->m_Dept, "Executive");
    m_pSet->m_Salary = 100000;
    if(m_pSet->Update())
        TRACE("Row Added OK\n");
    else
        TRACE("Row Not Added\n");
 } // end if CanAppend()

Working with `NULL` Values

If you want to set a field in the row to a NULL value, you should first check to see whether a NULL value is acceptable for that field by calling IsFieldNullable(). You then can set the field to a NULL value by calling SetFieldNull(). You also can check to see whether a field currently is set to a NULL value by calling IsFieldNull().

Row Locking

When updating rows, you have a choice of how the framework will lock rows that are being updated. Optimistic locking locks the row only during the processing of the Update() call, and pessimistic locking locks the row when you call Edit() and does not release it until after you call Update(). You can set the locking mode by calling CRecordset::SetLockingMode() after you have opened the recordset, but before any calls to Edit().

Using Statement Parameters

In many cases, you will want to execute some queries that are very similar, but with a few variable values. For example, suppose that you want to derive a CRecordset class that retrieves a set of employee records that fall within a certain salary range. Instead of deriving a separate class for each range you might want to deal with, you can create one class that uses statement parameters to modify the query at runtime.

To use parameters, you can add parameter placeholders to the SQL strings that make up the query for your recordset. Most often, you will parameterize the m_strFilter string, although you also may use parameters in the m_strSort string or in the SQL string passed to Open(). For the salary range example in this chapter, you might want to use a value of m_strFilter, such as the following:

m_strFilter = "WHERE Salary >= ? AND Salary <= ?";

This statement uses two parameters—MFC will substitute real values for the ? in the SQL whenever you call Open() or Requery() for the recordset. To implement the parameters, you add member variables to your recordset class for each parameter and set the m_nParams member of CRecordset to the number of parameters you will be using.

Next, you modify the DoFieldExchange() member of your recordset to move values between your parameter member variables and the SQL statement sent to the database. You add a call to CFieldExchange::SetFieldType() to set the field type to CFieldExchange::param and then add RFX macros for each of your parameters. The RFX macros for your parameters must appear in the order of the placeholders used in the SQL statement. The parameter name given in the RFX macro is not used for parameter matching, so you can choose any arbitrary name you want.

The following example helps illustrate adding parameters to DoFieldExchange():

void CChap21Set::DoFieldExchange(CFieldExchange* pFX)
{
    //{{AFX_FIELD_MAP(CChap21Set)
    pFX->SetFieldType(CFieldExchange::outputColumn);
    RFX_Long(pFX, _T("[EmpId]"), m_EmpId);
    RFX_Text(pFX, _T("[EmpName]"), m_EmpName);
    RFX_Text(pFX, _T("[Salary]"), m_Salary);
    RFX_Text(pFX, _T("[Dept]"), m_Dept);
    //}}AFX_FIELD_MAP
    pFX->SetFieldType(CFieldExchange::param);
    RFX_Text(pFX, "myFirstParam", m_strParamOne);
    RFX_Long(pFX, _"mySecondParam", m_nParamTwo);
}

The CRecordView Class | Next Section

Previous Section

The CRecordset Class