The Importance of SqlCeCommand.Parameters

One popular post on this blog discussed the reasoning behind the SqlCeCommand.Parameters functionality in the .NET SQL libraries. Since the original post back almost two years ago (August 14th, 2006), I have used other SQL libraries in other languages including Java and Python. They also provide parameterized queries in addition to the traditional text based queries. Thus, this design is quite common.

Although you might be tempted to write your queries using string.Format and SqlCeCommand.CommandText, it is much better to let the SQL library interpret your parameters automatically. Ilya Tumanov, a member of the .NET Compact Framework Team, commented on my original post with a list of advantages that are worth repeating:

Yes, parameterized query is a preferred way. Benefits are:

• No need to create bunch of string objects all the time (in your code).
• No need to convert parameters from binaries to strings (in your code).
• No need to parse strings to get binaries back (in SQL CE).
• No locale/format specific issues as binary is format less and locale independent.
• No need to redo each command from scratch, prepared execution plan can be used with just different parameters.
• Works for all data types including binary/image.

Performance benefits from using parameters range from significant to really huge.

The reasoning behind this post is to emphasize the red bullet point above. Recently, I have been receiving emails from users of the MyExperience tool who have tried to run the program on devices that are using non-English language versions of Windows Mobile. For the most part, MyExperience uses parameterized queries. I painstakingly went over every query a few years ago and made sure of that. Unfortunately, I missed a spot and this was the cause of the incompatibility.

The offending query looked something like this:

sqlCmd.CommandText = string.Format("SELECT VersionId FROM Versions WHERE VersionName='{0}' AND VersionNumber='{1}' AND VersionModifiedDate='{2}'", vName, vNumber, vDate);

A keen reader may note that this query is region specific because it includes a DateTime object. The String.Format method was formatting the date based on the regional configuration of the device. This seemed to be incompatible with the date format that SQL Server Mobile was expecting. To fix this, I updated this code to read:

sqlCmd.CommandText = "SELECT VersionId FROM Versions WHERE VersionName=@VName AND VersionNumber=@VNumber AND VersionModifiedDate=@VModDate";
sqlCmd.Parameters.Add("@VName", mev.VersionName);
sqlCmd.Parameters.Add("@VNumber", mev.VersionNumber);
sqlCmd.Parameters.Add("@VModDate", mev.ModificationDate);

Thus, I kept the data in binary format which is, as Ilya pointed out, region/locale independent.

Operation Could Not Be Completed. Device Is Not Connected.

When attempting to debug one of my Windows Mobile applications this evening, I ran into this error:

The operation could not be completed. The device is not connected.

The error occurred after I right clicked on a project in my solution and selected Debug->Start new instance. After a bit of Googling, I determined that the root cause of this message (in my case) was that my project was referring to content that was not actually on my local disk. Earlier in the day I had moved some XML files outside of the project directory, which my project still erroneously referred to. Once I deleted links to these files in my project, I was able to successfully debug my application.

There is an msdn blog post about this here. Apparently, you may receive this error message for reasons beyond what I experienced above.

SqlCeEngine.Upgrade() causes SqlCeException "The operation completed successfully."

I am building a simple desktop-based command-line analysis tool for a set of .SDF files that were collected on various mobile devices for a research project. Unfortunately, I am having issues connecting to these .SDF files from my tool (each of which are stored locally on my desktop hard drive). The problem seems to be that the version of SQL Compact Edition used on the mobile devices is different from the one I have installed on my desktop.

First, when connecting to one of the .SDF files from my desktop analysis tool, I receive the exception:

System.Data.SqlServerCe.SqlCeInvalidDatabaseFormatException was unhandled
Message="The database file has been created by an earlier version of SQL Server Compact. Please upgrade using SqlCeEngine.Upgrade() method."
Source="SQL Server Compact ADO.NET Data Provider"

I now catch this exception and call the recommended SqlCeEngine.Upgrade() method, so my code looks like this:

SqlCeConnection sqlConnection = GetConnection(pathToDatabase);

try
{
sqlConnection.Open();
}
catch (SqlCeInvalidDatabaseFormatException e)
{
Console.WriteLine("The database " + pathToDatabase + " appears to be an old version, running Upgrade()");
Stopwatch sw = Stopwatch.StartNew();
SqlCeEngine sqlCeEngine = new SqlCeEngine(GetConnectionString(pathToDatabase));
string newPathToDatabase = GetUpgradedDatabasePath(pathToDatabase);
string newConnectionString = GetUpgradedConnectionString(pathToDatabase, newPathToDatabase);
sqlCeEngine.Upgrade(newConnectionString);
sw.Stop();
Console.WriteLine("Upgraded the database in " + sw.ElapsedMilliseconds / 1000.0f + " secs");
sqlConnection = GetConnection(newPathToDatabase);
sqlConnection.Open();
}

The SqlCeEngine.Upgrade() call takes about one minute, afterwards it throws a very confusing exception:

System.Data.SqlServerCe.SqlCeException was unhandled
Message="The operation completed successfully."
Source="SQL Server Compact ADO.NET Data Provider"

Figure 1: One of the most confusing exception messages I've ever received.

