SQL Server Compact (CE) Database Maintenance Helper

• Download SQLCEHelper.zip - 63.4 KB

Introduction 

SQL server CE database might be a good option for smaller projects which require an embedded database and simple deployment process. Since the SQL CE (version 4) database does not support batch queries it might be difficult or boring work to organize updates propagation with new versions of the application. This article presents a .net class library that encapsulates the SQL CE database maintenance/updates functionality by delivering and executing SQL update commands in a structured way. Commands (SQL DML/DDL) are wrapped in a XML “Update” object. 

The idea of this project originated from a desktop application project which has been deployed to few locations and later it needed new version and the ability to upgrade the db schema on its own. I believe desktop developers have come across the same scenario many times. In fact, I wouldn't see anything wrong in using it in a web projects when using SQL Compact.

Background

In a nutshell, here is how it is working in an application lifecycle:

• During the development database schema modifications (in form of SQL DDL/DML) are added to the XML file which is part of the project (embedded resource) and has simple and well defined structure (presented later). Because we are dealing with SQL CE database every command is separated in in its own tag, like this:

<Command>ALTER TABLE [Employee] ADD COLUMN [Department_Fk] BIGINT null;</Command>

• The library’s UpdateManager .ProcessUpdates method has to be called as a very first action on a database before application normal functionality starts so that it can check/bring up the database to the newest version.
• XML file gets desterilized and commands are executed one by one with ADO .net.
• Database updates log (version info) is persisted into library’s dedicated table that gets created when the library is used for the first time.
• Client application works as normal until its new version arrives which again detects schema updates via UpdateManager .

The update commands are grouped in an object called Update which is represented by SqlCEHelper.Updates.Update class:

[Serializable]
public class Update
{
    public int Number { get; set; }

    [XmlArray("Commands")]
    [XmlArrayItem("Command")]
    public List<String> Commands { get; set; }

    public string Comments { get; set; }
}

Commands are executed one by one in a loop.

foreach (var command in update.Commands)
{
    c.CommandText = command;
    try
    {
        c.ExecuteNonQuery();
    }
    catch (Exception exception)
    {
        var sqlCeHelperUpdateException
            = new SqlCEHelperUpdateException
                ("An error occurred while performing database updates.",
                 exception)
                {ErroredCommand = command};
        sqlCeHelperUpdateException.Data.Add(0, update);

        throw sqlCeHelperUpdateException;
    }
}

An update is considered as successful only if all commands run without exceptions. When the entire update has successfully run a record is added the library’s dedicated table with the update number which means that the database is in higher version. Any exception thrown during executing update commands will stop the update procedure and will throw the SqlCEHelperUpdateException.

Using the code

The library code is not complicated at all and very simple to use. The demo project is wrapped into simple Winforms app and its main form Load event. There is nothing stopping developers to put it anywhere in their architectures as long as the code runs before first request to the database.

To begin using the code, reference the SqlCEHelper.Updates binaries or the project source. Create an instance of the SqlCEHelper.Updates.UpdateManager. Next call Configure method which requires simple Configuration object with the connection string and a string DbUpdatesLogTableName property. In the demo code I use "ExampleSqlHelperWinFormsApplication" value for that property. It is a table name for maintaining updates information. Once chosen the property value must remain unchanged for the life of the application.

// Intialize a new update manager
var updateManager = new UpdateManager();


// Build up the configuration
var configuration = new SqlCEHelper.Updates.Configuration ()
    {
        ConnectionString = Properties.Settings.Default.MyDatabaseConnectionString,
        // This must app app life scope variable:
        DbUpdatesLogTableName = "ExampleSqlHelperWinFormsApplication"
    };

// Configure the updater
updateManager.Configure(configuration);

Optionally, client application can subscribe to some events to inform the user that the db is being updated after they launched new version. Since this is just a Winforms app I append messages to a text box like here:

// Show some progress of updates,
// by listeninig to the updater events
updateManager.PerformingUpdate += (s, a) =>
    {
        textBox1.AppendText(string.Format
            ("Applying database update: {0}",
                a.Update.Comments));
        textBox1.AppendText(Environment.NewLine);
    };
updateManager.PerformedUpdate += (s, a) =>
{
    textBox1.AppendText(" - success.");
    textBox1.AppendText(Environment.NewLine);
};
updateManager.DbIsUptoDate += (s, a) =>
    {
        textBox1.AppendText("The database is up to date!");
    };

Finally call the ProcessUpdates method which requires a XML string with updates. It is recommended to create a solution-included XML file that maintains history of all changes for lifetime of the application. In the demo project I called it DbUpdates.xml and it is set as embedded resource.

<?xml version="1.0" encoding="utf-16"?>
<DatabaseUpdates xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Updates>

    <!--
      Update Number must be uniqe across application lifetime.
        Entire history of updates should remin here,
        so any old version db would get updated accordingly.
      -->

    <Update>
      <Number>1</Number>
      <Commands>
        <Command>ALTER TABLE [Employee] ADD COLUMN [DateBorn] DATETIME null;</Command>
      </Commands>
      <Comments>Added date born</Comments>
    </Update>

    <Update>
      <Number>2</Number>
      <Commands>

        <Command>ALTER TABLE [Employee] ADD COLUMN [Department_Fk] BIGINT null;</Command>

        <Command>
          create table [Department] (
          Id BIGINT IDENTITY NOT NULL,
          NAME NVARCHAR(500) NOT NULL,
          primary key (Id) );
        </Command>

        <Command>
          alter table [Employee]
          add constraint Employee_Department_Fk
          foreign key (Department_Fk)
          references [Department](Id);</Command>
      </Commands>

      <Comments>Implemented Departments and added foregin key constraint</Comments>
    </Update>

  </Updates>
</DatabaseUpdates>

Such file would usually reside somewhere within data access layer or a client application like in the demo. So here is the how to read the embedded resource XML and start the updates procedure:

// Read the updates XML
// (The Updates XML build action must be set as Embedded Resource)
string updatesXml = (new StreamReader(Assembly.GetExecutingAssembly()
    .GetManifestResourceStream
        ("ExampleSqlHelperWinFormsApplication.Resources.DbUpdates.xml"))
        .ReadToEnd().ToString());

try
{
    // Perform updates here
    updateManager.ProcessUpdates(updatesXml);
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message, "Error");
    this.Close();
}

Now the application can continue its execution as normal with the database being now up to date.

The app run for the first time:

The app run for the second time:

Points of Interest

With some changes and refactoring this code could potentially be made more generic and used for other databases. In fact nothing would stop one to get it quickly working against standard SQL server where batch queries are supported. Currently though the librabry is called SQLCEHelper so at least the name would need refactoring.

The code has proven to work updating CE database with several thousands of records in few deployment environments. It obviously works well even though my client application is using nNibernate for its DAL. I realize that it is still far from perfect (events in particular could be improved a lot) but it might be used as starting foundations for further development or used as is.