Microsoft

Leverage ADOCE to build remote database applications

In our continuing series on developing applications for Windows CE, we focus on accessing a remote database using C++ and ActiveX Data Objects for Windows CE.


Building Windows CE applications that can access a remote database requires a basic understanding of ActiveX Data Objects for Windows CE (ADOCE) standards and how to initialize COM objects. You must also know the right tools to employ. We'll touch on all these topics as I show you how to get up and running with a simple Windows CE database application using ADO in C++.

For our example project, we'll migrate a simple app that we discussed in a previous article. You can grab the code for this application, which takes advantage of a tabbed view and uses the object store as a database, by clicking this link.

More background info


I'll now migrate this application to ADOCE and eventually access a remote database. The desktop standard for ADO, of which ADOCE is a subset, offers a high-level interface to all kinds of data. Figure A provides an ADOCE functional overview, including the relationship of the standard's four classes:
  • ·        Connection
  • ·        Recordset
  • ·        Field
  • ·        Error

Figure A


Table A offers a brief description of the classes.
Table A

Class

Description

Connection

Represents a connection to a data source

Recordset

A virtual database of fields and rows as performed by a query.

Field

Encompasses a single field in a row.

Error

Returns error information from a data source provider.


The Connection and Recordset classes are most important because they create a connection and deal with database queries, respectively; the Error and Field classes depend entirely on connection and Recordset. You employ the Connection class to connect to a data provider, whether it is Access, SQL, or a third-party database.

Recordset performs queries and stores the results in that Recordset. In the context of Unified Modeling Language (UML), Recordset aggregates the whole Field class, and Connection aggregates the Error class. This implies that the Field class can be instantiated only within the lifetime of the Recordset object. Similarly, the Error class can be instantiated only within the lifetime of a Connection object.

