Without a doubt, databases are one of the most popular computer applications. Virtually every business uses databases to keep track of everything from its 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. As a Visual C++ developer, you have a somewhat simpler task because MFC includes classes built on the ODBC (Open Database Connectivity) and DAO (Data Access Objects) systems. Other Microsoft database technologies are gaining MFC support as well.
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.
This chapter gives you an introduction to programming with Visual C++'s ODBC classes. You will also learn about the similarities and differences between ODBC and DAO. Along the way, you will create a database application that can not only display records in a database but also update, add, delete, sort, and filter records.
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 composed of fields, and each field contains information 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. 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 name, department, manager's name, and so on. If you have 10 people in Sporting Goods, the name of the Sporting Goods manager is repeated in each of those 10 records. When Sporting Goods hires a new manager, all 10 records have to be updated. It would be much simpler if each employee record could be related to another database of departments and manager names.
A relational database is like several flat databases linked together. Using a relational database, you can not only search for individual records, as you can with a flat database but 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 sample 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 store employee, 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 by using some sort of database scripting language. The most commonly used database language is the Structured Query Language (SQL), which is used to manage not only databases on desktop computers but also huge databases used by banks, schools, corporations, and other institutions with sophisticated database needs. By using a language such as SQL, you can compare information in the various tables of a relational database and extract results made up of data fields from one or more tables combined.
TIP: Most developers pronounce SQL as Sequel.
Learning SQL, though, is a large task, one that is 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 will use the Employee table (refer to Figure 22.1) of the Department Store database in the sample database program you will soon develop. When you finish creating the application, you will have learned one way to update the tables of a relational database without knowing 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 on 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 will 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 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 accustomed 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 that the application framework knows where to display the data you want to view.
In the next section, you will 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 a number of steps you must complete:
In the following sections, you will 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:
FIG. 22.4 Connecting a data source to your application starts with the ODBC Data Source Administrator.
FIG. 22.5 Creating a new data source is as simple as choosing Access from a list of drivers.
FIG. 22.6 Name your data source whatever you like.
FIG. 22.7 Browse your way to the .mdb file that holds your data.
Your system is now set up to access the DeptStore.mdb database file with the Microsoft Access ODBC driver.
Now that you have created and registered your data source, it's time to create the basic Employee application. The steps that follow lead you through this process. After you complete these steps, you will 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.
FIG. 22.9 Create a single- document application.
FIG. 22.10 Arrange for a database view but no other file support.
FIG. 22.11 Choose the Department Store data source.
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 looks like this.
FIG. 22.14 Turn off print support.
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 will do that in the following section.
FIG. 22.15 The application summary mentions the data source as well as the usual information.
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 will discover while completing the following steps:
FIG. 22.17 Open the dialog box in the resource editor.
FIG. 22.18 Create a dialog box to be used in your database form.
FIG. 22.19 Connect the IDC_EMPLOYEE_DEPT control with the m_DeptID member variable of the recordset.
FIG. 22.20 All four controls are connected to member variables.
You've now created a data display form for the Employee application. Build and execute the program again, and you will 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 sophisticated database-access program without writing a single line of C++ code--an 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, which you will add.
When you can add and delete records from a database table, you will 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 in order to accomplish these common database tasks. You will need to add some menu items to the application, as first discussed in Chapter 8, "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 pane on the right.
FIG. 22.23 Add a menu item that adds a record to the Employee table.
Next, you will connect these commands to toolbar buttons, as first discussed in Chapter 9, "Status Bars and Toolbars." Follow these steps:
Now that you have added the menu items and the toolbar buttons, you need to arrange for code to catch the command message sent when the user clicks the button or chooses the menu item. Background information on this process is in Chapter 3, "Messages and Commands," and in Chapter 8 and Chapter 9. Because it is the view that is connected to the database, the view will catch these messages. Follow these steps:
FIG. 22.24 Add a button and connect it to the menu item.
FIG. 22.25 The minus-sign button will control the Delete() function.
FIG. 22.26 Add a function to catch the message.
FIG. 22.27 The new functions appear in the Member Functions box.
protected: BOOL m_bAdding;
m_bAdding = FALSE;
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
void CEmployeeView::OnRecordAdd()
{
m_pSet->AddNew();
m_bAdding = TRUE;
CEdit* pCtrl = (CEdit*)GetDlgItem(IDC_EMPLOYEE_ID);
int result = pCtrl->SetReadOnly(FALSE);
UpdateData(FALSE);
}
FIG. 22.28 Override the OnMove() function.
){kind=link}
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);
}
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 preceding section. Now, however, you can add new records by clicking the Add button on the toolbar (or by selecting the Record, Add Record command on 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.
You might 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 obtains 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.
Now that the user has a blank record on the screen, it's a simple matter to fill in the edit controls with the necessary data. To add the new record to the database, the user must 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 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 and then calls UpdateData() to transfer data out of the view window's controls and into the recordset class. A call to the recordset's CanUpdate() method determines whether 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.
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 SetReadOnly().
Deleting a record is simple. OnRecordDelete() just calls the recordset's Delete() function. When 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 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 will 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.
FIG. 22.30 After you add the four new functions, ClassWizard looks like this.
FIG. 22.31 The Filter menu has four commands.
FIG. 22.32 Create a filter dialog box.
FIG. 22.33 Create a dialog class for the Filter dialog box.
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.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
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.
void CEmployeeView::OnSortId()
{
m_pSet->Close();
m_pSet->m_strSort = "EmployeeID";
m_pSet->Open();
UpdateData(FALSE);
}
void CEmployeeView::OnSortName()
{
m_pSet->Close();
m_pSet->m_strSort = "EmployeeName";
m_pSet->Open();
UpdateData(FALSE);
}
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.
void CEmployeeView::OnFilterDepartment()
{
DoFilter("DeptID");
}
void CEmployeeView::OnFilterId()
{
DoFilter("EmployeeID");
}
void CEmployeeView::OnFilterName()
{
DoFilter("EmployeeName");
}
void CEmployeeView::OnFilterRate()
{
DoFilter("EmployeeRate");
}
All four functions call DoFilter(). You will write this function to filter the database records represented by the recordset class. Right-click CEmployeeView in ClassView and choose Add Member Function. The Function Type is void, and the declaration is DoFilter(CString col). It's a protected member function because it's called only from other member functions of CEmployeeView. Click OK to close the Add Member Function dialog box. Add the code from Listing 22.9.
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 capability 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, 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.
All the sorting functions 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. Then 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.
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 doesn't equal IDOK, the user must have clicked Cancel: The entire if statement is skipped, 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. 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. As with sorting, the recordset must first be closed; then DoFilter() sets the recordset's filter string and reopens the recordset.
What happens when the given filter results in no records being selected? Good question. The DoFilter() function handles this by obtaining the number of records in the new recordset and comparing them to zero. If the recordset 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 preceding 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 ODBC 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. If you are writing an application that uses an Access database and always will, you might want to use DAO for its extra functionality. If, as is more likely, your application uses another database format now or will move to another format in the future, use ODBC instead.
The DAO classes, which use the Microsoft Jet Database Engine, are so much like the ODBC classes that you can often convert an ODBC program to DAO simply by changing the classnames 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 COM objects. Using COM objects makes DAO a bit more up to date, at least as far as architecture goes, than ODBC.
Although DAO is implemented as COM objects, you don't have to worry about directly dealing with those objects. The MFC DAO classes handle all the details for you, providing data and function members that interact with the COM 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 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.
In summary, ODBC and DAO similarities are the following:
Some differences between ODBC and DAO include the following:
OLE DB is a collection of OLE (COM) interfaces that simplify access to data stored in nondatabase applications such as email mailboxes or flat files. An application using OLE DB can integrate information from DBMS systems such as Oracle, SQL Server, or Access with information from nondatabase systems, using the power of OLE (COM).
OLE DB applications are either consumers or providers. A provider knows the format for a specific kind of file (such as an ODBC data source or a proprietary format) and provides access to those files or data sources to other applications. A consumer wants to access a database. For example, you might choose to rewrite the Employees example of this chapter as an OLE DB consumer application.
You will receive some help from AppWizard if you choose to go this route. On Step 2, when you select your data source, one of the choices is an OLE DB data source. Your application will be a little more complex to write than the ODBC example presented here, but you will be able to manipulate the data in a way very similar to the methods just covered. For example, the MFC class COleDBRecordView is the OLE DB equivalent of CRecordView.
A full treatment of OLE DB is outside the scope of this chapter. You need to be comfortable with OLE interfaces and with templates in order to use this powerful tool. An OLE DB Programmer's Reference is in the Visual C++ online documentation. When you are familiar with OLE and ActiveX concepts and have used templates, that's a great place to start. l
© Copyright, Macmillan Computer Publishing. All rights reserved.