MFC provides classes to support database access through DAO or through ODBC. This lesson shows you how to use these classes to create applications that allow you to retrieve and manipulate data stored in an external database.
After this lesson, you will be able to:Estimated lesson time: 50 minutes
- Describe the MFC DAO and ODBC database classes and how they work together to provide access to desktop and client/server databases.
- Describe how to use the AppWizard to create a database application based on the CRecordView or CDaoRecordView class.
- Describe how to use MFC database objects to query a database from within your application code.
- Describe how to filter a recordset based on parameters supplied at run time.
MFC provides two distinct sets of classes for database access: one to allow access through DAO, the other through ODBC. DAO is generally used to access desktop databases and ODBC to connect to relational database servers through a named data source.
Both sets of classes are similar, and are based on a common programming model. The DAO and ODBC database classes often differ only in name and a few relatively minor implementation details. Table 7.1 lists the core ODBC classes, their DAO equivalents, and a brief description of their functions:
Table 7.1 ODBC Classes and Their DAO Equivalents
ODBC class | DAO class | Function |
---|---|---|
CDatabase | CDaoDatabase | Encapsulates a connection to a remote data source or desktop database |
CRecordset | CDaoRecordset | Encapsulates a set of records selected from a table in a database |
CRecordView | CDaoRecordView | Provides a dialog-based form view directly connected to a recordset object |
These classes work together to enable you to retrieve data from a data source so that the data can be displayed in a dialog-based view for browsing or updating. The following sections explain the role of each class. Bear in mind that, because the ODBC and DAO versions of the classes are so similar, we might often discuss the classes in general terms rather than naming them specifically. We will draw your attention to significant differences between the classes where they occur.
The CDatabase class typically establishes a connection to an ODBC data source such as a SQL Server database. The CDaoDatabase class usually establishes a connection to a desktop data source such as an Access database.
Establishing a connection to a database is a two-stage process. You first create a database object, then call that object's Open() member function. Both classes provide similar Open() functions. For CDatabase::Open(), you specify a DSN or a connection string; for CDaoDatabase::Open(), you specify a database file name. The Open() functions take arguments to allow you to specify whether the connection should be opened for exclusive or for read-only access.
For ODBC connections, use the CDatabase::OpenEx() function. The syntax for the OpenEx() function is as follows:
CDatabase::OpenEx (LPCTSTR lpszConnectString, DWORD dwOptions = 0); |
The lpszConnectString parameter is the connection string that specifies the DSN. If Windows NT authentication is not used on the data source, you can supply user and password information in the connection string. If you pass NULL to the lpszConnectString parameter, a dialog box will appear at run time that will prompt you to enter the DSN, user ID, and password.
The second parameter, dwOptions, is a bitmask value that determines the mode in which the data source is opened. The default value is 0, which specifies that the data source will be opened in the shared mode, with write access. Other values that can be used are combinations of those shown in Table 7.2.
Table 7.2 Optional Parameters for the CDatabase::OpenEx Member Function
dwOptions parameter | Meaning |
---|---|
CDatabase::openReadOnly | Opens the data source in a read-only mode |
CDatabase::useCursorLib | Loads the ODBC cursor library |
CDatabase::noOdbcDialog | Does not display the ODBC connection dialog box irrespective of whether insufficient information was provided in the connection string |
CDatabase::forceOdbcDialog | Always displays the ODBC connection dialog box |
This code shows how you might call OpenEx() to open a connection to the pubs DSN, using the sa account with a password of password.
CDatabase db; db.OpenEx("DSN=pubs;UID=sa;PWD=password",0); |
The CDaoDatabase class contains several features that allow you to make use of functionality specific to the Microsoft Jet database engine. For example, you can associate a collection of CDaoTableDef objects with a CDaoDatabase object. These are table definition objects that you can use to create and modify tables within a Jet-compatible database file.
You should always use the Close() function to close your database objects when you have finished with them. Connections to data sources are expensive resources that should be conserved.
A recordset encapsulates a set of records selected from a database. A recordset is comparable to a document in the document/view architecture, in that it holds the data that a record view object displays.
Recordsets enable scrolling from record to record, updating records (adding, editing, and deleting records), qualifying the selection with a filter, sorting the selection, and parameterizing the selection with information obtained or calculated at run time. Recordset objects contain member variables that correspond to columns selected from the database table. These member variables are updated with values from the corresponding database row, as the user moves through the recordset.
The exchange of data between data members of a recordset object and the corresponding table columns on the data source is implemented by a mechanism called Record Field Exchange (RFX). RFX is similar to the DDX mechanism used to transfer data between dialog box controls and dialog classes.
NOTE
In the DAO world, RFX is called DFX—short for DAO Record Field Exchange.
Recordset objects are created in association with a database object. You should pass the address of an existing database object to your recordset constructor, as shown in the following example that uses the ODBC classes:
CDatabase db; db.OpenEx("DSN=pubs;UID=sa;PWD=password",0); CRecordset rs(&db); |
If you derive a recordset class from CRecordset or CDaoRecordset, you can overload member functions to specify connection information, and pass a NULL value to the constructor when creating an object instance. If you use this method, MFC will create a temporary database object to associate with your recordset, using the connection information that you specify.
Once you have created your recordset object, you call the Open() member function to submit a query and populate the recordset with the records returned from the query. The following code shows how to call the Open() function for the CRecordset object created in the previous example:
rs.Open(CRecordset::dynaset, "SELECT * FROM authors", CRecordset::none); |
The first parameter of the CRecordset::Open() function determines the type of cursor that will be created for the data that is returned, as shown in Table 7.3.
Table 7.3 Cursor Types for the CRecordset::Open Member Function
Parameter | Meaning |
---|---|
CRecordset::dynaset | Creates a recordset that is dynamic in nature and can be scrolled in both directions. However, the ordering of data is determined when the record set is opened. Dynasets are applicable for most database operations because they use minimal memory by storing the keys from the result set only, and they show most changes by other users. The only changes that cannot be seen are record insertions. |
CRecordset::snapshot | Creates a recordset that does not display the changes made by other users to the data source; also can be scrolled in both directions. Snapshots are useful when you need to ensure the result set does not change while you are using it, for example, when generating reports. |
CRecordset::dynamic | Creates a recordset that is truly dynamic in nature. You can scroll through the recordset in both directions and every time a user makes a change, the data source content reflects that change. The dynamic cursor consumes the greatest amount of resources. The dynaset is useful for situations where the result set must be constantly up to date. |
CRecordset::forwardonly | Creates a read-only recordset that can be scrolled in the forward direction only. The forward-only cursor is the fastest and least memory-consumptive cursor. Many database tasks involve making a single pass through the result set, making this cursor useful for those tasks. |
The Open function's second parameter is an SQL command string. The last parameter is a bitmask value that allows you to specify whether the recordset will be append-only or read-only, whether it will allow processing of multiple rows of records, and so on. The value CRecordset::none indicates that none of the options have been set. For a complete list of values that this parameter can take, search for "CRecordset::Open" in the Visual C++ Help file.
CDaoRecordset::Open() is similar to CRecordset::Open(). The CDaoRecordset class allows you to create only a dynaset, a snapshot, or a Table recordset. A Table recordset is an updateable recordset that represents records from a single database table. Snapshot recordsets are read-only for the CDaoRecordset class.
You must call the Close() member function once you have finished with a recordset object. Make sure that you close a recordset before you attempt to close the database object with which it is associated.
The record view classes provide the means of displaying the data in a recordset. CRecordView and CDaoRecordView are derived from CFormView, a view class that provides an application with a client area based on a dialog template resource. This kind of application is often referred to as a forms-based application.
The record view classes add the ability to connect dialog box controls to member variables of an associated recordset. The user can view the current record through the controls on the form, as illustrated in Figure 7.7.
Figure 7.7 A CRecordView-based application
The exchange of data between the current record of the recordset and the controls of the record view is handled by the dialog data exchange/dialog data validation (DDX/DDV) mechanism, which is implemented for the record view classes. CWnd::UpdateData() is called from within the record view class's OnMove() function, which is called when the user moves from the current record. If the recordset is not read-only, the current record in the recordset is updated with the values from the record view controls. The RFX/DFX mechanism propagates these changes to the tables on the data source. Figure 7.8 illustrates the relationships between the ODBC database objects and the data exchange mechanisms.
Figure 7.8 DDX and RFX
Accessing an external resource such as a database carries a high risk of failure. The database might be offline, or a user might not have adequate permissions to access the database. Calls to open a database connection or a recordset should be protected by a try… catch… block to catch any exceptions that might occur. MFC provides special database exception classes, similar to the CFileException class, which provide information about the nature of the database error.
The CDatabase::Open() and the CRecordset::Open() functions throw a CDBException upon failure. The CDaoDatabase::Open() and the CDaoRecordset::Open() functions throw a CDaoException. An example of how to handle database exceptions is given later in this lesson.
To give you a better understanding of how the database, recordset, and record view classes work together, you will use AppWizard to create a database application. You will create a form-based application to retrieve and update data on the SQL Server sample database pubs, using the ODBC data source from Lesson 1. Although the ODBC classes are used throughout the following examples, the techniques demonstrated can easily be applied to a project based around the DAO classes.
Figure 7.9 AppWizard database options
Step 2 lets you choose between four options to specify the level of database support to include in the application. Choose the first option if you do not need to include database support. Select the second option to include the database header files in your project; this allows you to use the database classes and manually create your own recordsets. Select the third or fourth option to create a document/view-based application in which the document class contains a recordset; the view class is derived from CRecordView or CDAORecordView. The third option does not provide serialization routines, and is typically used when you want to create a simple form-based application that views and/or updates data held in a database. Use the fourth option if you require serialization support.
Figure 7.10 The Database Options dialog box
The new project opens with the IDD_MYDBAPP_FORM dialog template displayed in the dialog editor. IDD_MYDBAPP_FORM is the dialog template on which your record view class CMyDBAppView is based. The template is opened at this point as a hint that you need to add controls to your record view before you can display any recordset data.
Before you create the dialog template for your record view, you should take a brief look at the code generated for you by AppWizard, to help you understand how a database application is implemented.
CString CMyDBAppSet::GetDefaultSQL() { return _T("[dbo].[authors]"); } |
Note that the ODBC object names are enclosed in square brackets. Although this is required only when the object names contain spaces, the generated code always contains them by default.
To define a filter for your recordset, assign a string specifying a WHERE clause to the m_strFilter data member of your recordset class. You can also sort the records in your recordset by assigning a string specifying an ORDER BY clause to the m_strSort data member.
CString CMyDBAppSet::GetDefaultConnect() { return _T("ODBC;DSN=MyDSN"); } |
The framework does not create a CDatabase-derived object for your project. The recordset constructor receives a NULL parameter, causing a temporary CDatabase object to be created and used.
If you have used SQL Server authentication for your data source, you can prevent a SQL Server Login dialog box from appearing when you run the application by including authentication information in the following connection string:
CString CMyDBAppSet::GetDefaultConnect() { return _T("ODBC;DSN=MyDSN;UID=sa;PWD="); } |
The connection string shown assumes that the sa account has no password.
You can make your recordset class more efficient by deleting the RFX member variables and functions for the columns in the authors table that are not used by your application. The next exercise shows you how to use ClassWizard to do this.
You will now return to the IDD_MYDBAPP_FORM dialog template to add the controls to display the data held in these recordset variables.
Figure 7.11 The IDD_MYDBAPP_FORM dialog template
You will now use ClassWizard to associate member variables of the recordset class with these control IDs.
Look through the MyDBAppView.h and MyDBAppView.cpp files to see the changes ClassWizard has made. Note that you have not added any member variables to the CMyDBAppView class. You have merely associated the controls with existing member variables from the CMyDBAppSet class. ClassWizard implements the association by using the DDX_FieldText() function inside the record view's DoDataExchange() function, as shown here:
void CMyDBAppView::DoDataExchange(CDataExchange* pDX) { CRecordView::DoDataExchange(pDX); //{{AFX_DATA_MAP(CMyDBAppView) DDX_FieldText(pDX, IDC_AU_ADDRESS, m_pSet->m_address, m_pSet); DDX_FieldText(pDX, IDC_AU_CITY, m_pSet->m_city, m_pSet); DDX_FieldText(pDX, IDC_AU_FNAME, m_pSet->m_au_fname, m_pSet); DDX_FieldText(pDX, IDC_AU_LNAME, m_pSet->m_au_lname, m_pSet); DDX_FieldText(pDX, IDC_AU_PHONE, m_pSet->m_phone, m_pSet); DDX_FieldText(pDX, IDC_AU_STATE, m_pSet->m_state, m_pSet); DDV_MaxChars(pDX, m_pSet->m_state, 2); DDX_FieldText(pDX, IDC_AU_ZIP, m_pSet->m_zip, m_pSet); //}}AFX_DATA_MAP } |
Note that a standard DDV validation function can still be used.
Build and run the MyDBApp application. The application should appear as shown in Figure 7.12, although you might see a different record displayed.
Figure 7.12 The MyDBApp application
Note that the CRecordView class provides a set of VCR-style navigation buttons on the toolbar that allows you to move forward and backward through the recordset, and jump to the beginning or to the end. The framework maps the control IDs for these buttons to the record view's OnMove() function. The base class implementation of the OnMove() function calls CWnd::UpdateData(TRUE) to save changes made to the record as you move off a record; and calls CWnd::UpdateData(FALSE) to update the record view with values from the recordset as you move on to a record. UpdateData updates the view by calling its DoDataExchange() function.
We mentioned earlier that you could filter a recordset by assigning a string specifying a WHERE clause to the recordset's m_strFilter data member. The following exercises show you how to apply this technique so that the MyDBApp application displays records on the authors table filtered by state of residence. You will create a menu option that allows the users to select from a list of state codes from the authors table. The selected state code will be used to filter the recordset.
Next, you will create the Select State dialog box, which will display a list of state codes from the authors table.
Figure 7.13 The Select State dialog box
CDatabase aDB; try { aDB.OpenEx("DSN=MyDSN"); // Specify login information if using SQL Server authentication, // e.g., aDB.OpenEx("ODBC;DSN=MyDSN;UID=sa;PWD="); CRecordset aRS(&aDB); aRS.Open(CRecordset::forwardOnly, "SELECT DISTINCT state FROM authors"); while(! aRS.IsEOF()) { CString strValue; aRS.GetFieldValue(short(0), strValue); m_statelist.AddString(strValue); aRS.MoveNext(); } m_statelist.InsertString(0, "All records"); aRS.Close(); aDB.Close(); } catch(CDBException * ex) { TCHAR buf[255]; ex->GetErrorMessage(buf, 255); CString strPrompt(buf); AfxMessageBox(strPrompt); } |
Notice that this function uses locally created CDatabase and CRecordset objects to retrieve information from the database. The code creates a forward-only recordset, as it needs to make only a single pass through the records. The SELECT statement that is used to open the recordset is qualified by the DISTINCT keyword, which specifies that only unique rows can appear in the recordset. This means that the recordset will contain one record per state for each state found in the table.
The CRecordset::MoveNext() function moves through the recordset until CRecordset::IsEOF() returns true. The value of the state column in each row is retrieved by the CRecordset::GetFieldValue() function. This function allows you to dynamically retrieve data from a recordset field by specifying a zero-based numeric index. Because the recordset in this function returns only one column, we know that the value we require can be accessed by the index value 0. Note how this function enables you to use the CRecordset class directly, without deriving your own class and specifying RFX variables.
Values retrieved from the recordset rows are added to the dialog box's list box control. The CListBox::InsertString() function is use to insert an "All records" item at the top of the list.
Notice that the database code is enclosed within a try block, and that the catch handler extracts information from the CDBException object to display to the user.
Finally, you will implement the OnFilterState() function to handle the State option from the Filter menu. This function will display the Select State dialog box and use the state code chosen by the user to filter the records displayed by the application.
#include "StateDialog.h" |
CStateDialog aSD; if(aSD.DoModal() == IDOK) { if(aSD.m_strState == "All records") m_myDBAppSet.m_strFilter = ""; else m_myDBAppSet.m_strFilter.Format("state = '%s'", aSD.m_strState); m_myDBAppSet.Requery(); POSITION pos = GetFirstViewPosition(); if (pos != NULL) { CView* pView = GetNextView(pos); ASSERT_VALID(pView); pView->UpdateData(FALSE); } } |
The CRecordset::Requery() function refreshes the recordset after the filter has been changed. After a requery, the first record of the new set of records becomes the current record. It is important to make sure that you call UpdataData() on the record view to refresh the values displayed by the controls with data from the new current record.
You can parameterize your recordset by including question marks (?) as parameter placeholders in your filter string like this:
m_myDBAppSet.m_strFilter = "state = ?"; |
You can then use the RFX mechanism to replace the parameter placeholders with run-time values. To learn how to parameterize a recordset in this way, refer to the article "Recordset: Parameterizing a Recordset (ODBC)" in the Visual C++ Help file. Using a parameter in this way is more efficient than simply replacing the filter string. For a parameterized recordset, the database must process an SQL SELECT statement only once. For a filtered recordset without parameters, the SELECT statement must be processed each time you requery with a new filter value.
MFC provides two distinct sets of classes for database access: one to allow access through DAO, the other through ODBC. Both sets of classes are similar and are based on a common programming model.
A set of three core classes work together to enable you to retrieve data from a data source so that it can be displayed in a dialog-based view for browsing or updating. These classes are:
The database classes are used to establish a connection to a data source or database file. This is achieved by creating an object of the appropriate type, and calling the object's Open() function. The Open() functions allow you to supply connection information using a connection string, and to specify whether the connection should be opened for exclusive or for read-only access.
For ODBC connections, Microsoft recommends that you use the CDatabase::OpenEx() function. This function allows you to specify flags to pass additional ODBC information.
You should always use the Close() functions to close your database objects when you have finished with them.
A recordset encapsulates a set of records selected from a database. Recordsets enable scrolling from record to record, updating records, filtering and sorting records. Recordset objects contain member variables that correspond to columns selected from the database table. As the user moves through the recordset, the member variables are updated with values from the corresponding database row, by RFX.
Recordset objects are created in association with a database object. You should pass the address of an existing database object to your recordset constructor. For derived recordset classes, you can pass a NULL value to the constructor when creating an object instance, and the framework will create a temporary database object for the recordset to use. Overload the GetDefaultConnect() member function to supply connection information for the temporary database object.
After you have created your recordset object, you call the Open() member function to submit a query and populate the recordset with the records returned from the query.
The Open() function specifies the type of cursor to be created for the recordset. For a CRecordset object, this recordset can be one of the following:
CDaoRecordset objects support read-only snapshot recordsets, dynasets, and table recordsets, based on an entire table in an .mdb database.
The recordset Open() functions take an SQL command string to select records for the recordset. CDaoRecordset objects can take the name of a DAO table definition or query definition object.
Use the Close() functions to close your recordset objects when you have finished with them.
The record view classes provide the means of displaying recordset data in a view based on a dialog template resource. Controls in a record view are connected to member variables of an associated recordset. The exchange of data between the current record of the recordset and the controls of the record view is handled by the DDX/DDV mechanism.
The CDatabase::Open() and the CRecordset::Open() functions will throw a CDBException upon failure. The CDaoDatabase::Open() and the CDaoRecordset::Open() functions throw a CDaoException.
Step 2 of the MFC AppWizard (.exe) option allows you to specify the level of database support to include in an application. You can simply include the database header files in your project, and use the database and recordset classes manually. Alternatively, you can create a document/view-based application in which the document class contains a recordset, and the view class is derived from CRecordView or CDaoRecordView. You can create an application with serialization routines, or a simple form-based application that views and/or updates data held in a database.
When you select a database view for your application, you must specify a data source for your application's recordset class. AppWizard creates recordset and record view classes for your project, and a blank dialog template to which you add controls to display the recordset data. The record view class supplies a set of VCR-style buttons on the toolbar that allows you to navigate the recordset.
You can filter a recordset by assigning a string specifying a WHERE clause to the m_strFilter data member of your recordset class. You can also sort the records in a recordset by assigning a string specifying an ORDER BY clause to the m_strSort data member. For maximum efficiency, you can parameterize the m_strFilter string and use the RFX mechanism to specify parameters.