Saturday, August 7, 2010

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);

22 comments:

ErikEJ said...

The bug mentioned above has been fixed in 4.0 CTP2

xcluster said...

Hi!

You can add 0x002dd714 for version 3.1:

http://blog.opennetcf.com/ctacke/PermaLink,guid,d839eeb7-2cb6-4842-a504-93d1bab0b0af.aspx

ErikEJ said...

It is already there (3.0 = 3.1 - same file format)

Rico said...

Hi, I run this code and for some reason it fails with an error, the database is version 3.5 but won't upgrade. Here is the error, thanks!! $exception {"The file that is being referenced is not a valid SQL Server 2005 Compact Edition (version 3.1) or SQL Server 2005 Mobile Edition (version 3.0) database file to perform upgrade. [ Db name = C:\\Users\\rickt\\Documents\\Expression\\Blend 4\\Projects\\MuseSoftProjectTemplate\\MuseSoftProject\\MuseSoftProject\\MuseSoftProject35.sdf ]"} System.Exception {System.Data.SqlServerCe.SqlCeException}

ErikEJ said...

Rico: And you reference the version 4.0 System.Data.SqlServerCe.dll ??

Shimmy said...

You should wrap it in a using block:

using(var engine = new SqlCeEngine(conStr))
{

}

Since it's IDisposable.

ErikEJ said...

Shimmy: I am not so sure, it is an extension method...

Unknown said...

Thank you Erik... this was a phenomenal life-saver. You certainly the man when it comes to SQLCE

Bestors said...

You are the man. Helped me a lot with this code.

Sathya said...

Hey Hi,
i am new to this technology. i installed Visula studio 2012 and just running previous existing project. when start executing the project, seeing error message saying database is compatible with sql 4.0 version, please upgrade your assets.
i dont know where to add this code and how to run
Please help me to do that .

Thanks in Advance

ErikEJ said...

Sathya: Please ask in the MSDN forum, and maybe start learning / reading some as well.

Sathya said...

Could you please suggest some good material for VB + SQL Development

Sathya said...

Thanks,

Could you please suggest some links to refer VB + SQL Development

ErikEJ said...

MSDN - Bing - Google

Unknown said...

how to get filename from connection string? because as our file is store in C:\\Users\\\\AppData\\Roaming\\.

ErikEJ said...

Rajesh: You should be able to use the Database property of the SqlCeConnection object

Pedro said...

Thanks , that solved my problem. =)

Matafonoff said...

Thank you for extremely useful code snippet!

I've got 0x00357dd9 for completely correct SqlCE database file (with LCID set).

ErikEJ said...

Matafonoff: Would be extremely interested in having a look at that database file, could you email or share somehow?

Unknown said...

Hello Erik, thanks for this code ! My previous DB was version 3.5 and by adding 0x00357dd9 it worked. I also edited the connection string to include my DB password.

Unknown said...

I also used 0x00357dd9 with success for version 3.5

ErikEJ said...

Ricardo: Are you able to share your 3.5 database file where you used that number?