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.

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.

SQL Server Everywhere Renamed

SQL Server Everywhere has been renamed to SQL Server Compact Edition. I guess there was a lot of confusion about what SQL Server Everywhere was--particularly, disambiguating it with SQL Server Express.

(link)

Size Limit of SQL Server 2005 Mobile Database

Size limit of SQL Server 2005 Mobile database: 4 Gigabytes.
Size limit of SQL Server Everywhere Edition database: 4 Gigabytes.

(link)

SqlCeParameter Bug?

I made the following post to microsoft.public.sqlserver.ce on August 17th.

The following SqlCeCommand throws an exception. Is this a bug or am I doing something wrong?

SqlCeCommand sqlCmd = sqlConn.CreateCommand();
sqlCmd.CommandText = String.Format("INSERT INTO {0} (UserId, VersionId, {1}, PropertyName, PropertyValue, PropertyValueType) VALUES (@userid, @versionid, @headercolumnval, @propertyname, @propertyvalue, @propertyvaluetype)", strTable, headerName);
String strValue = p.Value.ToString();
sqlCmd.Parameters.Add("@userid", SqlDbType.NVarChar, 255, "UserId").Value = Globals.UserId;
sqlCmd.Parameters.Add("@versionid", SqlDbType.BigInt, 8, "VersionId").Value = Globals.VersionId;
sqlCmd.Parameters.Add("@headercolumnval", SqlDbType.NVarChar, 255, headerName).Value = headerColumnValue;
sqlCmd.Parameters.Add("@propertyname", SqlDbType.NVarChar, 255, "PropertyName").Value = p.Key;
sqlCmd.Parameters.Add("@propertyvalue", SqlDbType.NVarChar, 4000, "PropertyValue").Value = strValue;
sqlCmd.Parameters.Add("@propertyvaluetype", SqlDbType.NVarChar, 255, "PropertyValueType").Value = p.Type.FullName;
sqlCmd.Transaction = sqlTransact;
sqlCmd.Prepare();
sqlCmd.ExecuteNonQuery();
sqlCmd.Dispose();

Throws the following exception at the sqlCmd.ExecuteNonQuery() line:

System.InvalidOperationException was unhandled
Message="@propertyvalue : String truncation: max=255, len=463, value="(removed for posting clarity)"."
StackTrace:
at System.Data.SqlServerCe.SqlCeCommand.FillParameterDataBindings()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
at MyExperience.IO.DatabasePopulator.InsertProperty()
at MyExperience.IO.DatabasePopulator.ParseTriggers()
at MyExperience.IO.DatabasePopulator.Populate()
at MyExperience.MyExperienceFramework.Initialize()
at MyExperience.Application.MainForm.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at OpenNETCF.Windows.Forms.Application2.RunMessageLoop()
at OpenNETCF.Windows.Forms.Application2.Run()
at MyExperience.Application.Program.Main()

However, if I change the line:

sqlCmd.Parameters.Add("@propertyvalue", SqlDbType.NVarChar, 4000, "PropertyValue").Value = strValue;

to

sqlCmd.Parameters.Add("@propertyvalue", SqlDbType.NVarChar).Value = strValue;

no exception is thrown and, better yet, the full amount of data actually makes it into the database. That is, the value is not truncated to the 255 length that Sql somehow thinks exists as a constraint.

Note that my schema for this table looks like:

TableName:
TriggerProperties

Columns:
UserId (PK, nvarchar(255), not null)
VersionId (PK, nvarchar(255), not null)
TriggerName (PK, nvarchar(255), not null)
PropertyName (PK, nvarchar(255), not null)
PropertyValue (nvarchar(4000), not null)
PropertyValueType (nvarchar(255), not null)

I followed this post up with this one on August 18th:

Ah, it looks like SqlCeParameter forces the Size property to 255. That's strange given the fact that the docs say, as previously mentioned, that the nvarchar data type can range from 1 to 4000 characters.

However, bear in mind, that by simply avoiding setting the Size property (either via the constructor or the property itself) allows one to INSERT nvarchar data much larger than 255. In other words, it looks like this "bug" is at the parameter level and not database level.

This is the test code:

for (int i = 0; i <= 500; i+=100){ SqlCeParameter param = new SqlCeParameter("@Test", SqlDbType.NVarChar, i); Debug.WriteLine(i + ": " + param.Size);

//Just in case the property acts differently, set it and print again
param.Size = i;
Debug.WriteLine(i + ": " + param.Size);

}

prints:

0: 0
0: 0
100: 100
100: 100
200: 200
200: 200
300: 255
300: 255
400: 255
400: 255
500: 255
500: 255

Any input from MS (or MVPs) on this? I'm far from being a seasoned C# SQL developer so maybe I'm doing something wrong.

Unfortunately I haven't heard any substantive comments back on usenet from MS or otherwise. Anyone in the blogosphere have an idea?

Update 8/25/2006 @ 11:08PM: On August 21st, I e-mailed the SQL Server Everywhere team via their contact page about this issue. Here's what I said:

Not sure how appropriate it is to contact you about problems I'm experiencing with Sql Mobile 2005, but I thought I'd give this a shot. If this is the wrong venue of communication, just ignore this plead for help.

I'm having issues with the SqlCeParameter class, particularly with regards to how it handles setting the Size property. I can't seem to set the size to anything larger than 255 for the nvarchar datatype.

Please see my usenet post for more details (unfortunately no one with any expertise in this area has responded yet): direct link here

I received a response on August 24th:

Yes Jon you are right that there seems to be some problem with larger than 255 chars. I just tried and could repro it.

I shall come back to you on this after some more digging around to see if there is any work around.

To which I responded:

Thank you so much for the response. I was really hoping I'd hear back from someone at Microsoft to help me with this.

The work around that I've come up with is to simply leave out specifying the length parameter in the SqlCeParameter object. So, for my SQL data columns that are of type nvarchar and length > 255 I do this:

SqlCeParameter param = new SqlCeParameter("@custid", SqlDbType.NVarChar);

Rather than explicitly including a length and column:

SqlCeParameter param = new SqlCeParameter("@custid", SqlDbType.NVarChar, 2000, “CustomerID”);

You’ll note that in the former case your data can be of length > 255 and, critically, it will still make it into the database. In the latter case, however, you incur an exception.

Perhaps my workaround will shed some light on the underlying problem.

On August 25th I received another response:

I found the work around you used works perfectly and some of the internal apps also use it the same way as you mentioned. However, while digging I found that you can not use parameterized query when you have image/ntext columns (Large Object Datatype). Will update you more once I get some clarity.

To which I responded:

Hmm, I also use image data types in my SQL Mobile 2005 database and it appears to be working. In fact, a parameterized query, I believe, is the only way to get binary data into the database. What was the issue you ran into here?