SQLite is a small but very useful database engine widely deployed in the world. The folks on the team have been working hard to release a version compatible with Windows 8 and this one is available as an extension to Visual Studio.

Installation

To start using it, just go to the “Tools” menu and choose “Extensions and Update”:

Here, choose the “Online” section, search for “sqlite” then download and install “SQLite for Windows Runtime”:

Once Visual Studio restarted, just add the following reference to your project:

1. SQLite for Windows Runtime
2. Microsoft Visual C++ Runtime Package

Please note that to allow compilation of your project, this one cannot target the “Any CPU” configuration: you need to choose & build specific architecture (x86, x64 or ARM):

This means that, if you plan to release your application for any architecture, you’ll need to create (and submit) 3 different packages.

Finally, the last step you need to do is to grab a wrapper that will call the SQLite APIs. You can, for example, take sqlite-net (available on Nuget).

This will add 2 files to your solution: SQLite.cs (use to access the SQLite APIs) and SQLiteAsync.cs (the async version of the wrapper).

Now, you’re ready to use SQLite in your application! Documentation is very good so don’t hesitate to take a look at it but here are the basic steps you may want to perform.

Basic Actions

Access database

First thing you may want to do is simply to create a connection to the database. This can be perform by using the SQLiteConnection object (or its async version: SQLiteAsyncConnection), taking in parameter the path to the file which represent your database:

var path = Windows.Storage.ApplicationData.Current.LocalFolder.Path + @"\users.db";
var db = new SQLiteAsyncConnection(path);

Here is a quick snapshot of the methods available to the connection object (we’ll take a look at them further below):

Create table

Now that connection to the database is setup, we can start accessing data. First, we need to create some tables (at least one). This can be done by using one of the following (async) methods:

1. CreateTableAsync:
2. CreateTablesAsync:

Here is an example:

await db.CreateTableAsync<User>();

As you can see, we simply call the CreateTableAsync method, passing the table type as parameter (User in our case). So, as you might guess, this type must be defined somewhere:

[Table("Users")]
public class User
{
    [PrimaryKey, Unique]
    public Guid Id { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public int Age { get; set; }
}

Here, you can see some common attributes used with ORM tools like Table, PrimaryKey, Unique, etc. These attributes are used to configure tables when they will be created.

Note about using Guid type: In the code above, you can see that I’m using the Guid type. By default, this type is not supported in SQLite. You have to add the following compilation symbol to support Guid: SQLITE_SUPPORT_GUID

Insert data

To insert some data in the tables, you can add data one by one (using InsertAsync) or by using an IEnumerable object (using InsertAllAsync):

var tom = new User
{
    Id = Guid.NewGuid(),
    LastName = "LEBRUN",
    FirstName = "Thomas",
    Age = 30
};

await db.InsertAsync(tom);

var isUser = new List<User>()
{
    new User
    {
        Id = Guid.NewGuid(),
        LastName = "ANTOINE",
        FirstName = "Jonathan",
        Age = 18
     },
     new User
     {
         Id = Guid.NewGuid(),
         LastName = "SANTIN",
         FirstName = "Florent",
         Age = 29
     },
     new User
     {
         Id = Guid.NewGuid(),
         LastName = "MEZIL",
         FirstName = "Matthieu",
         Age = 30
     },
};

await db.InsertAllAsync(isUser);

Note that, as all methods are running asynchronously, I can use the await keyword (thanks to the async/await feature !).

Select data

Ok, now, we have our database with some tables and data inserted in the tables. But you’ll mostly want to query these tables to get their content. A possible method (we’ll see just after that there are other ways) is to use the QueryAsync method. This method take in parameter:

1. The SQL string that you want to execute (SELECT * From Table, UPDATE …)
2. The values of SQL parameters (if your query contains some of them)

Here is a example of a basic query that retrieve the total number of users in the database:

This query is a pretty simple one so let’s take a look at another one, a bit more complicated (e.g which use SQL parameters):

Here, you can see that each parameter of the query must be symbolized using the character “?”. Then, the value of the parameters is expressed as an object array in the second parameter of the method QueryAsync.

Update data

To update data in your database, you can use the method UpdateAsync. This method take as parameter the object (with its new properties updated) that you want to update.For example, look at the following code which get the first “old user”, update its age to 99 and query the database again to find this “very old guy”:

Except for the update method, the interesting part here is the use of the FindAsync method, which allow you to look for the first object corresponding to the predicate (this is more comfortable than using the SQL string syntax). For your information, you should know that there is another similar method that you can use: GetAsync.

Delete data

Deleting some data is as easy as getting or updating it. Indeed, the method DeleteAsync will do the job for you (as soon as you have retrieve the data you want to delete):

You may have noticed that I’ve not checked if the variable “veryOldUser” is not null before passing it in parameter to DeleteAsync. This is just for the demo and you should always perform this check before using your objects.

Drop table

Finally, you might need to drop a table from your database. To handle this scenario, simply use the method DropTableAsync:

Simple but useful!

Be careful now because if you tried to use a table that does not exist, you’ll encounter the following error:

Conclusions

As you can see, the APIs are very simple to use and performance is good. It could be very interesting to perform more performance tests but, except for the connection initialization which take a bit of time, I’ve not seen any performance issues.

Also, if you have some Windows Phone code using database in one of your application, you’ll be able to migrate it quite easily!

 

Feel free to contact me if you need more information!

 

Happy coding!