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:
The bug mentioned above has been fixed in 4.0 CTP2
Hi!
You can add 0x002dd714 for version 3.1:
http://blog.opennetcf.com/ctacke/PermaLink,guid,d839eeb7-2cb6-4842-a504-93d1bab0b0af.aspx
It is already there (3.0 = 3.1 - same file format)
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}
Rico: And you reference the version 4.0 System.Data.SqlServerCe.dll ??
You should wrap it in a using block:
using(var engine = new SqlCeEngine(conStr))
{
}
Since it's IDisposable.
Shimmy: I am not so sure, it is an extension method...
Thank you Erik... this was a phenomenal life-saver. You certainly the man when it comes to SQLCE
You are the man. Helped me a lot with this code.
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
Sathya: Please ask in the MSDN forum, and maybe start learning / reading some as well.
Could you please suggest some good material for VB + SQL Development
Thanks,
Could you please suggest some links to refer VB + SQL Development
MSDN - Bing - Google
how to get filename from connection string? because as our file is store in C:\\Users\\\\AppData\\Roaming\\.
Rajesh: You should be able to use the Database property of the SqlCeConnection object
Thanks , that solved my problem. =)
Thank you for extremely useful code snippet!
I've got 0x00357dd9 for completely correct SqlCE database file (with LCID set).
Matafonoff: Would be extremely interested in having a look at that database file, could you email or share somehow?
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.
I also used 0x00357dd9 with success for version 3.5
Ricardo: Are you able to share your 3.5 database file where you used that number?
Post a Comment