HOW TO: Upgrade a version 3.x database file to SQL Server Compact 4.0

See this excellent sample: http://blogs.msdn.com/b/jimmytr/archive/2010/04/26/upgrade-sql-ce-database-from-3-1-to-3-5.aspx

I have updated the sample extension method to work with 4.0. Notice that the Upgrade method allows you to upgrade both from 3.1 and 3.5 file formats.

Due to a bug in the 4.0 CTP1, it is currently not possible to do in-place database upgrades (as the code below does), see this thread: http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/872e49da-1d3e-457a-9288-c1e1f0c90dc8

public static class SqlCeUpgrade
{
    public static void EnsureVersion40(this System.Data.SqlServerCe.SqlCeEngine engine, string filename)
    {
        SQLCEVersion fileversion = DetermineVersion(filename);
        if (fileversion == SQLCEVersion.SQLCE20)
            throw new ApplicationException("Unable to upgrade from 2.0 to 4.0");

        if (SQLCEVersion.SQLCE40 > fileversion)
        {
            engine.Upgrade();
        }
    }
    private enum SQLCEVersion
    {
        SQLCE20 = 0,
        SQLCE30 = 1,
        SQLCE35 = 2,
        SQLCE40 = 3
    }
    private static SQLCEVersion DetermineVersion(string filename)
    {
        var versionDictionary = new Dictionary<int, SQLCEVersion> 
        { 
            { 0x73616261, SQLCEVersion.SQLCE20 }, 
            { 0x002dd714, SQLCEVersion.SQLCE30},
            { 0x00357b9d, SQLCEVersion.SQLCE35},
            { 0x003d0900, SQLCEVersion.SQLCE40}
        };
        int versionLONGWORD = 0;
        try
        {
            using (var fs = new FileStream(filename, FileMode.Open))
            {
                fs.Seek(16, SeekOrigin.Begin);
                using (BinaryReader reader = new BinaryReader(fs))
                {
                    versionLONGWORD = reader.ReadInt32();
                }
            }
        }
        catch
        {
            throw;
        }
        if (versionDictionary.ContainsKey(versionLONGWORD))
        {
            return versionDictionary[versionLONGWORD];
        }
        else
        {
            throw new ApplicationException("Unable to determine database file version");
        }
    }


}

Sample usage:

string filename = @"C:\Data\SQLCE\Northwind31.sdf";
var engine = new System.Data.SqlServerCe.SqlCeEngine("Data Source=" + filename);
engine.EnsureVersion40(filename);