ch15.htm

VC 21天 学习VC 的好东西

functionality.</P>
<P>
<H3><A NAME="Heading4"></A>Importing the ADO DLL</H3>
<P>If you look around in the MFC class hierarchy, you'll find that there are no classes
for use with ADO. If you don't want to use the controls approach, then what are your
options? Do you have to create the classes yourself? No, Microsoft has provided other
means for you to create and use classes for each of the objects in ADO, through the
use of a new C++ precompiler directive called #import.</P>
<P>The #import precompiler directive was first added to Visual C++ with the 5.0 release.
You can use this directive to import an ActiveX DLL that has been built with the
IDispatch interface description included in the DLL. This directive tells the Visual
C++ compiler to import the DLL specified by the #import directive and to extract
the object information from the DLL, creating a couple of header files that are automatically
included in your project. These header files have the filename extensions .TLH and
.TLI and are in the output directory for your project (the Debug or Release directory,
the same directory where you'll find the executable application after you've compiled
your project). These two files contain definitions of classes for each of the objects
in the DLL that you can use in your code. The #import directive also tells the compiler
to include the DLL as part of the project, eliminating the need to include the .LIB
file for the DLL in your project.</P>
<P>You can import the ADO DLL by placing the following code at the beginning of the
header file in which you are defining any database objects:</P>
<P>
<PRE>#define INITGUID
#import &quot;C:\Program Files\Common Files\System\ADO\msado15.dll&quot;
        &Acirc;rename_namespace(&quot;ADOCG&quot;) rename(&quot;EOF&quot;, &quot;EndOfFile&quot;)
using namespace ADOCG;
#include &quot;icrsint.h&quot;
</PRE>
<P>In these four lines of directives, the first line defines a constant that needs
to be defined for ADO. The second imports the ADO DLL, creating the two header files
mentioned earlier. After the filename to be imported, this directive includes two
attributes to the #import directive. The first, rename_namespace, renames the namespace
into which the DLL has been imported. This is followed with the line following the
#import, where the renamed namespace is specified as the one used. The second attribute,
rename, renames an element in the header files that are created using the #import
directive. The reason you rename elements in these header files is to prevent conflicts
with another element named elsewhere. If you examine the header file, the element
specified is not renamed in the file, but when the compiler reads the file, the element
is renamed. The final line includes the ADO header file, which contains the definition
of some macros that you will use when writing your ADO applications.</P>
<P>
<H3><A NAME="Heading5"></A>Connecting to a Database</H3>
<P>Before you can use any of the ADO objects, you need to initialize the COM environment
for your application. You can do this by calling the CoInitialize API function, passing
NULL as the only parameter, as follows:</P>
<P>
<PRE>::CoInitialize(NULL);
</PRE>
<P>This enables you to make calls to ActiveX objects. If you leave out this one line
of code from your application, or don't put it before you begin interacting with
the objects, you get an COM error whenever you run your application.</P>
<P>When you are finished with all ADO activity, you also need to shut down the COM
environment by calling the CoUninitialize function, as follows:</P>
<P>
<PRE>CoUninitialize();
</PRE>
<P>This function cleans up the COM environment and prepares your application for
shutting down.</P>
<P>Once you initialize the COM environment, you can create a connection to the database.
The best way to do this is not to declare a Connection object variable, but to declare
a Connection object pointer, _ConnectionPtr, and use it for all your interaction
with the Connection object. Once you declare a Connection object pointer, you can
initialize it by creating an instance of the Connection object, calling the CreateInstance
function, passing it the UUID of the Connection object as its only parameter, as
follows:</P>
<P>
<PRE>_ConnectionPtr pConn;
pConn.CreateInstance(__uuidof(Connection));
</PRE>


<BLOCKQUOTE>
	<P>
<HR>
<STRONG>TIP:</STRONG> When you work with these objects and functions, you need to use the
	correct number of underscore characters in front of the various object and function
	names. The _ConnectionPtr object has only a single underscore character, whereas
	the __uuidof function has two.
<HR>


</BLOCKQUOTE>

<P>Once you create the object, you can call the Open function to establish the connection
to the database. This function takes four parameters. The first parameter is the
connection definition string. This string defines the OLE DB data source for the
database. It may be an ODBC OLE DB driver, where OLE DB is sitting on top of an ODBC
data source, as you'll use in your sample application. If you are using SQL Server
or Oracle databases, it may be a direct connection to the OLE DB interface provided
by the database itself. The second parameter is the user ID for connecting to the
database. The third parameter is the password for connecting to the database. The
fourth parameter is the cursor type to use with the database. These types are defined
in the msado15.tlh header file that is created by the #import directive. A typical
use of the Open function to connect to an ODBC data source that doesn't need a user
ID or password is like the following:</P>
<P>
<PRE>pConn-&gt;Open(L&quot;Provider=MSDASQL.1;Data Source=TYVCDB&quot;, L&quot;&quot;, L&quot;&quot;, 
    &Acirc;adOpenUnspecified);
