If your building out apps for the new Windows Store Apps one of the areas you will quickly find is lacking is a good local data storage option.  There’s no SQL CE, or other rdms solution built in.  Microsoft suggests using an XML file to store all of your local data…

Or to use SQLite.  However the process of getting this up and running is a little undocumented.  So I figured it out and I thought I’d share the system I came up with.

 

To get started your going to need the following:

SQLite Binaries for visual Studio

Go Here : http://www.sqlite.org/download.html and Download : Precompiled Binaries for Windows Runtime

SQLite.Net

https://github.com/praeclarum/sqlite-net

 

To get setup run the .visx file you got from sqlite.org which will install the needed libraries into Visual Studio 2012.

Next unzip the SQLite.Net package, open it up in Visual Studio and build the project.

Now let’s get started.

 

Open up Visual Studio 2012 and create a new Windows Store Application.

image

Next Choose to add a refrence to your project and Select SQLite for Windows Runtime and the C++ Runtime from the Windows Extension Section

image

If you try and build now you will get this error:

image

Set your project to target x86 to resolve issue:

image

 

Now open up NuGet and add in SQLite.Net

image

For some reason Sqlite.Net wants to just copy it’s self to your root directory.  Since I’m OCD I move them into there own folder named lib.  (also once you copy them open up the 2 classes and update the namespace)

image

Ok now we have all the pieces.  If you want to follow along you may want to add a unit test class to the project as well as I will not be doing any UI work today.

At this point if you wanted you could create a SQLite database using a management studio, but lets go code first.  It’s so much more awesome.  So to start lets create a Data folder and under that a Models folder.

image

In the Models folder were going to create a new class as shown below.

using HowToUseSqlite.lib;

namespace HowToUseSqlite.Data.Models
{
    public class Person
    {
        [PrimaryKey, AutoIncrement]
        public int PersonId { get; set; }

        public string Name { get; set; }

        public string HomeTown { get; set; }

        public int Age { get; set; }
    }
}

As you can see we can set the primary key here using the [PrimaryKey, AutoIncrement] attribute.

 

Now lets start wiring up our Datasource.  In your /Data folder (up from models) create a class named DataSource as follows:

using HowToUseSqlite.lib;
using System;
using System.IO;
using Windows.Storage;

namespace HowToUseSqlite.Data
{
    public class DataSource : IDisposable
    {
        private const string DBFILENAME = "mySQLite.db";
        protected StorageFolder UserFolder { get; set; }
        protected SQLiteAsyncConnection Db { get; set; }

        public DataSource()
        {
            this.UserFolder = Windows.Storage.ApplicationData.Current.LocalFolder;
            var dbPath = Path.Combine(UserFolder.Path, DBFILENAME);
            this.Db = new SQLiteAsyncConnection(dbPath);
        }

        public void Dispose()
        {
            this.Db = null;
        }
    }
}

As you can see were going to use a SQLite database named  mySQLite.db that will be stored in the current user’s Storage folder.  By using the storage folder you will then be able to have the data synced with the user’s profile in the cloud.  Instantly making your app awesome!

Next we are going to create a method in our class to check and see if the database exists.

That method will look like this:

 

        public void InitDatabase()
        {
            //Check to ensure db file exists
            try
            {
                //Try to read the database file
                UserFolder.GetFileAsync(DBFILENAME).GetAwaiter().GetResult();
            }
            catch
            {
                //Will throw an exception if not found
                UserFolder.CreateFileAsync(DBFILENAME).GetAwaiter().GetResult();
            }
        }

Pretty basic – Just check to see if the file is there and if not create it.  The only real interesting stuff is if your new to the async await stuff.  And I’m just bypassing it outright here by calling for the results in the same thread out of convenience.  I’m sure someone will call me on it.

Next were going to check to see if the table already exists in the database (which obviously it does not).  To do this we will need to query a special table in SQLite named  sqlite_master.  Before we can query the table we need to create an object to hold the results.  Since this is a SQLite system database let’s create the class in /lib with the other SQLite.Net files.

namespace HowToUseSqlite.lib
{
    public class sqlite_master
    {
        public string name { get; set; }
    }
}

No we will query the sqlite_master database and ceck to see if it has our table yet.  If not we will just ask nicely to have it created.

        public void CreateTables()
        {
            var existingTables =
                Db.QueryAsync<sqlite_master>("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
                  .GetAwaiter()
                  .GetResult();

            //-- Person
            if (existingTables.Any(x => x.name == "Person") != true)
            {
                Db.CreateTableAsync<Person>().GetAwaiter().GetResult();
            }
        }

We now have a code first implementation.  The next thing I want to do is to set up an ORM style Repository that we can use to perform all the CRUD actions we want to do against our tables.   In the Data directory create the following file to give us our Repository functionality.

using HowToUseSqlite.lib;
using System.Threading.Tasks;

namespace HowToUseSqlite.Data
{
    public class Repositority<T> where T : new()
    {
        private SQLiteAsyncConnection db;
        public Repositority(SQLiteAsyncConnection db)
        {
            this.db = db;
        }

        public AsyncTableQuery<T> Items
        {
            get
            {
                return db.Table<T>();
            }
        }

        public async Task<int> Create(T newEntity)
        {
            return await db.InsertAsync(newEntity);
        }

        public async Task<int> Update(T entity)
        {
            return await db.UpdateAsync(entity);
        }

        public async Task<int> Delete(T entity)
        {
            return await db.DeleteAsync(entity);
        }
    }
}

This class is just a wrapper for a few of the SQLite functions.  Now on our Data source let’s expose a repository of our Person Table to give us that ORM like feel.

        public Repositority<Person> People { get; set; }

And with that our framework is set up.  To see it in action consider the following unit test displaying a full life cycle of a Person.

        [TestMethod]
        public void FullLifeCycleTest()
        {
            DataSource ds = new DataSource();
            ds.InitDatabase();
            ds.CreateTables();

            Person p = new Person
                {
                    Name = "Happy Little Guy",
                    HomeTown = "Happy Town",
                    Age = 10
                };

            ds.People.Create(p).GetAwaiter().GetResult();

            var lookup = ds.People.Items.Where(x => x.Name == "Happy Little Guy")
                .ToListAsync().GetAwaiter().GetResult().First();
            
            Assert.IsNotNull(lookup);

            lookup.Name = "Changed Name";
            ds.People.Update(lookup).GetAwaiter().GetResult();

            var lookup2 = ds.People.Items.Where(x => x.Name == "Changed Name")
                .ToListAsync().GetAwaiter().GetResult().First();

            Assert.IsNotNull(lookup2);

            ds.People.Delete(lookup2);

            var lookup3 = ds.People.Items.Where(x => x.Name == "Changed Name")
                .ToListAsync().GetAwaiter().GetResult();

            Assert.AreEqual(lookup3.Count, 0);


        }

 

The Final Code for the DataSource.cs Looks like this:

using System.Linq;
using HowToUseSqlite.Data.Models;
using HowToUseSqlite.lib;
using System;
using System.IO;
using Windows.Storage;

namespace HowToUseSqlite.Data
{
    public class DataSource : IDisposable
    {
        private const string DBFILENAME = "mySQLite.db";
        protected StorageFolder UserFolder { get; set; }
        protected SQLiteAsyncConnection Db { get; set; }

        public DataSource()
        {
            this.UserFolder = Windows.Storage.ApplicationData.Current.LocalFolder;
            var dbPath = Path.Combine(UserFolder.Path, DBFILENAME);
            this.Db = new SQLiteAsyncConnection(dbPath);

            People = new Repositority<Person>(Db);
        }

        public void InitDatabase()
        {
            //Check to ensure db file exists
            try
            {
                //Try to read the database file
                UserFolder.GetFileAsync(DBFILENAME).GetAwaiter().GetResult();
            }
            catch
            {
                //Will throw an exception if not found
                UserFolder.CreateFileAsync(DBFILENAME).GetAwaiter().GetResult();
            }
        }

        public void CreateTables()
        {
            var existingTables =
                Db.QueryAsync<sqlite_master>("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
                  .GetAwaiter()
                  .GetResult();

            //-- Person
            if (existingTables.Any(x => x.name == "Person") != true)
            {
                Db.CreateTableAsync<Person>().GetAwaiter().GetResult();
            }
        }


        public Repositority<Person> People { get; set; }


        public void Dispose()
        {
            this.Db = null;
        }
    }
}

The Complete Source Code can be downloaded here: http://sympletech.com/Files/HowToUseSqlite.rar

Share →

One Response to Using SQLite in Windows Store Apps

  1. victor says:

    Not so far I have found new cool tool to work with SQLite on win – Valentina Studio. Its free edition can do things more than many commercial tools!!
    I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>