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. Up until Visual C++, 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, 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, then, you'll 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'll create a database application that cannot 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, much more than can be covered in an introductory chapter like this one. However, limited space notwithstanding, in this section, you'll get a quick introduction to basic database concepts, as well as discover the two main types of databases: flat and relational.
Simply, 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 the one shown above, each record containing information about a different person. To find a person's address or phone number, you search for their name. When you find their 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, the relational database model is more appropriate.
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 database management system (DBMS) can link these tables together in various ways by comparing keys that were defined by the person who created the database.
The example relational database that you'll 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 14.1, 14.2, and 14.3 show the tables that I defined when I created the database. The Employees table contains information about each of the store's employees, the Manager's 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.)
Figure 14.1 : The Employees table contains data fields for each store employee.
Figure 14.2 : The Managers table contains information about each store department's manager.
Figure 14.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. 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.
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 book to cover relational databases in any useful way, you'll use the Employee table (Figure 14.1) of the Department Store database in the sample database program you'll soon develop. When you're done 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.
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. However, how you plan to use this object depends on whether you'll need to write code that calls the object's member functions. (Unless you're doing nothing more with the database than viewing its content, you will need to call CRecordset member functions in your program.) The CRecordset object represents the actual data that's currently selected from the data source. The CRecordset object's member functions enable you to manipulate the data from the database in various ways.
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 special in that it is kind of 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 record set. 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 a number of steps you must complete. These steps are listed below:
In the following sections, you'll see how to perform the above steps as you create the Employee application, which enables you to add, delete, update, sort, and view records in the Employee's table of the sample department store database.
Before you can create a database application, you must have the database that you want to access registered with the system. This process registers the selected tables in the database as data sources that you can access through the ODBC driver. Follow the steps below to accomplish this important task.
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.
The data-source 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.
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.
Selecting the Single Document option ensures that the Employee
application will not allow more than one window to be open at
a time.
By selecting the Database View Without File Support option, you're
telling AppWizard to automatically generate the classes you need
in order 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.
You've now associated the Employees table of the Department Store
data source with the Employee application.
You've now created 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 14.17. 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.
Figure 14.17 : 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.
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.
By selecting member variables of the application's CEmployeeSet
class (derived from MFC's CRecordset class) as member
variables for the controls in the database view, you're establishing
a connection through which data can flow between the controls
and the data source.
You've now created a data-display form for the 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 14.24. As you can see, the application now 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.
Figure 14.24 : 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. Follow the steps below to include add and delete commands in the Employee application.
Listing 14.1 LST14_01.TXT:-Code for the OnRecordAdd() Function
m_pSet->AddNew(); m_bAdding = TRUE; CEdit* pCtrl = (CEdit*)GetDlgItem(IDC_EMPLOYEE_ID); int result = pCtrl->SetReadOnly(FALSE); UpdateData(FALSE);
Listing 14.2 LST14_02.TXT:-Code for the OnMove() Function
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);
Listing 14.3 LST14_03.TXT:-Code for the OnRecordDelete() Function
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 on your keyboard. 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 and 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 now may be wondering how the C++ code you added to the application works. When you click the Add button, or select the Record, Add command, MFC routes program execution to the OnRecordAdd() command handler. In that function, the program first calls the CEmployeeSet object's AddNew() member function, like this:
m_pSet->AddNew();
The AddNew() member function sets up a blank record for the user to fill in. The new blank record doesn't appear on the screen, however, until the view window's UpdateData() function is called.
After creating the new record, the program sets the Boolean variable m_bAdding to TRUE, which indicates to the program that the user is in the process of adding a new record:
m_bAdding = TRUE;
Now, because the user is entering a new record, he needs to be able 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 and then calls the control's SetReadOnly() member function, like this:
CEdit* pCtrl = (CEdit*)GetDlgItem(IDC_EMPLOYEE_ID); int result = pCtrl->SetReadOnly(FALSE);
The SetReadOnly() member function changes the read-only style of a control. When the function's single argument is FALSE, the control is enabled for normal editing. When the function's argument is TRUE, the control is set to its read-only state, which prevents the user from changing the value displayed in the control.
Finally, as I mentioned previously, the program must call the view window's UpdateData() member function in order to display the new blank record, like this:
UpdateData(FALSE);
When the UpdateData() function's argument is FALSE, data is transferred from the record set to the control. When the argument is TRUE, data is transferred from the control to the record set.
Now that the user has a blank record on the screen, he can start filling 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. However, because you overrode the function and added your own code, you've enabled the program to save the new record.
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)
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's (CRecordView) version of OnMove(), which performs the default behavior for moving 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:
m_bAdding = FALSE;
Then, the program calls UpdateData() to transfer data out of the view window's controls:
UpdateData(TRUE);
A call to the record set's CanUpdate() method determines whether it's okay to update the data source, after which a call to the record set's Update() member function adds the new record to the data source:
if (m_pSet->CanUpdate()) m_pSet->Update();
In order to rebuild the record set, the program must call the record set's Requery() member function, like this:
m_pSet->Requery();
A call to the view window's UpdateData() member function transfers new data to the window's controls:
UpdateData(FALSE);
Finally, the program sets the Employee ID field back to read-only, so that the user can no longer change its contents:
CEdit* pCtrl = (CEdit*)GetDlgItem(IDC_EMPLOYEE_ID); pCtrl->SetReadOnly(TRUE);
When the user clicks the Delete button (or selects the Delete Record command from the Record menu), the OnRecordDelete() function gets called. In that function, deleting the record is just a matter of calling the record set's Delete() function:
m_pSet->Delete();
Once the record is deleted, however, the program should display another record in its stead. That's where the call to the record set's MoveNext() function comes in:
m_pSet->MoveNext();
The MoveNext() function moves the record set forward to the next record. 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 record set. A call to the record set's IsEOF() function will determine whether the record set was at the end. If the call to IsEOF() returns TRUE, the record set needs to be repositioned on the last record, which it is currently beyond. The record set's MoveLast() function takes care of this task. The code looks like this:
if (m_pSet->IsEOF()) m_pSet->MoveLast();
When the last record has been deleted from the record set, the record pointer will be at the beginning of the set. The program can test for this situation by calling the record set's IsBOF() function. If this function returns TRUE, the program sets the current record's fields to NULL, like this:
m_pSet->SetFieldNull(NULL);
Finally, the last task is to update the view window's display:
UpdateData(FALSE);
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 the steps below.
Listing 14.4 LST14_04.TXT:-Code for the OnSortDept() Function
m_pSet->Close(); m_pSet->m_strSort = _DeptID_; m_pSet->Open(); UpdateData(FALSE);
Listing 14.5 LST14_05.TXT:-Code for the OnSortID() Function
m_pSet->Close(); m_pSet->m_strSort = _EmployeeID_; m_pSet->Open(); UpdateData(FALSE);
Listing 14.6 LST14_06.TXT:-Code for the OnSortName() Function
m_pSet->Close(); m_pSet->m_strSort = "EmployeeName"; m_pSet->Open(); UpdateData(FALSE);
Listing 14.7 LST14_07.TXT:-Code for the OnSortRate() Function
m_pSet->Close(); m_pSet->m_strSort = "EmployeeRate"; m_pSet->Open(); UpdateData(FALSE);
Listing 14.8 LST14_08.TXT:-The DoFilter() Function
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);
}
}
void DoFilter(CString col);
You've now added the ability to sort and filter records in the employee database. Go ahead and compile 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.
For example, if you wanted to see every employee who works in the hardware department, you'd select the Filter, Department command, and then type HARDWARE into the Filter dialog box. When you dismiss the dialog box, all the records that have HARDWARE for the department ID are selected. If the application can match no records to the filter string, a message box appears, after which the application reselects all the records in the Employees database table.
When the user selects the Sort, Department command, the frameworks calls the OnSortDept() member function, whose job it is to sort the records as directed by the user. The first thing the function must do is close the record set:
m_pSet->Close();
The record set must be closed because the program is about to create a new record set based on the criteria the user selected. Because the user requested that the database be sorted by department, the new database must perform the sort using the DeptID field. A CRecordset object (or any object of a class derived from CRecordset, such as this program's CEmployeeSet object) uses a special string to determine how the records should be sorted. When the record set is being created, the object checks this string, called m_strSort, and sorts the records accordingly. So, after closing the record set, the next thing the program does is set the sort string, like this:
m_pSet->m_strSort = "DeptID";
Now that the new sort string is prepared, reopening the record set is all that's required to sort the records:
m_pSet->Open();
Although the records are now sorted, they are not yet redisplayed in the view window. As you now know, to update the display, you must call the view window's UpdateData() member function with an argument of FALSE, like this:
UpdateData(FALSE);
The other sorting functions-OnSortName(), OnSortRate(), and OnSortID()-all work similarly.
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.
Knowing the field on which to filter is only half the battle, however. The program also needs to know what value to apply to the filter. In the Employee application, this value is obtained from the user via a dialog box. So, the first thing DoFilter() does is display the dialog box:
CFilterDlg dlg; int result = dlg.DoModal();
When the user closes the dialog box, result will contain a value representing the button used to exit. If the user clicked the OK button to exit the dialog box, result will be equal to IDOK. Therefore, the program checks for this value before doing anything else:
if (result == IDOK)
If result does not equal IDOK, 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'll 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 syntactically correct, its simplest form being
ColumnID = "ColumnValue"
In the above statement, ColumnID is the name of a column in the table. In the Employee application, this is the value that the user selects from the Filter menu, being EmployeeID (listed as ID in the menu), EmployeeName (listed as Name), EmployeeRate (listed as Rate), or DeptID (listed as Department). The program constructs the filter string like this:
CString str = col + " = '" + dlg.m_filterValue + "'";
As an example, if the user were to select the Filter, Department command and then type HARDWARE into the Filter dialog box, the resultant filter string created by the above line would be
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:
m_pSet->Close();
Then, the program sets the record set's filter string, like this:
m_pSet->m_strFilter = str;
To re-create the record set based on the given filter, the program reopens the record set:
m_pSet->Open();
What happens when the given filter results in no records being selected? Good question. The DoFilter() function handles this eventuality by getting the number of records in the new record set, like this:
int recCount = m_pSet->GetRecordCount();
The program can then check to see whether the filter resulted in an empty record set:
if (recCount == 0)
If the record set is empty, the program displays a message box telling the user of the problem:
MessageBox("No matching records.");
Then, the program closes the record set, resets the filter string to an empty string, and reopens the record set:
m_pSet->Close(); m_pSet->m_strFilter = ""; m_pSet->Open();
The above code restores the record set to include all the records in the Employees table.
Finally, whether the filter resulted in a subset of records or the record set had to be restored, the program must redisplay the data, by calling UpdateData():
UpdateData(FALSE);
In the previous section, you got 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.
The DAO classes, which use the Microsoft Jet Database Engine, are so much like the ODBC classes, you can often convert an 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.
In summary, ODBC and DAO similarities are listed below:
Some differences between ODBC and DAO are listed 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 a 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: