Windows CE Database Programming Using ADO

Dilemma: Your boss just came into your office and told you he wants to join in on this whole "Wireless Revolution" thing. He wants you to develop a database app (no problem, you think) that will tie into the company's centralized database (ok, still no big deal), and it needs to run under Windows CE (doh!!) because he just ordered 500 CE units for your company's sales force and they'll be here in a week. The uncertainty creeps in. You're heard of Windows CE, of course, but you've never actually written anything for it. You remember hearing it's scaled down, but it's still Windows, right? How hard can it be to write a simple database app for it? You flash your boss a smile and in your most confident programmer-guru voice, you tell him he's picked the right person for the job. As he walks out you wonder if you've signed your one-way ticket to the unemployment line because you just promised Rome built in one day.

Never fear, true believer! Microsoft has made programming data access on CE devices as easy as it is on the desktop! Well...almost as easy. The good news is that ADO is available for all current versions of Windows CE. The bad news is that like all things CE, there are limits to what can be done and hoops to jump through to get it all working. But I'm getting ahead of myself. Let's start at the beginning, shall we?

Introduction

ActiveX Data Objects (ADO) is the central component to Microsoft's Universal Data Access (UDA) strategy. Microsoft promotes UDA by saying it "provides high-performance access to a variety of information sources, including relational and non-relational, and an easy to use programming interface that is tool and language independent." Packaged along with OLEDB and ODBC, ADO is the programming model of choice for data access in the Microsoft environment. While plagued with a few annoying bugs (and cryptic error messages) in its first few release versions, ADO has matured and become a solid and efficient method of data access for Microsoft products. So much, in fact, it's Microsoft's recommended access method for all things data. If your web page is connecting to a database on an IIS server, you're using ADO. If your VB app is connecting to SQL Server, you're using ADO. And now it seems like if you want to write a data-enabled app for CE, you're using ADO. So in keeping with the goal of data-access standardization across all Windows platforms, comes ActiveX(R) Data Objects for Microsoft Windows(R) CE (ADOCE).

Whew, you think, this is going to be easier that I thought! I know ADO already, so ADO under Windows CE is the same, right? All I have to do is download MDAC for CE, and I'm done! Sorry to break it to you, but sometimes life just isn't that easy.

ADOCE Specifications

The first thing you need to know about ADOCE is that it is a subset of ADO. This means there is only implementation of the Recordset and Field objects. No Command object, no Connection, no Error, no Property. It does, though, offer automatic two-way synchronization with the desktop, ANSI SQL support, and (generally) standard ADO compatibility. Armed with that information, let's see what we need to install to develop with ADOCE.

The first snag we hit is what software we need to install. All the documentation on ADOCE says that you need to have Microsoft Windows CE Services 2.1 or later installed. The only problem is that the Windows CE Services installation CD can only be found packaged with an actual Windows CE device. If you don't have an actual device, or know anyone who does, you're out of luck. Au contraire, you say, didn't Microsoft replace Windows CE Services with Microsoft ActiveSync 3.0, which is freely downloadable on the web? Why yes, ActiveSync 3.0 did replace CE Services, and you can find it on Microsoft's site or on many CE device manufacturers' sites. Also, yes, ADOCE will install with ActiveSync instead of CE Services, but only after a bit of installation script massaging. Following the directions in the Microsoft KB article Q242499 , you have to download the ADOCE 2.0 SDK, extract it, and edit the ADOCE.STF file so that it accepts ActiveSync instead of CE Services. So the bottom line is that if you really want to develop ADOCE applications without an actual CE device, Microsoft makes you work a bit for it. OK, that said, let's go down the installation steps:

1. Install Microsoft Windows CE Services 2.1 or later (if you have it).
2. Install Microsoft ActiveSync 3.0. This will replace CE Services and bring you up to date on CE connectivity software.
3. Install the Windows CE Toolkit for whichever development system you're using (Visual Basic 6.0 or Visual C++ 6.0). Here's some more bad news: The CE Toolkit has to be purchased ($200) if you don't have access to a Universal MSDN subscription. But your boss was the one who told you to do it, right? This sure sounds like a justifiable expense to me!
4. Download the ADOCE 2.0 SDK, extract it (make changes to ADOCE.STF if you don't have CE Services), and run it.
5. Download (free!) and install the appropriate SDK for the device you'll be developing for (H/PC, H/PC Pro, P/PC) from msdn.microsoft.com/cetools/.

That should be all you need to do to get started. Now that we've got everything installed, let's take a look at what ADOCE can do.

Desktop Synchronization

ADOCE allows two-way direct synchronization between a database on the CE device and a database on the desktop. The easiest way to set this up is with an Access database (.mdb) on the desktop and its cousin, the Pocket Access database (.cdb) on the CE device. Using ActiveSync 3.0, you can specify an Access database, check which tables you want to import on to the CE device, and set how often you want the two databases synchronized. For example, say you have a database named Contacts.mdb on your desktop computer. It has one table named Business. To bring this database over to your CE device follow these steps:

1. Make sure the device is connected to your computer and open ActiveSync.
2. Chose Import Database Tables from the Tools menu.
3. Select the Access database you want to import (in our example, Contacts.mdb) and click Open.
4. A dialog titled "Import from Database to Mobile Device" will appear. You should see the tables listed in an unexpanded tree view. You can select which tables and fields you want to import by checking their corresponding check boxes.
5. If you want to keep the tables on the CE device synchronized with the desktop Access database, select the 'Keep tables synchronized' check box.
6. Click OK, and congratulations! You are now the proud owner of a brand-new fully-synchronizable Pocket Access database!

Now that you have a .cdb file on the CE device, you can write an application that uses that database, and every time the device is synchronized with the desktop, your data is synchronized too!

ADOCE Code Examples

Now that we've got the basics down, ADOCE is installed, and we have our little .cdb database for synchronization, let's get down and dirty into some code. For this article, all of the example code is written in Visual Basic 6.0. (If you're using C++ 6.0, the conversion should be fairly easy, if you've ever used ADO before.)

Note: To use ADOCE in VB6, you have to select the Microsoft CE ADO Control (ADOCE) 2.0 under Project References, not Components.

If you're using a .cdb file, as in our example, you open the Recordset first:

Dim rs 'Recordset
Set rs = CreateObject("ADOCE.Recordset")
rs.Open "Buisness", "\Contacts.cdb", adOpenKeyset, adLockOptimistic

As you can see, this looks a lot like the standard ADO Recordset.Open function. Well, you're right, except for a few changes. First, since we're doing this example in VB and the VB Runtime on CE is actually the VB Scripting Runtime (for more information, see our Visual Basic for Windows CE tutorial) so the recordset is not explicitly declared, opting instead for the CreateObject function known all too well to VBScript Web Developers. The Recordset.Open has the same parameters normally found in ADO (Recordset.Open Source, ActiveConnection, CursorType, LockType, Options) but instead of using a Connection object, we put in the path to the .cdb file we want to use.

Alternatively, if you aren't using an existing database and want to create a new virtual recordset that will be stored within the CE operating system itself, just put double-quotes ("") for the ActiveConnection argument like so:

'Create the table
Dim rs 'Recordset
Set rs = CreateObject("ADOCE.Recordset")
rs.Open "create table Business (fname text, lname text, company_
text, address text, phone integer)"
Set rs = Nothing

'Open the table
Set rs = CreateObject("ADOCE.Recordset")
rs.Open "Buisness", "", adOpenKeyset, adLockOptimistic

Now that you've got the recordset, reading from it and writing to it is fairly straightforward ADO:

Dim rs 'Recordset
Set rs = CreateObject("ADOCE.Recordset")
rs.Open "Business", "\Contacts.cdb", adOpenKeyset, adLockOptimistic

'Read Recordset
txtFName.Text = rs.Fields("fname").Value
txtLName.Text = rs.Fields("lname").Value
txtCompany.Text = rs.Fields("company").Value
txtAddress.Text = rs.Fields("address").Value
txtPhone.Text = rs.Fields("phone").Value

'Add New Record
rs.AddNew
rs.Fields("fname").Value = "Robert"
rs.Fields("lname").Value = "Smith"
rs.Fields("company").Value = "The Cure"
rs.Fields("address").Value = "London, England"
rs.Flelds("phone").Value = "134551212"
rs.Update
rs.Close
Set rs = Nothing

And that's about all there is to it.

In closing, here are a few tidbits about ADOCE to remember:

• Just like in ADO Desktop, if you use parameters in AddNew or Update, the changes are automatically made to the database.
• ADOCE does not support batch updates.
• With the exception of the Value property, all the properties of the Field object are read-only.
• You can't use the UPDATE SQL statement for updates, you must use Recordset.Update.
• Keep your tables as small and efficient as possible. Remember, you're working with a device that has very limited memory!

Conclusion

Well, it's the end of the week, and your boss barges into your office. "Where's my program?!" He shouts. "The Installation program was uploaded to the Apps server an hour ago, sir." You reply coolly. "Would you like to see a demonstration?" You say as you whip out your Pocket PC and start loading up last quarter's sales figures. "Uh...no, that's all right," your boss stammers, "I'll send a memo out to the troops and get them to start installing it....Uh, good work! You didn't buckle under the pressure, that'll get you far in this business." Your boss walks out and you lean back in your chair, dreaming of future promotions and stock options and think, "Ahh, job satisfaction...and I owe it all to ADO...."

Interesting Links to Check Out

• Microsoft Windows CE
• Microsoft Windows CE Toolkits
• Developing Data-Enabled Applications for Windows CE with Visual Basic and ADOCE
• MSDN Library - ADOCE