</PRE>
<H3><A NAME="Heading6"></A>Executing Commands and Retrieving Data</H3>
<P>Once you have the connection open, you can use a Command object to pass SQL commands
to the database. This is the normal method of executing SQL commands with ADO. To
create a Command object, follow the same process that you used to create a Connection
object. Declare a Command object pointer, _CommandPtr, and then create an instance
of it using the UUID of the Command object, as follows:</P>
<P>
<H3><A NAME="Heading7"></A>_CommandPtr pCmd;</H3>
<PRE>pCmd.CreateInstance(__uuidof(Command));
</PRE>
<P>Once you create your Command object, assuming that you have already established
the connection to the database, set the active connection property of the Command
object to the open Connection object pointer, as follows:</P>
<P>
<PRE>pCmd-&gt;ActiveConnection = pConn;
</PRE>
<P>Next, specify the SQL command to be executed by setting the CommandText property
of the Command object, as follows:</P>
<P>
<PRE>pCmd-&gt;CommandText = &quot;Select * from Addresses&quot;;
</PRE>
<P>At this point, you have two options for how you execute this command and retrieve
the records. The first is to call the Command object's Execute method, which will
return a new Recordset object, which you'll want to set to a Recordset object pointer,
as follows:</P>
<P>
<PRE>_RecordsetPtr pRs;
pRs = pCmd-&gt;Execute();
</PRE>
<P>The other approach to running the command and retrieving the records is to specify
that the Command object is the source for the records in the Recordset. This requires
creating the Recordset object as follows:</P>
<P>
<PRE>_RecordsetPtr pRs;
pRs.CreateInstance(__uuidof(Recordset));
pRs-&gt;PutRefSource(pCmd);
</PRE>
<P>Now, you'll need to create two NULL variant values to pass as the first two parameters
to the Recordset's Open method. The third parameter will be the cursor type to use,
followed by the locking method to use. Finally, the fifth parameter to the Recordset's
Open method is an options flag that indicates how the database should evaluate the
command being passed in. You do this with the following code:</P>
<P>
<PRE>// Create the variant NULL
_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;
// Open the recordset
pRs-&gt;Open(vNull, vNull, adOpenDynamic, adLockOptimistic, adCmdUnknown);
</PRE>
<P>You could take another approach to accomplish all of the preceding tasks with
only a few lines of code. Skip the use of the Command and Connection objects altogether,
placing all the necessary connection information in the Recordset's Open function.
You can specify the SQL command as the first parameter and the connection information
as the second parameter, instead of the two NULLs that you passed previously. This
method reduces all of the preceding code to the following few lines:</P>
<P>
<PRE>_RecordsetPtr pRs;
pRs.CreateInstance(__uuidof(Recordset));
pRs-&gt;Open(_T(&quot;Provider=MSDASQL.1;Data Source=TYVCDB&quot;),
          _T(&quot;select * from Addresses&quot;), adOpenDynamic,
           adLockOptimistic, adCmdUnknown);
</PRE>


<BLOCKQUOTE>
	<P>
<HR>
<STRONG>TIP:</STRONG> Although placing all of the command and connection information into
	the Recordset Open function is fine for a simple application, such as the one that
	you will build today, you are better off using the Connection object with any application
	that has more than a couple of database queries. This allows you to make a single
	connection to the database and use that one connection for all interaction with the
	database.az
<HR>


</BLOCKQUOTE>

