Over a million developers have joined DZone.

A New SQLite Wrapper for Windows Phone 8 and Windows 8 – The Basics

· Cloud Zone

Download the Essential Cloud Buyer’s Guide to learn important factors to consider before selecting a provider as well as buying criteria to help you make the best decision for your infrastructure needs, brought to you in partnership with Internap.

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 codefrom the Codeplex page. The solution contains two projects:  one is calledSQLiteWinRTand the other one is calledSQLiteWinRTPhone.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 thelinkfor the Windows Store apps version, here, instead, is thelinkfor 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 newDatabaseobject, 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 aStorageFolderobject, 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 theOpenAsync()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 calledPeoplewith 3 columns:Id(which is the primary key and it’s an auto increment value),NameandSurname(which simply contains strings).

To execute the query we call the async methodExecuteStatementAsync()on theDatabaseobject, 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).

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 thePeopletable we’ve created in the step before, we need to pass two dynamic values:NameandSurname.

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 theStatementclass, that can be used to perform additional operations on the query, like adding parameters and iterating over the results. To prepare aStatementobject, you’ll need to call thePrepareStatementAsync()method passing as parameter the query to execute. How to manage parameters? The simplest way is to usenamed 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:@nameand@surname.

How to define the value of these parameters? By calling theBindTextParameterWithName()method on theStatementobject 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 nameMatteo Paganiand one with nameJohn Doe. To execute the query, we call the methodStepAsync()on theStatementobject. There are also other versions of theBindParameterWithName()method, according to the data type of the parameter (for example, if it’s a number you can use the methodBindIntParameterWithName()).

But theStepAsync()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 thePeopletable), we prepare aStatementand we execute it with theStepAsync()method. The difference is that, this time, theStepAsync()method is performed into awhilestatement: it’s because the method will iterate over all the rows returned by the query so, every time we enter into thewhileloop, a new row is retrieved. In this sample, we expect to see the MessageBox twice: one for the userMatteo Paganiand one for the userJohn Doe. In the sample you see also how to get the values from the results: theStatementobject offers some methods that starts with theGetprefix, 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 bothNameandSurnameare strings we use theGetTextAt()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 thePeopletable only the rows where the columnNamecontains the valueMatteo.

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 theEnableColumnsPropertymethod of theStatementobject: once you’ve done it, you can access to the  values by using theColumnsproperty of theStatementobject: 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.

The Cloud Zone is brought to you in partnership with Internap. Read Bare-Metal Cloud 101 to learn about bare-metal cloud and how it has emerged as a way to complement virtualized services.

Topics:

Published at DZone with permission of Matteo Pagani, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}