Diary of a Windows developer

This post is the second part of a series about a new SQLite wrapper for Windows Phone 8 and Windows 8, you can read the first part here.

One of the biggest flaws of the sqlite-net library for Windows 8 and Windows Phone 8 is that it doesn’t support relationships: there isn’t an attribute that can be used to mark a class property as a foreign key. Plus, sqlite-net offers some methods to manually execute a SQL statement, but the problem is that doesn’t offer a way to iterate through the results, but it always expects a specific object in return.

Let’s take a very common situation as example: to the database with the People  table we’ve created in the previous post we want to add a new table called Orders, to keep track of the orders made by the users. With sqlite-net we would have created two classes, one to map the People table and one to map the Orders table. Then, we could have execute a manual query in a similar way:

private async void OnExecuteJoin(object sender, RoutedEventArgs e)
{
   SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);
   string query = "SELECT * FROM People INNER JOIN Orders ON Orders.PersonId = People.Id";
   List<Person> personOrders = await conn.QueryAsync<Person>(query);
}

Which is the problem with the sqlite-net approach? That, as you can see, when you call the QueryAsync() method it requires a <T> parameter, which is the type that you expect as a result of the query. Sqlite-net will automatically deserialize the result and will provide the object for you, ready to be used in your application. Smart, isn’t it? The problem is that this approach doesn’t work when we have a relationship: the sample code that you see is wrong, because when we have created a relationship between the tables People and Orders we don’t expect anymore to get, as a result, a Person object or an Order object, but a combination of both of them. The workaround, in this case, would be to create a third class, something like PeopleOrders, that will contain all the properties of the People class combined with the properties of the Orders class. Suddenly, it doesn’t sound so smart, isn’t it?

With this new wrapper, instead, we’ll be able to support this scenario, because we can simply iterate trough the rows returned by the query and pick the data we need using the Statement object. The only downside is that we’ll have to do a lot of manual work: we’ll have to create our objects from scratch, since there is no automatic serialization and deserialization of the data.

Let’s see, step by step, how to support relationships with the new wrapper. We’ll reuse the knowledge we’ve learned in the previous post.

Let’s update the database

The first thing to do is to create a new table, that will store the new Orders table. Let’s see the code:

private async void OnCreateDatabaseClicked(object sender, RoutedEventArgs e)
{
    Database database = new Database(ApplicationData.Current.LocalFolder, "people.db");

    await database.OpenAsync();

    string query = "CREATE TABLE PEOPLE " +
                   "(Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                   "Name varchar(100), " +
                   "Surname varchar(100))";

    await database.ExecuteStatementAsync(query);
        string query2 = "CREATE TABLE ORDERS " +
                        "(Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                        "Description VARCHAR(200)," +
                        "Amount INTEGER," +
                        "PersonId INTEGER, " +
                        "FOREIGN KEY(PersonId) REFERENCES People(Id) ON DELETE CASCADE)";

        await database.ExecuteStatementAsync(query2);
}

The People table is the same we’ve seen in the previous post; the Orders table contains some columns to store the info about the order (description, amount, etc.) plus a column to manage the relationship, that will act as a foreign key. Specifically, we add a column called PersonId, that will store the id of the user that made the order taken from the Id column of the People table. We also define that this column is a foreign key and that, if we delete a user, all his orders will be deleted too (with the ON DELETE CASCADE statement). To define the key we use the following statement:

FOREIGN KEY(PersonId) REFERENCES People(Id)

that means that the PersonId column of the Orders table will hold a reference to the Id column of the People table.

Manage the orders

Now we’re ready to start using the relationships and add an order made by the user Matteo Pagani, which Id (that has been auto generated) is 1 (we’ve added this user using the code from the previous post).

