Updating and Adding Database Records Through ADO - What Is ADO?

Updating and Adding Database Records Through ADO

* What Is ADO?

* ADO Objects

* Using the ADO ActiveX Control

* Importing the ADO DLL

* Connecting to a Database

* Executing Commands and Retrieving Data

* _CommandPtr pCmd;

* Navigating the Recordset

* Accessing Field Values

* Updating Records

* Adding and Deleting

* Closing the Recordset and Connection Objects

* Building a Database Application Using ADO

* Creating the Application Shell

* Building a Custom Record Class

* Connecting and Retrieving Data

* Populating the Form

* Saving Updates

* Navigating the Record Set

* Adding New Records

* Deleting Records

* Summary

* Q&A

* Workshop

* Quiz

* Exercise

Now that you've gotten your feet wet with an ODBC database application, one of the oldest Microsoft database access technologies, it's time to turn your attention to the newest Microsoft database access technology, ActiveX Data Objects (ADO). Designed for use with all of Microsoft's programming and scripting technologies, ADO presents the Visual C++ programmer with new challenges in database programming, while still keeping the functionality familiar. Today, you will learn

* How ADO works and how it uses the OLE DB technology for providing simple database access.

* How you can build a simple ADO application in a couple of minutes using ActiveX controls.

* How you can build a complete database application using regular forms controls.

* How you can use special ADO macros to build a custom record set class for use in your database applications.

CAUTION: This chapter works with some features that may not be included in all versions of Visual C++. Although ADO is an important new area of pro-gramming with Microsoft data access technologies, this chapter discusses some things that you may not have the ability to do with your version of Visual C++.

What Is ADO?

A couple years ago, Microsoft designed a new data access technology called OLE DB. This data access technology was intended to be much more than simply a way of getting data into and out of databases. This technology was intended to be the means of accessing data, regardless of where that data may be located. Through the OLE DB technology, you could access mail messages, spreadsheets, files, and so on. Anything that might have data could possibly be accessed through the OLE DB technology. This was one of the first technologies to be produced from the research and development of the object- oriented file system at the heart of what Microsoft has been calling 'Cairo' for the past few years.

NOTE: Many of the technologies bundled under the product name of Cairo will be released some time next year in the Windows NT 5.0 operating system.

As you can imagine, with the range of functionality that OLE DB must have to access data in all of those different sources, it might be quite complex to work with this technology. Well, it is. This is where ActiveX Data Objects come into play. ADO was designed as another layer on top of OLE DB, specifically for providing database access.

One of the goals in designing ADO was to create a control that could be used to provide data access and control in Web pages, caching the data records on the client. Part of the reason for this goal was to allow a Web browser user to access an entire set of data records, without having to pull down each individual record, one at a time, to navigate and make changes to the records. Because of this capability with ADO, the ADO control is distributed with Microsoft's Internet Explorer Web browser (version 4.0 and above).

ADO Objects

To make ADO as easily usable in scripting languages such as VBScript as it is in programming environments such as Visual Basic, Microsoft tried to keep the number of objects to a minimum. As a result, you have a small number of basic objects:

* Connection

* Error

* Command

* Parameter

* Recordset

* Field

Along with these objects, you have collection objects for containing collections of Error, Parameter, and Field objects.

The Connection Object

The Connection object is used for establishing and maintaining a connection to a database. This object is configured with the connection information, including database location, user ID, and password, before opening the connection. Once all of this information is appropriately configured, the connection object should have its Open method called to open the connection. Once the Connection object goes out of scope, the connection is automatically closed. If you want more control over closing and opening the database connection, you can call the Connection object's Close method to close the connection.

The Connection object is also the object through which any high-level connection functionality is controlled. This includes all transaction control, through the Connection object's BeginTrans, CommitTrans, and RollbackTrans methods.

The Error Object

Whenever a database error occurs, the error information from the database is placed into an ADO Error object. The error information in the error object is the database error information, not ADO error information. Whenever you encounter an error and need to look up the error information to determine what went wrong, you'll need to examine the database error codes and descriptions, not the ADO error codes.

The Command Object

