So today finally came that day, we put our first steps into moving to SQL Server Compact Edition 4.0 for our offline database. I was delayed by a day by introducing a new way of managing references to third party components in our build system because I didn't want to replace the Version for the assembly in every project file. How I did that is the content of another blog post though.

First I was confused as to why our data layer kept loading the sql ce 3.5, even though we had no more references to it. Of course I still have it in the GAC to make sure, that we don't by mistake keep getting the wrong version. Well, after some quality time with the debugger, it was way to obvious: The ProviderName for SQL Compact edition contains the major version. Therefore, when you open a connection with the old provider name, it'll load the old assembly from the GAC and provide you with SQLCE3.5 types. Those, when comparing CLR types against types from the 4.0 assemblies, of course will not match.

After I changed the Provider name from System.Data.SqlServerCe.3.5 to System.Data.SqlServerCe.4.0, it prompted me that the database file was created with an older version of SqlServerCe, and that I should call the Upgrade method. In case you were wondering, it throws a SqlCeInvalidDatabaseFormatException with a NativeError of 25138 (SSCE_M_DBUPGRADENEEDED) which I found here.

I went on to implement the upgrade logic. We already have code that checks the error code of the sql exception for unencrypted databases, so I added another branch for the error code that creates a new Engine in Exclusive mode using the new SqlCeConnectionStringBuilder, calls Upgrade and Disposes the engine again. After that, timecockpit started up perfectly fine. I have not done any serious benchmarking yet, but things seem to be working just as fine. As SQLCE4 is supposed to be more stable concerning multi-threading it will allow us to remove the Big DataContext Lock, which I have high hopes for increasing performance.

One of the new features is private deployment. This really hit some of our customers for our first version, as we have quite a few developers who wanted a specific version of sql CE 3.5. Now, with the private deployment possibilities of CE 4.0, we can get rid of one installation component and keep it seperate. An important thing to this is mentioned in ErikJ's blog entry on private deployment: you have to reference System.Data.SqlServerCe.dll version 4.0.0.1 instead of 4.0.0.0 as this indicates a private deployment. Make sure you have the native assemblies in x86 or amd64 folders next to System.Data.SqlServerCe.dll and things should work out.

Since we did not want to clutter our executable projects by adding native assemblies, I wrote a MSBuild file which is imported and keeps the native assemblies out of the project files themselves. Additionally we can update those references in one place for all of our project. Thinking about it, I guess I'll write up some blog posts on how we organise our msbuild files / references.

But all in all, my first SQL Compact Edition 4.0 steps are rather painless!