SQL Compact 3.5 Tidbits and Gotchas!

Introduction

Rather than the normal kind of code demo article, I just wanted to put together a few notes about how to go about using Microsoft SQL Compact 3.5. During a recent ongoing project, I've had a few problems that took a while to figure out, mainly because of documentation either not being clear or just not being there at all! This is such a neat package for desktop applications that I'm sure it's going to become more and more popular with developers, and not just for mobile applications. Obviously, a lot of developers will want to opt for using SQL Express which is also a great tool, but comes with a few caveats. It is potentially a lot more complex to install and distribute an application using SQL Express than Compact 3.5 for starters. One of the main requirements for my situation was making the application installation process as easy as possible for users and to have a fully functioning database solution running 'in process'.

During planning on my current application, I came across a very good paper that goes into detail comparing SQL Compact 3.5 and SQL Express, which can be found here. This really helped during the initial decision making as to which direction to go in. There are some really fundamental SQL Server things missing in SQL Compact 3.5 such as Stored Procedures, which for some will be a real show stopper.

Designing Databases

Obviously, the first thing you are going to want to do is actually get your hands dirty and design your SQL Server Compact database ... now, where is the provider???? This stumped me for a while since I could make .sdf files with Visual Studio 2008 but they didn't appear to be 3.5 version databases! Eh? Well, you need to install the design tools! By default, the Visual Studio installation process installs SQL Server Compact 3.5, but it doesn't install the design time tools allowing you to create 3.5 version databases. You can get the tools from the installation disc; the file is called SSCEVSTools-enu.msi, this file is also included with the Express versions of Visual Studio.

SQL Compact and LINQ (SQLMetal)

As you might expect, LINQ is supported; that is a huge topic to cover here, and very much out of scope really, besides Matt Sollars has already done a cracking job on CodeProject, which you can read here.

Unfortunately, SQL Compact isn't compatible with the Visual Studio built in Visual O/R Mapping feature, so to generate the required .dbml file to power LINQ, SQLMetal.exe is the only option. What I have done in my project in terms of LINQ is actually copy SQLMetal.exe into my solution directory and add it to the source control. Then, I created a simple batch file that can be run if/when the shape of the database changes. This way, I can be assured that the newly generated code classes will be compatible with the solution, and should you be in a multi-developer situation, everyone is generating the .dbml file in the same way. My particular batch file looks like this:

SqlMetal /database:DB /Pluralize /provider:SQLCompact /code:"DB.cs" /language:csharp 
      "DataBase\DB.sdf" /Password:dbpass1! /entitybase:AppName.DataLayer.ITable

SqlMetal /database:DB /Pluralize /provider:SQLCompact /dbml:DB.dbml "DataBase\DB.sdf" 
      /namespace:AppName.DataLayer /Password:dbpass1!

SqlMetal /database:DB /provider:SQLCompact /code:"DB.cs" /map:DB.xml "DataBase\DB.sdf" 
     /Password:dbpass1!

sqlmetal /?

PAUSE

One thing to note here is that you don't actually need to create the code file (the .cs command in the fist line of the batch file) unless you want an XML map file. Visual Studio will auto generate your classes when you include the .dbml file in the solution. This makes handling the changes in database shape a bit of a faff frankly. Say, you add some fields or tables to a Compact database; in order to update the classes in your solution, you'll have to remove the current .dbml, recreate it using your batch file, and then include the new .dbml file which will make Visual Studio pop off and do its thing and create the classes (in C# or VB). If you are also building an XML map file (and the required code file in your batch file), remember to delete the .cs file that SQLMetal builds, or you'll have duplicated partial classes included in your solution code which will cause build errors.

Connection Strings / CreateDatabase()

This one caused me a good few days of confusion. Anyone developing software will know of the horrors of anything being hard coded, especially database connection strings. In my application, I was using the standard way to tell an application where the database lives using the |DataDirectory| macro within the properties of the solution:

Data Source=|DataDirectory|\DataBase\DB.sdf;Password=dbpass1!;Persist Security Info=True 

All works great until you start to include classes aimed at bullet proofing your application once it is released into the wilds. Obviously, a disconnected database application aimed for the desktop needs to have resilience built into it. For starters, you don't want to be shipping empty database files with the application; it should be smart enough to create one on the fly at start up (initial run after installation), or use the existing one on subsequent application runs. If you are using LINQ, there is a fantastic method called ... you guessed it ...

CreateDatabase():

Grand! I thought to myself. Well, yes and no. It works! However, you cannot use the |DataDirectory| macro in the connection string when using this method. There is a bug in SQL Compact 3.5 that renders this useless. You have to provide a fully qualified connection string before this method will work. It's no real pain, but this bug doesn't appear to be documented very well, and it really is a show-stopper for an otherwise very useful method when used in the real world. To get around this, you just need to use something like:

AppLocation = AppDomain.CurrentDomain.BaseDirectory;
DatabaseLocation = Path.Combine(AppLocation, "Database");
PathToDatabase = Path.Combine(DatabaseLocation, "DB.sdf");

I have these defined in a separate class where I store all these application wide bits and bobs so they are easily accessible to anything in the application. So, before issuing a DatabaseExists() or CreateDatabase() method call, you need to build the qualified connection string on your main context class, like:

static DB _context;
_context = new DB("Data Source=" + ApplicationUtilities.DatabaseLocation + 
                  "\\DB.sdf;Password=dbpass1!;Persist Security Info=True");
if (!_context.DatabaseExists())
{
    try
    {
        _context.CreateDatabase();
        DatabaseCreated = true;
        log.Info("Database created successfully");
    }
    catch (Exception ex)
    {
        throw new DBDatabaseException("Failed to Create the Database!", ex);
    }
}
else
{
    DatabaseCreated = false;
}

Private Installation

One major boon of using SQL Compact 3.5 over SQL Express is that it is so very tiny!! 1.8MB is very small for a database solution however you look at it. Compared to the installation for SQL Express, it's orders of magnitude smaller. The other major (IMHO) gain is the ease of installation for the end user (and ease of developing the installer!!) as it's simply an XCopy process to get the DLLs out of your MSI or Setup.exe file into your installation file, and off we all merrily go. I followed the Microsoft instructions for performing a private installation of SQL Compact 3.5 to the letter, it did not work; I went over them countless times after that with no luck. I eventually found the solution to this ... you need to add the following to your app.config file:

<system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.3.5" />
      <add name="Microsoft SQL Server Compact Data Provider" 
         invariant="System.Data.SqlServerCe.3.5" 
         description=".NET Framework Data Provider for Microsoft SQL Server Compact" 
            type="System.Data.SqlServerCe.SqlCeProviderFactory,
                  System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, 
                  PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
</system.data

I'm yet to find that information on the Microsoft website, it certainly is not part of their instructions on how to do a private installation of SQL Compact 3.5.

That's all Folks

For now, that is pretty much everything that I wanted to cover. I hope this helps someone else get to using SQL Compact 3.5, I'll update this as and when I find anything that should be here basically.

History

• 1 August 2008 - Initial version.