The Command object is used to execute commands in the database. You can use this object to run SQL statements or call stored procedures (SQL functions that are stored in the database). Any time that a command returns rows of data, you need to attach the Command object to a Recordset object for the returned data to be stored in.

When you call a stored procedure, as with functions in any other programming language, you'll often need to pass parameters to the stored procedure. To pass these parameters, you'll attach a series of Parameter objects to the Command object. Each of the Parameter objects will have the name of the parameter that it holds the value for, along with the value that should be passed to the database for that particular parameter.

The Parameter Object

The Parameter object is used for passing variables and for calling stored procedures or parameterized queries. These are attached to a Command object for use in calling the command that has been programmed into the Command object.

The Recordset Object

The Recordset object contains a set of records from the database. The set of records is the result of a command being sent to the database that results in a set of records being returned. You can navigate through the Recordset, much like you do with the Recordset objects for other database access technologies. You can also access the fields in each record in the Recordset through the Field objects that are associated with the Recordset. You can update the records in the Recordset, and then use the Recordset to update the database. You can also insert new records into the Recordset, or delete records and have those changes made in the database.

The Field Object

The Field object represents a single column in the Recordset. Each Field object contains the column name, data value, and how the data value should be represented. Because ADO was designed to be used in Microsoft's scripting languages, and the only data type available in these scripting languages is the Variant data type, the Field objects always contain a Variant data value. The data value is automatically converted to the correct data type when updating to the database. As the programmer working with the ADO objects, you will have to convert the value from a Variant to whatever data type you need it to be, as well as convert it back to a Variant when updating the value.

Using the ADO ActiveX Control

There are two different ways in which you can use the ADO control in your Visual C++ applications. The simple way to incorporate ADO into your application is through the use of ActiveX controls. You can add the ADO data control to your Visual C++ project, just like any other ActiveX control, as shown in Figure 15.1.

FIGURE 15.1. Adding the ADO ActiveX control to a project.

Once you add the ADO control to your project, and place it on a window, you'll need to specify the data connection in the control properties, as shown in Figure 15.2. You'll also need to specify the source for the records that will be retrieved by the control, as shown in Figure 15.3.

FIGURE 15.2. Specifying the database connection.

FIGURE 15.3. Specifying the record source.

To use the ADO control efficiently, you'll also want to use data-bound controls that are ADO-enabled, such as the Microsoft DataGrid control. When you add these controls to the window with the ADO control, you'll specify the ADO control as the data source for the control, as shown in Figure 15.4. If the control is designed to only provide access to a single field in a record set, you'll also need to specify which field is to be used for the control.

FIGURE 15.4. Specifying the data source.

Once you add all these controls to the window and configure them, you can run your application and have full database access through ADO without having written a single line of code, as shown in Figure 15.5.

FIGURE 15.5. A running ADO control database application.

This is such a simple way to build database applications: Just place controls on a window and configure the properties to tell it where to get the data. What's the downside of building ADO applications this way? First, using this approach involves a lot of unnecessary overhead in building ADO applications. For each SQL query or table that you want to pull in a separate record set, you have to add a separate ADO control. Each of these ADO controls will establish a separate connection to the database, which could cause problems with databases that have a limited number of connections available (not to mention the additional overhead on the application). Finally, not all data-bound controls are ADO enabled. ADO is such a new technology that there are few controls that you can use with it at this time. You may find some controls that allow you to retrieve and display data for the user, but do not allow the user to change and edit the data. Others may not even provide you with that much functionality.

Importing the ADO DLL

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.

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.

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:

#define INITGUID

#import 'C:Program FilesCommon FilesSystemADOmsado15.dll'

Arename_namespace('ADOCG') rename('EOF', 'EndOfFile')

using namespace ADOCG;

#include 'icrsint.h'

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.

Connecting to a Database

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:

::CoInitialize(NULL);

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.

When you are finished with all ADO activity, you also need to shut down the COM environment by calling the CoUninitialize function, as follows:

CoUninitialize();

This function cleans up the COM environment and prepares your application for shutting down.

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:

_ConnectionPtr pConn;

pConn.CreateInstance(__uuidof(Connection));

TIP: 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.

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:

