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.
#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:
- Use the default type: Command
- Click the Select Datasource button
- Choose an OLE DB provider type
- Select a datasource on the Connection tab
- Fill in any necessary login information
- Test the connection
- Make advanced settings if appropriate
- Click OK to finish configuring the datasource
- 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:
ULONG m_status;
BEGIN_COLUMN_MAP(CContactsAccessor)
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));
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.
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.
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)
{
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)
{
m_contacts.ClearRecord();
HRESULT hr = m_contacts.Insert();
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:
- Remove
_ATL_MIN_CRT
from the Win32 Release
- 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.