<H3><A NAME="Heading8"></A>Navigating the Recordset</H3>
<P>Once you've retrieved a set of records from the database, and you are holding
them in a Recordset object, you'll need to navigate the set of records. This functionality
is available, just as you would expect, through the MoveFirst, MoveLast, MovePrevious,
and MoveNext functions. None of these functions take any parameters because they
perform the functions that you would expect them to perform.</P>
<P>Along with these functions, the Recordset object also has two properties, BOF
and EOF (which you should normally rename to prevent a collision with the default
definition of EOF), which can be checked to determine if the current record in the
set is beyond either end of the set of records.</P>
<P>
<H3><A NAME="Heading9"></A>Accessing Field Values</H3>
<P>When you need to begin accessing the data values in each of the fields is where
working with ADO in Visual C++ begins to get interesting. Because ADO is intended
to be easy to use in Microsoft's scripting languages, VBScript and JScript, which
only have variant data types, all data elements that you'll retrieve from fields
in the ADO Recordset are variant values. They have to be converted into the data
types that you need them to be. There are two ways of doing this. The first way is
the straight-forward way of retrieving the values into a variant and then converting
them, as in the following code:</P>
<P>
<PRE>_variant_t vFirstName;
CString strFirstName;
vFirstName = pRs-&gt;GetCollect(_variant_t(&quot;FirstName&quot;));
vFirstName.ChangeType(VT_BSTR);
strFirstName = vFirstName.bstrVal;
</PRE>
<P>The not-so-straight-forward way to do this is actually the better way, and in
the long run, is a lot easier to work with. Microsoft has created a series of macros
that perform the conversion for you and that maintain a set of variables of the records
in the set. To do this, you'll define a new class to use as the interface for your
record set. This class will be a descendent of the CADORecordBinding class, which
is defined in the icrsint.h header file, which you included just after the #import
directive. This class will not have any constructor or destructor but will have a
series of macros, along with a number of variables. Each field in the set of records
has two variables, an unsigned long, which is used to maintain the status of the
variable, and the field variable itself. These variables must be regular C variables,
and they cannot be C++ classes such as CString. A simple example of this class declaration
is the following:</P>
<P>
<PRE>class CCustomRs :
    public CADORecordBinding
{
BEGIN_ADO_BINDING(CCustomRs)
    ADO_FIXED_LENGTH_ENTRY(1, adInteger, m_lAddressID, lAddressIDStatus,     &Acirc;FALSE)
    ADO_VARIABLE_LENGTH_ENTRY2(2, adVarChar, m_szFirstName, 
        &Acirc;sizeof(m_szFirstName), lFirstNameStatus, TRUE)
    ADO_FIXED_LENGTH_ENTRY(3, adDate, m_dtBirthdate, lBirthdateStatus,     &Acirc;TRUE)
    ADO_FIXED_LENGTH_ENTRY(4, adBoolean, m_bSendCard, lSendCardStatus,     &Acirc;TRUE)
END_ADO_BINDING()
public:
    LONG m_lAddressID;
    ULONG lAddressIDStatus;
    CHAR m_szFirstName[51];
    ULONG lFirstNameStatus;
    DATE m_dtBirthdate;
    ULONG lBirthdateStatus;
    VARIANT_BOOL m_bSendCard;
    ULONG lSendCardStatus;
};
</PRE>
<P>Once you define this record layout class to match the record layout that will
be returned by your database query, you can declare a variable of this class for
use in your application, as follows:</P>
<P>
<PRE>CCustomRs m_rsRecSet;
</PRE>
<P>Next, you need to create a pointer to an IADORecordBinding interface, as follows:</P>
<P>
<PRE>IADORecordBinding *picRs = NULL;
</PRE>
<P>This is a pointer to a COM interface that is part of the ADO Recordset object.
Once you retrieve the set of records, you need to retrieve the pointer to the IADORecordBinding
interface and bind the custom record set class to the Recordset object, as in the
following code:</P>
<P>
<PRE>if (FAILED(pRs-&gt;QueryInterface(__uuidof(IADORecordBinding), (LPVOID &Acirc;*)&amp;picRs)))
    _com_issue_error(E_NOINTERFACE);
picRs-&gt;BindToRecordset(&amp;m_rsRecSet);
</PRE>
<P>Now, as you navigate the records in the set, you just need to access the member
variables of your custom record class to retrieve the current value for each field.</P>
<P>
<H4>The BEGIN_ADO_BINDING and END_ADO_BINDING Macros</H4>
<P>The key to the second method of accessing the data values in the record set is
in the macros that are used in defining the record class. The set of macros start
with the BEGIN_ADO_BINDING macro, which takes the class name as its only parameter.
This macro sets up the structure definition that is created with the rest of the
macros that follow.</P>
<P>The set of macros is closed by the END_ADO_BINDING macro. This macro doesn't take
any parameters, and it wraps up the definition of the record binding structure that
is created in the class. It is in the rest of the macros, which are used between
these two, where the real work is done.</P>
<P>
<H4>The ADO_FIXED_LENGTH_ENTRY Macros</H4>
<P>The ADO_FIXED_LENGTH_ENTRY macro is used for any database fields that are fixed
in size. It can be used with a date or boolean field, or even a text field that is
a fixed size, with no option for any variation in the database. There are two versions
of this macro; you add a 2 to the end of the name of the second version (ADO_FIXED_LENGTH_ENTRY2).</P>
<P>Both versions require the same first three and last parameters. The first version
requires an additional parameter that is not required in the second version. The
first parameter is the ordinal number of the field in the record set. This is the
position in the field order as returned by the SQL query that is run to populate
the record set. The second parameter is the data type of the field; the available
data types are defined in the header file created by the #import directive. The third
parameter is the variable into which the data value is to be copied. For the first
version of the macro, the fourth parameter is the variable for the field status (the
unsigned long that you defined with the variable for the actual value). The last
variable is a boolean that specifies whether this field can be modified.</P>
<P>
<H4>The ADO_NUMERIC_ENTRY Macros</H4>