pConn->Open(L'Provider=MSDASQL.1;Data Source=TYVCDB', L'', L'',

AadOpenUnspecified);

Executing Commands and Retrieving Data

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:

_CommandPtr pCmd;

pCmd.CreateInstance(__uuidof(Command));

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:

pCmd->ActiveConnection = pConn;

Next, specify the SQL command to be executed by setting the CommandText property of the Command object, as follows:

pCmd->CommandText = 'Select * from Addresses';

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:

_RecordsetPtr pRs;

pRs = pCmd->Execute();

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:

_RecordsetPtr pRs;

pRs.CreateInstance(__uuidof(Recordset));

pRs->PutRefSource(pCmd);

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:

// Create the variant NULL

_variant_t vNull;

vNull.vt = VT_ERROR;

vNull.scode = DISP_E_PARAMNOTFOUND;

// Open the recordset

pRs->Open(vNull, vNull, adOpenDynamic, adLockOptimistic, adCmdUnknown);

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:

_RecordsetPtr pRs;

pRs.CreateInstance(__uuidof(Recordset));

pRs->Open(_T('Provider=MSDASQL.1;Data Source=TYVCDB'),

_T('select * from Addresses'), adOpenDynamic,

adLockOptimistic, adCmdUnknown);

TIP: 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

Navigating the Recordset

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.

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.

Accessing Field Values

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:

_variant_t vFirstName;

CString strFirstName;

vFirstName = pRs->GetCollect(_variant_t('FirstName'));

vFirstName.ChangeType(VT_BSTR);

strFirstName = vFirstName.bstrVal;

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:

class CCustomRs :

public CADORecordBinding

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:

CCustomRs m_rsRecSet;

Next, you need to create a pointer to an IADORecordBinding interface, as follows:

IADORecordBinding *picRs = NULL;

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:

if (FAILED(pRs->QueryInterface(__uuidof(IADORecordBinding), (LPVOID A*)&picRs)))

_com_issue_error(E_NOINTERFACE);

picRs->BindToRecordset(&m_rsRecSet);

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.

The BEGIN_ADO_BINDING and END_ADO_BINDING Macros

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.

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.

The ADO_FIXED_LENGTH_ENTRY Macros

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).

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.

The ADO_NUMERIC_ENTRY Macros

You use the ADO_NUMERIC_ENTRY macros with numeric fields only. They are similar to the ADO_FIXED_LENGTH_ENTRY macros in that there are two different versions of the macro, named in the same way. In these macros, the first five parameters are the same in both versions, along with the final parameter. Like with the ADO_FIXED_LENGTH_ENTRY macros, the first version has an additional parameter that is not used in the second version.

The first three parameters for the ADO_NUMERIC_ENTRY macros are the same as those for the ADO_FIXED_LENGTH_ENTRY macros, as are the last parameter and the next to last parameter for the first version. It is the fourth and fifth parameters that are unique to these macros. The fourth parameter specifies the precision of the value in this field of the record set. The fifth parameter specifies the scale of the value. Both of these parameters are crucial in correctly converting the value to and from a variant data type.

The ADO_VARIABLE_LENGTH_ENTRY Macros

The final series of macros is the ADO_VARIABLE_LENGTH_ENTRY macros. You use this series of macros with database fields that are likely to vary in length. With a SQL-based database, you want to use this series of macros with any varchar (variable-length character string) columns. There are three versions of this macro. In all three versions, the first four parameters are the same, and the final parameter is the same. It is the parameters between them that vary.

The first parameter is the ordinal position of the column in the record set as returned by the SQL query. The second parameter is the data type. The third parameter is the variable in which the data value should be placed. The fourth parameter for all versions of the macro is the size of the variable into which the value is to be placed. This prevents the data from being written past the end of the variable that you defined for it to be placed in. As with the previous macros, the final parameter specifies whether the field is updateable.

In the first version of this macro, there are two parameters between the fourth and final parameters. The second version of this macro only has the first of these two parameters, and the third version only has the second of these two parameters. The first of these two parameters is the status variable for use with this field. The second of these two parameters is the length of the field in the database. The preceding example used the second version of this macro.

Updating Records

When you need to update values in a record in the recordset, how you handle it depends on which of the two methods you used to retrieve the data elements from the recordset. If you retrieved each field and converted it from a variant yourself, you need to update each individual field that has been changed. The update is done using the Recordset object's Update method, which takes two variables, the field being updated and the new value for the field. You could make this update using the following code:

_variant_t vName, vValue;

vName.SetString('FirstName');

vValue.SetString('John');

pRs->Update(vName, vValue);

If you created your record class and bound it to the recordset, updating the record is a little simpler. Once you have copied the new values into the variables in the record class, you can call the record-bound version of the Update function, as in the following:

picRs->Update(&m_rsRecSet);

This updates the record in the Recordset object to be updated with the values in the record class that you have bound to the set.

Adding and Deleting

Adding and deleting records from an ADO recordset is similar to how you accomplish it in other database access technologies. However, there are some slight subtleties to how you perform the addition of new records.

For deleting the current record, you can call the Recordset object's Delete method. This method requires a single parameter that specifies how the delete is supposed to be done. Most likely, you'll pass the adAffectCurrent value so that only the current record in the recordset is deleted, as in the following code:

pRs->Delete(adAffectCurrent);

pRs->MovePrevious();

As with any other database access technology, once you've deleted the current record, there is no current record, so you need to navigate to another record before allowing the user to do anything else.

When you are adding a new record, you can call the Recordset object's AddNew method. Once you have added a new record, the new record is the current record in the record set. If you check the variables in the record class that you created, you'll find that they are all empty. However, you cannot just begin entering data values into these fields. To allow the user to immediately enter the various data elements in the new record, you'll blank out the values in the record class and pass this variable as the only parameter to the Add New class. You need to call it through the record-binding interface pointer, as in the following example:

CString strBlank = ' ';

COleDateTime dtBlank;

m_rsRecSet.m_lAddressID = 0;

strcpy(m_rsRecSet.m_szFirstName, (LPCTSTR)strBlank);

m_rsRecSet.m_dtBirthdate = (DATE)dtBlank;

m_rsRecSet.m_bSendCard = VARIANT_FALSE;

picRs->AddNew(&m_rsRecSet);

This allows you to provide the user with a blank record, ready for editing. Once the user has entered all the various values in the record, copy all these values back to the record variable. Then, call the Update method to save the record.

Closing the Recordset and Connection Objects

Once you finish working with a record set, you'll close the record set by calling the Close method, as follows:

pRs->Close();

Once you finish all database interaction for the entire application, you'll also close the connection to the database by calling the Connection object's Close method:

pConn->Close();

Building a Database Application Using ADO

The sample application that you will build today is another simple database application, basically the same as the one you built yesterday. You'll use ADO to retrieve a set of records from an Access database, providing functionality to navigate the record set. The user will be able to make changes to the data in the record set, and those changes will be reflected in the database as well. The user will also be able to add new records to the record set and delete records as desired. You will accomplish all of this using ADO as the means of accessing the database, which will go through the ODBC driver that was configured yesterday.

Creating the Application Shell

The application that you will build today will be an SDI-style application. As with sev-eral other sample applications that you build in the course of reading this book, everything that you do in today's application is just as applicable to an MDI or dialog-style application. To start the application, you'll use the MFC AppWizard to build the application shell, using most of the SDI-style application default settings.

To start your application, create a new AppWizard project, naming the project something appropriate, such as DbAdo. Specify on the first panel of the AppWizard that you are building an SDI-style application. Accept all the default settings for steps 2 through 5, being sure to leave the second step stating that you want no database support included in the application. On the final AppWizard step, specify that the view class should be inherited from the CFormView class.

Once you finish creating your application shell, design the main dialog form for use in your application. Add the standard controls for each of the fields in the Addresses table from the database you used yesterday (or if you used a different database yesterday, add controls for all the fields in the table that you used), as shown in Figure 15.6. Configure the controls using the properties listed in Table 15.1.

TIP: If you want to save a little time when building the example, you can leave out most of the controls and database fields from the application. The key fields that you'll need to include on the screen are ID, First and Last Names, Birthdate, and Send Card. If you want to leave out the other fields from the application, that's fine. You will need to include these fields in the CCustomRs class that you create in this chapter.

FIGURE 15.6. The main form layout.

TABLE 15.1. CONTROL PROPERTY SETTINGS.

Object  Property Setting

Static Text ID IDC_STATIC

Caption Address ID

Edit Box ID IDC_EDIT_ADDRESSID

Static Text ID IDC_STATIC

Caption First Name

Edit Box ID IDC_EDIT_FIRSTNAME

Static Text ID IDC_STATIC

Caption Last Name

Edit Box ID IDC_EDIT_LASTNAME

Static Text ID IDC_STATIC

Caption Spouse Name

Edit Box ID IDC_EDIT_SPOUSENAME

Static Text ID IDC_STATIC

Caption Address

Edit Box ID IDC_EDIT_ADDRESS

Static Text ID IDC_STATIC

Caption City

Edit Box ID IDC_EDIT_CITY

Static Text ID IDC_STATIC

Caption State Or Province

Edit Box ID IDC_EDIT_STATEORPROVINCE

Static Text ID IDC_STATIC

Caption Postal Code

Edit Box ID IDC_EDIT_POSTALCODE

Static Text ID IDC_STATIC

Caption Country

Edit Box ID IDC_EDIT_COUNTRY

Static Text ID IDC_STATIC

Caption Email Address

Edit Box ID IDC_EDIT_EMAILADDRESS

Static Text ID IDC_STATIC

Caption Home Phone

Edit Box ID IDC_EDIT_HOMEPHONE

Static Text ID IDC_STATIC

Caption Work Phone

Edit Box ID IDC_EDIT_WORKPHONE

Static Text ID IDC_STATIC

Caption Work Extension

Edit Box ID IDC_EDIT_WORKEXTENSION

Static Text ID IDC_STATIC

Caption Fax Number

Edit Box ID IDC_EDIT_FAXNUMBER

Static Text ID IDC_STATIC

Caption Birthdate

Edit Box ID IDC_EDIT_BIRTHDATE

Static Text ID IDC_STATIC

Caption Send Card

Check Box ID IDC_CHECK_SENDCARD

Static Text ID IDC_STATIC

Caption Notes

Edit Box ID IDC_EDIT_NOTES

Once you add all of the controls to the form, use the Class Wizard to attach variables to each of these controls, as specified in Table 15.2. The variables should match the data types of the columns in the database that the control will be used to display.

TABLE 15.2. CONTROL VARIABLES.

Object  Name Category Type

IDC_CHECK_SENDCARD m_bSendCard Value BOOL

IDC_EDIT_ADDRESS m_strAddress Value CString

IDC_EDIT_ADDRESSID m_lAddressID Value long

IDC_EDIT_BIRTHDATE m_oledtBirthdate Value COleDateTime

IDC_EDIT_CITY m_strCity Value CString

IDC_EDIT_COUNTRY m_strCountry Value CString

IDC_EDIT_EMAILADDRESS m_strEmailAddress Value CString

IDC_EDIT_FAXNUMBER m_strFaxNumber Value CString

IDC_EDIT_FIRSTNAME m_strFirstName Value CString

IDC_EDIT_HOMEPHONE m_strHomePhone Value CString

IDC_EDIT_LASTNAME m_strLastName Value CString

IDC_EDIT_NOTES m_strNotes Value CString

IDC_EDIT_POSTALCODE m_strPostalCode Value CString

IDC_EDIT_SPOUSENAME m_strSpouseName Value CString

IDC_EDIT_STATEORPROVINCE m_strStateOrProvince Value CString

IDC_EDIT_WORKEXTENSION m_strWorkExtension Value CString

IDC_EDIT_WORKPHONE m_strWorkPhone Value CString

Building a Custom Record Class

Before you go any further in building your application, you need to create your custom record class that you will bind to the record set. This class will need public variables for each of the columns in the database table that you are selecting, as well as status variables for each of these columns. You'll also build the set of macros to exchange the column values between the record set and the class variables. To create this class, create a new class using the same method you used in previous days, specifying that a generic class. Specify a suitable class name, such as CCustomRs, and specify the base class as CADORecordBinding with public access.

