SQLite for Metro/Windows Store app

 

If you write Metro/Windows Store app, there is a lot of chance you will need a DB. There is nothing out of the box (no SQL Compact 2012 WinRT) but hopefully we have now a viable alternative : SQLite.

 

Installation

The installation is in two parts. The first is mandatory (Installation of the SQLite engine) and the second is not (a C# wrapper for SQLite).

First thing first, the engine :

Go to the Tools/Extension and Updates menu

Then select SQLIte for Windows RT:

Now, add those references in your project :

Voila, the engine is installed !

Now, it is time for the wrapper. It is a NuGet package :

Now you have everything to start. Well…almost. For the time being, you can’t have an “AnyCPU” compilation, so you need to select the platform. If you need th x86 and ARM platform, you’ll need two projects you can package together for the store.

 

Create the database

 

To create the database, you have to use a kind of Code-First method (People using EF will understand). You create a class, and set some attributes on properties such as PrimaryKey, MaxLength,etc….

Example :

public class Company
    {
        [SQLite.AutoIncrement, SQLite.PrimaryKey]
        public int ID { get; set; }

        [SQLite.MaxLength(50)]
        public string Name { get; set; }

        [SQLite.Indexed(Name = "IndexVAT",Order = 0,Unique = true)]
        public string VAT { get; set; }
    }

Here I create class (table)  called Company. It has 3 properties (Columns) :

ID, which is the primary key, and is auto-increment.

Name, with a maximum length of 50caracters.

VAT, which is indexed, has order 0 (If you index several columns under one index, you have to set the order) and is unique.

Now it is done, I can create the DB this way :

using (var db = new SQLiteConnection(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "mydb.sqlite")))
{
  db.CreateTable();
}

CRUD

CRUD operations are very simple :

CREATE

using (var db = new SQLiteConnection(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "mydb.sqlite")))
{
       db.Insert(new Company { Name = "Microsoft" });
}

READ

using (var db = new SQLiteConnection(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "mydb.sqlite")))
{
       var companylist = db.Table().Where(d => d.Name != "Apple").ToList()
}

UPDATE

using (var db = new SQLiteConnection(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "mydb.sqlite")))
{
       var company = db.Table().First();
       company.Name = "Microsoft";
       db.Update(company);
}

DELETE

using (var db = new SQLiteConnection(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "mydb.sqlite"))))))
{
   // Don't try this at home
   db.DeleteAll();
}

 

Transactions

There are two ways of using transaction. A basic way, with less control (It just rollbacks if exception otherwise it commits) and one where you have more control.

Basic :

using (var db = new SQLiteConnection(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "mydb.sqlite"))))))
{
   db.RunInTransaction(() =>
   {
      db.Insert(new Employee { FirstName = "Jean" });
   });
}

Normal :

using (var db = new SQLiteConnection(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "mydb.sqlite"))))))
{
    db.BeginTransaction();

    try
    {
      db.DeleteAll();
      db.Commit();
    }
    catch (Exception exception)
    {
      db.Rollback();
    }
}

 

Now you have all the basics, happy coding !!

The following two tabs change content below.
Olivier

Olivier

Mobile Engineer at Arcana Studio
Freelance developer. Passionate for mobile Development and IoT. Expert in WinRT and Xamarin. MVP Windows Platform Development Nokia Developer Champion