Multiple Recordsets

Both the EX31A and EX31B examples relied on a single recordset. In many cases, you'll need simultaneous access to multiple recordsets. Suppose you're writing a program that lets the user add Section records, but you want the user to select a valid CourseID and InstructorID. You'll need auxiliary Course and Instructor recordsets in addition to the primary Section recordset.

In the previous examples, the view object contained an embedded recordset that was created with the CRecordset default constructor, which caused the creation of a CDatabase object. The view's OnInitialUpdate function called CRecordset::Open, which called the virtual CRecordset::GetDefaultConnect function, opened the database, and then called the virtual CRecordset::GetDefaultSQL function. The problem with this scenario is that there can be only one recordset per database because the database is embedded in the recordset.

To get multiple recordsets, you have to do things differently—you must create the CDatabase object first. Then you can construct as many recordsets as you want, passing a CDatabase pointer as a parameter to the CRecordset constructor. You start by embedding a CDatabase object in the document in place of the CRecordset object. You also include a pointer to the primary recordset. Here are the document data members:

CEx31bSet* m_pEx31bSet;
CDatabase m_database;

In your overridden CDocument::OnNewDocument function, you construct the primary recordset on the heap, passing the address of the CDatabase object to the recordset constructor. Here's the code you insert:

if (m_pEx31bSet == NULL) {
    m_pEx31bSet = new CEx31bSet(&m_database);
    CString strConnect = m_pEx31bSet->GetDefaultConnect();
    m_database.Open(NULL, FALSE, FALSE, strConnect, FALSE);
}

The CRecordView::OnInitialUpdate function still opens the recordset, but this time CRecordset::Open does not open the database. (It's already open.) Now the code for setting the view's m_pSet data member is a little different:

m_pSet = GetDocument()->m_pEx31bSet;

Figure 31-5 shows the new relationship between the document, the view, and the primary recordset. Also shown are possible auxiliary recordsets.

Figure 31-5. Object relationships for multiple recordsets.

The EX31C Multiple Recordset Example

The EX31C program is similar to EX31B except that the new database_recordset relationships are implemented and an auxiliary recordset allows listing of the sections an instructor teaches. The EX31C window looks like the screen shown below.

Build the EX31C project, and test the application. Sequence through the instructor records, and watch the Sections Taught list change.

As you can see, there's a new list-box control in the form dialog box. Also, there's one short helper function in the view class, LoadListbox, which loads the list box with the rows in the Section recordset, as shown here:

void CEx31cView::LoadListbox()
{
    CEx31cDoc* pDoc = GetDocument();
    CListBox* pLB = (CListBox*) GetDlgItem(IDC_SECTIONS);
    CSectionSet sect(&pDoc->m_database);  // db passed via constructor

    sect.m_strFilter.Format("InstructorID = `%s'",
        (LPCSTR) m_pSet->m_InstructorID);

    sect.Open();
    pLB->ResetContent();
    while (!sect.IsEOF()) {
        pLB->AddString(sect.m_CourseID + " " + sect.m_SectionNo);
        sect.MoveNext();
    }
    // sect closed by CRecordset destructor
}

Notice that this function sets up a filter string based on the value of the InstructorID field in the primary recordset. LoadListbox is called from these member functions: OnInitDialog, OnMove, OnUpdate, OnRecordAdd, and OnRecordDelete.

Parameterized Queries

The EX31C example sets up and executes a new query each time it accesses the auxiliary recordset. It's more efficient, however, if you set up a single parameterized query, which enables ODBC to bind to a parameter in your program. You can simply change the value of that parameter and re-execute the query.

Here are the steps for querying the section set for all the sections a selected instructor teaches.

1. Add a parameter data member to the section recordset class:

   CString m_InstructorIDParam;

2. Add the following line to the section recordset constructor, which sets the value of the inherited m_nParams data member to the total number of query parameters, in this case 1:

   m_nParams = 1;

3. Add the following code to the section recordset DoFieldExchange function:

   pFX->SetFieldType(CFieldExchange::param);
   RFX_Text(pFX, "Param", m_InstructorIDParam); // Any name will do

4. Add the following code to the view class prior to calling Open for the section recordset:

   sect.m_strFilter = "InstructorID = ?";
   sect.m_InstructorIDParam = m_pSet->m_InstructorID;