Sunday, October 17, 2010

HOW TO: Detect SQL Server Compact version information

There are various aspects to getting the version information for SQL Server Compact. This blog post is an attempt to cover them all, let me know if something is missing.

Assembly Version

The System.Data.SqlServerCe.dll ADO.NET Provider version. (AssemblyVersion)

This is not part of the file information, but .NET specific, part of the assembly’s identity.

var version = typeof(System.Data.SqlServerCe.SqlCeConnection).Assembly.GetName().Version;

This will give you the assembly version of the System.Data.SqlServerCe.dll, not the build number.

For 3.1, this is 9.0.242.0 for the desktop and 3.0.3600.0 for devices (not exactly what you would expect)

For 3.5 RTM, this is 3.5.0.0

For 3.5 SP1 and SP2 this is 3.5.1.0

“ServerVersion”

The ServerVersion property of the SqlCeConnection object.

var ver = new System.Data.SqlServerCe.SqlCeConnection().ServerVersion;

For 3.5/4.0 this will give you the build version of the database engine, including any patches. For 3.5 SP2 this is "3.5.8080.0"

(Sadly for 3.1, this returns “9.0.242.0”)

File version of the managed DLLs

Getting the file system version of the managed ADO.NET provider will give you more precise version information (in particular for 3.0/3.1), and will not require guessing the location of any DLL files (as below for getting the version for unmanaged files).

string fullPath = System.Reflection.Assembly.GetAssembly(typeof(System.Data.SqlServerCe.SqlCeConnection)).Location;
var asmInfo = System.Diagnostics.FileVersionInfo.GetVersionInfo(fullPath);
string asmFileVersion = asmInfo.FileVersion;

For 3.1, this returns 3.0.5300.0 (for patches the final zero will be from 1 to 14)

File version of the unmanaged DLLs

As you may know, the SQL Server Compact engine consists both of managed DLL files (the System.Data.SqlServerCe.dll that has been referenced in all code above), and a number of C++ unmanaged DLL files.

Getting information about these files is more challenging, as we need to locate the unmanaged DLL files. This can be done like so for 3.5 and 4.0 (if installed by an administrator on the machine):

Microsoft.Win32.RegistryKey registryKey = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server Compact Edition\v3.5", false);
string pathName = (string)registryKey.GetValue("InstallDir");
var info = System.Diagnostics.FileVersionInfo.GetVersionInfo(System.IO.Path.Combine(pathName, "sqlceme35.dll"));
string fileVersion = info.FileVersion;

File format/version of database file

The sample in this blog entry gives you the file version, there are only difference in this per major version (2.0, 3.1/3.0, 3.5 and 4.0)

http://erikej.blogspot.com/2010/08/how-to-upgrade-version-3x-database-file.html

Product version information

Information from the product team about product release versions is available here: SQL Server Compact Release Versions and here: Description of the various build versions of SQL Server Compact Edition

In addition, I have lists of available hotfixes on this blog for version 3.5 SP1 and 3.5 SP2

2 comments:

Nweb said...

Excellent piece of information

Regards
nwebsolution
W:www.nwebsolution.com

BERTHOLD EDMUND said...

nice ! i'm working with webmatrix beta2 and i wished they stuck with the 3.5 version , there are so much tools or apis not compatible with the 4.0 version.

http://camus.web01.appliedi-labs.net/