Introduction to WTL OLE DB Database Applications

• Download sample project - 11 Kb
• Download sample database - 9 Kb
• Download sample executable - 28 Kb

Introduction

This article describes using an ATL wizard-generated OLE DB consumer with WTL's CWinDataExchange (DDX) to create a basic WTL database application. The sample project is a standard WTL dialog-based application that uses edit controls to display the data. It acquires data from a Microsoft Access 97/2000 database via the OLE DB for Jet 4.0 provider.

The sample project demonstrates inserting, deleting, and saving rows as well as forward and reverse navigation through the rowset. It also shows basic OLE DB error handling techniques.

Enabling the ATL Object Wizard

The ATL Object Wizard is normally not accessible from a WTL project. Follow these two steps to overcome this shortcoming. First, add an "idl" file using the Project-->Add To Project-->Files menu. For example, the sample project provided with this article contains "Atl.idl", which is basically a dummy file with a single statement: library Atl { };. After the file is added, go to Project-->Settings and exclude the file from all builds as shown in the following picture.

Second, add the following compiler directives to the project's stdafx.h file. These defines are never actually used for anything but trick the ATL Object Wizard into allowing the insertion of ATL objects into WTL projects. Only a few of the ATL objects will work correctly with WTL projects. For example, Data Access-->Consumer and Miscellaneous-->Dialog can be safely added but none of the items from the Objects and Controls categories.

// included only to enable ATL object wizard support

#if __NEVER
   #include < atlcom.h >

   BEGIN_OBJECT_MAP(ObjectMap) END_OBJECT_MAP()
#endif

Creating an OLE DB Consumer

OLE DB, similar to client-server, is a two-tier paradigm. A Consumer interfaces with application code while a Provider interfaces with the database. Once the ATL wizard is enabled as described above, you can use the New Atl Object selection from the Insert menu to add a Consumer object from the Data Access category (see picture below) to your WTL project.

Follow these steps (with appropriate values for your situation) using the ATL Object Wizard Properties sheet to configure an OLE DB consumer:

1. Use the default type: Command
2. Click the Select Datasource button
3. Choose an OLE DB provider type
4. Select a datasource on the Connection tab
5. Fill in any necessary login information
6. Test the connection
7. Make advanced settings if appropriate
8. Click OK to finish configuring the datasource
9. Select a Database Table and click OK

Your object wizard properties should now look something like the next picture. Note that you must check Change, Insert, and Delete if you wish to enable those functions for your consumer. If you wish, you can also change the names given to your classes or header file.

Once you accept the settings, the ATL wizard creates an OLE DB consumer header file and adds it to your project. It also adds atldbcli.h to stdafx.h. At this point, the consumer is almost ready to use. The next section of this article explains how to incorporate a few changes that help the consumer perform inserts on tables containing autonumber columns.

Modifying the OLE DB Consumer

The sample project uses an MSAccess database with one table, Contacts, which has an autonumber primary key column, one text and one memo column, two long columnns, and a float column. The sample database was created in Access 97 and then Access 2000 enabled. Since the sample project uses Jet 4.0, your system must have the appropriate DLLs available. (Note: Jet 3.51, ODBC, and SQL Server have some significant differences and are discussed in a later article in this series.)

The provider uses an "accessor" to determine how to transfer data to and from the consumer. Each column in the Contacts table will have an associated member variable defined in the CContactsAccessor class. Each column will also have a corresponding entry for the member variable in the COLUMN_MAP as shown in this example from the sample application's "Contacts.h".

BEGIN_COLUMN_MAP(CContactsAccessor)
   COLUMN_ENTRY(1, m_ID)
   COLUMN_ENTRY(2, m_First)
   COLUMN_ENTRY(3, m_Last)
   COLUMN_ENTRY(4, m_Area)
   COLUMN_ENTRY(5, m_Phone)
   COLUMN_ENTRY(6, m_Value)
END_COLUMN_MAP()

Autonumber Columns

In order to use an autonumber column with inserts, you must add an additional member variable and change the column entry for the m_ID variable from COLUMN_ENTRY to COLUMN_ENTRY_STATUS as follows:

// status flag for m_ID

ULONG m_status;

BEGIN_COLUMN_MAP(CContactsAccessor)
   // add status flag to column entry

   COLUMN_ENTRY_STATUS(1, m_ID, m_status)
   ...

In addition, the status flag is set to a specific value in the ClearRecord() method. ClearRecord(), shown below, prepares a row for insert and is a convenient location for row initializations of any type. DBSTATUS_S_DEFAULT tells the provider to use default values if an explicit value is not supplied. In the sample application, the provider is told to increment the autonumber primary key.

void ClearRecord()
{
   memset(this, 0, sizeof(*this));

   // generate autonumber on insert

   m_status = DBSTATUS_S_DEFAULT;
}

A Bug?

Finally, you may need to comment out one of the wizard-generated properties because it does not work with MDAC 2.6 and 2.7.

// invalid property for some Jet 4.0 configurations

// dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);

Preparing MainDlg

Make the changes described in the next few subsections to use the OLE DB consumer with an application's main dialog. The general process is to add the appropriate header files, add Dynamic Data Exchange (DDX) to the multiple inheritance list, add a member variable for the consumer, create a DDX map, add code to open the database connection in InitDialog(), and add code for database commands such as Next and Insert.

