Embedded Visual Basic Windows Ce And Pocket Pc Mobile Applications [Electronic resources]

Transferring Data to and from a PC

One of the bigger challenges facing Windows CE developers, whether using eVB or eVC, is getting data to and from the device. Stand-alone applications have their uses and merit, but to truly integrate with an enterprise solution, some method of data transfer must be used.

All the code for handling data transfer is in the D2D project for Chapter 9.

There are several commercially available tools, and numerous ways to roll-your-own solution, from socket and serial communication to ActiveSync plug-ins and custom RAPI DLLs. One of the easiest methods to move data between a PC and a device is to use two API calls available through ActiveSync: DESKTOPTODEVICE and DEVICETODESKTOP. As their names suggest, one transfers data from the desktop to the device, and the other vice versa.

DESKTOPTODEVICE and DEVICETODESKTOP also have some limitations that include the following:

• Because they are ActiveSync APIs, they only work with ActiveSync installed

  and running. This means that transferring data requires the device be connected to the ActiveSync host.

• The API calls must be made from the desktop. This means that the sync can only be initiated from the desktop and not the device.

• The API doesn't throw an error, so debugging can be very challenging. A non-zero return is an error, but determining the cause of the error is difficult at best.

• The API calls only work with Access databases.

• You can vertically partition data (selectively choose columns to transfer) but you can't horizontally partition data (selectively choose rows to transfer). In SQL terms, this means you can use any column name in your SQL statement, but you can't use a WHERE clause.

Even with these limitations, DESKTOPTODEVICE and DEVICETODESKTOP can be useful. A little programming creativity may be required to get around the last limitation, but it isn't an insurmountable hurdle. Building temporary tables that are already horizontally partitioned is my usual method.

Note

Because DESKTOPTODEVICE and DEVICETODESKTOP are ActiveSync APIs, they must be declared in your VB application. The declarations here state that the Lib is in the Program Files\Microsoft ActiveSync directory, but it's a good idea to change the declaration for production code just in case the user has installed ActiveSync somewhere other than the default location.

DESKTOPTODEVICE

Declare Function DESKTOPTODEVICE _
Lib "\Program files\Microsoft ActiveSync\adofiltr.dll" _
(ByVal DesktopLocn As String, _
ByVal TableList As String, _
ByVal Sync As Boolean, _
ByVal Overwrite As Boolean, _
ByVal DeviceLocn As String) As Long

As its name suggests, DESKTOPTODEVICE transfers data from a desktop PC (specifically, the ActiveSync host) and a connected device.

Let's first look at what each of the parameters are for:

• DesktopLocn is the full path to the desktop database, including the filename.

• TableList is more than the name implies. It's both a list of tables and fields to transfer. If it's not an empty string (which means copy all columns and all tables), it must be a string in the general form

  Tablename. 
  Fieldname.. 
  

Note

Because parameter 2 can be a bit confusing, let's look at a few examples. The following are samples for parameter 2 only (not the entire API call) and what data will be transferred.

• "Contacts..Customers..Notes.." All columns from the Contacts, Customers, and Notes tables will be transferred.

• "Contacts.Name.Phone.Fax.Email.CompanyID.." The Name, Phone, Fax, Email, and CompanyID columns (fields) will be transferred from the Contacts table.

• "Contacts.Name.Phone..Customers.Address..Notes.Description.." The Name and Phone columns from Contacts, the Address column from Customers and the Description column from Notes will be transferred.

Notice that in all cases, the final table is terminated with a pair of periods (..). If these are omitted, the API call will fail.

• Sync determines whether ActiveSync will continue to synchronize the table(s) transferred. This is just like selecting a database for sync through the ActiveSync program itself.

• Overwrite determines whether the operation will overwrite the database on the device if it already exists.

• DeviceLocn is the path and filename for the database on the device. If this parameter is left as an empty string, the tables will be created in the native data store.

Assume that you have a simple Access database on your desktop called D2D.mdb, and it contains two tables, Customers, and States, with the table definitions in Listing 9.16.

Listing 9.16 Table Definitions for the DESKTOPTODEVICE and DEVICETODESKTOP API Samples

<Table>
<Name>Customers</Name>
<Field>
<Name>CustomerID</Name>
<Type>AutoNumber</Type>
<Size>4</Size>
</Field>
<Field>
<Name>FirstName</Name>
<Type>Text</Type>
<Size>50</Size>
</Field>
<Field>
<Name>LastName</Name>
<Type>Text</Type>
<Size>50</Size>
</Field>
<Field>
<Name>Phone</Name>
<Type>Text</Type>
<Size>50</Size>
</Field>
<Field>
<Name>Email</Name>
<Type>Text</Type>
<Size>50</Size>
</Field>
<Field>
<Name>Company</Name>
<Type>Text</Type>
<Size>50</Size>
</Field>
</Table>
<Table>
<Name>States</Name>
<Field>
<Name>ID</Name>
<Type>AutoNumber</Type>
<Size>4</Size>
</Field>
<Field>
<Name>Name</Name>
<Type>Text</Type>
<Size>50</Size>
</Field>
<Field>
<Name>Abbreviation</Name>
<Type>Text</Type>
<Size>2</Size>
</Field>
</Table>

Now say you want to transfer some of the data from this database to your device, but not all of it. You want FirstName, LastName, Email from Customers, and the entire States table. We simply make a single call to DESKTOPTODEVICE like this:

Private Sub cmdDesktopToDevice_Click()
Dim lRet As Long
' get the Customers and States tables
' Get Firstname, Lastname and Email from Customers
' Get all columns in States
' put the data in the root directory of the device
' NOTE: if you supply table names, you MUST terminate with ".."
lRet = DESKTOPTODEVICE("C:\Book\Chapter 9\D2D\D2D.mdb", _
"Customers.FirstName.LastName.Email..States..", False, True, "\D2D.cdb")
If lRet <> 0 Then
MsgBox "And error occurred transferring data"
End If
End Sub

Notice that I gave both the full source path and destination path. You will likely have to use a different source path.

If you look on the device at this point, you should have a file called D2D in the root directory (PocketPC hides the extension).

Another nice feature ActiveSync provided here is a log of data transfers, and it provides it automatically. Any time DESKTOPTODEVICE (or DEVICETODESKTOP) is called, a log file is created in the device's Profile directory, which can be found as a subdirectory of ActiveSync. If your device is called PocketPC, for instance, and you installed ActiveSync in the default location, the profile directory will be

C:\Program Files\Microsoft ActiveSync\Profiles\Pocket_PC

The file created will be db2ce.txt. In this example, it will look something like Listing 9.17.

Listing 9.17 A Log File Automatically Generated by ActiveSync

Monday, July 09, 2001 4:54:50 PM, User: ctacke, synchronization started.
Database:
option: 255 - Overwrite existing tables
option: 0 - Keep this database synchronized
Monday, July 09, 2001 4:54:50 PM, Database transfer begun.
Copying table Customers
Processing indexes:
Index processing has been stopped. There are no more indexes available.
SQL: SELECT `FirstName`,`LastName`,`Email` FROM `Customers`
Copying of 4 record(s) completed.
Copying table States
Processing indexes:
Index processing has been stopped. There are no more indexes available.
1 index(es) were successfully created.
SQL: SELECT `ID`,`Name`,`Abbreviation` FROM `States`
Copying of 6 record(s) completed.
Monday, July 09, 2001 4:54:51 PM, Database transfer complete.
Transfer Statistics:
2 Table(s)
10 Record(s)
6 Packet(s)
5594 Bytes

This can provide at least some help for debugging problems you may encounter when testing or deploying your applications. Notice that it provides the exact SQL statements it runs to extract the data.

DEVICETODESKTOP

Declare Function DEVICETODESKTOP Lib _
"\Program files\Microsoft ActiveSync\adofiltr.dll" _
(ByVal DesktopLocn As String, _
ByVal TableList As String, _
ByVal Sync As Boolean, _
ByVal Overwrite As Boolean, _
ByVal DeviceLocn As String) As Long

DEVICETODESKTOP is the complementary function of DESKTOPTODEVICE and works very similarly. Even their parameter lists are identical.

Let's continue the example from the previous section and transfer the database back to the desktop, this time transferring the whole thing (at least as far as the device is concerned). This confirms that the call to DESKTOPTODEVICE worked properly.

Private Sub cmdDeviceToDesktop_Click()
Dim lRet As Long
' Get the entire D2D database (as it exists on the device)
' Change the local name
lRet = DEVICETODESKTOP("C:\Book\Chapter 9\D2D\D2Dsmall.mdb", ", _
False, True, "\D2D.cdb")
If lRet <> 0 Then
MsgBox "And error occurred transferring data"
End If
End Sub