SQLite for WinRT

Intro

Often you need to store some data from the application you are building. One option for Windows Store apps is a local database in the file system, SQLite. It’s pretty easy to setup and to use.

SQLite is self-contained serverless database engine and is available for free for many platforms ranging from Linux to Windows. In this small tutorial I’d like to show you how to get started using it in you Windows Store app.

Bits & Pieces

First you’ll have to get the database engine. There is a .visx installation package available at www.sqlite.org. I downloaded the Precompiled Binaries for Windows Runtime and installed it. This makes it to show up in Visual Studio when adding a reference in your app under the extension tab.

image

To use the binaries you’ll need an open-source wrapper too. The most convenient way to get these is via NuGet. Just do a search for SQLite and install sqlite-net. This NuGet package contains a couple of helper classes and attributes to create and access the database.

image

Code

To get it to work you have to create the entities in code and decorate it with the right attributes.

This is how two entities might look like:

public class Artist
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [MaxLength(64)]
    public string Name { get; set; }
}

public class Album
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [Indexed]
    public int ArtistId { get; set; }

    public string Name { get; set; }
    public int ReleaseYear { get; set; }
}

Than, you can create and query the database like this:

// Set the path in which to store the database.
string dbRootPath =
             Path.Combine(new[]
                          {
                            ApplicationData.Current.LocalFolder.Path, "SqlightTest.sqlite";
                          });

// Create a new connection
var _db = new SQLiteConnection(dbRootPath);

// Create the tables if they not exists
_db.CreateTable<Artist>();
_db.CreateTable<Album>();

// insert a new artist in the database
int key = _db.Insert(new Artist {Name = "The Who"});

// insert some records using the artist key
_db.Insert(new Album {ArtistId = key, Name="My Generation", ReleaseYear = 1965});
_db.Insert(new Album {ArtistId = key, Name="A Quick One", ReleaseYear = 1966});
_db.Insert(new Album {ArtistId = key, Name="The Who Sells Out", ReleaseYear = 1967});
_db.Insert(new Album {ArtistId = key, Name="Tommy", ReleaseYear = 1969});

// query the database using chained methods
var result = _db.Table<Album>().Where(s => s.Name.StartsWith("T"))
                               .OrderByDescending(x=>x.ReleaseYear);

// or using linq
var result2 = from x in _db.Table<Artist>()
              where x.Name == "The Who"
              select x;

Wrap up

I hope this is enough the get you started using a local database in your Windows Store apps.

4 comments for “SQLite for WinRT

  1. Pingback: SQLite for WinRT
  2. November 27, 2012 at 6:53 pm

    Thanks for the article. I’m an old ‘Oo fan from way back; but you’re missing what I consider their best album, “Who’s Next” (72?)

    Unless you’ve become a punk fan, you probably meant “Wrap Up” rather than “Warp Up”

    Finally: what if you want to query your table in either Lambda or LINQ for a set, such as:

    List bestOoAlbums = new List(“Whos Next”, “Quadrophenia”, “Live at Leeds”);
    var result = _db.Table().Where(s => bestOoAlbums.Contains(s.Name)
    .OrderByDescending(x=>x.ReleaseYear);

    I know that multiple where clauses can be added, but how do you specify that they are “OR” as opposed to “AND”? IOW, would this:

    var result = _db.Table().Where(s => s.Name == “Whos Next”).
    .Where(s => s.Name == “Quadrophenia”)
    .Where(s => s.Name == “Live at Leeds”)
    .OrderByDescending(x=>x.ReleaseYear);

    …return nothing, because it is looking for albums named ALL those things (which is, obviously, impossible)?

  3. December 31, 2012 at 11:27 am

    Really Great post . i have doubt using this i was able to copy the data present in my sqitle database file into the storage file , now i want to do operations on it like insert for amount of data and manipulate some data in few data tables , but it is showing a exception only read data , please let me know how can i work around it . Thanks in Advance

  4. February 15, 2013 at 12:56 pm

    I think you should be able to write something like:

    var result = _db.Table().Where(s => s.Name == “Whos Next”).
    .Where(s => s.Name == “Quadrophenia” || s.Name == “Live at Leeds”)
    .OrderByDescending(x=>x.ReleaseYear);

    This will return a list where name is one or the other.

    Using 2 where clause will not have a desired result. The second will filter the collection returned by the first.

Leave a Reply

%d bloggers like this: