Using Lex.Db on the Universal Windows Platform

15 September 2015 Diederik-Krols UWP

This article explains how to get started with Lex.Db on the Universal Windows Platform. Lex.Db is a file-based database written in .NET by Lex Lavnikov as a fast, light-weight alternative for SQLite. It does not require you to install a Visual Studio Extension on your development machine, to reference C++ libraries, or to separately compile your app for each target platform. Lex.Db runs on any .NET flavor:

  • Universal Windows 10 Platform
  • Xamarin.iOS & Xamarin.Android
  • Universal Windows Store 8.* Apps
  • .NET 4.*
  • Silverlight 5.*
  • Windows Phone 8.*
  • WinRT.

For more info, check the official documentation and the author’s blog.

As usual, I wrote a small sample app. It started with a copy of my previous sample app on SQLite. Obviously the data access layer was changed, but I also modified the CoverFlow-based UI to better deal with landscape oriented pictures. The menu at the left hand side is visually inspired by the SplitView Pane in compact mode, but it’s just the first column of the page’s main grid:

AppBrowse

Lex.Db comes to you through NuGet:

LexDbNuget

The Lex.Db namespace has a small number of classes in it. The DbTableAsync class hosts the asynchronous table calls:

LexDbNamespace

Here’s a class diagram of the two main classes – representing a database instance and a table:

LexDb

With Lex.Db you define your entities in C# and then create a table for each type. Attributes are not supported, so we start with a POCO class. Here’s my definition of a VintageMuscleCar:

/// <summary>
/// Represents a vintage muscle car.
/// </summary>
public class VintageMuscleCar
{
    /// <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 day of birth.
    /// </summary>
    public DateTime ReleaseDate { get; set; }

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

Every table is stored as two files (data and index). Each time your app starts up, you have to tell to Lex.Db where the files are and what they represent:

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<VintageMuscleCar>().Automap(p => p.Id, true);

    // Initialize database
    db.Initialize();
}

Here’s the code to clear and repopulate the VintageMuscleCar table. For this kind of operations, Lex.Db provides some extra-fast operators like Purge, BulkRead, and BulkWrite:

public async static Task ResetCars()
{
    // Clear
    db.Purge<VintageMuscleCar>();

    // Repopulate
    var cars = await Dal.DefaultCars();
    await Dal.SaveCars(cars);
}

public async static Task<List<VintageMuscleCar>> DefaultCars()
{
    var cars = new List<VintageMuscleCar>();

    var car = new VintageMuscleCar();
    car.Name = "1965 Corvette C2 Stingray";
    // (... more properties ...)
    cars.Add(car);

    car = new VintageMuscleCar();
    car.Name = "1965 Pontiac GTO";
    // (... more properties ...)
    cars.Add(car);

    // More cars...

    return cars;
}

Lex.Db also comes with overloads for all CRUD operators that take a list instead of a single entity. Here’s the rest of the Dal.cs class from the sample app, illustrating some quintessential database calls:

public static IEnumerable<VintageMuscleCar> GetCars()
{
    return db.Table<VintageMuscleCar>();
}

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

public static int SaveCar(VintageMuscleCar car)
{
    db.Table<VintageMuscleCar>().Save(car);
    return car.Id;
}

public async static Task SaveCars(IEnumerable<VintageMuscleCar> cars)
{
    await db.Table<VintageMuscleCar>().SaveAsync(cars);
}

public async static Task<int> DeleteCars(IEnumerable<VintageMuscleCar> cars)
{
    return await db.Table<VintageMuscleCar>().DeleteAsync(cars);
}

Here’s the sample app in Edit-mode:

AppEdit

Its source code lives on GitHub, it was written with Visual Studio 2015.

Enjoy!

XAML Brewer

Tags :