Headers and Stuff

Add the WTL DDX header file < atlddx.h > and the consumer header file ("Contacts.h" in the case of the sample application) as includes in maindlg.h. Then, enable DDX inheritance as shown below. CMainDlg may inherit from other classes as well.

class CMainDlg : public CDialogImpl< CMainDlg >, public CWinDataExchange< CMainDlg >

In addition, add a member variable for the OLE DB consumer. For example:

CContacts m_contacts;

DDX Mapping

The purpose of DDX is to copy data values between member variables and controls such as edits and checkboxes. You must manually create a DDX map (sorry, no wizard) containing an entry for each member variable and control pair and then use DDX_LOAD to copy data from member variables to controls and DDX_SAVE to copy in the other direction.

The following example shows a DDX map that links dialog controls, such as IDC_ID, to associated database column member variables, such as m_ID. Note the use of DDX data types such as DDX_UINT and, also, that you can use built-in data validity checks such as text length and numeric range of floats.

BEGIN_DDX_MAP(< CMainDlg >)
   DDX_UINT(IDC_ID, m_contacts.m_ID)
   DDX_TEXT_LEN(IDC_FIRST, m_contacts.m_FIRST, 20)
   DDX_TEXT_LEN(IDC_LAST, m_contacts.m_LAST, 30)
   DDX_UINT(IDC_AREA, m_contacts.m_AREA)
   DDX_UINT(IDC_PHONE, m_contacts.m_PHONE)
   DDX_FLOAT_RANGE(IDC_VALUE, m_contacts.m_VALUE, 0.0, 1.0)
END_DDX_MAP()

Hint: Creating the DDX map is relatively easy if you start with a copy of the column map from the OLE DB consumer and use search/replace to change COLUMN to DDX. Then, change the class name and add datatypes, control IDs, and table prefixes. It also simplifies things if the control names are similar to the column names.

Open Rowset in InitDialog

Add the following code to the dialog's InitDialog() method to open the rowset for processing.

// open the rowset and move to the first

// record. Display the data if successful

HRESULT hr = m_contacts.Open();
if (hr == S_OK)
{
   if (m_contacts.MoveFirst() == S_OK)
      DoDataExchange(DDX_LOAD);
}
else AtlTraceErrorRecords(hr);

Error Handling

Most OLE DB commands return HRESULT values, such as S_OK, E_FAIL, and E_NOINTERFACE (for problems trying to initialize an OLE DB interface like IROWSET). When an application is run in debug mode in the Visual Studio IDE, AtlTraceErrorRecords() is useful for printing errors messages to the debug window. Here is an example error listing for return value E_FAIL:

ATL: OLE DB Error Record dump for hr = 0x80004005
ATL: Row #:    0 Source: "Microsoft JET Database Engine" Description: "Could
not find file 'Oledb1.mdb'." Help File: "(null)" Help Context: 5003024 GUID:
{0C733A8B-2A1C-11CE-ADE5-00AA0044773D}
ATL: OLE DB Error Record dump end

Command Handlers

Here are two examples of command handlers used to control OLE DB operations in the sample application. Download the sample project if you wish to see all five (previous, next, insert, save, and delete).

The atldbcli.h header file provides several navigation methods for OLE DB rowsets, such as moving to the first, last, next, or previous row. Here is an example of moving to the next row and then loading data from the rowset to the dialog's edit controls.

LRESULT OnNext(WORD, WORD, HWND, BOOL& bHandled)
{
   // move to current row + 1 or beep if already at last row

   if (m_contacts.MoveNext() == S_OK)
      DoDataExchange(DDX_LOAD);
   else ::MessageBeep((UINT)-1);

   return 0;
}

Here is an example of inserting a new row into the rowset. Since the sample project uses an autonumber primary key column, steps must be taken to increment the autonumber. As discussed in a previous section of this article, new rows are initialized with the ClearRecord() method of the accessor. After the row buffer is initialized, the row is inserted into the database and its data displayed in the dialog's edit controls with DDX.

LRESULT OnInsert(WORD, WORD, HWND, BOOL& bHandled)
{
   // initialize the columns for a new row

   m_contacts.ClearRecord();

   // insert the new row

   HRESULT hr = m_contacts.Insert();

   // display the new row in the form

   if (hr == S_OK)
   {  if (m_contacts.MoveLast() == S_OK)
	 DoDataExchange(DDX_LOAD); }
   else AtlTraceErrorRecords(hr);

   return 0;
}

Preprocessor Definitions

If a database table, such as Contacts in the sample application, contains floating point values, you must modify the Project Settings C/C++ tab as follows to enable floating point support:

1. Remove _ATL_MIN_CRT from the Win32 Release
2. Add _ATL_USE_DDX_FLOAT to All Configurations

Terms Of Use

The sample project available with this article is free. Use the code however you wish.

Please note that this code is capable of permanently deleting records from a live database. USE AT YOUR OWN RISK!

THIS SOFTWARE IS DISTRIBUTED AS-IS, WITHOUT WARRANTIES OF ANY KIND.