Once you have created your new class, delete the constructor and destructor functions from both the header and source code files for the new class. Edit the header file for your new class, importing the ADO DLL and filling in the macros and variables, as in Listing 15.1.

LISTING 15.1. THE CUSTOM RECORD CLASS.

1: #define INITGUID

2: #import 'C:Program FilesCommon FilesSystemADOmsado15.dll'

Arename_namespace('ADOCG') rename('EOF', 'EndOfFile')

3: using namespace ADOCG;

4: #include 'icrsint.h'

5:

6: class CCustomRs :

7: public CADORecordBinding

8: ;

Once you've created this class, you need to add a variable to the document class. Add a new member variable to the document class, specifying the variable type as CCustomRs, the name as m_rsRecSet, and the access as private. You'll also need to include the custom record class header file in the document source code file, as in Listing 15.2.

LISTING 15.2. THE DOCUMENT SOURCE CODE INCLUDES.

1: // dbadoDoc.cpp : implementation of the CDbAdoDoc class

2: //

4: #include 'stdafx.h'

5: #include 'dbado.h'

6:

7: #include 'CustomRs.h'

8: #include 'dbadoDoc.h'

9: #include 'dbadoView.h'

Another detail that you need to attend to before going any further is providing a way for the view to get a pointer to the record class from the document class. This function should return a pointer to the record class variable. To add this function to your application, add a new member function to the document class, specifying the function type as CCustomRs*, the function declaration as GetRecSet, and the function access as public. Edit this function, adding the code in Listing 15.3.

LISTING 15.3. THE CDbAdoDoc GetRecSet FUNCTION.

1: CCustomRs* CDbAdoDoc::GetRecSet()

2:

One last piece of functionality that you'll add before getting to the real heart of ADO programming is the function for reporting ADO and database errors. This function will display a message to the user, reporting that an error occurred and displaying the error code and error message for the user. To add this function to your application, add a new member function to your document class. Specify the function type as void, the function declaration as GenerateError(HRESULT hr, PWSTR pwszDescription), and the access as public. Edit the function, entering the code in Listing 15.4.

LISTING 15.4. THE CDbAdoDoc GenerateError FUNCTION.

1: void CDbAdoDoc::GenerateError(HRESULT hr, PWSTR pwszDescription)

2:

Connecting and Retrieving Data

You can perform all of the connecting to the database and retrieving the record set in the OnNewDocument function in the document class. Before you can add this functionality, you need to add a few more variables to the document class. You'll need a Recordset object pointer, an IADORecordBinding interface pointer, a couple of string variables for holding the database connection string, and the SQL command to execute to populate the record set. Add all of these variables to the document class as specified in Table 15.3.

TABLE 15.3. DOCUMENT CLASS MEMBER VARIABLES.

Name  Type Access

m_pRs _RecordsetPtr Private

m_piAdoRecordBinding IADORecordBinding* Private

m_strConnection CString Private

m_strCmdText CString Private

In the OnNewDocument function, you'll perform a series of steps for connecting and retrieving the record set. First, you'll set the strings for the database connection and the SQL command to be run. Next, you'll initialize the COM environment and initialize the two pointers so that they are both NULL. You'll create the Recordset object using the CreateInstance function. Open the Recordset, connecting to the database and running the SQL command at the same time. Bind the record class to the record set using the IADORecordBinding interface pointer. Finally, tell the view class to refresh the bound data, displaying the initial record for the user using a view class function that you'll add in a little while. To add all this functionality, edit the OnNewDocument function in the document class, adding the code starting with line 8 in Listing 15.5.

LISTING 15.5. THE CDbAdoDoc OnNewDocument FUNCTION.

1: BOOL CDbAdoDoc::OnNewDocument()

2:

// Any errors?

catch (_com_error &e)

return TRUE;

Before moving any further, it's a good idea to make sure that you add all the code necessary to clean up as your application is closing. You need to close the record set and release the pointer to the record binding interface. You'll also shut down the COM environment. To add all this functionality to your application, add a function to the DeleteContents event message in the document class. Edit this function, adding the code in Listing 15.6.

