Special Edition Using Visual C++ 5 - Chapter 22

## Chapter 22

## Database Access

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.

- Basic database concepts

  Regardless of what type of database you want to access, the basic concepts, like records and fields,remain the same.

- How the flat and relational database models differ

  Flat model databases are the simplest type of databases, but relational databases are the most efficient and powerful.

- MFC's ODBC database classes

  The ODBC classes enable you to access databases without all the hassles previously associated with database programming.

- How to use AppWizard to create a basic ODBC application

  AppWizard is an amazing tool for getting your database application up and running quickly and easily.

- How to implement add, delete, update, sort, and filter abiltities to your database program

  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's DAO classes

  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.

- MFC's OLE DB

  When you need to integrate data from a database with data from another kind of program, OLE DB can make it possible.

### Understanding Database Concepts

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.

Using the Flat Database Model

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.

Accessing a Database

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.”)

The Visual C++ ODBC Classes

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.

### Creating an ODBC Database Program

Although creating a simple ODBC database program is easy with Visual C++, there are number of steps you must complete:

1. Register the database with the system.

2. Use AppWizard to create the basic database application.

3. Add code to the basic application in order to implement features not automatically supported by AppWizard.

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.

Registering the 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:

1. Create a folder called Database on your hard disk, and copy the file named DeptStore.mdb from this book's CD-ROM to the new Database folder.

   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.

2. From the Windows 95 Start menu, run Control Panel. When Control Panel appears, double-click the 32-Bit ODBC icon. The Data Sources dialog box appears, as shown in Figure 22.4.

Fig. 22.4

Connecting a data source to your application starts with the ODBC Data Source Administrator.

3. 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.

4. 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.

5. 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.

6. 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:

1. Select File, New from Developer Studio's menu bar. Click the Projects tab.

2. Select MFC AppWizard (exe) and type Employee into the Name box, as shown in Figure 22.8. Click OK. The Step 1 dialog box appears.

Fig. 22.8

Create an ordinary MFC application with AppWizard.

3. 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.

4. 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.

5. 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.

6. 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.

7. Accept the default, No Compound Document Support, and click Next.

8. In the Step 4 dialog box, turn off the Printing and Print Preview option so that the dialog box resembles Figure 22.14. Click Next.

Fig. 22.14

Turn off print support.

9. 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.

10. 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.

Creating the Database Display

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:

1. Select the Resource View tab to display the application's resources.

2. Open the resource tree by clicking + next to the Employee Resources folder. Then, open the Dialog resource folder the same way. Double-click the IDD_EMPLOYEE_FORM dialog box ID to open the dialog box into the resource editor, as shown in Figure 22.17.

Fig. 22.17

Open the dialog box in the resource editor.

3. 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.

4. Use the dialog box editor's tools to create the dialog box shown in Figure 22.18 by adding edit boxes and static labels. (Editing dialog boxes is introduced in Chapter 2, "Dialogs and Controls.") Give the edit boxes the following IDs: IDC_EMPLOYEE_ID, IDC_EMPLOYEE_NAME, IDC_EMPLOYEE_RATE, and IDC_EMPLOYEE_DEPT. Set the Read-Only style (found on the Styles page of the Edit Properties property sheet) of the IDC_EMPLOYEE_ID edit 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.

5. Choose View, ClassWizard to bring up ClassWizard, and click the Member Variables tab.

6. With the IDC_EMPLOYEE_DEPT resource ID selected, click the Add Variable button. The Add Member Variable dialog box appears.

7. Click the arrow next to the Member Variable Name drop-down list, and select m_pSet->m_DeptID, as shown in Figure 22.19.

Fig. 22.19

Connect the IDC_EMPLOYEE_DEPT control with the m_DeptID member variable of the recordset.

8. 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.

9. 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.

Adding and Deleting Records

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:

1. Select the ResourceView tab, open the Menu folder, and double-click the IDR_MAINFRAME menu ID. The menu editor appears, as shown in Figure 22.22.

Fig. 22.22

Developer Studio's menu editor is in the right-hand pane.

2. 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.

3. In the ID edit box, enter ID_RECORD_ADD, and in the Caption box, enter &Add Record, as shown in Figure 22.23. This adds a new command to the Record menu.

Fig. 22.23

Add a menu item to add a record.

4. 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:

1. In the ResourceView pane, open the Toolbar folder, and then double-click the IDR_MAINFRAME ID. The application's toolbar appears in the resource editor.

