SQL Compact Table Export

This is a very simple variation on my last blog post: instead of copying the table to the same database, you can now copy it to another database (although with the same table name, but you can easily change that). The table data is exported in the Export method of the SqlCeTableTool class using a SqlCeDataReader for reading and a SqlCeResultSet for writing. Simple stuff, really...

Sample code: CopyTableSample2.zip

SQL Compact Table Copy

How do you make a copy of a SQL Compact table? Version 3.5 does not support the SELECT INTO command that allows you to easily do just that. Instead, it provides the INSERT INTO SELECT FROM command that allows you to copy existing table data into another existing table. So we are only missing one part: how to create a replica of the existing table structure.

If you want to stick to managed code only, you can read all the database structure information from the INFORMATION_SCHEMA views. To get information ot of these views, you just have to issue a SELECT command. For instance:

SELECT * FROM INFORMATION_SCHEMA.TABLES

returns all the existing tables in the database. In our case, we need to get information from an existing table and build a CREATE TABLE command out of it in order to create a replica of the original table. For this purpose, you can issue the following SQL command:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName' ORDER BY ORDINAL_POSITION

With a little work you can read from this data and build a CREATE TABLE command. If you look at the returned data, you will see that you have all the information you need. In fact, you just need to set up a StringBuilder in your code, and start adding the columns and their types. You can fin this in the sample code (see below), although the code still does not support the numeric data type.

After creating the table, you can now copy the data, making sure that you execute a SET IDENTITY_INSERT command on the target table if it has an identity column.

The sample code has a very simple UI that allows you to specify a connection string, the original and target table names and a button to perform the copy.

Sample code: CopyTableSample.zip

The Remote HTC G Sensor Client Application

Without further ado, here's the final version of the desktop HTC G Sensor client. This very simple .NET 3.5 forms application reads the stream of data from the HTC G Sensor via RAPI and displays it on the screen. Obviously, you can adapt this to meet your particular needs.

The main project in on the HTCGConsumer folder. Before trying to use this sample application, please make sure that the HTCClient.dll file is on your output directory, otherwise you will get an exception. This is the desktop client DLL that connects to the device via RAPI and is consumed by the HTC.GSensor.dll assembly through the HTC.GSensor.RemoteClient class.

Finally, don't forget to remove the RAPI restrictions on your device if you are running a WM6.x device. Enjoy.

RAPI2 article on CodeProject

Thanks to Alberto's head-up, here's an interesting article on RAPI and its lesser-known COM interface: RAPI2: The Friend You Never Knew You Had

Flashback

"I'll buy it if it works on my Pocket PC with a MIPS CPU..."

This was the rough message that a potential customer of my products wrote in a recent email. This is not unusual - some of my customers use the products because they still work with SQL CE 2.0. Apparently for business customers, a ten year old technology seems quite fitting for their daily work.

The device component that ships with my products goes all the way back to Pocket PC 2002, when Microsoft decided to support only ARM-based CPUs and drop support for MIPS and other brands. I remember that this was a bit of a nuisance back then because there were some non-ARM devices on the market that suddenly lost the ability to upgrade to version 2002 (something similar is happening now, isn't it?).

What this all means is that this particular person wants me to build the device DLL using the Pocket PC 2000 SDK. To my big disgrace, I found that my VMs were not properly configured so I set out to install a fresh XP VM with the Embedded Visual Tools 3.0 (2002 edition) plus the Pocket PC 2000 SDK. Fortunately, I kept copies of these museum-grade pieces of software because you cannot find them anymore from official Microsoft sources (namely MSDN).

So I started to install the thing and was prompted to enter the product key. Where did I save it? Damn... Nowhere to be found! I do have the eVC4.0 one, but not the one for version 3.0. Or do I? Actually, if you are going down memory lane like I did this afternoon, remember that the key for eVC3.0 is the same as for eVC4.0 (someone told me long ago that the product would validate any key, but I did not try that). So look for a valid download souce for eVC4.0 (like CNET) and use the key they provide.

Now that all is set up, let's enter the time machine...

The HTC G Sensor RAPI Client

Now that we have a device RAPI extension DLL to capture the HTC G Sensor data it's time to write the PC client DLL. I wrote this as a native code DLL because it made my life simpler when interfacing with RAPI. It exposes five C-style functions that can be readily P/Invoked from .NET:

• HTCGSensor_New - Creates a new native client object
• HTCGSensor_Delete - Destroys the native client object
• HTCGSensor_Open - Opens the remote HTC G Sensor server
• HTCGSensor_Close - Closes the remote HTC G Sensor server
• HTCGSensor_Read - Reads the HTC G Sensor data

The last three should be familiar to you right now, but why do we need the first two? Believe me that I learned this one quite painfully: when exposing native objects to managed code, always provide their memory management functions. The reason is quite simple: if you are allocating the native objects from the native C++ heap, you must delete them using the C++ memory management library. That's why you see a "new" and a "delete".

So what do these functions look like? They are actually very simple:

HTCGCLIENT_API void* HTCGSensor_New()
{
    HTCGSensorClient* pClient = new HTCGSensorClient();
    return pClient;
}

Why the void* return type? These exported functions are meant to be used by the P/Invoke mechanism that should know nothing about the pointer type: it will be mapped to an IntPtr type and treated as an opaque data type. Likewise, there's no need to check the data type on input:
 
HTCGCLIENT_API int HTCGSensor_Open(void* pClient)
{
    HTCGSensorClient* client = (HTCGSensorClient*)pClient;
    if(client)
        return client->Open();
    return E_FAIL;
}

So these functions are there to act as conduits between the a C++ object of type HTCGSensorClient, where the bulk of the work is done, and the calling .NET assembly. It should come as no surprise that this class also implements the Open, Close and Read methods, besides the standard constructor and destructor. Things get a bit more interesting here because we must open a connection to the device server through RAPI (the sample code uses an updated version of the CRemoteAPI class that I published ages ago). In a nutshell, the Open function (see the sample code) initializes RAPI and remotely invokes the HTCGSensorStream function on the HTCGServer.dll file (by default it should be placed on the device's \Windows folder, but you might want to change this). Note that a reference to a client-side version of the IRAPIStream object is kept and that's how the Read function gets the G sensor data. This function is implemented in terms of a write and a read operation. The write tells the server to fetch the G sensor data and then waits on the read for the data to arrive (that's why this might prove slow for high-frequency sampling: for each sample there must be a round-trip to the server). The data is copied to an HTCGSensorData variable passed in by pointer reference. Closing the client implies writing a command to the server and closing the RAPI connection.
 
On my next post I will wrap this small project up by publishing the high-level .NET assemblies that consume the HTC G Sensor data.
 
Sample code: HTCGClient.zip

The HTC G Sensor RAPI Server

RAPI is one of the most useful features in the Windows CE OS because it allows you to remotely access your device while connected via ActiveSync or WMDC. It's like having a generic socket server on your device that is always listening for a connection from the connected PC and that, besides implementing a nuber of very useful APIs (Registry and File System to name a few) it also allows you to extend it by using special DLLs. Unfortunately this feature also is a security issue because it allows any connected PC to scan your device's file for instance. This issue was solved with the Windows Mobile 5 version of the OS by enforcing security policies that vary in degree of strictness. For Smatphones, the security policy has always been much stricter than for "Pocket PCs" (the main distinction between both is whether or not the screen is touch-sensitive), with their "two-tiered" security model. Pocket PCs have a single-tier security model and are a bit kinder on security. In a nutshell, you usually get these devices either partially or totally locked for RAPI connections. If your device is completely locked, then there's no hope for you and you can stop reading this right now. On the other hand, if you have a partially locked device you can either use VS2008's Device Secutity Manager tool to crack open the device's security or use the SDK cerificates to sign your DLL (you don't want to spend money on this by purchasing Geotrust signing events, do you?). Anyway, if you plan to sell your DLL as part of an application's setup file, you will have to sign it (and do some other crazy stuff like marking the DLL file with the system attribute bit and adding a bit of XML to the cab).

Writing the DLL is way simpler than all of the above. Open VS2008 and create a new project and select Visual C++ / Smart Device / Win32 Smart Device Project. Name this project "HTCGServer". When the Win32 Smart Device Project Wizard shows up, click the Next button to select the desired SDKs. Next, select the "DLL" radio button and the "Export symbols" check box.

Because this is a RAPI extension library, we need to make a reference to the RAPI header files which ship
with the SDKs. On my system the header file is on this folder:

C:\Program Files\Windows CE Tools\wce500\Windows Mobile 5.0 Pocket PC SDK\Activesync\Inc

For the Windows Mobile 6.0 SDK, you can find the same files here:

C:\Program Files\Windows Mobile 6 SDK\Activesync\inc

Go to your project's properties and select the "C/C++" option on the left tree and add one of the above paths (please validate these paths against your system first) to the "Additional Include Directories" property. This is where you add paths for header files other than the ones that ship with the SDK and that are not in your project's source path. Make sure that both the "Release" and "Debug" configurations have these paths set before you go on.

Now, let's take a look at the HTCGServer.h and HTCGServer.cpp fils that the Project Wizard created for us. Both files contain samples of DLL exportable symbols: a variable, a function and a class. All of them are marked with the HTCGSERVER_API macro that is defined at the top of HTCGServer.h:

#ifdef HTCGSERVER_EXPORTS

#define HTCGSERVER_API __declspec(dllexport)
#else
#define HTCGSERVER_API __declspec(dllimport)
#endif

This works in a very simlple way: when you compile this header file from within the DLL project, the HTCGSERVER_EXPORTS macro is defined, so HTCGSERVER_API resolves to __declspec(dllexport), meaning that you want to export this symbol from the DLL. You can confirm this by opening your project's property pages and inspecting the C/C++ / Preprocessor / Preprocessor Definitions property: you will find the HTCGSSERVER_EXPORTS definition there (done for you again by the Project Wizard).
 
Now erase everything below these lines and write the following:
 
#include <rapi.h>
 
extern "C"
{
HTCGSERVER_API int HTCGSensorStream(DWORD cbInput, BYTE* pInput, DWORD* pcbOutput, BYTE** ppOutput, IRAPIStream* pStream);
};

These lines include the RAPI header file (required for the IRAPIStream declaration) and declare a "C" function named HTCGSensorStream. This is the function that will be called by the RAPI runtime and it cannot be exported with the standard C++ linker names (these are mangled in order to include parameter type information for better run-time type checking). The number and types of the parameters are defined by RAPI for DLL extension functions and these can be of two types: blocking and non-blocking. A blocking extension function works like a regular function call: does its work and returns data back to the caller. A non-blocking function (or streamed call) uses the pStream parameter to keep an open communications channel to the caller on the PC: that's exactly what we'll use.
 
Let's create a stub for this function on the HTCGServer.cpp file:

//
// Starts the HTC G Sensor streaming to the PC via RAPI
//
int HTCGSensorStream(DWORD cbInput, BYTE* pInput, DWORD* pcbOutput, BYTE** ppOutput, IRAPIStream* pStream)
{
  *ppOutput = NULL;
  *pcbOutput = 0;
  LocalFree(pInput);
  pStream->Release();

  return 0;
}

Right now, the function does nothing but return after cleaning up. I'll get back to it after finishing the data server class. Now you can go back to last post's sample code and copy the HTCGSensor.h and HTCGSensor.cpp files to the new poject directory and add them to the project tree. This is all we need to write the final part of the extension DLL: the server class.

The server class is actually quite simple: it opens the sensor in the constructor, closes it in the destructor and contains a single function (Run) that cntinually reads data from the stream and writes back the sensor data to the listening PC software. This particular implementation uses a polling mechanism that waits for a command from the client and writes sensor data back. It's quite simple to understand how this works, but it's not the best approach performance-wise. From my own experience I have learned that you get the best performance when you put the stream reading and writing in different threads. This allows you to stream data from the server to the client without waiting for a specific command. Performance improves at the cost of complexity.

HRESULT HTCGSensorServer::Run()

{
  HRESULT hr = S_OK;
  bool bRun = true;

  HTCGSensorData sensorData;
 
  while(bRun)
  {
    DWORD dwMsg = 0, dwRead, dwWrite;

    hr = m_pStream->Read(&dwMsg, sizeof(dwMsg),
                         &dwRead);
    if(FAILED(hr) || dwRead != sizeof(dwMsg))
      bRun = false;
    else
    {
      switch(dwMsg)
      {
        case CMD_QUIT:
          bRun = false;
          break;
        case CMD_READ:
          if(m_sensor.Read(&sensorData))
            hr = m_pStream->Write(&sensorData,
                                  sizeof(sensorData),
                                  &dwWrite);
          else
            bRun = false;
          break;
      }
    }
  }
  return hr;
}
 
Easy, right? The loop waits for two different commands: one for reading the sensor and the other to stop the loop (thereby exiting the server). Now you just need to call this function from the HTCGSensorStream entry point and your server is ready. Add the following lines to the top of the function:
 
HTCGSensorServer server(pStream);
server.Run();
 
Done.
 
On my next post I will implement the PC client DLL. Meanwhile, here's the sample code: HTCGServer.zip