Friday, 22 February 2013

Lex.Db for Windows 8 Store Apps (Part 1) - The Basics


For my first technical post on this new blog I wanted to cover a new project called Lex.DB which has caught my interest today. For a brief summary of this project I can write no better summary than the description on the github page:
"Lex.DB is a lightweight, superfast, in-process database engine, completely written in C#".
Great, so where would we use this? Well in my case it looks like an interesting option for local data storage in Windows 8 Store Applications. I've recently started developing Windows 8 Store Apps and one problem area I hit early on was the lack of a built in SQL like storage mechanism for local data. Microsoft's approach and demo's really seemed to push connected storage to cloud based solutions and didn't cover a lot of approaches for disconnected local storage. My research lead to two main options, both of which I have been experimenting with.

Option 1 was to manage my own serialisation of data to and from XML files using the DataContractSerializer. I have an application in development at the moment using this which stores persistent application data in the XML files which is then loaded into in memory objects while the app is running. This approach works but has some limitations in that most of the time I have a large amount of the data loaded into memory. This is perhaps fine for the small application I'm working on, but not scalable for larger line of business style apps.

Option 2 was SQLite, which is a library providing an in-process transactional SQL database engine that can be used with WinRT with the help of a nuget package called SQLite-Net. I won't go into the details of how this works in this post, but will say that's it's a good option for Windows 8 Store apps from what I've seen so far. It does have some downsides due to it's platform dependant nature. The biggest of which is the need to compile your application three times whenever you wish to deploy a new version.

So that brings me to Lex.DB which I have only briefly worked with so far. This project doesn't have the same platform issues as SQLite and it also avoids the need for all data be held in memory at runtime. Instead it seems that the indexes are held in memory so that data can be queried and then the full objects retrieved as needed. My initial impressions are quite good, although some features such a relationships are not supported currently. It may therefore require some work to build a workable solution for all but the simplest of data models. A big advantage is that it supports both Windows 8 Store apps as well as Windows Phone 8 apps.

This post will aim to cover the basics of using Lex.DB in a Windows 8 Store application. Later posts will dive in a bit deeper with some sample code I've started putting together to use Lex.DB in a more real world sample application. At this time I don't know how scalable this can be for a larger application where we need to manage relationships, but we can take the journey together and see where it leads us.

NOTE: Lex.DB is being actively developed at the time of writing and features are changing often. This post was written at the release of version Newer versions may implement features beyond the scope of this post which I may write out in future blog posts.

A small caveat I'd like to put here before we start. I'm self taught in C# and certainly lack knowledge of lots of areas. Therefore my code may be suboptimal or even downright wrong! I welcome any comments for suggestions to improve it as I go.

Let's Get Started

In this post I'm going to show a very simple app to demonstrate saving and loading data. I won't concern myself with splitting out code in the MVVM pattern and will simply use the code behind of the MainPage to show the core usage of Lex.DB.

We start with a new Blank C# Windows Store application in Visual Studio. The first thing we need to do is to bring in Lex.DB using NuGet.

To start with we need a model to represent the objects we will be storing using Lex.DB.

Let's creat a folder called Models in the project and creat a new class to represent the data model, which for this sample we'll called Person. This class simply has some basic properties to represent a person object.
public class Person
     public int PersonID { get; set; }
     public string Forename { get; set; }
     public string Surname { get; set; }
On the MainPage.xaml.cs we firstly needed to add a using statement to access Lex.DB
using Lex.Db;
We need an instance of the Lex.DB class (named DBInstance) which we'll include as a private variable to be used by the page's methods as required.
DbInstance _db = new DbInstance("SampleDB");
The argument we pass here is the name we want for our database. Our next steps are to tell Lex.DB what models we want to store. This initialisation code will be performed one time only when the page is loaded, so we'll be using the OnNavigatedTo method for this demo. To separate things a little I've created an async method to handle the actual initialisation asynchronously (any such data access should be async to avoid blocking the UI) which is then called from the page's OnNavigatedTo method.
protected async override void OnNavigatedTo(NavigationEventArgs e)
     await InitLexDB();

private async Task InitLexDB()
     _db.Map<Person>().Automap(i => i.PersonID, true);
     await _db.InitializeAsync();
The Map method is where we tell Lex.DB which model classes we wish to store. Lex.DB includes an AutoMap option which maps all of the public properties using reflection. For this demo we won't go any further than this but there are ways to be more specific regarding the mapping of properties. AutoMap takes two arguments (the second of which is optional). The first is an expression to tell with which field is going to be our primary key. The second is a boolean to determine if Lex.DB should manage the key for us (like an auto incrementing ID in SQL). I've chosen to utilise this, but we could set this to false if we want to control the key ourselves. The next line initialises everything using the asynchronous method.

At this point we have everything setup and can begin reading and writing to the database using it's asynchronous methods. Normally these would probably be triggered by UI events and the requirements of the different views but to keep this post focused I want to simply add a new Person object each time the MainPage is loaded. After this the full list of objects is retrieved and displayed using a TextBlock element. This bares no resemblance to real world usage for now but shows how simple it is use Lex.DB.

To do this we creat two new Async methods. One to add a person and another to return a list of all Person records from the database.
private async Task AddPerson()
     Person newPerson = new Person { Forename = "Joe", Surname = "Bloggs" };
     await _db.Table<Person>().SaveAsync(newPerson);

private async Task<List<Person>> LoadAllPeople()
     Person[] people = await _db.Table<Person>().LoadAllAsync();
     return people.ToList();
The AddPerson method first creates a new Person object. We then use the SaveAsync method to store this new Person to the database. This is completed using the generic table method. It we weren't concerned about this being async we could use the simpler Save syntax:
In this case Lex.DB would work out where to store the object based on it's type. But this is Windows 8, so async is the way to go.

The LoadAllPeople method returns a Task of type List<Person>. The LoadAllAsync method is doing the work and returning to us all the records in the Person table. Since there is no ToList exposed for the LoadAllAsync, instead we store the returned data into an array. We can then return this on the next line by using the array's own ToList extension method.

Finally we can update OnNavigatedTo to use the new methods we have created. After we have loaded the data we loop through the list of people and for simplicity just added the names to a TextBlock.
protected async override void OnNavigatedTo(NavigationEventArgs e)
     await InitLexDB();
     await AddPerson();

     List<Person> people = await LoadAllPeople();

     foreach (Person p in people)
          DataOutput.Text += p.Forename + " " + p.Surname + " - ";
And that's our completed demo for this post. One thing I will quickly show is that just as easily as pulling all of the records from the database, we can pull a single person using the ID value if we know it. To do that we used the following command:
Person person = await _db.Table<Person>().LoadByKeyAsync(1);
The LoadByKeyAsync method takes in the value of the primary key we are searching for. This is similar to Entity Framework's DbSet Find() method.


In this post I've hopefully shown the basic usage of Lex.DB which will be the ground work for further exploration in my next posts. We'll look at the Indexing features which can be utilised and then start thinking about how we would need to structure a Windows 8 Store application to begin using Lex.DB in a more real world manner.

View Part 2

No comments:

Post a Comment