Going Mobile

Upgrading to Visual Studio 2008/SQL Server Compact Edition 3.5

If you're using SQL CE 3.1 or earlier, watch out for this glitch.

• By Dan Fergus
• 02/26/2008

After all its various CTPs and RTMs, I feel like I've been using VS 2008 for what seems like forever (in reality, it's closer to a year). And, for the most part, it has performed exceedingly well. There are great new tools in VS 2008 that help with many aspects of application development. And on the device side, VS 2008 provides us with better and more convenient access to tools targeted specifically at device development.

I've written several device applications using VS 2008 that have been very successful. But none of the successful applications involved a SQL Server Compact Edition (SQL CE) database. As my latest upgrade has shown me -- though it was ultimately successful and beneficial -- things don't always go as expected.

I've spent the last several articles telling you how great SQL CE is and I'm not changing that story in the least. I do, however, want to let you know about a few difficulties you may encounter when you upgrade to VS 2008 and Compact 3.5

When you load a VS 2005 project into VS 2008, all references to Compact 3.1 are upgraded to Compact 3.5, and any Compact 3.1 databases are upgraded to 3.5. (If your database was/is encrypted, then the database will not be upgraded, which makes sense because VS doesn't know your password. Also, SQL CE 2.0 databases must first be upgraded to 3.0/1 before they can be upgraded to 3.5.) The upgrade results do mention this, but who looks at that, anyway? I know I didn't the first few times I made an upgrade.

This is all best-case stuff, but what if, somehow, you get a 3.1 database on the device and the 3.5 code tries to access it? I'm not making this up -- it can happen. I keep several versions of a database with different amounts of data in them to use at different stages of development. Or perhaps you have a huge database that you back up routinely by copying the entire .SDF file to a safe site. These files aren't part of the solution and won't be upgraded automatically. If your VS 2008/Compact 3.5 application then attempts to open a 3.1 database, you get the following exception:

"The database file has been created by an earlier version of SQL Server Compact. Please upgrade using SqlCeEngine.Upgrade() method."

No problem -- with a little bit of code, as shown in Listing 1, I can solve that issue. (All of you C++/C# purists who abhor goto statements, you might want to look the other way.)

Honestly, this isn't a big deal, right? And there are ways you can write the code in your application to avoid the goto statement. Now you have an application that runs, reads the new 3.5 database, detects an older database, performs an upgrade to the newer version and continues. Life is good.

However, at some time in the development lifecycle, you'll undoubtedly want to browse the .SDF database file, on the device or on the development box, using SQL Server Management Studio 2005 because that's what you've always done. This presents a new problem: You can't!

You see, SQL Management Studio 2005 won't open a Compact 3.5 database file. The file formats aren't compatible between 3.0/1 and 3.5. In 3.5, new change tracking features for Sync Services and support for the TimeStamp/Rowversion data type required changes to the file format. The Compact team decided not to wait for the release of SQL Server 2008 in order to get new bits to us. So, between the off-release bits and the file changes, we have to put up with a few annoyances to get the better speed and functionality with Compact 3.5. I'll make that trade any day.

At this point, you have two options to be able to manage the new 3.5 database: Use the VS 2008 Server Explorer or get a CTP version of SQL Server 2008 (I'm using the November 2007 CTP; you may find a more recent one that works differently by the time you read this). The only difficulty with the upgrade to a CTP of SQL Server 2008 is that many companies -- usually the bigger ones -- don't like to have pre-release product versions on their systems. If this is your situation, all you need to know is that the Server Explorer in Visual Studio works just fine.

And if you install SQL Server 2008 CTP, you should know that you can't read SSCE 3.1 files using the 2008 Management Studio. The point to remember is this: VS/SQL2008 = Compact 3.5, and VS/SQL 2005 = Compact 3.1, period. This whole versioning thing can, without a doubt, lead to confusion and frustration. But keep in mind that this is a problem only if you're going "halfway." Once you make the move completely to SQL Server 2008, VS 2008 and SQL Server Compact 3.5, there really isn't a problem at all.

With that all of that aside, there is one bright spot. In operation, on the device, none of this makes a difference. That's because you can have Compact 3.1 and 3.5 running on the same device. If your application was installed with Compact 3.1, it'll work even if a second application is installed that uses Compact 3.5. However, based on my experience, your application can't access both the Compact 3.1 and Compact 3.5 database. I'm going to play with this more to see if I can get it to work.

So, to summarize, here's what we've learned: Upgrading to VS 2008 has many benefits, but keep in mind that an upgrade when using SQL CE requires a commitment to move everything to VS 2008 -- and that's really not a bad thing.

But what happens if you can't move all of your SQL Server Compact databases to 3.5 but want, or need, to move to VS 2008? The good news is that you can make it work. Remember, the problem here is really the support of the tools in Visual Studio, not in the runtime operation of SQL Server Compact. If you install VS 2008 on a machine that already has VS 2005, all of the runtime scenarios should work fine. If you only install VS 2008, then you'll also need to install the SQL Server Compact 3.1 runtime to be able to use 3.1 databases from VS 2008.

But remember: This is only runtime. For Compact tooling, VS/SQL 2005 = Compact 3.1, VS/SQL2008 = Compact 3.5. To get the right runtime, the trick is to make sure that you have the correct references in your project.