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.
Sometimes, life really is
like a box of chocolates. The same can also
be said for software upgrades. Take, for instance, Visual Studio 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
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.
Dan Fergus is the chief architect at Forest Software Group, developing .NET applications, including Pocket PC sports team applications. He speaks at major conferences, does consulting, and teaches Compact Framework, VB.NET, and ASP.NET courses. He coauthored The Definitive Guide to the .NET Compact Framework (Apress). Reach him at firstname.lastname@example.org.