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.