Now we'll put everything together and build another working programan MDI application that connects to any DAO data source. The application dynamically displays tables in scrolling view windows, and it allows the user to type in the SQL QUERY statement, which is stored in the document along with data source and table information. AppWizard generates the usual MDI main frame, document, application, and view classes, and we change the view class base to CRowView and add the DAO-specific code. Figure 32-2 shows the EX32A program in operation.
The document's File menu includes the following commands:
DAO Open MDB
DAO Open ISAM
DAO Open ODBC
The user must choose one of these commands after opening a document. As you will see, the code for opening the database is different depending on the data source type.
You can learn a lot about this application by looking at the three-view window in Figure 32-2. The two view windows in the upper part of the main window are tied to the same document, and the lower view window is tied to another document. The dialog bar shows the SQL statement associated with the active view window.
Figure 32-2. The EX32A program in operation.
The EX32A example includes source code listings and resource requirements. Here is a table of the files and classes.
Header File | Source Code File | Class | Description |
Ex32a.h | Ex32a.cpp | CEx32aApp | Main application |
MainFrm.h | MainFrm.cpp | CMainFrame | MDI main frame |
ChildFrm.h | ChildFrm.cpp | CChildFrame | MDI child frame |
Ex32aDoc.h | Ex32aDoc.cpp | CEx32aDoc | EX32A document |
Ex32aView.h | Ex32aView.cpp | CEx32aView | Scrolling database view class |
Rowview.h | Rowview.cpp | CRowView | Row view base class |
Tablesel.h | Tablesel.cpp | CTableSelect | Table selection dialog class |
IsamSelect.h | IsamSelect.cpp | CIsamSelect | ISAM-type data source selection dialog class |
StdAfx.h | StdAfx.cpp | Precompiled headers |
Now we'll go through the application's classes one at a time, excluding CRowView. You'll see the important data members and the principal member functions.
The application class is the unmodified output from AppWizard. Nothing special here.
These classes are the standard output from AppWizard except for the addition of the dialog bar created in the CMainFrame::OnCreate member function.
The document class manages the database connections and recordsets. Each document object can support one main recordset attached to one data source. A document object can have several views attached. Data sources (represented by CDaoDatabase objects) are not shared among document objects; each document has its own.
The important CEx32aDoc data members are listed in the following table.
Data Member | Description |
m_pRecordset | Pointer to the document's recordset object |
m_database | Document's embedded CDaoDatabase object |
m_strDatabase | Database pathname (MDB file) |
m_strConnect | ODBC connection string or ISAM connection string |
m_strQuery | Entire SQL SELECT statement |
m_bConnected | Flag that is TRUE when the document is connected to a recordset |
m_nFields | Number of fields (columns) in the recordset |
m_nRowCount | Number of records (rows) in the recordset |
m_nDatabaseType | enum {UNK, MDB, ISAM, ODBC} |
This overridden CDocument function is called when the user loads a document from disk. The document contains the name of the database and the query string, so the program can open the database and run the query upon loading.
BOOL CEx32aDoc::OnOpenDocument(LPCTSTR lpszPathName) { if (!CDocument::OnOpenDocument(lpszPathName)) return FALSE; PutQuery(); switch (m_nDatabaseType) { case UNK: break; case MDB: DaoOpenMdb(); break; case ISAM: DaoOpenIsam(); break; case ODBC: DaoOpenOdbc(); break; } return TRUE; }
This overridden CDocument function closes the database if one is connected:
void CEx32aDoc::OnCloseDocument() { m_strQuery.Empty(); PutQuery(); if (m_bConnected) { delete m_pRecordset; // Destructor calls Close m_database.Close(); m_bConnected = FALSE; m_pRecordset = NULL; m_nRowCount = 0; } CDocument::OnCloseDocument(); }
These functions are called in response to the user choosing the DAO Open ODBC command from the File menu. DaoOpenOdbc, which is also called by OnOpenDocument, calls CDaoDatabase::Open with the connect parameter string. The string "ODBC;" causes the ODBC data source selection dialog to be displayed. Notice the use of the try/catch block to detect SQL processing errors.
void CEx32aDoc::OnFileDaoOpenOdbc() { m_strConnect.Empty(); m_strQuery.Empty(); DaoOpenOdbc(); } void CEx32aDoc::DaoOpenOdbc() { // can't open ODBC using Access driver if (m_strConnect.IsEmpty()) { m_strConnect = "ODBC;"; } BeginWaitCursor(); try { // nonexclusive, read-only m_database.Open("", FALSE, TRUE, m_strConnect); } catch (CDaoException* e) { ::DaoErrorMsg(e); EndWaitCursor(); e->Delete(); return; } m_strConnect = m_database.GetConnect(); TRACE("database name = %s, connect = %s\n", (const char*) m_strDatabase, (const char*) m_strConnect); OpenRecordset(); m_nDatabaseType = ODBC; EndWaitCursor(); }
These functions are called in response to the user choosing the DAO Open ISAM command from the File menu. DaoOpenIsam, which is also called by OnOpenDocument, gets a directory name from the user (through the CIsamSelect class) and then calls CDaoDatabase::Open with the connect parameter string. The CIsamSelect::m_strIsam string specifies the type of file. Example strings are "dBASE III", "FoxPro 2.6", and "Excel 8.0".
void CEx32aDoc::OnFileDaoOpenIsam() { m_strConnect.Empty(); m_strQuery.Empty(); DaoOpenIsam(); } void CEx32aDoc::DaoOpenIsam() { BeginWaitCursor(); if (m_strConnect.IsEmpty()) { CIsamSelect isamDlg; if (isamDlg.DoModal() != IDOK) { return; } m_strConnect = isamDlg.m_strIsam + ";DATABASE=" + isamDlg.m_strDirectory; TRACE("m_strConnect = %s\n", (const char*) m_strConnect); } try { // nonexclusive, read-only m_database.Open("", FALSE, TRUE, m_strConnect); } catch(CDaoException* e) { ::DaoErrorMsg(e); EndWaitCursor(); e->Delete(); return; } m_strConnect = m_database.GetConnect(); TRACE("database name = %s, connect = %s\n", (const char*) m_strDatabase, (const char*) m_strConnect); OpenRecordset(); m_nDatabaseType = ISAM; EndWaitCursor(); }
These functions are called in response to the user choosing the DAO Open MDB command from the File menu. DaoOpenMdb, which is also called by OnOpenDocument, uses the MFC CFileDialog class to get an MDB file pathname from the user. Compare the CDaoDatabase::Open call with the calls in the two preceding functions. Notice that the MDB pathname is passed as the first parameter.
void CEx32aDoc::OnFileDaoOpenMdb() { m_strDatabase.Empty(); m_strQuery.Empty(); DaoOpenMdb(); } void CEx32aDoc::DaoOpenMdb() { if (m_strDatabase.IsEmpty()) { CFileDialog dlg(TRUE, ".mdb", "*.mdb"); if (dlg.DoModal() == IDCANCEL) return; m_strDatabase = dlg.GetPathName(); } BeginWaitCursor(); try { // nonexclusive, read-only m_database.Open(m_strDatabase, FALSE, TRUE); } catch (CDaoException* e) { ::DaoErrorMsg(e); EndWaitCursor(); e->Delete(); return; } m_strDatabase = m_database.GetName(); TRACE("database name = %s, connect = %s\n", (const char*) m_strDatabase, (const char*) m_strConnect); OpenRecordset(); m_nDatabaseType = MDB; EndWaitCursor(); }
This function closes the DAO database, enabling the document to be saved.
void CEx32aDoc::OnFileDaoDisconnect() { if (m_bConnected) { delete m_pRecordset; // Destructor calls Close m_database.Close(); m_bConnected = FALSE; m_pRecordset = NULL; m_nRowCount = 0; UpdateAllViews(NULL); } }
This helper function is called by DaoOpenOdbc, DaoOpenIsam, and DaoOpenMdb. The CTableSelect class allows the user to select a table name, which is used to construct a SELECT statement. Calls to CDaoRecordset::MoveLast and CDaoRecordset::GetAbsolutePosition set the record count for ODBC, ISAM, and MDB data sources.
void CEx32aDoc::OpenRecordset() { GetQuery(); if (m_strQuery.IsEmpty()) { CTableSelect tableDlg(&m_database); if (tableDlg.DoModal() != IDOK) { m_database.Close(); // escape route return; } m_strQuery.Format("select * from [%s]", tableDlg.m_strSelection); PutQuery(); } m_pRecordset = new CDaoRecordset(&m_database); try { m_pRecordset->Open(dbOpenDynaset, m_strQuery, dbReadOnly); } catch (CDaoException* e) { ::DaoErrorMsg(e); UpdateAllViews(NULL); m_bConnected = FALSE; e->Delete(); return; } if (!m_pRecordset->IsBOF()) { // might be expensive for a really big table // View adjusts its m_nRowCount if you supply a big value here m_pRecordset->MoveLast(); // to validate record count } m_nRowCount = m_pRecordset->GetAbsolutePosition() + 2; TRACE("m_nRowCount = %d\n", m_nRowCount); GetFieldSpecs(); UpdateAllViews(NULL); m_bConnected = TRUE; }
The MFC CDaoRecordset class has m_strFilter and m_strSort data members, as does the ODBC CRecordset class. You can't use these strings, however, if your recordset doesn't have bound fields; you must construct the entire SELECT statement as shown above.
This message handler is called in response to the user clicking the Requery button on the dialog bar. This message handler reads the query string value and regenerates the recordset. Note that the CDaoRecordset::Requery function doesn't handle an updated SELECT statement, so we close and reopen the recordset instead.
void CEx32aDoc::OnRequery() { GetQuery(); // Requery won't work because we're changing the SQL statement BeginWaitCursor(); if(m_pRecordset->IsOpen()) { m_pRecordset->Close(); } try { m_pRecordset->Open(dbOpenDynaset, m_strQuery, dbReadOnly); } catch (CDaoException* e) { ::DaoErrorMsg(e); m_nRowCount = 0; UpdateAllViews(NULL); EndWaitCursor(); e->Delete(); return; } if (!m_pRecordset->IsBOF()) { m_pRecordset->MoveLast(); // to validate record count } m_nRowCount = m_pRecordset->GetAbsolutePosition() + 2; TRACE("m_nRowCount = %d\n", m_nRowCount); GetFieldSpecs(); UpdateAllViews(NULL); EndWaitCursor(); }
These utility functions move the document's query string to and from the edit control on the dialog bar.
The Serialize function reads and writes the m_strConnect, m_strDatabase, and m_strQuery data members.
This class is derived from CRowView and implements the virtual functions.
The CEx32aView class uses the integer variable m_nSelectedRow to track the currently selected row. The recordset pointer is held in m_pSet.
This virtual CView function is called through the application framework when the view is created and when the document's contents change in response to a database open or requery event. If several views are active for a given document, all views reflect the current query but each can maintain its own current row and scroll position. OnUpdate also sets the value of the m_pSet data member. This can't be done in OnInitialUpdate because the recordset is not open at that point.
These functions are implementations of the CRowView class pure virtual functions. They take care of drawing a specified query result row, and they track the current selection.
This virtual function, which is called from CRowView, simply returns the record count value stored in the document.
The OnDrawRow virtual function is called from CRowView member functions to perform the actual work of drawing a designated row. OnDrawRow reads the recordset's current row and then calls the CDaoRecordset::Move function to position the cursor and read the data. The try/catch block detects catastrophic errors resulting from unreadable data. The DrawDataRow helper function steps through the columns and prints the values. Notice that OnDrawRow displays "**RECORD DELETED**" when it encounters a record that has been deleted by another user since the dynaset was first created. OnDrawRow and DrawDataRow are shown here:
void CEx32aView::OnDrawRow(CDC* pDC, int nRow, int y, BOOL bSelected) { int x = 0; int i; CEx32aDoc* pDoc = GetDocument(); if (m_pSet == NULL) return; if (nRow == 0) { // title row for (i = 0; i < pDoc->m_nFields; i++) { pDC->TextOut(x, y, pDoc->m_arrayFieldName[i]); x += pDoc->m_arrayFieldSize[i] * m_nCharWidth; } } else { try { m_pSet->SetAbsolutePosition(nRow - 1); // adjust for title row // SetAbsolutePosition doesn't throw exception until AFTER // end of set if (m_pSet->GetAbsolutePosition() == (nRow - 1)) { DrawDataRow(pDC, y); } } catch (CDaoException* e) { // might be a time delay before delete is seen in this program if (e->m_pErrorInfo->m_lErrorCode == 3167) { pDC->TextOut(0, y, "**RECORD DELETED**"); } else { m_pSet->MoveLast(); // in case m_nRowCount is too big pDoc->m_nRowCount = m_pSet->GetAbsolutePosition() + 2; } e->Delete(); } } } void CEx32aView::DrawDataRow(CDC* pDC, int y) { int x = 0; CString strTime; COleVariant var; CString str; CEx32aDoc* pDoc = GetDocument(); for (int i = 0; i < pDoc->m_nFields; i++) { var = m_pSet->GetFieldValue(i); switch (var.vt) { case VT_BSTR: str = (LPCSTR) var.bstrVal; // narrow characters in DAO break; case VT_I2: str.Format("%d", (int) var.iVal); break; case VT_I4: str.Format("%d", var.lVal); break; case VT_R4: str.Format("%10.2f", (double) var.fltVal); break; case VT_R8: str.Format("%10.2f", var.dblVal); break; case VT_CY: str = COleCurrency(var).Format(); break; case VT_DATE: str = COleDateTime(var).Format(); break; case VT_BOOL: str = (var.boolVal == 0) ? "FALSE" : "TRUE"; break; case VT_NULL: str = "----"; break; default: str.Format("Unk type %d\n", var.vt); TRACE("Unknown type %d\n", var.vt); } pDC->TextOut(x, y, str); x += pDoc->m_arrayFieldSize[i] * m_nCharWidth; } }
Because we're working with a dynaset, we want to show database changes made by other programs. The timer handler calls CWnd::Invalidate, which causes all records in the client area to be refreshed, as shown here:
void CEx32aView::OnInitialUpdate() { CRowView::OnInitialUpdate(); } void CEx32aView::OnTimer(UINT nIDEvent) { Invalidate(); // Update view from database }
This is a ClassWizard-generated dialog class that contains a list box used for selecting the table. For the student registration database, the dialog looks like the one shown below.
The CTableSelect data members are as follows.
Data Member | Description |
m_pDatabase | Pointer to the recordset's CDaoDatabase object |
m_strSelection | ClassWizard-generated variable that corresponds to the list-box selection |
The constructor takes a database pointer parameter, which it uses to set the m_pDatabase data member, as shown here:
CTableSelect::CTableSelect(CDaoDatabase* pDatabase, CWnd* pParent /*=NULL*/) : CDialog(CTableSelect::IDD, pParent) { //{{AFX_DATA_INIT(CTableSelect) m_strSelection = ""; //}}AFX_DATA_INIT m_pDatabase = pDatabase; }
This self-contained function creates, opens, and reads the data source's list of tables and puts the table name strings in the dialog's list box, as shown here:
BOOL CTableSelect::OnInitDialog() { CListBox* pLB = (CListBox*) GetDlgItem(IDC_LIST1); int nTables = m_pDatabase->GetTableDefCount(); TRACE("CTableSelect::OnInitDialog, nTables = %d\n", nTables); CDaoTableDefInfo tdi; for (int n = 0; n < nTables; n++) { m_pDatabase->GetTableDefInfo(n, tdi); TRACE("table name = %s\n", (const char*) tdi.m_strName); if (tdi.m_strName.Left(4) != "MSys") { pLB->AddString(tdi.m_strName); } } return CDialog::OnInitDialog(); }
It's handy for the user to choose a list-box entry with a double click. This function is mapped to the appropriate list-box notification message, as shown here:
void CTableSelect::OnDblclkList1() { OnOK(); // Double-clicking on list-box item exits dialog }
This ClassWizard-generated dialog class contains a list box and an edit control used for selecting the ISAM-type data source. The user must type the directory for the files, as shown here.
The CIsamSelect class data members are as follows.
Data Member | Definition |
m_strIsam | ClassWizard-generated variable that corresponds to the list-box selection |
m_strDirectory | ClassWizard-generated variable that corresponds to the edit control contents |
This function sets the initial values of the list box, which are the options from the "Connect Property" topic in Books Online, as shown here:
BOOL CIsamSelect::OnInitDialog() { CListBox* pLB = (CListBox*) GetDlgItem(IDC_LIST1); pLB->AddString("dBASE III"); pLB->AddString("dBASE IV"); pLB->AddString("dBASE 5"); pLB->AddString("Paradox 3.x"); pLB->AddString("Paradox 4.x"); pLB->AddString("Paradox 5.x"); pLB->AddString("Btrieve"); pLB->AddString("FoxPro 2.0"); pLB->AddString("FoxPro 2.5"); pLB->AddString("FoxPro 2.6"); pLB->AddString("Excel 3.0"); pLB->AddString("Excel 4.0"); pLB->AddString("Excel 5.0"); pLB->AddString("Excel 7.0"); pLB->AddString("Text"); CDialog::OnInitDialog(); return TRUE; // Return TRUE unless you set the focus to a control. // EXCEPTION: OCX Property Pages should return FALSE. }