private async void OnInsertOrderClicked(object sender, RoutedEventArgs e)
{
    Database database = new Database(ApplicationData.Current.LocalFolder, "people.db");

    await database.OpenAsync();

    string query = "INSERT INTO ORDERS(Description, Amount, PersonId) VALUES (@Description, @Amount, @PersonId)";
    Statement statement = await database.PrepareStatementAsync(query);
    statement.BindTextParameterWithName("@Description", "First order");
    statement.BindIntParameterWithName("@Amount", 200);
    statement.BindIntParameterWithName("@PersonId", 1);

    await statement.StepAsync();
}

If you’ve read my previous post, there should be nothing special in this code: we execute a insert query and we add, as parameters, the description of the order, the amount of the order and the id of the person that made the order. In the end, we execute the query using the StepAsync() method of the Statement object.

Now, it’s time to retrieve the data: let’s do a join statement, to retrieve all the orders with the information about the user that made it, like we’ve seen in the first sample with sqlite-net (the one that wasn’t working ).

private async void GetAllOrdersClicked(object sender, RoutedEventArgs e)
{
    Database database = new Database(ApplicationData.Current.LocalFolder, "people.db");

    await database.OpenAsync();

    string query = "SELECT * FROM ORDERS INNER JOIN PEOPLE ON ORDERS.PersonId=PEOPLE.Id";

    Statement statement = await database.PrepareStatementAsync(query);
    statement.EnableColumnsProperty();

    while (await statement.StepAsync())
    {
        MessageBox.Show(string.Format("{0} by {1} {2}", statement.Columns["Description"],
                                      statement.Columns["Name"], statement.Columns["Surname"]));
    }

}

The query is exactly the same: what changes is that, now, we’re able to iterate through the results so, thanks to the Statement object and the StepAsync() method, we’re able to extract all the values we need (Description, Name and Surname) and display it using a MessageBox. In a real application, probably, we would have populated a collection of data, that we would have displayed in the application using a ListBox or a LongListSelector, for example. Just notice that I’ve enabled the columns property feature (with the EnableColumnsProperty() method), so that we can access to the columns directly using their names as index of the Columns collection.

In the end

With the latest two posts we’ve seen a different approach to use SQLite in a Windows Phone 8 or Windows 8 app: with sqlite-net, we have an approach similar to the one we’ve learned with SQL CE in Windows Phone. Thanks to the power of LINQ, performing operations on database is really simple and, as with every other ORM, you can keep thinking using the objects approach, instead of having to deal with SQL. But all this flexibility comes with a price: the biggest limitation, right now, with sqlite-net, is that almost impossible to manage relationships. On the other side, with the new wrapper by Microsoft, you have full control, thanks to the great support to manual SQL statements; in complex applications, where you have to deal with lot of tables and relationships, it’s a big pro and it may help you a lot also to fine tuning performances; on the other side, you’ll have to deal on manual queries even for the most simple ones, like creating a table or adding some data. It’s up to you (and to the project you’re working on) to choose which wrapper is the best for you!

One of the topics that gathered more attention on my blog is SQLite: developing an application without the need to store, somehow, the data locally is almost impossible, so developers are always searching the best way to achieve this objective. SQLite is one of the most interesting solutions out there, since it’s open source and it’s available for almost every platform on the market (mobile, web, client, etc.).

I’ve already talked you about sqlite-net, a library that is available for Windows Phone 8 and Windows 8 that can be used to perform operations on a SQLite database using a high level API that support LINQ operations. This library is very simple to use, but it has some limitations: the biggest one is that doesn’t support relationships out of the box.

Now Peter Torr (a member of the Windows Phone development team in Microsoft) with the support of Andy Wigley (a former Windows Phone Development MVP that now has joined Microsoft) have released on Codeplex a new SQLite wrapper, that is totally different from sqlite-net and that satisfies another type of approach: total control. In fact, this new wrapper doesn’t support any LINQ operation but only manual SQL statement: the biggest pro is that you can perform any type of operation, even managing relationships. The biggest cons is that you’ll have to rely on old plain SQL queries to do any operation, even the simplest ones, like creating a table or inserting some data.

Let’s see, step by step, how to use it and how the approach is different from using sqlite-net. In this first post we’re going to see the basics, in the next one we’ll talk about relationships.