LISTING 15.6. THE CDbAdoDoc DeleteContents FUNCTION.

1: void CDbAdoDoc::DeleteContents()

2:

Populating the Form

To display the record column values for the user, you'll add a function for copying the values from the record class to the view variables. This function first needs to get a pointer to the record class from the document class. Next, it will check the status of each individual field in the record class to make sure that it's okay to copy, and then it will copy the value. Once all values have been copied, you can call UpdateData to display the values in the controls on the form. To add this functionality to your application, add a new member function to the view class. Specify the function type as void, the function declaration as RefreshBoundData, and the access as public. Edit this new function, adding the code in Listing 15.7.

LISTING 15.7. THE CDbAdoView RefreshBoundData FUNCTION.

1: void CDbAdoView::RefreshBoundData()

2:

NOTE: Because you are working directly with the custom record class that you created in this function, you must include the header file for your custom record class in the view class source file, just as you did with the document class source file.

Saving Updates

When you need to copy changes back to the record set, reverse the process of copying data from the controls on the form to the variables in the record class. You could take the approach of copying all values, regardless of whether their values have changed, or you could compare the two values to determine which have changed and need to be copied back. Call the function that does this before navigating to any other records in the record set so that any changes that the user has made are saved to the database. To add this functionality to your application, add a new member function to the view class. Specify the function type as void, the function declaration as UpdateBoundData, and the access as private. Edit the function, adding the code in Listing 15.8.

LISTING 15.8. THE CDbAdoView UpdateBoundData FUNCTION.

1: void CDbAdoView::UpdateBoundData()

2:

Navigating the Record Set

For navigating the record set, add a series of menus for each of the four basic navigation choices: first, previous, next, and last. Because the Recordset object and the record-binding interface pointers are in the document object, the event messages for these menus must be passed to the document class to update the current record and then to navigate to the selected record. However, the view class needs to receive the event message first because it needs to copy back any changed values from the controls on the form before the update is performed. Once the navigation is complete, the view also needs to update the form with the new record's column values. Looking at the sequence of where the event message needs to be passed, it makes the most sense to add the event message handler to the view class, and from there, call the event message handler for the document class.

To add this functionality to your application, add the four menu entries and the corresponding toolbar buttons. Using the Class Wizard, add a event message handler function to the view class for the command event for all four of these menus. Edit the event function for the Move First menu, adding the code in Listing 15.9.

LISTING 15.9. THE CDbAdoView OnDataFirst FUNCTION.

1: void CDbAdoView::OnDataFirst()

2:

Now add the MoveFirst function to the document class and perform all the actual record set functionality for this function. To add this, add a member function to the document class in your application. Specify the function type as void, the declaration as MoveFirst, and the access as public. Edit this function, adding the code in Listing 15.10.

LISTING 15.10. THE CDBADODOC MOVEFIRST FUNCTION.

1: void CDbAdoDoc::MoveFirst()

2:

// Any errors?

catch (_com_error &e)

Edit and add the same set of functions to the view and document classes for the MovePrevious, MoveNext, and MoveLast ADO functions. Once you've added all these functions, you should be ready to compile and run your application. Your application will be capable of opening the Addresses database table and presenting you with each individual record, which you can edit and update, as in Figure 15.7.

FIGURE 15.7. The running application.

Adding New Records

Now that you are able to retrieve and navigate the set of records in the database table, it would be nice if you could add some new records to the table. You can add this function-ality in exactly the same fashion that you added the navigation functionality. Add a menu, trigger an event function in the view class from the menu, update the current record values back to the record set, call a function in the document class, and refresh the current record from the record set. As far as the menu and the view class are concerned, the only difference between this functionality and any of the navigation menus and functions is the ID of the menu and the name of the functions that are called, just as with the different navigation functions. It's in the document function where things begin to diverge just a little.

In the document class function for adding a new record, once you've updated the current record, you'll make sure that adding a new record is an option. If it is, then you'll build an empty record and add it to the record set. Once you've added the empty record, navigate to the last record in the set because this will be the new record. At this point, you can exit this function and let the view class refresh the form with the data values from the new, empty record.

