vcg06.htm

Visual C++与数据库的连接经典实例

<BR>

<BR>It's a generally accepted programming practice (GAPP) to place variable declaration statements at the beginning of C++ procedures and to add inline comments that describe the use of the variable in your code. Unlike generally accepted accounting practices (GAAPs), which are promulgated by the AICPA (American Institute of Certified Public Accountants), GAPPs aren't certified by a standardizing body. GAPPs arise by tradition. Declaring variables at the beginning of your procedure keeps variable declarations in a single, known location. Typically, variables are declared in a header (.H) file if they're utilized in more than one source file and in the source file if that file is the only location where the variable is to be referenced.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<A NAME="E69E130"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Persistent Member Objects</B></FONT></CENTER></H4>

<BR>

<P>Persistent member objects are objects whose properties are contained in a file. Persistent objects are often called <I>physical objects</I>. The properties of persistent member objects exist independently of your application. The following member objects of the Database object are persistent:

<BR>

<UL>

<LI>TableDef, Field, and Index objects are persistent table, field, and index definitions that are stored in one or more table(s) or files in the database. Jet databases store definitions in system tables of the .MDB file. If a database table isn't indexed, no Index objects exist for the specified Table object. (The Indexes collection of the TableDef object has a Count property value of 0.) The FILE.DDF and FIELD.DDF files store table and index definitions for Btrieve databases. Without FILE.DDF and FIELD.DDF, you can't create a Visual C++ Database object for a Btrieve database.

<BR>

<BR>

<LI>QueryDef objects are persistent objects that are available only when you connect to a Jet database. QueryDef objects, which represent an SQL statement that is translated by the Access query optimizer, are stored in a system table of the Access .MDB file. QueryDef objects don't contain data from the tables that are involved in the query.

<BR>

<BR>

</UL>

<P>Changes your application makes to persistent member objects appear in each instance of a persistent data object in your application, as well as in instances of the same member object in other applications that are running in a multiuser environment. However, changes to persistent objects made by other applications may not appear until the object is reopened or refreshed. Concurrency and consistency of persistent objects are discussed in a forthcoming section.

<BR>

<BR>

<A NAME="E69E131"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B><I>Recordset</I> Objects Created from Virtual Tables</B></FONT></CENTER></H4>

<BR>

<P>Although the data in tables is persistent, the Jet representation of the data is impersistent. A Recordset is a virtual table that is a temporary image (copy) of all or a part of a table or the resulting set of columns and rows returned by running an SQL query against one or more tables. Instances of Recordset objects are referenced by an object variable of the Recordset type and don't have a Name property. Recordset objects are stored in RAM and have no physical manifestation; you can't copy a Recordset object directly to a disk file. If the image is larger than the amount of free RAM available to your application, portions of the virtual table are paged to a temporary file that usually is located in your \Windows\Temp folder (as pointed to by the TEMP = environment variable). The Recordset object exists only for the duration of the life of the variable of the Recordset data type that points to the virtual table. You can choose between creating the dynamic and static Recordset objects described in the following two sections.

<BR>

<BR>

<A NAME="E70E43"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Dynamic <I>Recordset</I> Objects</B></FONT></CENTER></H5>

<BR>

<P>Dynamic Recordset objects, like persistent member objects, reflect changes made by others in a multiuser environment to the persistent objects of the database. Thus, your application sees the most current version of the physical tables that underlie your member objects. Your application can alter the data values of most, but not all, dynamic virtual tables; the exceptions are discussed later in this chapter. The following are the two types of dynamic Recordset objects that are based on virtual tables:

<BR>

<UL>

<LI>Recordset objects of the Table type represent the data contained in a single table and are the default Recordset type. Table-type Recordset objects use the Seek method to locate a specific record in an indexed field. You can't use the Find... methods with Table-type Recordset objects.

<BR>

<BR>

<LI>Recordset objects of the Dynaset type represent data contained in a single table or the result of executing an SQL SELECT query or a persistent QueryDef object against one or more tables. Dynaset-type Recordset objects are the most common type of Recordset object used by Visual C++ developers.

<BR>

<BR>

</UL>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>The Recordset object replaces the Table, Dynaset, and Snapshot objects defined by Access 1.x.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>The advantage of using Recordset objects of the Dynaset type is that the Recordset is populated initially with only a subset of the underlying records. For example, when you open a large Recordset object of the Dynaset type, only the first 100 rows are retrieved from the underlying table(s). Subsequent Move... operations on the record pointer of the Recordset retrieve additional groups of 100 rows as needed. Applying the MoveLast method to a Recordset retrieves all the rows, which might take a substantial amount of time if your Recordset contains thousands of rows.

<BR>

<BR>

<A NAME="E70E44"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Static <I>Recordset</I> Objects</B></FONT></CENTER></H5>

<BR>

<P>A Recordset object of the Snapshot type is the Database object's sole static member. A Snapshot-type Recordset object captures a static image of a persistent member object or a Dynaset object. The data values contained in Snapshot-type Recordset objects are read-only at all times. You can apply any of the methods for Dynaset-type Recordset objects to conventional Snapshot objects, except methods that add, delete, or update data. You specify a Snapshot-type Recordset by substituting the intrinsic dbOpenSnapshot constant for the <I>nType</I> argument of the CDaoRecordset::Open() method.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>Intrinsic global database constants that begin with db are discussed in the section &quot;Understanding Flags and Intrinsic Symbolic Constants.&quot;</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>Snapshot-type Recordset objects retrieve every row from underlying table or query, so opening a Snapshot-type Recordset object on a large table or query result set causes a performance hit. However, operations on Snapshot-type Recordsets with 100 or fewer rows usually are faster than those of the Dynaset type.