Configure the wrapper

For the moment, since it’s written in native code, the library isn’t available on NuGet: you’ll have to download the source code from the Codeplex page. The solution contains two projects:  one is called SQLiteWinRT and the other one is called SQLiteWinRTPhone. The first one is for Windows Store apps, the second one for Windows Phone 8 apps (Windows Phone 7 is not supported, since it doesn’t support native code): you’ll have to add to your solution the project that fits your needs. For this tutorial I’m going to create a Windows Phone 8 application, so I’m going to use the second project. Plus, as we did for sqlite-net, we need to install the official SQLite runtime, that is available as a Visual Studio extension: here is the link for the Windows Store apps version, here, instead, is the link for the Windows Phone 8 version.

Now you’re ready to use it! We’re going to use the same sample we used in the sqlite-net post: a table to store data about people.

Create the database

Let’s see how to do it:

private async void OnCreateDatabaseClicked(object sender, RoutedEventArgs e)
{
    Database database = new Database(ApplicationData.Current.LocalFolder, "people.db");

    await database.OpenAsync();

    string query = "CREATE TABLE PEOPLE " +
                   "(Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                   "Name varchar(100), " +
                   "Surname varchar(100))";

    await database.ExecuteStatementAsync(query);
}

First we create a new Database object, that we’re going to use to make operations on the database. There are various ways to instantiate it: in this sample we’re going to create it by specifying the folder in the isolated storage where to save the file and the file name. The folder is passed to the constructor using a StorageFolder object, which is the Windows Runtime class that identifies a folder in the isolated storage. In the sample, we’re simply using the storage’s root. If the database doesn’t exist, it will be created; otherwise it will simply be opened.

Then we can open the connection, by calling the OpenAsync() method: it accepts, eventually, a parameter to set the opening mode (like read only). If we don’t set it, it will be used the default one, that supports read and write operations.

Then, we go manual! As already anticipated, the wrapper doesn’t support LINQ operations, so you’ll have to write the needed SQL statements to perform the operation. I assume that you already know the basics of SQL, so I won’t describe in details the queries: in this sample we define a query to create a table called People with 3 columns: Id (which is the primary key and it’s an auto increment value), Name and Surname (which simply contains strings).

To execute the query we call the async method ExecuteStatementAsync() on the Database object, passing as parameter the string with the query statement. ExecuteStatementAsync() is the method we need to use when the query doesn’t return any value and when we don’t need to define any parameter (we’ll see later how to use them).

As you can see, there’s a huge difference with the sqlite-net approach: there’s no mapping with a class and there’s no automatic conversion to a table. Everything is made with plain SQL statements.

Perform operations on the database

The approach to perform operations (insert, update, select, etc.) is the same we’ve seen: we open the connection, we define the query statement and we execute the query. The only difference is that when you do an insert, for example, usually you need to set some parameters, because some elements of the query are not fixed but dynamic. For example, if we want to add a new row in the People table we’ve created in the step before, we need to pass two dynamic values: Name and Surname.

Here is the code to perform this operation:

private async void OnAddDataClicked(object sender, RoutedEventArgs e)
{
    Database database = new Database(ApplicationData.Current.LocalFolder, "people.db");

    await database.OpenAsync();

    string query = "INSERT INTO PEOPLE (Name, Surname) VALUES (@name, @surname)";
    Statement statement = await database.PrepareStatementAsync(query);
    statement.BindTextParameterWithName("@name", "Matteo");
    statement.BindTextParameterWithName("@surname", "Pagani");

    await statement.StepAsync();

    statement = await database.PrepareStatementAsync(query);
    statement.BindTextParameterWithName("@name", "John");
    statement.BindTextParameterWithName("@surname", "Doe");

    await statement.StepAsync();
}