Microsoft ADOCE providers: Pocket Access or SQL CE
Microsoft offers CE developers two primary data providers: CEDB and SQLOLEDB. (We won't discuss third-party solutions in the article.)

CEDB is also known as the object store, or Pocket Access provider. When connecting to a database using CEDB, you can connect to the object store or specify a Pocket Access file data source that lets you access a .CDB file. This .CDB file can be located on the main memory of the Windows CE device or on media such as a compact flash expansion card. CEDB comes with the Pocket PC / Pocket PC 2002 platform.

Microsoft sells SQLOLEDB, or. SQL CE, separately from the Pocket PC OS. You can locally store SQL CE databases, which create files with the extension .SDF, or you can synchronize them over the network with a central SQL database server.

SQL CE takes about 700 KB of RAM, and many consider it the database of choice when developing commercial applications.

Synching up: Remote Data Access or replication?
What about connecting to an offsite database through a remote connection? Traditional ADO programmers would say, “No problem, simply specify the remote server." But unfortunately, ADOCE does not support remote servers.

Using the Microsoft methodology, you must install SQL CE on the PDA with one of two configuration options: Remote Data Access (RDA) mode or replication mode. Both of these approaches have some limitations, which led me to choose a third-party service for my sample application. But in many cases, these two options can meet your needs.

RDA provides a simple way for the Windows CE device to push and pull data from a remote SQL server. You must manually push and pull tables by using a separate RDA COM object. RDA communicates with the SQL server through IIS and employs IIS’s security features to maintain a secure connection.

Replication is by far the most robust solution if users actually use their CE devices to alter large data tables and eventually synchronize with the main system. You can replicate tables onto SQL CE, make changes, and eventually merge those changes back with the SQL server in an asynchronous manner. This solution, however, requires customizations on the SQL server.

RDA is a secure solution implemented in a cumbersome manner through a separate RDA class. Implementing replication is even more complicated and uses yet another Replication class. And I haven't even mentioned that SQL CE is not the easiest piece of software to install, register, and get up and running.

Which led me to my choice to use a third-party service provided to us by Odyssey Software. ViaDB is a universal OLE DB that simply relays OLE DB requests over a wireless or network connection. You must install ViaDB, which is free for a single-user developer license, on the SQL server and on any client Windows CE devices.

The implementation: Movie Tracker DB
In my example for this article, I'm implementing an application using eMbedded Visual C++ 3.0 as the compiler, ADOCE as the database access methodology, and ViaDB as the wireless provider. Of course the code has been written so that you can easily switch to a CEDB or SQLOLEDB provider.

I also decided to preserve the CDatabase interface of our application so that most of the code required no changes. The CDatabase and CRecord classes were simply replaced with a parallel that employed ADOCE, and the application recompiled to provide a Movie Tracker database over a wireless network.

Initializing the COM objects
The first thing I did was initialize the Connection and Recordset classes. Since eMbedded Visual C++ 3.0 does not support COM in the compiler, I had to resort to more traditional methods of achieving COM automation, as shown in Listing A.

We wrote the entire application using ADOCE 3.1. (You can download a copy of the SDK.) After initializing the Connection and Recordset objects, we opened up the ViaDB provider using the Open method of the Connection object, as shown in Listing B. These tasks are executed only once in the application’s entire lifetime.

Opening the database
The CDatabase::SeekDB() method contains the code which queries the database and stores the table in the virtual database declared by the Recordset object.

The first thing we do is create a table as described in Listing C. This is useful only upon initialization of the database. Table B describes the database fields.
Table B

Name

Type

Description

sTitle

nvarchar(255)

Contains the movie title between 0 and 255 characters.

sDesc

ntext

Contains the description which is limited to 1024 by our code.

nHour

int

A 32-bit integer containing the number of hours a movie plays for.

nMinute

int

A 32-bit integer containing the number of minutes a movie plays for.

fRating

float

A 32-bit float containing the user rating a particular movie obtains.


You don't check the return value because the create will fail if the table already exists (this is an expected outcome).

At this point, you want to make sure the Recordset is not open, since you open it at the end of this function call. The code in Listing D checks for an open Recordset and closes any it finds.

The code in Listing E moves toward selecting all the entries in the table. You retrieve this data over the wireless connection into your Recordset object via a standard SQL query.

Before exiting this function, you should make sure the SQL table is not empty. If it is, return a failing Boolean with the code shown in Listing F.

Writing to the database
As I was coding up my SQLOLEDB solution, I realized the provider does not support AddNew and the Delete option present in the Recordset object. That meant that I had to code SQL statements to INSERT a row into the database and to DELETE a row from the database.

Before you write a record, check the state of the Recordset, and if the connection is open, close it, as shown in Listing G.

If you use SQL statements to add the entries into the table, apostrophes in the strings must exist in duplicate. SQL interprets duplicate apostrophes as single apostrophes. The code in Listing H shows how you replace single apostrophes with doubles.

At last, you can insert the five fields into our remote database by executing a Recordset::Open() method, as you did to create the database. You can see this code in Listing I.

Since you employ COM automation and COM in the compiler is not supported, you have to manually convert all the strings to bstrings and allocate and deallocate all the necessary memory.

Reading from the database
Viewing the first or second tab in the application enumerates the database entries. You use the CDatabase::ReadRecord() method to accomplish this. The first action ReadRecord verifies that the Recordset has been opened, as shown in Listing J.

To obtain the fields from the Recordset object, you must retrieve a field collection, then check the Item and Value property for each of the five fields in the collection. Use Microsoft Framework Classes' DDX to put the resultant values in the appropriate text boxes. >Listing K shows you how to read all five elements and store them in the appropriate record variables, which are passed to a function that copies them into MFC’s member variables.

Closing the database
At the destructor of Cdatabase, check for the state of Recordset. If it's open, close it, and then close the Connection object. This terminates the remote connection with the SQL database. >Listing L illustrates the destructor.

Try it yourself
I have exposed a SQL server and ViaDB connection at ShakeHome.com. I opened up port 21210 through the firewall. If you are connected directly to the Internet, the sample application should function on any Pocket PC 2002 device with network connectivity (ActiveSync does not count). So you can try it out yourself and see how simple creating a remotely accessed CE application can be, if you choose the right tools.

 

Editor's Picks