At first I thought I should just catch this exception and carry on normally (as the exception says that the operation did indeed complete successfully); however, after some experimentation I noticed that the new, upgraded .SDF file built by the Upgrade() command is automatically deleted right before this exception is thrown so there is no upgraded .SDF file to connect to. Thus, it appears that the SqlCeException is valid but that the message is just wrong. I have attempted to use both the SqlCeEngine.Upgrade() call (which upgrades the database in place) and the SqlCeEngine.Upgrade(newConnectionString) call (which makes a new database). Both fail with the same exception.

Figure 2: Note how the upgraded database appears in the filesystem momentarily before being automatically deleted just before the Upgrade() method throws an exception.

Any help here would be most appreciated. Has anyone else encountered this error? Note, I also posted this to the SQL CE newsgroup here.

Update July 6th, 8:04PM: One workaround for this problem is to use the old v3.1 libraries in your desktop program. Thus, rather than use the .NET tab in the "Add Reference" dialog in Visual Studio, you would select "Browse" and then browse to the 3.1 libraries. For me, this is "C:\Program Files\Microsoft SQL Server Compact Edition\v3.1" Thankfully, Microsoft was smart enough to allow both sets of dlls on a machine (e.g., their 3.5 installation did not overwrite the 3.1 installation) as I also have a folder entitled "C:\Program Files\Microsoft SQL Server Compact Edition\v3.5" with the v3.5 libraries.

Storage Card Path Identification

I responded to a thread on Google Groups about the "correct" way to identify the Storage Card path on Windows Mobile devices back in February 2007 (link). According to the Windows Mobile Team Blog, you should use FindFirstFlashCard (link). I've used it successfully on a variety of HTC WM5 SmartPhone devices, however, this issue has recently cropped up as a (potential) issue for one of our MyExperience tool users. The crux of the problem is that you cannot assume \Storage Card\ will be the default path for the storage or flash card on your Windows Mobile device, which is why FindFirstFlashCard is necessary. For example, on the Fujitsu LOOX PocketPC, the storage card path is \SD-MMCard\. The problem seems to be that either some device manufactuers don't properly support the FindFirstFlashCard method or that certain libraries on the device do not use FindFirstFlashCard and improperly assume the storage card is at \Storage Card\. More investigation is needed.

The code I use in MyExperience (actually, the Roam library which MyExperience relies on):

public static String GetFirstFlashCardPath()
{
Win32FindData win32FindData;
IntPtr intPtr = FindFirstFlashCard(out win32FindData);
String path = win32FindData.cFileName;
FindClose(intPtr);

if (String.IsNullOrEmpty(path))
{
throw new ResourceNotFoundException("
Could not find a flash card on this device");
}

return path;
}

The full code is available here.

Preview of Windows Mobile 6.1 Standard

There is a nice rundown of the new features in Windows Mobile 6.1 Standard (i.e., the WM flavor with no touch screen) on PocketNow, which includes screenshots and a video. The interface aesthetic is definitely improving but it's still no iPhone. Pocket IE still seems rather sluggish (though its Deepfish like zoom capability is long overdue). Perhaps WM7 will begin to treat the Internet as a fundamental part of the mobile experience (like the iPhone does) rather than an add-on or a way to access mail.

Installing Windows Mobile Device Center on Windows Server 2008

I struggled for about an hour installing Windows Mobile Device Center on Windows Server 2008. Here's so you don't have to:

1. My first real lead was that Windows Mobile Device Center requires Windows Media Player to be installed. Yes, requires. How did I know that? Well, it certainly wasn't from this incredibly vague error message when running the WMDC installer: "The update could not be installed because at least one Windows component required by Windows Mobile Device Center is missing." Fortunately, I found a few links on the web talking about this, mainly this one.
2. So, the key is adding Windows Media Player to Windows Server 2008; however, the download page for Windows Media Player doesn't include releases for Windows Vista much less Windows Server 2008. The key is to turn on the "Desktop Experience" of your Windows Server installation. Instructions on how to do that are here.
3. After reboot, you should be able to successfully install WMDC.

Fixing a Gray ActiveSync

As a Windows Mobile developer, I typically work with multiple WM devices that I continuously plug in and out of our USB ports as I'm testing software. Sometimes, this confuses ActiveSync. For example, say my WM device is plugged into my desktop, ActiveSync is green. All is good. I unplug the phone to test something (e.g., GPS sensor) and walk around. Five minutes later, I stop the GPS software and plug my phone back into my desktop. However, now ActiveSync stays gray. Rather than restarting, which is what I usually do in this situation (though I hate restarting cause I usually have a 100 windows open), I do the following:

1. Open ActiveSync and Task Manager.
   
2. In Task Manager, click on the Applications tab. Select "Microsoft ActiveSync." Select "End Task."
3. Now click on the Processes tab. Select the "wcescomm.exe" and then press the "End Process" button. Do the same thing for "WCESMgr.exe" if it's there.
4. Unplug your device from the computer
   
5. Go to Start Menu->All Programs and relaunch ActiveSync.
   
6. Wait until ActiveSync starts and then plug your phone back in.