2. Click the blank toolbar button to select it, and then use the editor's tools to draw a blue + on the button.

3. Double-click the new button in the toolbar. The Toolbar Button Properties property sheet appears. Select ID_RECORD_ADD in the ID box, as shown in Figure 22.24.

Fig. 22.24

Add a button and connect it to the menu item.

4. 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:

1. Bring up ClassWizard and select the Message Maps tab.

2. Set the Class Name box to CEmployeeView, click the ID_RECORD_ADD ID in the Object IDs box, and then double-click COMMAND in the Messages box. The Add Member Function dialog box appears, as shown in Figure 22.26.

Fig. 22.26

Add a function to catch the message.

3. 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.

4. Add a member function for the ID_RECORD_DELETE command in the same way. The list of functions should resemble Figure 22.27. Click OK to close ClassWizard.

Fig. 22.27

The new functions appear in the Member Functions box.

5. 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;

6. Double-click the CEmployeeView constructor in ClassView to edit it, and add this line at the bottom of the function:

        m_bAdding = FALSE;

7. 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);

       }

8. 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.

9. 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);

       }

10. 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().

Sorting and Filtering

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:

1. Add a Sort menu to the application's menu bar, as shown in Figure 22.29. Let Developer Studio set the command IDs.

Fig. 22.29

The Sort menu has four commands for sorting the database.

2. 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.

3. 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.

4. Use ClassWizard to arrange for CEmployeeView to catch the four new filtering commands, using the function names suggested by ClassWizard.

5. Create a new dialog box by choosing Insert, Resource and double-clicking Dialog, then edit the dialog so it resembles the dialog box shown in Figure 22.32. Give the edit control the ID ID_FILTERVALUE.

Fig. 22.32

Create a filter dialog box.

6. 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.

7. The Create New Class dialog box appears. In the Name box, type CFilterDlg, as shown in Figure 22.33.

Fig. 22.33

Create a dialog class for the Filter dialog box.

8. 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.

### Choosing Between OBDC and DAO

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.

- ODBC and DAO can both manipulate ODBC data sources. However, DAO is less efficient at this task because it is best used with .mdb database files.
- AppWizard can create a basic database application based on either the ODBC or DAO classes. Which type of application you want to create depends—at least in some part—on the type of databases with which you'll be working.
- ODBC and DAO both use objects of an MFC database class to provide a connection to the database being accessed. In ODBC, this database class is called CDatabase, whereas in DAO the class is called CDaoDatabase. Although these classes have different names, the DAO database class contains some similar members to those found in the ODBC class.
- ODBC and DAO both use objects of a recordset class to hold the currently selected records from the database. In ODBC, this recordset class is called CRecordset, whereas in DAO the class is called CDaoRecordset. Although these classes have different names, the DAO recordset class contains not only almost the same members as the ODBC class but also a large set of additional member functions.
- ODBC and DAO use similar procedures for viewing the contents of a data source. That is, in both cases, the application must create a database object, create a recordset object, and then call member functions of the appropriate classes to manipulate the database.

  Some differences between ODBC and DAO include:

- Although both ODBC and DAO MFC classes are similar (very similar in some cases), some similar methods have different names. In addition, the DAO classes feature many member functions not included in the ODBC classes.
- ODBC uses macros and enumerations to define options that can be used when opening record sets. DAO, on the other hand, defines constants for this purpose.
- Under ODBC, snapshot recordsets are the default, whereas under DAO, dynamic recordsets are the default.
- The many available ODBC drivers make ODBC useful for many different database file formats, whereas DAO is best suited to applications that will need to access only .mdb files.
- ODBC is implemented as a set of DLLs, whereas DAO is implemented as OLE objects.
- Under ODBC, an object of the CDatabase class transacts directly with the data source. Under DAO, a CDaoWorkspace object sits between the CDaoRecordset and CDaoDatabase objects, thus enabling the workspace to transact with multiple database objects.

### OLE DB

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.

### From Here...

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:

- Chapter 2, "Dialog Boxes and Controls," teaches you about MFC's many control classes and how you can incorporate them into your own programs.
- Chapter 4, "Messages and Commands," shows how MFC's message-routing system works and how you can adapt that system to your own menus and commands.
- Chapter 9, "Building a Complete Application: ShowString," combines menus, dialogs, and code into a complete working application.
- Chapter 23, "SQL and the Enterprise Edition," tackles another sort of database programming: large scale enterprise SQL programming.

© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.