Running SQL Compact from CD-ROM (read only media) on all Windows platforms

In order to create a read only application to be run from CD-ROM (or similar) in managed code, there are some issues that must be considered in order to support all current Microsoft Windows platforms (XP (if you can call that “current”) and Vista/Win7), and both x86 and AMD64 (x64) processor architectures.

The user will need to have .NET Framework 2.0 installed, it will already be installed if the OS is Vista or later.

For the SQL Compact runtime engine, simply include the SQL Compact managed and unmanaged DLL files in the application folder. Make sure to set the platform of your .exe to x86, or include both the x86 and AMD64 runtimes. Doing either of these 2 options will allow the SQL Compact engine to run on all Windows x86 and x64 platforms (XP/2003 and later).

For the procedure for including the relevant runtime files in your project, see this blog post: http://blogs.msdn.com/stevelasker/archive/2008/10/22/privately-deploying-sql-server-compact-with-the-ado-net-entity-provider.aspx and this sample from Steve Lasker: Running Compact from Read Only Media (DVD, CD, Locked USB Key).

As reported here by the SQL Compact team (sadly, not before April 1st (!) 2009), the runtime needs to re-create indexes if a SDF file has been moved from Vista/Server 2008 to XP/Server 2003. On read-only media, this cannot happen, and you will get an obscure “Permission denied” error message.

A possible workaround for this would be to open the file on both platforms (using Virtual PC or similar, if need be), and distribute 2 SDF files on the read only media.

In order to open a SDF file form read-only media, you also need to add two additional parameters to the connection string in use: “Mode=Read Only” and “Temp Path=<path>”

The sample code below illustrates how you could implement the 2 paragraphs above when creating your connection string. In addition, the path to the SDF file is dynamically created.

 

using System;
namespace ReadOnly
{
class Program
{
static void Main(string[] args)
{
    string connStr;
    string sdfName;
    // Choose file based on OS version
    if (System.Environment.OSVersion.Version.Major < 6)
    {
        // This SDF was created on XP or Win2003
        sdfName = "Northwind5.sdf";
    }
    else
    {
        // This SDF was created on Vista or Win7
        sdfName = "Northwind.sdf";
    }

    // Notice the "Mode = Read Only" and "Temp Path=%TEMP%" options added to the connection string
    connStr = String.Format(@"Data Source = {0}\{1};Mode = Read Only;Temp Path={2}", 
    System.IO.Path.GetDirectoryName(System.Reflection. Assembly.GetExecutingAssembly().GetName().CodeBase),
    sdfName, 
    System.IO.Path.GetTempPath());                
}
}
}