Presented by: Jocelyn Garner
Software Design Engineer, Microsoft Visual C++ development system/Microsoft Foundation Classes (MFC)
Ever since the MFC Data Access Objects (DAO) database classes were released in October of 1995, the question of which set of classes to use has been on the mind of MFC database developers. This paper does not attempt to answer the question in general terms. Instead the emphasis here is on examining the options so that you, the developer, can decide which set of MFC database classes is best for each situation.
Most MFC developers are familiar with the Open Database Connectivity (ODBC) database classes¾they’ve been around for three and a half years. With MFC 4.2, there are some significant improvements in those classes.
As C++ developers, you may not be familiar with DAO because to date it has been available only with Microsoft® Access and the Visual Basic® programming system. If you are familiar with DAO, you need to know that MFC’s implementation of DAO is different, but still comprehensive.
Developers who know the MFC ODBC classes need to know that the MFC DAO classes have more functionality than the ODBC classes, but that the DAO classes do not supplant them.
For those of you who are not familiar with MFC in general, let me remind you that MFC has a design philosophy that pervades the database classes as well.
MFC encapsulates the Windows® operating system API as a thin wrapper, providing the C++ capabilities you want along with the abstraction you need. We add value to the underlying API when it makes sense. Most of the time though, MFC tries to stay out of the way.
Just as MFC encapsulates a more complex API, so do the database classes encapsulate more complicated technologies. Because of MFC’s portability across Intel, Unix, and Alpha, database application solutions can be portable.
We use the same recordset model that Microsoft Access and Visual Basic use, so developers who already use those products don’t have to learn a new paradigm. In addition, because the architecture of the two sets of classes is essentially the same, developers using one set of classes can easily switch and use the other.
The ODBC database classes have been available in MFC since version 1.5. Visual C++ version 2.0 offered 32-bit versions of the same classes. These classes are widely in use, are based on an industry-accepted standard, and are favored over other database development options because of the portability of ODBC¾the ability to use applications created with these classes with a variety of ODBC data sources. Recent performance improvements make the ODBC database classes an attractive option.
The DAO database classes in MFC 4.0 give you direct access to desktop data sources without the use of ODBC in most cases. The ability to have more than one database type open at a time, to address a wide variety of data sources, and the Data Definition Language capabilities make the DAO database classes a serious development option.
By now you’re ready to ask which set of database classes you should use. The answer to this question is difficult to determine without a lot of information from you regarding your project. The first thing to consider, however, is what data sources you’re using. If you’re using mostly desktop data, we encourage you to consider the MFC DAO database classes, because you’ll find them to be efficient and powerful. If you primarily use ODBC server-based data, your project may function more efficiently with the ODBC-based classes.
Other considerations include the kind of network you have, the scalability requirements, and whether speed is more important than anything else. The best thing to do is to prototype applications using the set of database classes you think will work best. Do some benchmarking to determine your best performance options.
The decision is really up to you.
The set of choices you have for creating database applications is vast. It turns out to be a continuum of choices, with desktop database applications at one end and strict client/server database applications at the other. Probably the only two choices that might seem obvious are to use the MFC DAO database classes with Microsoft Access 97 .mdb data, and to use the MFC ODBC database classes with Microsoft SQL Server™ 6.5. These two pairings were pretty much designed to work with each other and both are very efficient. But you probably already knew about those choices. What about everything else?
Briefly, here is the process by which you can make a decision:
How large a database do you need? Does more than one person need to get to the data at a time? Sometimes the data source you select can determine most of the rest of these steps.
If you need to have an interface with lots of user input (such as users being able to design their own queries), you have to tune and distribute your database(s) carefully. For example, if you need to populate list boxes with data that doesn’t change much, and you’ve selected a server-based data source, it makes sense to store the typically unchanging data locally rather than on the server.
There are a number of network protocols in use today, and each one has its impact on data moving across the network. A discussion of networks is outside the scope of this paper, but you need to know how to optimize your database application to avoid encountering your network’s data pitfalls.
Microsoft has a variety of options for the database developer (Microsoft Access, Visual Basic, Visual Basic Enterprise and Visual C++/MFC, and SQL Server), and in some cases the functionality overlaps a bit. The scope of this paper is to discuss the Visual C++/MFC options specifically, but if you haven’t also considered these other options, you should look at them, too.
You’ll see this statement several times in this paper because it’s important. You may have selected well, but unless your solution actually works, the job isn’t finished!
Again, for this paper, we’re assuming that you want to know about MFC’s database classes. Let’s examine as many of the options as possible so that you will recognize the direction you want to take for your first prototype.
The MFC ODBC database classes use the recordset model found in Microsoft Access and Visual Basic. You can filter, sort, scroll through, and otherwise manipulate the records using the member functions built into the classes. The underlying ODBC driver affects the functionality of a particular application. So to be more portable, your applications have to be more general, or rely on a lower level of ODBC functionality. You can call ODBC directly, if necessary, to accomplish a particular task. Now let’s examine the individual classes in this set.
As always with MFC, you can make calls to the underlying API (in this case ODBC) as necessary.
A CDatabase object represents a connection to a data source, through which you can operate on the data source. A data source is a specific instance of data hosted by some database management system (DBMS). Examples include Microsoft SQL Server, Microsoft Access, Borland dBASE, and xBASE. You can have one or more CDatabase objects active at a time in your application, and you can have multiple connections to a database object.
A CRecordset object represents a set of records selected from a data source. Known as “recordsets,” CRecordset objects are available in three forms: dynasets, snapshots, and dynamic recordsets. A dynaset is a recordset that stays synchronized with updates by other users. A snapshot is a static recordset that reflects the state of the database at the time of the snapshot. A dynamic recordset is similar to a dynaset, and is generally only used with Microsoft SQL Server. Each form represents a set of records fixed at the time the recordset is opened, but when you scroll to a record in a dynaset or a dynamic recordset, it reflects changes subsequently made to the record, either by other users or by other recordsets in your application.
MFC 4.2 adds new navigation functionality to the CRecordset class in the form of bookmarks and an ability to identify the AbsolutePosition of a record and navigate to it. Bookmarks are unique identifiers that you can use to return to a specific record by calling that identifier.
Class CRecordView is an MFC construct¾a form for displaying data. Because CRecordView is based on CFormView, it has all of the inherited functionality of that base class. Essentially, a form view stretches a dialog template over the client area of a window. This makes adding controls and displaying field data very easy to do.
When you use AppWizard and ClassWizard to create an ODBC-based database application, the columns of the recordset are automatically bound (statically) to member variables, which can then be added to the dialog template.
A CDBException object represents an exception condition arising from the database classes. The class includes two public data members you can use to determine the cause of the exception or to display a text message describing the exception. CDBException objects are constructed and thrown by member functions of the database classes.
The CFieldExchange class supports the record field exchange (RFX) routines used by the database classes. Use this class if you are writing data exchange routines for custom data types; otherwise, you will not directly use this class. RFX exchanges data between the field data members of your recordset object and the corresponding fields of the current record on the data source. RFX manages the exchange in both directions, from the data source and to the data source.
In its native format, DAO consists of 21 objects and 20 collections. DAO then provides individual objects such as tables and fields, as well as collections to which they belong. This clear hierarchy of objects makes it easy to apply object-oriented principles to database development.
DAO has been around for a while. DAO version 1.0 appeared in Microsoft Access version 1.0, providing only an interface to table and query structures, and objects to represent tables, dynasets, and snapshots with a limited number of properties. Data Access Objects 1.0 in Visual Basic version 3.0 added TableDef, QueryDef, and Field objects to programmatically expose structures.
DAO version 2.0 in Microsoft Access version 2.0 had the first vestiges of OLE Automation and full programmatic access to almost all Microsoft Jet functionality. It had a full object model with a robust set of objects and properties.
DAO version 2.5 consisted of ODBC Desktop Database Drivers that were created for 16-bit platforms for use with ODBC Desktop Database Drivers version 2.0. The 16-bit version was shipped for use with the 16-bit version of Visual Basic version 4.0.
DAO version 3.0 shipped with Microsoft Access for Windows 95, Visual Basic version 4.0 (32-bit), Microsoft Excel version 7.0, and Visual C ++ version 4.0. DAO was enhanced to support a stand-alone interface for any compatible host.
DAO version 3.5 shipped with Microsoft Access for Windows 97, and includes the new ODBCDirect COM objects. The MFC DAO database classes do not include classes for these objects.
Most important, the DAO interfaces are based on OLE COM, which positions DAO well for the future technologies and operating systems.
Here is the DAO hierarchy chart. At the top, you see the DBEngine object, which contains all of the other objects. This is the only object without a collection, because you can have only one engine. You can have multiple workspaces, databases, and so on, which is why the remaining objects have collections to which they belong.
In a workspace, you can have more than one database, either a base table (.MDB) or an attached/linked table. In each database will be one or more tables, queries, and recordsets, and in each of those will be fields and/or indexes, as well as other types of objects.
Also connected with the workspace are the user and group objects that constitute the security model for DAO.
Off by itself, and connected to the engine object, is the Errors object.
Objects in the Errors collection are appended in a manner different from the other DAO collections. The most detailed errors are placed at the end of the collection, and the most general errors are placed at the beginning.
Now let’s talk about how MFC implements DAO. Because we don’t individually wrap each DAO object, MFC essentially flattens the DAO hierarchy. We offer you 8 objects instead of 21.
We encapsulate all of the DAO functionality with the exception of the Security objects - Users and Groups and the new ODBCDirect objects. We did this deliberately. In looking at the security objects, for example, we felt that creating classes around them would not add any value to the use of DAO, so we just let you make direct calls to DAO to handle those objects. This is in keeping also with the MFC philosophy: Create classes where it makes sense from a value-added standpoint. We do, however, provide instructions on how to implement the security model in MFC Technical Note 54.
We also manage the DAO requirements for adding objects to collections. In DAO, you create an object then append it to a collection. With one exception, we do this automatically. For that one case, it made sense to have the developer be able to Append the object or not, as a separate step.
While dynamic binding is possible with the ODBC database classes, functionality for doing so is not built into the MFC classes. It IS built into the DAO database classes, and you can do dynamic binding rather easily. Our second demo today will talk more about that.
DAO offers the Data Definition Language from SQL that lets you create databases, tables, recordsets, etc. DDL is not available in the ODBC classes.
Finally, you can always make direct calls to the underlying DAO OLE objects as you need to.
The five MFC DAO classes that derive from CObject (CDaoWorkspace, CDaoDatabase, CDaoTableDef, CDaoQueryDef, and CDaoRecordset) have all of that base class’s functionality.
CDaoException derives from CException, and has that class’s advantages, including the ability to display the error messages from the underlying DAO Errors object.
CDaoRecordView, as mentioned earlier, derives from CFormView, which derives from CScrollView, etc. You can see all the advantages a CDaoRecordView class has for making a form-based display of data quick and easy to implement. In addition, there is wizard support for CDaoRecordView. The functionality in this class is virtually identical to the CRecordView class.
The CDaoFieldExchange class supports the DAO record field exchange (DFX) routines used by the DAO database classes. You only call this object directly if you are creating custom DFX routines.
CDaoWorkspace encapsulates both the DBEngine object and the Workspace object. The fact that the MFC DAO classes offer a workspace is very important. The ODBC database classes do not support having more than one database connection at a time.
Transactions are done at the Workspace level in the DAO database classes instead of the Recordset level as in the ODBC classes. One transaction can affect all open databases and recordsets, or you can isolate transactions so that they affect only specific databases, and so on.
Most of the time you don’t have to worry about creating a workspace object. MFC will open one for you if you don’t. The DAO database classes support having multiple workspaces if you need them.
Finally, you don’t have to worry about a workspace object going out of scope or closing before the database session is through. You can use a workspace pointer to access the Workspaces collection, access the Databases collection, access properties of the database engine, and so on.
CDaoDatabase is similar in architecture to the ODBC-based CDatabase class. CDaoDatabase also encapsulates the database connection. Because you don’t have to always use ODBC, the location of the data source is expressed as a path for most desktop data sources. CDaoDatabase can store tabledef and querydef objects, a great convenience for you as a developer. CDaoDatabase works with both local and remote data sources. We’ll see a list of the data sources you can use a little later in this paper.
The database object also persists for the duration of the session. You can explicitly close the database connection as necessary. Just for purposes of comparison, the CDatabase class has 21 member functions, and CDaoDatabase has 26 member functions. The member functions themselves are very similar, with a few more available in CDaoDatabase.
There is no class that corresponds to CDaoTableDef among the ODBC database classes. TableDef objects let you examine the schema (structure) of a database, regardless of whether the table is a native Microsoft Access table (base table) or the table is linked. You can add fields & indexes to external data sources if you open them directly with DAO. If you link the table, you can examine the structure, but you cannot change it. You can base a recordset on a table. Doing so gives you several advantages, including the use of a high-speed search member function called Seek.
You use CDaoTableDef to determine whether data in a table can be edited by calling CDaoTableDef::CanUpdate. MFC takes care of managing the DAO Fields and Indexes collections for you. With CDaoTableDef, you have the option of appending a table to the TableDefs collection or not¾with all other objects, appending is done automatically.
The SQL you use to retrieve records is stored in a CDaoQueryDef object. This object lets you store the “questions” you ask of your data, such as “How many customers did X dollars of business last month?” You can retrieve and reuse stored queries, and they can be used in one of three ways:
CDaoRecordset is also very similar to the ODBC-based CRecordset class. Recordsets can be based on tables as well as dynasets and snapshots. Remember, the recordset represents both the records you’ve retrieved and a way to move through the data. Options for moving through the data include Seek (for table-type recordsets only), Find and Move operations, AbsolutePosition, and, if your data source supports them, bookmarks. Bookmarks are unique identifiers that you can use to return to a specific record by calling that identifier.
The bulk of the functionality in the MFC DAO database classes is found in CDaoRecordset. CRecordset has only 44 member functions compared to 91 member functions for CDaoRecordset. This additional functionality is found in the navigation, caching, setting and retrieving of field values and in setting and retrieving recordset attributes.
The CDaoRecordView and CRecordView classes have functionality that is nearly identical. They both also have the advantages that come with being based on CFormView. Remember, a form view is like a dialog template stretched across the client area of a window, and that makes it easy to add controls and to display field data. AppWizard and ClassWizard support the form-based display of data. If you use AppWizard to create your initial application, the columns of your database are automatically bound to member variables for you.
Exception handling is slightly different for the DAO database classes. Class CDaoException will return the error messages of the underlying DAO OLE object. Most of the time, you can retrieve more information about errors than is typically available with the ODBC-based classes. In MFC, all DAO errors are expressed as exceptions, of type CDaoException.
When you catch an exception of this type, you can use CDaoException member functions to retrieve information from any DAO error objects stored in the database engine’s Errors collection. As each error occurs, one or more error objects are placed in the Errors collection. When another DAO operation generates an error, the Errors collection is cleared, and the new error object is placed in the Errors collection.
The CDaoFieldExchange class supports the DAO record field exchange (DFX) routines used by the DAO database classes. Use this class if you are writing data exchange routines for custom data types; otherwise, you will not directly use this class. DFX exchanges data between the field data members of your CDaoRecordset object and the corresponding fields of the current record on the data source. DFX manages the exchange in both directions, from the data source and to the data source. See Technical Note 53, available under MFC in Books Online, for information about writing custom DFX routines.
A CDaoFieldExchange object provides the context information needed for DAO record field exchange to take place. CDaoFieldExchange objects support a number of operations, including binding parameters and field data members and setting various flags on the fields of the current record. DFX operations are performed on recordset-class data members of types defined by the enum FieldType in CDaoFieldExchange. Possible FieldType values are:
Here then is a picture of the ODBC-based database classes, as you’ve known them. The bar at the top represents the ODBC-based MFC classes that talk to ODBC. The ODBC drivers for each database interpret the SQL calls and translate them for each data source. A variety of data sources are shown with their corresponding drivers along the bottom of the diagram to remind you of ODBC’s flexibility.
Figure 1. The MFC ODBC database classes
Figure 2 is a picture of the DAO database classes. They communicate via OLE with DAO, and DAO talks to the Jet database engine. The Jet database engine has separate DLLs that are used to communicate with various desktop data sources.
Figure 2. The MFC DAO database classes - desktop data sources
The Microsoft Jet database engine can open data sources such as the FoxProÒ database and Paradox directly. But unless you need to change the schema of these data sources, it is actually more efficient to link these tables to a Microsoft Access database. This is why the FoxPro and Paradox tables (which are just examples) are shown in two places. The dotted line is meant to imply that while it is possible to open the data sources directly, it is less efficient.
A linked table appears and behaves just like any other table in your Microsoft Jet database (although there are slight performance differences associated with connecting to and retrieving remote data). Information necessary to establish and maintain a connection to the remote data source is stored within the table definition.
In contrast, when you open a table directly, you must supply the connection information at the beginning of each session to establish a connection to the data source. None of the information needed to establish a connection to the remote data source is stored in your Microsoft Jet database. To open a table directly, you must use the CDaoTableDef::Create, and you must supply connection information (such as the data source, user name, password, and database name).
With the DAO database classes, you can reach server-type databases such as Microsoft SQL Server and ORACLE by way of ODBC. Figure 3 completes the picture as far as reaching all types of data sources.
Figure 3. The MFC DAO Database Classes, including SQL databases
Finally, Figure 4 is the complete picture of the MFC database classes. Please understand that the vertical line on the MFC bar and the ODBC bar indicates that the two sets of MFC database classes are designed to be used as an either/or decision. You have options for reaching all types of data sources, but you cannot mix MFC database classes from both sets.
Figure 4. The MFC Database Classes
When you write applications using the MFC ODBC classes, you can connect to any data source for which you have an ODBC driver. The operation of ODBC Driver Manager and ODBC drivers is transparent in applications you write with these classes, but the individual driver capabilities affect the functionality of an application.
Generally, MFC dynasets (but not forward-only recordsets) require an ODBC driver with level 2 API conformance. If the driver for your data source conforms to the level 1 API set, you can still use both updatable and read-only snapshots and forward-only recordsets, but not dynasets. However, a level 1 driver can support dynasets if it supports extended fetching and keyset-driven cursors.
The ODBC Desktop Driver Pack version 3.0 supports the level 2 ODBC API call SQLExtendedFetch.
AppWizard and ClassWizard will automatically bind columns of a data source statically to member variables in your application. This is the easiest way to establish a connection between your application and a data source, but not the most flexible. You can add custom Record Field Exchange (RFX) calls to your application by using class CFieldExchange. See Technical Note 43: RFX Routines for more information.
You might also consider binding the database columns dynamically. At the most general level, you follow these steps:
The recordset selects records and uses record field exchange (RFX) to bind both the “static” columns (those mapped to recordset field data members) and the dynamic columns (mapped to extra storage that you allocate).
Now let’s talk about the data sources you can connect to with the DAO database classes, binding columns statically, binding them dynamically, and the double-buffering of records.
You’ll get the fastest access to Microsoft Access databases, naturally, since they are native to Jet. Microsoft Access 97 has the database format that is native to DAO version 3.5. You’ll get the fastest performance if you use a Microsoft Access 97 database.
Jet uses a separate DLL to provide access to Microsoft Jet version 1.x and 2.0 databases. The storage engine and format were completely revised with Microsoft Jet version 3.0. Given the large number of structural changes, Microsoft Jet version 3.0 treats version 2.0 databases as external ISAMs. This has an impact on performance, so if you have not already considered upgrading your Microsoft Access database, this is a good reason to do so.
You can also access installable ISAM databases and ODBC data sources. ISAMs (indexed sequential access method) databases such as FoxPro and dBASE can be opened directly or linked to Access databases for best performance. Here is a list of the data sources DAO can access:
Remember that Microsoft Access versions 1.x, 2.0, and 7.0 databases fall into this category.
You can reach ODBC Data sources such as SQL Server and Oracle through ODBC, so you have the option to use DAO for those data sources. An ODBC data source is any DBMS for which you have the appropriate ODBC driver. For Visual C++ versions 2.0 and later, you need 32-bit ODBC drivers (except on Win32s, where you need 16-bit ODBC drivers). Here is a list of ODBC drivers included in this version of Visual C++.
The Microsoft Desktop Database Drivers version 3.0 (which cover the last six items in the list) offers the best performance for those data sources. These are 32-bit drivers only.
Linking external data sources such as SQL Server to a Microsoft Access table is the most efficient way to handle the data access. Before you can connect your application to a remote data source, you must make sure that the remote data is accessible to your application’s users and that your application is properly designed to handle remote data source security challenges. You must also make sure that your application interacts correctly with case-sensitive data sources and that the installable ISAM is correctly initialized for the data source you want to access. Finally, you must check to see that your code doesn’t use objects or calls that are specific to Microsoft Jet data sources when accessing non-Jet data sources.
The easiest way to set up a link is to go into Microsoft Access and use the File/Attach Table command in Microsoft Access version 2.0 or the File/Get External Data/Link Tables command in Microsoft Access 95 and Microsoft Access 97.
Connection information is stored in the base table (.MDB) you are using. If you move the location of the external data, you must reestablish the link from within Microsoft Access, or by calling CDaoTableDef::RefreshLink from within your code.
The DAO Record field exchange mechanism works the same way RFX works in the ODBC-based database classes. The recordset object’s field data members, taken together, constitute an “edit buffer” that holds the selected columns of one record. When the recordset is first opened and is about to read the first record, DFX binds (associates) each selected column to the address of the appropriate field data member. When the recordset updates a record, DFX calls DAO to send the appropriate commands to the database engine. DFX uses its knowledge of the field data members to specify the columns (fields) in the data source to write.
The wizards support binding columns statically. You can add your own DFX calls as you can with the ODBC-based classes. First, members must be added to the CDaoRecordset derived class for each bound field and parameter. Following this, CDaoRecordset::DoFieldExchange should be overridden. Note that the data type of the member is important. It should match the data from the field in the database or at least be convertible to that type. MFC Technical #53 describes the process in greater detail.
The CDaoFieldExchange class supports the DAO record field exchange (DFX) routines used by the DAO database classes. Use this class if you are writing data exchange routines for custom data types. A CDaoFieldExchange object provides the context information needed for the DAO record field exchange to take place. CDaoFieldExchange objects support a number of operations, including binding parameters and field data members and setting various flags on the fields of the current record.
While it is possible to bind columns dynamically using the ODBC-based classes, the support for doing so is not built into the MFC classes. Dynamic binding IS built into the DAO database classes, and it is fairly easy to do.
There are other things you can do to optimize performance such as retrieving a part of a record instead of an entire record. We’ll cover ways to optimize your application a little later in this presentation.
DFX and dynamic binding are not mutually exclusive options. With the DAO database classes, you can mix static and dynamic binding calls for maximum efficiency.
In MFC's CDaoRecordset class, double-buffering is a mechanism that simplifies detecting when the current record in a recordset has changed. Using double-buffering with your DAO recordsets reduces the amount of work you have to do when adding new records and editing existing records. By default, your MFC DAO recordsets keep a second copy of the edit buffer (the field data members of the recordset class, taken collectively; DAO Help calls the corresponding buffer a “copy buffer”). As you make changes to the data members, MFC compares them to the copy (the "double-buffer") to detect the changes.
The alternative to double-buffering-not keeping a copy of the data-requires you to make additional function calls when you edit a field of the current record.
Double-buffering has been a part of the ODBC-based database classes all along. With the DAO database classes, you can turn that mechanism off if you need to for improved efficiency.
The master switch for this mechanism is called m_bCheckCacheForDirtyFields, dirty meaning “changed.” When you turn this switch ON, you can turn off double-buffering for all or part of the fields. If this master switch is OFF, the entire double-buffering mechanism is disabled.
The fields for which you’re most likely to turn off double-buffering include memo fields, picture fields, and other BLOBs (binary large objects).
The DAO SDK includes some C++ database classes that are separate and distinct from the MFC DAO Database classes. These C++ classes encapsulate the individual objects in the DAO hierarchy. While you can mix the DAO SDK C++ classes with the MFC DAO database classes, the DAO SDK C++ classes do not follow the MFC guidelines for operator overloading, and you must exercise caution when using these classes together. For more information, see the article “The DAO of Databases: Using Data Access Objects and the Jet Engine in C++” in the Microsoft Systems Journal, January 1996.
The following table compares features of the DAO SDK classes and the MFC DAO database classes.
| DAO SDK Database Classes | MFC DAO Database Classes |
| Simple migration from Visual Basic | Simple migration from MFC ODBC database classes |
| Direct mappings to DAO’s OLE automation objects | Conforms to MFC standard two-phase construction |
| More Jet/DAO functionality | AppWizard and ClassWizard support |
| Doesn’t conform to MFC standard two-phase construction | Hides more difficult DAO functionality |
Using the DAO SDK classes is an easier transition for those accustomed to Visual Basic, but it doesn’t conform to MFC. Those developers who already use the MFC ODBC database classes will find the MFC DAO classes to be more familiar in architecture and usage.
Let’s focus first on the common database functionality in both sets of classes.
ODBC classes rely on the underlying driver.
DAO classes have better support for MDB, good for installable ISAMs and the same support as the ODBC database classes for server-based data.
ODBC classes at Database level.
DAO classes at Workspace level.
CDatabase::noOdbcDialog: Do not display the ODBC connection dialog box, regardless of whether enough connection information is supplied.
CDatabase::forceOdbcDialog: Always display the ODBC connection dialog box.
There are some obvious, but often overlooked ways to improve performance for an application. For example, find out what your network might do to enhance or deter data retrieval (for example, asynchronous queries).
The type of ODBC driver you have may also affect performance across the network.
When you open a CDatabase object, you can supply a data source name or NULL to present the user with a selection dialog. MFC ODBC database classes do not support exclusive access to a database, so connections are always shared. You can, however, open a database as read-only. Remember that if you do, all recordsets derived from this CDatabase object will also be read-only. The following options make writing console applications easier. These new options were among the most requested by customers.
You have the option of loading the ODBC Cursor Library with your application. Depending on the capabilities of the underlying driver, you may not need it. The Cursor Library masks some functionality of the underlying ODBC driver, effectively preventing the use of dynasets (if the driver supports them). The only cursors supported if the Cursor Library is loaded are static snapshots and “forwardOnly” cursors.
The type of CRecordset object you open greatly affects the performance of your application. If you want to have a dynamic recordset with bi-directional scrolling, choose the CRecordset::dynaset type. For a static recordset with bi-directional scrolling, choose CRecordset::snapshot type. If you don’t need to scroll around in the data, choose CRecordset::forwardOnly type, which creates a read-only recordset with only forward scrolling. The new CRecordset::dynamic type recordset is a recordset with bi-directional scrolling. Changes made by other users to the membership, ordering, and data values are visible following a fetch operation. Note, however, that many ODBC drivers do not support this type of recordset.
Additional options for CRecordset include:
If you want, you can use bulk row fetching to improve performance. Before opening your recordset object, you can define a rowset size with the SetRowsetSize member function. The rowset size specifies how many records should be retrieved during a single fetch. When bulk row fetching is implemented, the default rowset size is 25. Note that if bulk row fetching is not implemented, the rowset size remains fixed at 1.
After you have initialized the rowset size, call the Open member function. Here you must specify the CRecordset::useMultiRowFetch option of the dwOptions parameter to implement bulk row fetching. You can additionally set the CRecordset::userAllocMultiRowBuffers option. The bulk record field exchange mechanism uses arrays to store the multiple rows of data retrieved during a fetch. These storage buffers can be allocated automatically by the framework, or you can allocate them manually. Specifying the CRecordset::userAllocMultiRowBuffers option means that you will do the allocation.
When you use a CDaoDatabase object, you use a string expression that is the name of an existing Microsoft Jet (.MDB) database file. If the filename has an extension, it is required. If your network supports the uniform naming convention (UNC), you can also specify a network path, such as “\\\\MYSERVER\\MYSHARE\\MYDIR\\MYDB.MDB.” (Double backslashes are required in string literals, because “\” is the C++ escape character.) Some considerations apply when using the database this way.
If a database is already open for exclusive access by another user, MFC throws an exception. Use that exception to let your user know that the database is unavailable.
If you open the database with an empty string ("") and you’re connecting to an ODBC data source, a dialog box listing all registered ODBC data source names is displayed so that the user can select a database. You should avoid direct connections to ODBC data sources; use a linked table instead.
CDaoDatabase object can be opened for exclusive access. By default, it is opened for shared access. You can open it as a read-only data source, or for read/write access. All recordsets derived from a CDaoDatabase object inherit the read capabilities of the database object.
As with the CRecordset object, a CDaoRecordset has more than one type. If you select dbOpenDynaset, you have a dynaset-type recordset with bi-directional scrolling. This is the default. A dbOpenSnapshot selection gives you a snapshot-type recordset with bi-directional scrolling. Finally, you can open a table-type recordset with bi-directional scrolling using dbOpenTable. You cannot open a recordset based on a table with the MFC ODBC database classes.
A CDaoRecordset object has options similar to that of a CRecordset object.
Note The constants dbConsistent and dbInconsistent are mutually exclusive. You can use one or the other, but not both in a given instance of Open.
The following recommendations apply to any interaction that involves ODBC. This applies to the ODBC database classes, but also to the DAO database classes used with server-based data where DAO uses ODBC to communicate with the data source. Many of these recommendations come from Colleen Lambert’s article "ODBC: Architecture, Performance, and Tuning," found on the Microsoft Developer Network CD.
The following recommendations are specifically applicable to the DAO database classes. The starred items in this list refer primarily to interactions with ODBC (server-based) data sources.
The next three items apply specifically to data you’re trying to access from a remote data source.
The following features are unsupported on all known ODBC-accessible servers-usually operations that are Microsoft Jet-specific extensions to SQL such as:
The documentation that ships with Visual C++ should be your first source of information about MFC’s implementation of DAO. Be sure to look over the MFC DAO samples and MFC Technical notes as well as the Class Library Reference and Programming with MFC.
For additional information on how DAO works, consult the Microsoft Jet Database Engine Programmer’s Guide, published by Microsoft Press.
Two additional articles found on the Microsoft Developer Network CD are recommended:
This describes in great detail how Microsoft Jet uses ODBC to retrieve server data. Required reading for anyone using DAO to write significant server applications.
This paper provides a good overview of how ODBC works and addresses performance issues in a realistic and useful fashion.
MFC’s fundamental concept of providing high-level abstractions to more complicated technologies is true in these database classes. You have choices-ODBC or DAO-to give you your best route to success with your clients.
We’ve taken quite a bit of time to examine ways to improve performance, seen some numbers on how the classes perform on a variety of data sources, and given you lots of things to think about. Whatever you do, prototype before you decide. We hope that today’s session will help narrow your options and make your decision easier to reach.
© 1997 Microsoft Corporation. All rights reserved.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.
Microsoft, FoxPro, Visual Basic, Visual C++, and Windows are registered trademarks of Microsoft Corporation.
Other product and company names listed herein may be the trademarks of their respective owners.