Universal App with Lex.DB

26 June 2014 Diederik-Krols Universal Windows Apps, Windows Phone, WinRT

This article shows how to create a Universal Windows App that stores its local data in Lex.DB. This is a lightweight, developer-friendly in-process database engine, completely written in C#. For an introduction to building Store Apps on top of Lex.DB, please check this article of mine. For a more advanced dive into performance tuning, check this one – and make sure you don’t skip the valuable comments from Lex Lavnikov, the author of Lex.DB, at the end.

Lex.DB can be used on .NET 4+, Silverlight 5+, Windows Phone 8+, WinRT 8+, and Xamarin. Recently this alternative for SQLite was upgraded to support Universal Windows Apps.

I created a straightforward sample app, based on the Universal App with SQLite blog post by Nicolò Carandini. I added a tiny MVVM Framework with BindableBase and RelayCommand just for fun. The sample app manages a list of Person instances.

This is the Person class, as simple as can be:

public class Person
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string Degree { get; set; }

    public string Occupation { get; set; }
}

The sample app comes with commands to add a Person, delete the selected Person, and reset the database to its default content. Here’s how it looks like in the emulator and the simulator:

EmulatorAndSimulator

You just need to add the Lex.DB Nuget package in your solution:

LexdbNuget

Each of the platform-specific projects will reference its own Lex.DB dll. That’s a lot simpler than SQLite, where you need to install an SDK, reference the C++ runtime *and* integrate some extra source code into your projects. The following screenshots illustrate the impact of both databases to your Visual Studio solution, with SQLite on the left, and Lex.DB on the right:

ProjectReferences

Here’s how the data access layer creates a reference to the database – in a static constructor:

private static DbInstance db;

static Dal()
{
    // Create database
    db = new DbInstance("Storage", ApplicationData.Current.RoamingFolder);

    // 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();
}

The following method returns the content of the Person table:

public static IEnumerable<Person> GetPeople()
{
    return db.Table<Person>();
}

I defined two methods to insert/update Person instances: one for a single instance (it returns the generated primary key) and another one to save a list (in one transaction – read the already mentioned perfomance tuning article for more details):

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

public static Task SavePeople(IEnumerable<Person> people)
{
    return db.Table<Person>().SaveAsync(people);
}

Here’s how to give your database some initial (default) content:

public static Task ResetPeople()
{
    // Clear
    db.Purge<Person>();

    // Repopulate
    return Dal.SavePeople(
        new List<Person>()
        {
            new Person() { Name="Leonard Leakey Hofstadter", Degree="Ph.D.", Occupation="Experimental physicist"},
            new Person() {Name="Sheldon Lee Cooper", Degree="Ph.D.", Occupation="Theoretical physicist"},
            new Person() {Name="Howard Joel Wolowitz", Degree="M.Eng.", Occupation="Aerospace engineer"},
            new Person() {Name="Rajesh Ramayan Koothrappali", Degree="Ph.D.", Occupation="Astrophysicist"}
        });
}

For the sake of completeness, here’s the delete method:

public static Task DeletePeople(IEnumerable<Person> people)
{
    return db.Table<Person>().DeleteAsync(people);
}

Here’s the full source code of the sample app, it was written in Visual Studio 2013 Update 2: U2UC.WinUni.LexDBSample.zip (1.2MB)

Enjoy!

XAML Brewer

Tags :