Please enter the Statement class, that can be used to perform additional operations on the query, like adding parameters and iterating over the results. To prepare a Statement object, you’ll need to call the PrepareStatementAsync() method passing as parameter the query to execute. How to manage parameters? The simplest way is to use named parameters, which can be added to a query simply by prefixing the @ symbol to the name of the parameter. In the sample, the insert query accepts two parameters: @name and @surname.

How to define the value of these parameters? By calling the BindTextParameterWithName() method on the Statement object with two parameters: the first one is the name of the parameter, the second one is the value we want to assign. In the sample, we’re going to add two users to the table: one with name Matteo Pagani and one with name John Doe. To execute the query, we call the method StepAsync() on the Statement object. There are also other versions of the BindParameterWithName() method, according to the data type of the parameter (for example, if it’s a number you can use the method BindIntParameterWithName()).

But the StepAsync() method has another purpose: it can be used also to iterate through the results of the query, in case we’re performing a query that can return one or more values. Let’s see, for example, how to perform a select to retrieve the data we’ve just inserted:

private async void OnGetDataClicked(object sender, RoutedEventArgs e)
{
    Database database = new Database(ApplicationData.Current.LocalFolder, "people.db");

    await database.OpenAsync();

    string query = "SELECT * FROM PEOPLE";
    Statement statement = await database.PrepareStatementAsync(query);

    while (await statement.StepAsync())
    {
        MessageBox.Show(statement.GetTextAt(0) + " " + statement.GetTextAt(1));
    }
}

The first part of the code is the same we’ve seen before: we define the query (in this case, we retrieve all the rows of the People table), we prepare a Statement and we execute it with the StepAsync() method. The difference is that, this time, the StepAsync() method is performed into a while statement: it’s because the method will iterate over all the rows returned by the query so, every time we enter into the while loop, a new row is retrieved. In this sample, we expect to see the MessageBox twice: one for the user Matteo Pagani and one for the user John Doe. In the sample you see also how to get the values from the results: the Statement object offers some methods that starts with the Get prefix, that accepts as parameter the column index of the value to retrieve. There are some method for the most common data types: in this sample, since both Name and Surname are strings we use the GetTextAt() method, passing 0 as index to get the name and 1 as index to get the surname.

Of course we can combine what we’ve learned in the last two samples and, for example, we can perform a select query that contains some parameters:

private async void OnGetSomeDataClicked(object sender, RoutedEventArgs e)
{
    Database database = new Database(ApplicationData.Current.LocalFolder, "people.db");

    await database.OpenAsync();

    string query = "SELECT * FROM PEOPLE WHERE Name=@name";
    Statement statement = await database.PrepareStatementAsync(query);
    statement.BindTextParameterWithName("@name", "Matteo");

    while (await statement.StepAsync())
    {
        MessageBox.Show(statement.GetTextAt(0) + " " + statement.GetTextAt(1));
    }
}

In this sample, we retrieve from the People table only the rows where the column Name contains the value Matteo.

We have also another option to access to the columns of the rows we’ve retrieved with a query, but it’s disabled by default because it slower and it returns every value as string, instead of its proper type. To enable it you have to call the EnableColumnsProperty method of the Statement object: once you’ve done it, you can access to the  values by using the Columns property of the Statement object: it’s a collection and you can access to each item by using the column’s name as index. Here is a sample:

private async void OnGetSomeDataWithColumnsPropertyClicked(object sender, RoutedEventArgs e)
{
    Database database = new Database(ApplicationData.Current.LocalFolder, "people.db");

    await database.OpenAsync();

    string query = "SELECT * FROM PEOPLE";
    Statement statement = await database.PrepareStatementAsync(query);

    statement.EnableColumnsProperty();

    while (await statement.StepAsync())
    {
        MessageBox.Show(statement.Columns["Name"] + " " + statement.Columns["Surname"]);
    }
}

Coming soon

In this post we’ve learned the basic concepts to use this new wrapper to perform operations on a SQLite database and which are the differences with another popular wrapper, sqlite-net. In the next post we’ll see how to deal with relationships, one of the most problematic scenarios to manage nowadays. Meanwhile, you can play with the sample project.