<BR>

<BR>

<A NAME="E69E132"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Consistency Issues with <I>Recordset</I> Objects</B></FONT></CENTER></H4>

<BR>

<P>As noted in the two preceding sections, all instances of data objects based on persistent objects, and all instances of dynamic data objects based on virtual tables, reflect changes made to the object when any application changes the value of the data contained in a persistent object. Changes to the structure of a persistent object also are reflected in other instances of the object, but structural changes to database objects are drastic operations that should occur infrequently.

<BR>

<P>For example, if you change the Value property of a data item in the Field object of a Recordset object of the Table type in one instance of a Database object, the Value property of the corresponding data item changes in all other instances of the same Database object. However, the record pointers that determine the current record of each Recordset object of the Table or Dynaset type are independent of one another and of the record pointers of other instances of these objects. Thus, the new value would be apparent only to others in a multiuser environment whose open form coincidentally displays the changed data item. To ensure that the Recordset contains data that is consistent with underlying persistent objects, you apply the Refresh method. The Refresh method re-creates the Recordset object to which it is applied; if the Recordset is based on a QueryDef object, Refresh re-executes the query.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>You should apply the Refresh method to a Recordset object periodically to ensure that the Recordset object of instances of a Data control object sharing a multiuser Database object reflect current data. Applying the Refresh method to a Data control before and after updating or deleting records with transaction processing applications is good database programming practice. You must use the Refresh method with a Data control to open a database whose Connect and/or RecordSource properties you specify in run mode.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<A NAME="E68E38"></A>

<H3 ALIGN=CENTER>

<CENTER>

<FONT SIZE=5 COLOR="#FF0000"><B>Understanding the Properties and Methods of the DAO <I>DBEngine</I> Object</B></FONT></CENTER></H3>

<BR>

<P>The DBEngine object isn't directly exposed with the MFC DAO classes. Instead, it's accessed through a number of member functions that are in the CDaoWorkspace class. DBEngine has several properties and methods that are useful for advanced database applications. Table 6.1 lists the properties of the DBEngine object.

<BR>

<BR>

<P ALIGN=CENTER>

<CENTER>

<FONT COLOR="#000080"><B>Table 6.1. Properties of the DBEngine object.</B></FONT></CENTER>

<BR>



<CENTER><TABLE  BORDERCOLOR=#000040 BORDER=1 CELLSPACING=2 CELLPADDING=3 >

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<I>DAO Property</I>

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<I>CDaoWorkspace</I><I> Member </I><I>Property Function</I>

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<I>Notes/Parameter </I><I>Information</I>

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<A NAME="I2"></A>DefaultUser

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

SetDefaultUser()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Takes a pointer to a string variable of 20 characters or less containing the default user ID (UID) to be used for all Workspace sessions if no user ID is provided. The default value is Access's default Admin user, &quot;Admin.&quot;

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

DefaultPassword

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

SetDefaultPassword()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Takes a pointer to a string variable of 14 characters or less containing the default password (PWD) to be used for all Workspace sessions. The default value is an empty string (&quot;&quot;).

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

IniPath

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

SetIniPath()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

A pointer string containing

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080><BR></FONT>



<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

GetIniPath()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

the name of a Windows registry subkey that specifies the location of the System.mdw workgroup file (Jet 3.0) or System.mda system file (Jet 1.x and 2.0) for secure Access applications.

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

LoginTimeout

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

GetLoginTimeout() SetLoginTimeout()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Gets or sets the number of seconds before an error is generated when you're attempting to log in to an ODBC data source. The default is 20 seconds.

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Version

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

GetVersion()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Returns a CString object that will contain the version number of the Jet database engine.</FONT>

</TABLE></CENTER><P>You must set DBEngine property values before opening a CDaoWorkspace object that depends on the property values. Ordinarily, you place CDaoWorkspace.PropertyFunction() calls before you open the database.

<BR>

<P>Table 6.2 lists the methods applicable to the DBEngine object. Again, these methods are accessed using the CDaoWorkspace class object member functions. The most commonly used methods of the DBEngine object are Idle, CompactDatabase, and RepairDatabase.

<BR>

<BR>

<P ALIGN=CENTER>

<CENTER>

<FONT COLOR="#000080"><B>Table 6.2. Methods for the DBEngine object.</B></FONT></CENTER>

<BR>



<CENTER><TABLE  BORDERCOLOR=#000040 BORDER=1 CELLSPACING=2 CELLPADDING=3 >

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<I>Method</I>

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<I>CDaoWorkspace</I><I> Member </I><I>Method Function</I>

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<I>Notes/Parameter </I><I>Information</I>

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Idle

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Idle()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Allows the Jet database engine to catch up on background processing when there is substantial processing activity going on simultaneously in your multiuser application. The only valid parameter is dbFreeLocks.

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

CompactDatabase

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

CompactDatabase()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Compacts a Jet database that isn't open in any application to save file space or to convert a Jet database from one version to another. Don't use Jet's CompactDatabase method to change the version of .MDB files that are also used by Access applications.

</FONT>

<TR>