Using Lex.DB as a local storage in a Windows 8 Store app

10 December 2013 Diederik-Krols WinRT

Lex.DB is a lightweight, in-process database engine, completely written in C#. It can be used on .NET 4+, Silverlight 5+, Windows Phone 8+, WinRT, and Android (through Xamarin). That makes it a direct competitor against SQLite. The latter seems to become the de facto standard for storing structured data in Store Apps. Now while SQLite is technology neutral, unfortunately it’s not processor neutral. In a Windows 8 Store app, the dependency on SQLite requires you to create x64, x86, and ARM specific packages. As a side effect, this screws up the Visual Studio XAML Designer on your 64-bit development machine. Embedding processor-specific components has a negative impact on development, debugging, and deployment. That’s a high price to pay. So I went looking for a developer-friendly storage engine that doesn’t have these issues, and that’s how I came across Lex.DB.

I created a Windows 8.1 MVVM Store App that maintains a local cache of representative business objects. Here’s how the app looks like:

You may recognize this app from my previous blog post on SQLite. It’s the exact same app indeed; the migration from SQLite to Lex.DB took me less than 10 minutes. And I love the result.

The easiest way to install Lex.DB is through NuGet:

You’ll end up with just a reference to a .dll, the opportunity to create a processor-neutral app package, and an operational Visual studio XAML designer:

Unlike SQLite, the business object does not have to be decorated with attributes. Here’s the Person class of which I’ll store some instances:

internal class Person
{
    /// <summary>
    /// Gets or sets the identifier.
    /// </summary>
    public int Id { get; set; }

    /// <summary>
    /// Gets or sets the name.
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// Gets or sets the description.
    /// </summary>
    public string Description { get; set; }

    /// <summary>
    /// Gets or sets the status.
    /// </summary>
    /// <remarks>Is an enum in the viewmodel.</remarks>
    public int Status { get; set; }

    /// <summary>
    /// Gets or sets the picture.
    /// </summary>
    /// <remarks>Is a blob in the database.</remarks>
    public byte[] Picture { get; set; }
}

Although it's persisted, you need to initialize the database once, every time your app runs. It has to have a name, as well as the list of mappings to the model classes that you want to store in it. You put code like this e.g. in the OnNavigatedTo event of the main page of your app, or -like I did- in the static constructor of your data access layer class:

private static DbInstance db;

static Dal()
{
    // Create database
    db = new DbInstance("storage");

    // Define table mapping
    // * 1st parameter is primary key
    // * 2nd parameter is autoGen e.g. auto-increment
    db.Map<Person>().Automap(p => p.Id, true); 
            
    // Initialize database
    db.Initialize();
}

AutoMap creates a table field for each class property. Its first parameter indicates the primary key, the second parameter is optional, it determines whether or not the primary key value will be automatically generated.
After initialization, the DB is ready to use. This is the list of calls in the data access layer of the sample app:

public static List<Person> GetAllPersons()
{
    return db.Table<Person>().LoadAll().ToList();
}

public static Person GetPersonById(int id)
{
    return db.Table<Person>().LoadByKey(id);
}

public static void SavePerson(Person person)
{
    db.Table<Person>().Save(person);
}

public static void DeletePerson(Person person)
{
    db.Table<Person>().Delete(person);
}

Admit it: CRUD operations don't get easier than this.

There’s also a purge statement to clear a table or the whole database; and the save and delete methods have an overload that takes a list of objects. These came in handy for (re-)populating the initial table:

public static async Task InitializePersonTable()
{
    // Clear table
    db.Table<Person>().Purge();

    // (Re-)Populate table
    List<Person> persons = new List<Person>();
    
    Person person = new Person();
    person.Id = 1;
    // Person properties
    persons.Add(person);

    person = new Person();
    person.Id = 2;
    // Person properties
    persons.Add(person);

    // More of these ...

    db.Table<Person>().Save(persons);
}

By the way: all calls do exist in a synchronous and an asynchronous implementation.


So after less than ten minutes, I ended up with a cleaner data access layer, a model class without technical attributes, a processor-neutral app package, and an operational Visual Studio XAML Designer. SQLite is nice, but when you’re less interested in transactions, referential integrity, and SQL languages, then Lex.DB may be a better choice to store local data in a Windows 8 Store app.


Here’s the full code of the sample project. Is was written in Visual Studio 2013 for Windows 8.1: U2UC.WinRT.LexDbSample.zip (1.70 mb)


Enjoy,
Diederik

Tags :