Without a doubt, databases are one of the most popular computer applications. Virtually every business uses databases to keep track of everything from their customer list to the company payroll. Unfortunately, there are many different types of database applications, each of which defines its own file layouts and rules. In the past, programming database applications was a nightmare, because it was up to the programmer to figure out all the intricacies of accessing the different types of database files.
Now, however, Visual C++ includes classes that are built upon the ODBC (Open Database Connectivity) and DAO (Data Access Objects) systems. Believe it or not, by using AppWizard, you can create a simple database program without writing even a single line of C++ code. More complex tasks do require some programming, but not as much as you might think.
In this chapter, you get an introduction to programming with Visual C++'s ODBC classes. You'll also learn about the similarities and differences between ODBC and DAO. Along the way, you create a database application that can not only display records in a database, but also update, add, delete, sort, and filter records.
Regardless of what type of database you want to access, the basic concepts, like records and fields,remain the same.
Flat model databases are the simplest type of databases, but relational databases are the most efficient and powerful.
The ODBC classes enable you to access databases without all the hassles previously associated with database programming.
AppWizard is an amazing tool for getting your database application up and running quickly and easily.
Although AppWizard can create a functional database program for you, you have to do a little programming to incorporate more sophisticated database commands into your application.
MFC also features a second set of database classes that are best suited to creating applications that will access .mdb database files. Although the DAO classes are not suited to all database projects, they are more powerful than their ODBC counterparts.
When you need to integrate data from a database with data from another kind of program, OLE DB can make it possible.
Before you can write database applications, you have to know a little about how databases work. Databases have come a long way since their invention, so there's much you can learn about them. This section provides a quick introduction to basic database concepts, including the two main types of databases: flat and relational.
Simply put, a database is a collection of records. Each record in the database is comprised of fields, and each field contains information that's related to that specific record. For example, suppose you have an address database. In this database, you have one record for each person. Each record contains six fields: the person's name, street address, city, state, ZIP code, and phone number. So, a single record in your database might look like this:
NAME: Ronald Wilson
STREET: 16 Tolland Dr.
CITY: Hartford
STATE: CT
ZIP: 06084
PHONE: 860-555-3542
Your entire database will contain many records like this one , with each record containing information about a different person. To find a person's address or phone number, you search for the name. When you find the name, you also find all the information that's included in the record with the name.
This type of database system uses the flat database model. For home use or for small businesses, the simple flat database model can be a powerful tool. However, for large databases that must track dozens, or even hundreds, of fields of data, a flat database can lead to repetition and wasted space. Suppose you run a large department store and want to track some information about your employees, including their names, departments, managerís names, and so on. If you have 10 people in Sporting Goods, the name of the Sporting Goods manager will be repeated in each of those 10 records. When Sporting Goods gets a new manager, all 10 records will have to be updated. It would be much simpler if each employee record could be related to another database of departments and manager names.
Using the Relational Database Model
A relational database is like several flat databases linked together. Using a relational database, you cannot only search for individual records as you can with a flat database, but you can also relate one set of records to another. This enables you to store data much more efficiently. Each set of records in a relational database is called a table. The links are accomplished through keys, values that define a record. (For example, the employee ID might be the key to an employee table.)
The example relational database that you use in this chapter was created using Microsoft Access. The database is a simple system for tracking employees, managers, and the departments for which they work. Figures 22.1, 22.2, and 22.3 show the tables: the Employees table contains information about each of the store's employees, the Managers table contains information about each store department's manager, and the Departments table contains information about the departments themselves. (This database is very simple and probably not usable in the real world.)
Fig. 22.1 The Employees table contains data fields for each store employee.
Fig. 22.2 The Managers table contains information about each store department's manager.
Fig. 22.3 The Departments table contains data about each store department.
Relational databases are accessed using some sort of database scripting language. The most commonly used database language is SQL, which is used not only to manage databases on desktop computers but also on the huge databases used by banks, schools, corporations, and other institutions with sophisticated database needs. By using a language like SQL, you can compare information in the various tables of a relational database and extract results that are made up of data fields from one or more tables combined.
Most developers pronounce SQL as "Sequel."
Learning SQL, though, is a large task, one that is way beyond the scope of this book (let alone this chapter). In fact, entire college-level courses are taught on the design, implementation, and manipulation of databases. Because there isn't space in this chapter to cover relational databases in any useful way, you'll use the Employee table (refer to Figure 22.1) of the Department Store database in the sample database program you'll soon develop. When you're finished creating the application, you'll have learned one way you can update the tables of a relational database without learning even a word of SQL. (Those of you who live and breathe SQL will enjoy Chapter 23, ìSQL and the Enterprise Edition.î)
When you create a database program with Visual C++'s AppWizard, you end up with an application that draws extensively upon the various ODBC classes that have been incorporated into MFC. The most important of these classes are CDatabase, CRecordset, and CRecordView.
AppWizard automatically generates the code needed to create an object of the CDatabase class. This object represents the connection between your application and the data source that you'll be accessing. In most cases, using the CDatabase class in an AppWizard-generated program is transparent to you, the programmer. All the details are handled by the framework.
AppWizard also generates the code needed to create a CRecordset object for the application. The CRecordset object represents the actual data that's currently selected from the data source, and its member functions manipulate the data from the database.
Finally, the CRecordView object in your database program takes the place of the normal view window you're used to using in AppWizard-generated applications. A CRecordView window is like a dialog box that's being used as the application's display. This dialog box-type of window retains a connection to the application's CRecordset object, hustling data back and forth between the program, the window's controls, and the recordset. When you first create a new database application with AppWizard, it's up to you to add edit controls to the CRecordView window. These edit controls must be bound to the database fields they represent, so the application framework knows where to display the data you want to view.
In the next section, you'll see how these various database classes fit together as you build the Employee application step-by-step.
Although creating a simple ODBC database program is easy with Visual C++, there are number of steps you must complete:
In the following sections, you'll see how to perform these steps as you create the Employee application, which enables you to add, delete, update, sort, and view records in the Employees table of the sample department store database.
Before you can create a database application, you must register the database that you want to access as a data source that you can access through the ODBC driver. Follow these steps to accomplish this important task:
The DeptStore.mdb file is a database created with Microsoft Access. You'll be using this database as the data source for the Employee application.
Fig. 22.4 Connecting a data source to your application starts with the ODBC Data Source Administrator.
Click the Add button. The Create New Data Source dialog box appears. Select the Microsoft Access Driver from the list of drivers as shown in Figure 22.5 and click Finish.
The Microsoft Access Driver is now the ODBC driver that will be associated with the data source you'll be creating for the Employee application.
Fig. 22.5 Creating a new data source is as simple as choosing Access from a list of drivers.
When the ODBC Microsoft Access 7.0 Setup dialog box appears, enter Department Store into the Data Source Name text box, and enter Department Store Sample in the Description text box, as shown in Figure 22.6.
The datasource name is simply a way of identifying the specific data source that you're creating. The Description field enables you to include more specific information about the data source.
Fig. 22.6 Name your data source whatever you like.
Click the Select button. The Select Database file selector appears. Use the selector to locate and select the DeptStore.mdb file (see Figure 22.7).
Fig. 22.7 Browse your way to the .mdb file that holds your data.
Click OK to finalize the database selection, and then in the ODBC Microsoft Access 97 Setup dialog box, click OK to finalize the data-source creation process. Finally, click OK in the ODBC Data Source Administrator dialog box.
Your system is now set up to access the DeptStore.mdb database file with the Microsoft Access ODBC driver.
Creating the Basic Employee Application
Now that you have your data source created and registered, it's time to create the basic Employee application. The steps that follow lead you through this process. After you've completed these steps, you'll have an application that can access and view the Employees table of the department store database:
Fig. 22.8 Create an ordinary MFC application with AppWizard.
Select Single Document, as shown in Figure 22.9, to ensure that the Employee application will not allow more than one window to be open at a time. Click Next.
Fig. 22.9 Create a Single Document application.
Select the Database View Without File Support option, as shown in Figure 22.10, so that AppWizard will generate the classes you need to view the contents of a database. This application will not use any supplemental files besides the database, so it doesn't need file (serializing) support. Click the Data Source button to connect the application to the data source you set up earlier.
Fig. 22.10 Arrange for a Database View, but no other file support.
In the Database Options dialog box, drop down the ODBC list and select the Department Store data source, as shown in Figure 22.11. Click OK.
Fig. 22.11 Choose your Department Store data source.
In the Select Database Tables dialog box, select the Employees table, as shown in Figure 22.12, and click OK. The Step 2 dialog box reappears, filled in as shown in Figure 22.13.
You've now associated the Employees table of the Department Store data source with the Employee application. Click Next to move to step 3.
Fig. 22.12 Select which tables from the data source you want to use in this application.
Fig. 22.13 After selecting the data source, the Step 2 dialog box should look like this.
Accept the default, No Compound Document Support, and click Next.
Fig. 22.14 Turn off print support.
Accept the defaults for step 5 by clicking Next. In step 6, just click Finish to finalize your selections for the Employee application. The New Project Information dialog box that appears should look like Figure 22.15.
Fig. 22.15 The application summary mentions the data source as well as the usual information.
Click OK, and AppWizard creates the basic Employee application.
At this point, you can compile the application by clicking the Build button on Developer Studio's toolbar, by selecting the Build, Build command from the menu bar, or by pressing F7 on your keyboard. After the program has compiled, select the Build, Execute command from the menu bar or press Ctrl+F5 to run the program. When you do, you see the window shown in Figure 22.16. You can use the database controls in the application's toolbar to navigate from one record in the Employee table to another. However, nothing appears in the window because you've yet to associate controls with the fields in the table that you want to view. You'll do that in the following section.
Fig. 22.16 The basic Employee application looks nice but doesn't do much.
The next step in creating the Employee database application is to modify the form that displays data in the application's window. Because this form is just a special type of dialog box, it's easy to modify with Developer Studio's resource editor, as you'll discover as you complete the following steps:
Fig. 22.17 Open the dialog box in the resource editor.
Click the static string in the center of the dialog box to select it, and then press Delete to remove the string from the dialog box.
Each of these edit boxes will represent a field of data in the database. The first edit box is read-only because it will hold the database's primary key, which should never be modified.
Fig. 22.18 Create a dialog box to be used in your database form.
Choose View, ClassWizard to bring up ClassWizard, and click the Member Variables tab.
Fig. 22.19 Connect the IDC_EMPLOYEE_DEPT control with the m_DeptID member variable of the recordset.
Associate other member variables (m_pSet->EmployeeID, m_pSet->EmployeeName, and m_pSet->EmployeeRate) with the edit controls in the same way. When you're finished, the Member Variables page of the MFC ClassWizard property sheet should look like Figure 22.20.
By selecting member variables of the application's CEmployeeSet class (derived from MFC's CRecordset class) as member variables for the controls in Database view, you're establishing a connection through which data can flow between the controls and the data source.
Fig. 22.20 All four controls are connected to member variables.
Click the OK button in the MFC ClassWizard property sheet in order to finalize your changes.
You've now created a data-display form for the Employee application. Build and execute the program again, and you see the window shown in Figure 22.21. Now the application displays the contents of records in the Employee database table. Use the database controls in the application's toolbar to navigate from one record in the Employee table to another.
Fig. 22.21 The Employee application now displays data in its window.
After you've examined the database, try updating a record. To do this, simply change one of the record's fields (except the employee ID, which is the table's primary key and can't be edited). When you move to another record, the application automatically updates the modified record. The commands in the application's Record menu also enable you to navigate through the records in the same manner as the toolbar buttons.
Notice that you've created a fairly sophisticated database-access program without writing a single line of C++ codeóa pretty amazing feat. Still, the Employee application is limited. For example, it can't add or delete records. As you may have guessed, that's the next piece of the database puzzle that you'll add.
Once you can add and delete records from a database table, you'll have a full-featured program for manipulating a flat (that is, not a relational) database. In this case, the "flat database" is the Employees table of the department store relational database. Adding and deleting records in a database table is an easier process than you might believe, thanks to Visual C++'s CRecordView and CRecordSet classes, which provide all the member functions you need to accomplish these common database tasks. You'll need to add some menu items to the application, as first discussed in Chapter 10, "Building a Complete Application: ShowString." Follow these steps to include add and delete commands in the Employee application:
Fig. 22.22 Developer Studio's menu editor is in the right-hand pane.
Click the Record menu item to open it, and click the blank menu item at the bottom of the menu. Choose View, Properties and pin the Properties dialog box in place.
Fig. 22.23 Add a menu item to add a record.
In the next blank menu item, add a delete command with the ID ID_RECORD_DELETE and the caption &Delete Record.
Next, you will connect these commands to toolbar buttons, as first discussed in Chapter 11, "Toolbars and Status Bars.î Follow these steps:
Fig. 22.24 Add a button and connect it to the menu item.
Select the blank button again and draw a red minus sign, giving the button the ID_RECORD_DELETE ID, as you can see in Figure 22.25. Drag and drop the Add and Delete buttons to the left of the Help (question mark) button.
Fig. 22.25 The minus-sign button will control the Delete function.
Now that you have added the menu items and the toolbar buttons, you need to arrange for code to "catch" the command message that is sent when the user clicks the button or chooses the menu item. Background information on this process is in Chapter 4, "Messages & Commands;" Chapter 9, "Building a Complete Application: ShowString;" and Chapter 10, "Toolbars and Status Bars." Because it is the view that is connected to the database, the view will catch these messages. Follow these steps:
Fig. 22.26 Add a function to catch the message.
Click the OK button to accept the default name for the new function. The function appears in the Member Functions box at the bottom of the ClassWizard dialog box.
Fig. 22.27 The new functions appear in the Member Functions box.
Open the EmployeeView.h file by double-clicking CEmployeeView in the ClassView pane. In the Attributes section of the class's declaration, add the following lines:
protected:
BOOL m_bAdding;
Double-click the CEmployeeView constructor in ClassView to edit it, and add this line at the bottom of the function:
m_bAdding = FALSE;
Double-click the OnRecordAdd() function and edit it so that it looks like Listing 22.1. This code is explained in the next section.
Listing 22.1óCEmployeeView::OnRecordAdd()
void CEmployeeView::OnRecordAdd()
{
m_pSet->AddNew();
m_bAdding = TRUE;
CEdit* pCtrl = (CEdit*)GetDlgItem(IDC_EMPLOYEE_ID);
int result = pCtrl->SetReadOnly(FALSE);
UpdateData(FALSE);
}
Right-click CEmployeeView in ClassView and choose Add Virtual Function. Select OnMove from the list on the left, as shown in Figure 22.28, then click Add and Edit to add the function and edit the skeleton code immediately.
Fig. 22.28 Override the OnMove() function.
Edit OnMove() function so that it has the code in Listing 22.2. This code is explained in the next section.
Listing 22.2óCEmployeeView::OnMove()
BOOL CEmployeeView::OnMove(UINT nIDMoveCommand)
{
if (m_bAdding)
{
m_bAdding = FALSE;
UpdateData(TRUE);
if (m_pSet->CanUpdate())
m_pSet->Update();
m_pSet->Requery();
UpdateData(FALSE);
CEdit* pCtrl = (CEdit*)GetDlgItem(IDC_EMPLOYEE_ID);
pCtrl->SetReadOnly(TRUE);
return TRUE;
}
else
return CRecordView::OnMove(nIDMoveCommand);
}
Double-click the OnRecordDelete() function and edit it so that it looks like Listing 22.3. This code is explained in the next section.
Listing 22.3óCEmployeeView::OnRecordDelete()
void CEmployeeView::OnRecordDelete()
{
m_pSet->Delete();
m_pSet->MoveNext();
if (m_pSet->IsEOF())
m_pSet->MoveLast();
if (m_pSet->IsBOF())
m_pSet->SetFieldNull(NULL);
UpdateData(FALSE);
}
You've now modified the Employee application so that it can add and deleteóas well as updateórecords. After compiling the application, run it by selecting the Build, Execute command from Developer Studio's menu bar or by pressing Ctrl+F5. When you do, you see the Employee application's main window, which doesn't look any different than it did in the previous section. Now, however, you can add new records by clicking the Add button on the toolbar (or by selecting the Record, Add Record command in the menu bar) and delete records by clicking the Delete button (or by clicking the Record, Delete Record command).
When you click the Add button, the application displays a blank record. Fill in the fields for the record; then when you move to another record, the application automatically updates the database with the new record. To delete a record, just click the Delete button. The current record (the one on the screen) vanishes and is replaced by the next record in the database.
Examining the OnRecordAdd() Function
You now may be wondering how the C++ code you added to the application works. OnRecordAdd() starts with a call to the AddNew() member function of CEmployeeSet, the class derived from CRecordSet. This sets up a blank record for the user to fill in, but the new blank record doesn't appear on the screen until the view window's UpdateData() function is called. Before that happens, you have a few other things to tackle.
After the user has created a new record, the database will need to be updated. By setting a flag in this routine, the move routine will be able to determine whether the user is moving away from an ordinary database record or a newly added one. That's why m_bAdding is set to TRUE here.
Now, because the user is entering a new record, it should be possible to change the contents of the Employee ID field, which is currently set to read-only. To change the read-only status of the control, the program first gets a pointer to the control with GetDlgItem() and then calls the control's SetReadOnly() member function to set the read-only attribute to FALSE.
Finally, the call to UpdateData() will display the new blank record.
Examining the OnMove() Function
Now that the user has a blank record on the screen, it is a simple matter to fill in the edit controls with the necessary data. To actually add the new record to the database, the user most move to a new record, an action that forces a call to the view window's OnMove() member function. Normally, OnMove() does nothing more than display the next record. Your override will save new records as well.
When OnMove() is called, the first thing the program does is check the Boolean variable m_bAdding in order to see whether the user is in the process of adding a new record. If m_bAdding is FALSE, the body of the if statement is skipped and the else clause is executed. In the else clause, the program calls the base class (CRecordView) version of OnMove(), which simply moves to the next record.
If m_bAdding is TRUE, the body of the if statement is executed. There, the program first resets the m_bAdding flag, then calls UpdateData() to transfer data out of the view window's controls and into the recordset. A call to the recordset's CanUpdate() method determines if it's okay to update the data source, after which a call to the recordset's Update() member function adds the new record to the data source.
In order to rebuild the recordset, the program must call the recordset's Requery() member function, and then a call to the view window's UpdateData() member function transfers new data to the window's controls. Finally, the program sets the Employee ID field back to read-only, with another call to GetDlgItem() and to SetReadOnly().
Examining the OnRecordDelete() Function
Deleting a record is quite simple. OnRecordDelete() just calls the recordset's Delete() function. Once the record is deleted, a call to the recordset's MoveNext() arranges for the record that follows to be displayed.
A problem might arise, though, when the deleted record was in the last position or when the deleted record was the only record in the recordset. A call to the recordset's IsEOF() function will determine whether the recordset was at the end. If the call to IsEOF() returns TRUE, the recordset needs to be repositioned on the last record. The recordset's MoveLast() function takes care of this task.
When all records have been deleted from the recordset, the record pointer will be at the beginning of the set. The program can test for this situation by calling the recordset's IsBOF() function. If this function returns TRUE, the program sets the current record's fields to NULL.
Finally, the last task is to update the view window's display with another call to UpdateData().
In many cases when you're accessing a database, you want to change the order in which the records are presented, or you may even want to search for records that fit certain criteria. MFC's ODBC database classes feature member functions that enable you to sort a set of records on any field. You can also call member functions in order to limit the records displayed to those whose fields contain given information, such as a specific name or ID. This latter operation is called filtering. In this section, you'll add sorting and filtering to the Employee application. Just follow these steps:
Fig. 22.29 The Sort menu has four commands for sorting the database.
Use ClassWizard to arrange for CEmployeeView to catch the four new sorting commands, using the function names suggested by ClassWizard. Figure 22.30 shows the resultant ClassWizard property sheet.
Fig. 22.30 After you add the four new functions, ClassWizard should look like this.
Add a Filter menu to the application's menu bar, as shown in Figure 22.31. Let Developer Studio set the command IDs.
Fig. 22.31 The Filter menu has four commands.
Use ClassWizard to arrange for CEmployeeView to catch the four new filtering commands, using the function names suggested by ClassWizard.
Fig. 22.32 Create a filter dialog box.
Start ClassWizard while the new dialog box is on the screen. The Adding a Class dialog box appears. Select the Create a New Class option and click OK.
Fig. 22.33 Create a dialog class for the Filter dialog box.
Click ClassWizard's Member Variables tab. Connect the IDC_FILTERVALUE control to a member variable called m_filterValue. Click the OK button to dismiss ClassWizard.
Now that the menus and dialogs have been created and connected to skeleton functions, it's time to add some code to those functions. Double-click OnSortDepartment() in ClassView and edit it to look like Listing 22.4.
Listing 22.4óCEmployeeView::OnSortDepartment()
void CEmployeeView::OnSortDepartment()
{
m_pSet->Close();
m_pSet->m_strSort = "DeptID";
m_pSet->Open();
UpdateData(FALSE);
}
Double-click OnSortID() in ClassView and edit it to look like Listing 22.5. Double-click OnSortName() in ClassView and edit it to look like Listing 22.6. Double-click OnSortRate() in ClassView and edit it to look like Listing 22.7.
Listing 22.5óCEmployeeView::OnSortId()
void CEmployeeView::OnSortId()
{
m_pSet->Close();
m_pSet->m_strSort = "EmployeeID";
m_pSet->Open();
UpdateData(FALSE);
}
Listing 22.6óCEmployeeView::OnSortName()
void CEmployeeView::OnSortName()
{
m_pSet->Close();
m_pSet->m_strSort = "EmployeeName";
m_pSet->Open();
UpdateData(FALSE);
}
Listing 22.7óLST14_07.TXT: Code for the OnSortRate() Function
void CEmployeeView::OnSortRate()
{
m_pSet->Close();
m_pSet->m_strSort = "EmployeeRate";
m_pSet->Open();
UpdateData(FALSE);
}
At the top of EmployeeView.cpp, add the following line after the other #include directives:
#include "FilterDlg.h"
Edit OnFilterDepartment(),OnFilterID(), OnFilterName(), and OnFilterRate() using Listing 22.8.
Listing 22.8óThe Four Filtering Functions
void CEmployeeView::OnFilterDepartment()
{
DoFilter("DeptID");
}
void CEmployeeView::OnFilterId()
{
DoFilter("EmployeeID");
}
void CEmployeeView::OnFilterName()
{
DoFilter("EmployeeName");
}
void CEmployeeView::OnFilterRate()
{
DoFilter("EmployeeRate");
}
These four functions all call DoFilter(). You will write this function to filter the database records represented by the record set class. Right-click CEmployeeView in ClassView and choose Add Member Function. The type is void and the declaration is DoFilter(CString col). Itís a protected member function, because itís only called from other member functions of CEmployeeView. Double-click DoFilter() in ClassView and add the code from Listing 22.9.
Listing 22.9óCEmployeeView::DoFilter()
void CEmployeeView::DoFilter(CString col)
{
CFilterDlg dlg;
int result = dlg.DoModal();
if (result == IDOK)
{
CString str = col + " = '" + dlg.m_filterValue + "'";
m_pSet->Close();
m_pSet->m_strFilter = str;
m_pSet->Open();
int recCount = m_pSet->GetRecordCount();
if (recCount == 0)
{
MessageBox("No matching records.");
m_pSet->Close();
m_pSet->m_strFilter = "";
m_pSet->Open();
}
UpdateData(FALSE);
}
}
You've now added the ability to sort and filter records in the employee database. Build the application and run it. When you do, the application's main window appears, looking the same as before. Now, however, you can sort the records on any field, just by selecting a field from the Sort menu. You can also filter the records by selecting a field from the Filter menu, and then typing the filter string into the Filter dialog box that appears. You can tell how the records are sorted or filtered by moving through them one at a time. Try sorting by department, or rate, for example. Then try filtering on one of the departments you saw scroll by.
Examining the OnSortDept() Function
The sorting functions all have the same structure. They close the recordset, set its m_strSort member variable, open it again, and then call UpdateData() to refresh the view with the values from the newly sorted recordset. You don't see any calls to a member function with Sort in its name. So when does the sort happen? When the recordset is reopened.
A CRecordset object (or any object of a class derived from CRecordset, such as this program's CEmployeeSet object) uses a special string, called m_strSort, to determine how the records should be sorted. When the recordset is being created, the object checks this string and sorts the records accordingly.
Examining the DoFilter() Function
Whenever the user selects a command from the Filter menu, the framework calls the appropriate member function, either OnFilterDept(), OnFilterID(), OnFilterName(), or OnFilterRate(). Each of these functions does nothing more than call the local member function DoFilter() with a string representing the field on which to filter.
DoFilter() displays the same dialog box no matter which filter menu item was chosen, by creating an instance of the dialog box class and calling its DoModal() function.
If result does not equal IDOK, the user must have clicked Cancel: the entire if statement gets skipped over and the DoFilter() function does nothing but return.
Inside the if statement, the function first creates the string that will be used to filter the database. Just as you set a string to sort the database, so too do you set a string to filter the database. In this case, the string is called m_strFilter. The string you use to filter the database must be in a form like this:
ColumnID = 'ColumnValue'
The column ID was provided to DoFilter() as a CString parameter, and the value was provided by the user. So if, for example, the user chooses to filter by department and types hardware in the filter value box, DoFilter() would set str to DeptID = 'hardware'.
With the string constructed, the program is ready to filter the database. Just as with sorting, the record set must first be closed, then DoFilter() sets the recordset's filter string, then it reopens the recordset.
What happens when the given filter results in no records being selected? Good question. The DoFilter() function handles this by getting the number of records in the new recordset and comparing them to zero. If the record set is empty, the program displays a message box telling the user of the problem. Then the program closes the recordset, resets the filter string to an empty string, and reopens the recordset. This restores the recordset to include all the records in the Employees table.
Finally, whether the filter resulted in a subset of records or the recordset had to be restored, the program must redisplay the data, by calling UpdateData(), as always.
In the previous section, you read an introduction to Visual C++'s ODBC classes and how they're used in an AppWizard-generated application. Visual C++ also features a complete set of DAO classes that you can use to create database applications. DAO is, in many ways, almost a superset of the ODBC classes, containing most of the functionality of the OBDC classes and adding a great deal of its own. Unfortunately, although DAO can read ODBC data sources for which ODBC drivers are available, it's not particularly efficient at the task. For this reason, the DAO classes are best suited for programming applications that manipulate Microsoft's .mdb database files, which are created by Microsoft Access. Other file formats that DAO can read directly are those created by Fox Pro and Excel.
The DAO classes, which use the Microsoft Jet Database Engine, are so much like the ODBC classes that you can often convert a ODBC program to DAO simply by changing the class names in the program: CDatabase becomes CDaoDatabase, CRecordset becomes CDaoRecordset, and CRecordView becomes CDaoRecordView. One big difference between ODBC and DAO, however, is the way in which the system implements the libraries. ODBC is implemented as a set of DLLs, whereas DAO is implemented as OLE objects. Using OLE objects makes DAO a bit more up to date, at least as far as architecture goes, than ODBC.
Although DAO is implemented as OLE objects, you don't have to worry about dealing with those objects directly. The MFC DAO classes handle all the details for you, providing data and function members that interact with the OLE objects. The CDaoWorkspace class provides more direct access to the DAO database-engine object through static member functions. Although MFC handles the workspace for you, you can access its member functions and data members in order to explicitly initialize the database connection.
Another difference is that the DAO classes feature a more powerful set of methods that you can use to manipulate a database. These more powerful member functions enable you to perform sophisticated database manipulations without having to write a lot of complicated C++ code or SQL statements.
Some differences between ODBC and DAO include:
OLE DB is a collection of OLE (ActiveX) interfaces that simplify access to data stored in non-database applications such as e-mail mailboxes or flat files. An application using OLE DB can integrate information from DBMS systems like Oracle, SQL Server, or Access with information from non-database systems using the power of OLE (ActiveX).
A full treatment of OLE DB is outside the scope of this chapter. You need to be comfortable with OLE interfaces to use this powerful tool. If you have only created OLE (ActiveX) applications with MFC and AppWizard, you may be in for a shock when you see what Microsoft considers "simplified." There are lots of calls to QueryInterface(), and lots of variables with names like pIColsInfo or rgColInfo. Still, when you wander down through all the interfaces and all the setting up, you can call a function like GetData() and take information out of a non-database application just as though it was a database, and that can be a big time-saver.
There is an OLE DB Programmer's Reference in the Visual C++ online documentation. Once you are familiar with OLE and ActiveX concepts, that's a great place to start.
There's no doubt that using AppWizard and MFC's database classes makes writing database applications infinitely easier than the old-fashioned, roll-up-your-sleeves method. In fact, AppWizard can generate an fully-functional database browser with very little help from you. Even when you have to get your hands dirty, though, implementing additional database commands in your application is just a matter of calling a few member functions. And, although the ODBC classes are adequate for most database projects, the DAO classes are newer, more powerful, and best suited for manipulating .mdb files, such as those created by Microsoft Access.
For more information on related topics, please consult the following chapters:
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.