To add this functionality to your application, add a new menu entry for adding a new record. Add a command event-handler function to the view class for this new menu, adding the same code to the function as you did with the navigation functions, but call the AddNew function in the document class. Now, add the AddNew function to the document class. Add a new member function to the document class, specifying the type as void, the declaration as AddNew, and the access as public. Edit the function, adding the code in Listing 15.11.

LISTING 15.11. THE CDbAdoDoc AddNew FUNCTION.

1: void CDbAdoDoc::AddNew()

2:

}

// Any errors?

catch (_com_error &e)

Now add the function that creates the blank record. In this function, you'll set each of the field variables in the record class to an almost empty string. To add this function to your class, add a new member function to the document class. Specify its type as void, its declaration as CreateBlankRecord, and its access as private. Edit this new function, adding the code in Listing 15.12.

LISTING 15.12. THE CDbAdoDoc CreateBlankRecord FUNCTION.

1: void CDbAdoDoc::CreateBlankRecord()

2:

If you compile and run your application, you should be able to insert and edit new records in the database table.

Deleting Records

The final piece of functionality that you'll add to your application is the ability to delete the current record from the set. This function can follow the same form as all the navigation and add functions with a menu entry calling an event-handler function in the view class. The function in the view class can even follow the same set of code that you used in these previous functions, updating the current record, calling the corresponding function in the document class, and then refreshing the current record to the form.

In the document class function, the record deletion should follow almost the same path that you took for adding a new record. Update the current record, check to see if it's possible to delete the current record, check with the user to verify that he wants to delete the current record, and then call the Delete function and navigate to another record in the set.

To add this functionality to your application, add a new menu entry for the delete function and then attach an event-handler function for the menu's command event in the view class. Edit this function, adding the same code as in the navigation and add record functions and calling the Delete function in the document class. Now, add a new member function to the document class. Specify the new function's type as void, the declaration as Delete, and the access as public. Edit this function, adding the code in Listing 15.13.

LISTING 15.13. THE CDbAdoDoc Delete FUNCTION.

1: void CDbAdoDoc::Delete()

2:

}

20: }

// Any errors?

catch (_com_error &e)

When you compile and run your application, you should be able to delete any records from the set that you want.

Summary

Today, you learned about Microsoft's newest database access technology, ActiveX Data Objects. You saw how you can use ADO as a simple ActiveX control to provide database access through data-bound controls without any additional programming. You also learned how to import the DLL, providing a rich set of data access functionality that you can use and control in your applications. You learned how to retrieve a set of data, manipulate the records in the set, and save your changes back in the database. You learned two different ways of accessing and updating the data values in a record in the record set and how you can do a little more work up front to save a large amount of work in the midst of the application coding.

Q&A

Q Because Visual C++ doesn't support ADO with its wizards, why would I want to use it?

A ADO is the database access technology direction for Microsoft. It's still in the early stages of this technology, but it will gradually become the data access technology for use with all programming languages and applications.

Q If ADO uses ODBC to get to my database, why wouldn't I want to just go straight to the ODBC interface to access my database?

A ADO can use ODBC to access those databases that don't have a native OLE DB interface. If you are using either Microsoft's SQL Server database or an Oracle database, there are OLE DB interfaces available, in which case ADO would not go through ODBC to get to the database. In these cases, using ADO gives your application better performance than using the ODBC interface. With the upcoming operating system releases from Microsoft, you'll find that using ADO is likely to provide you with access capabilities that extend far beyond conventional databases. ADO is a new technology that you'll start seeing in more use in the coming years. Because of its growing importance, it's a good thing to start working with ADO now so that you'll already be prepared to work with it when it's everywhere.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered and exercises to provide you with experience in using what you've learned. The answers to the quiz questions and exercises are provided in Appendix B, 'Answers.'

Quiz

1. What does ADO stand for?

2. What does ADO use for database access?

3. What are the objects in ADO?

4. How do you initialize the COM environment?

5. How do you associate a Connection object with a Command object?

6. How do you associate a Command object with and populate a Recordset object?

Exercise

Enable and disable the navigation menus and toolbar buttons based on whether the recordset is at the beginning of file (BOF) or end of file (EOF, renamed to EndOfFile).