SQLite-WinRT: Database programming on Windows Phone and Windows 8

Today at TechEd North America in New Orleans, I am presenting a session on programming the SQLite database on Windows Phone 8. You can watch the video and download the slides from here: Support Your Demanding LOB Apps With SQLite and Windows Phone 8. The sample application I walked through is here:

SqlitePhoneSample.zip

The session is a deep dive on the new SQLite-WinRT API that we released on Codeplex last week. This new API is a thin wrapper around the native database engine, sqlite3.dll and exposes the key methods of the database engine through a modern Windows Runtime API callable from C#, Visual Basic and C++ that is all async methods and which reports unexpected results by throwing exceptions. The code is all up at https://sqlwinrt.codeplex.com. There’s no download for this project – you just download the source code, which is a complete Visual Studio 2012 solution containing projects for Windows Phone 8 and Windows 8 Store App components.

Why another database API on Windows Phone?

That’s a valid question! After all, we already have the Local Database in the Windows Phone SDK which is a LINQ to SQL API where the underlying database is SQL Server Compact Edition 3.7-ish. And we also have the SQLite-NET API for accessing SQLite which also exposes a LINQ API for working with a SQLite database. The Local Database LINQ to SQL API is not available for Windows 8 Store App development so not a great choice if you want to share code between your phone and tablet/PC apps, but SQLite-NET is available for both phone and Windows 8, as is SQLite-WinRT – so why do we need this new API?

SQLite-WinRT is for those developers who prefer to work with SQL statements rather than the object-oriented layer that LINQ gives you. It is also for those coming to Windows Phone 8 or Windows 8 from other platforms who have worked with SQLite before probably programming with C/C++ – the methods exposed by the SQLite-WinRT API are very thin wrappers around the SQLite C/C++ API. It’s all about giving choice to our developers – we have heard feedback that not all developers like to work with a local database using LINQ, so this API is for them.

To give you a comparison, this is how you might select a Customer from the Customer table using SQLite-NET:

 var db =  new SQLite.SQLiteAsyncConnection(App.DBPath); 
var _customer = await  (from c in db.Table<Customer>() 
                        where c.Id == customerId  
                        select c).FirstOrDefaultAsync(); 

if (customer != null) 
{  
    var Id = _customer.Id;
    var Name = _customer.Name;
}

And this is how you would do the same thing in SQLite-WinRT:

 using (var db = new SQLiteWinRTPhone.Database(
    ApplicationData.Current.LocalFolder, "demo.db")) 
{ 
    await db.OpenAsync(); 
    using (var stmt = await db.PrepareStatementAsync("SELECT name, age FROM people")) 
    { 
        while (await stmt.StepAsync()) 
        { 
            var name = stmt.GetTextAt(0); 
            var age = stmt.GetIntAt(1); 
        } 
    } 
}

The choice is yours!

Getting Started with SQLite-WinRT

Start by installing the SQLite Extension SDK. This is the native dll, sqlite3.dll, which contains the database engine. To install this, in Visual Studio, on the Tools menu, open Extensions and Updates. In the left hand pane, select Online – Visual Studio Gallery, then enter SQLite into the search box. Select SQLite for Windows Phone and click the Download button to download and install the vsix package. (The same procedure works for the SQLite for Windows Runtime extension for Windows 8 Store Apps.)

image

After you’ve installed that, the native sqlite3 dlls are installed into a folder under C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v8.0\ExtensionSDKs\SQLite.WP80\. That’s just for your information – you should never have to manually copy the sqlite3.dll from there in order to use the database

Warning: You will be prompted whenever an update is available for the SQLite database engine. When you install it, you will inadvertently ‘break’ SQLite-WinRT, in the sense that Visual Studio will be unable to open the project. That is because the project references the C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v8.0\ExtensionSDKs\SQLite.WP80\3.7.17 path in a number of places, and when you upgrade SQLite, the version part of that path will change.

I will be adding a powershell script into the download to make it easy to update the SQLite-WinRT project file with the correct path, but if this happens to you before I do it, just edit the SQLiteWinRTPhone.vcxproj file (carefully!) to correct references to the old version and update them to pojnt at whatever new version you have installed.

Creating your Project

Create your C# or Visual Basic project as normal. Then go to https://sqlwinrt.codeplex.com and download the source code from there – remember there is no ‘official download’ for this Codeplex project.

 

image

The source code for this project is a solution containing two projects, the SQLite-WinRT component for Windows Phone, and the same thing but for Windows 8 Store Apps. Copy the Windows Phone project and use ‘Add Existing Project…’ to add it into your solution, then add a reference to the SQLite-WinRT project from your C#/VB project. That’s all you have to do!

Remember, if Visual Studio cannot load the SQLite-WinRT project, it’s probably because the SQLite extension has been updated. Read the Warning a few paragraphs before to resolve this.

Creating a database

To create a database, you can use code such as this:

     private async void LoadDatabase()
    {
      // Get a reference to the SQLite database
      db = new SQLiteWinRTPhone.Database(ApplicationData.Current.LocalFolder, "sqlitedemo.db");

      await db.OpenAsync();

      string sql = @"CREATE TABLE IF NOT EXISTS
                                Customer (Id      INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                                          Name    VARCHAR( 140 ),
                                          City    VARCHAR( 140 ),
                                          Contact VARCHAR( 140 ) 
                            );";
      string description = "Create Customer table";
      await ExecuteSQLStatement(db, sql, description);

      sql = @"CREATE TABLE IF NOT EXISTS
                   Project (Id          INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                            CustomerId  INTEGER,
                            Name        VARCHAR( 140 ),
                            Description VARCHAR( 140 ),
                            DueDate     DATETIME,
                            FOREIGN KEY(CustomerId) REFERENCES Customer(Id) ON DELETE CASCADE 
                            )";
      description = "Create Project table";
      await ExecuteSQLStatement(db, sql, description);

      // Turn on Foreign Key constraints
      sql = @"PRAGMA foreign_keys = ON";
      description = "Enable foreign key constraints";
      await ExecuteSQLStatement(db, sql, description);

      DBLoaded.Set();
    }

    private static async Task ExecuteSQLStatement(
                                SQLiteWinRTPhone.Database db, string sql, string description)
    {
      try
      {
        await db.ExecuteStatementAsync(sql);
        Debug.WriteLine(description + " executed OK");
      }
      catch (Exception ex)
      {
        var result = SQLiteWinRTPhone.Database.GetSqliteErrorCode(ex.HResult);
        throw new ApplicationException(description + " Failed with error " + result);
      }
    }

You can call this code from your Application_Loaded and Application_Activated event handlers. One particular thing I want to call out here: notice the last line of the LoadDatabase method:

 DBLoaded.Set();

DBLoaded is a ManualResetEvent which is used to flag when the database opening logic has completed. It is declared at the top of my App.Xaml.cs class, along with a ‘Gatekeeper’ method called GetDatabaseAsync():

   public partial class App : Application
  {
    static SQLiteWinRTPhone.Database db;
    static ManualResetEvent DBLoaded = new ManualResetEvent(false);

    public static Task<SQLiteWinRTPhone.Database> GetDatabaseAsync()
    {
      return Task.Run(() =>
      {
        DBLoaded.WaitOne();
        return db;
      });
    }

The ManualResetEvent is created unset, and doesn’t get set until the last line of LoadDatabase(). All other database access code in my viewmodel classes makes a call to GetDatabaseAsync as the first thing it does. The DBLoaded.WaitOne() call in there blocks until DBLoaded is set, so this prevents any other database accessing code from executing until the LoadDatabase() method has completed.

Handling Errors

The SQLWinRT API throws exceptions when something goes wrong. Unfortunately, exceptions thrown in a WinRT component are not the most developer-friendly things ever devised. They are COMException objects, have no useful error text in them and just return to you a HResult. But you can get a meaningful error message if you call the handy GetSqliteErrorCode method which is used like this:

       try
      {
        await db.ExecuteStatementAsync(sql);
      }
      catch (COMException ex)
      {
        var result = SQLiteWinRTPhone.Database.GetSqliteErrorCode(ex.HResult);
        throw new ApplicationException(description + " Failed with error " + result);
      }

GetSqliteErrorCode() returns the – yes, you guessed it – SQLite error code, which mostly are self explanatory (such as ‘SQLITE_CONSTRAINT’) are documented at https://www.sqlite.org/c3ref/c_abort.html.

CRUD operations

Create

To create records in the database, use code similar to this:

 try 
{
    // Connection already opened in app.xaml.cs - get reference
    SQLiteWinRT.Database db = await App.GetDatabaseAsync();
    using (var custstmt = await db.PrepareStatementAsync(
      "INSERT INTO Customer (Name, City, Contact) VALUES (@name, @city, @contact)"))
    {  
        // NOTE that named parameters have a leading "@",":" or "$".         
        custstmt.BindTextParameterWithName("@name", customer.Name);
        custstmt.BindTextParameterWithName("@city", customer.City);
        custstmt.BindTextParameterWithName("@contact", customer.Contact);

        // Use StepAsync to execute a prepared statement 
        await custstmt.StepAsync();
     } 
} 
catch (System.Runtime.InteropServices.COMException) 
{ … }

This uses a SQL statement containing named parameters (@name, @city, @contact), which you prepare using PrepareStatementAsync() and which you ‘plug in’ the values using the BindtypeParameterWithName(string parametername, type value) method. An alternative is to use anonymous parameters (shown below when describing Update).

Notice that we use the StepAsync() method to execute a row-returning SQL statement. StepAsync may seem curious, but the SQLite C/C++ API method this wraps is called Step(), so that’s the name we adopt for our WinRT method.

IMPORTANT: Remember to dispose of your Statement objects when you’re done with them – a good way of doing that is to use the C# using statement, as shown above.

Read

To read objects, use a SELECT statement:

 public async Task<CustomerViewModel> GetCustomerAsync(int customerId)
{
    var db = await App.GetDatabaseAsync();
    CustomerViewModel customer = null;
    using (var readstmt = await db.PrepareStatementAsync(
              "SELECT Id, Name, City, Contact FROM Customer WHERE Id = " + customerId)) 
    {
      if (await readstmt.StepAsync() == true)
        {
       var customer = new CustomerViewModel()
              {
      Id = readstmt.GetIntAt(0),
                 Name = readstmt.GetTextAt(1),
            City = readstmt.GetTextAt(2),
          Contact = readstmt.GetTextAt(3)
        }; 
        }
    }
    return customers;
}

StepAsync() returns true if there is a row to return. The example above would reasonably be expected to return just a single row, but if you get multiple rows returned from the database, then you would loop around creating your in-memory objects (your viewmodel objects) and entering them into some collection until StepAsync() returns false.

An alternative form of SELECT allows you to extract data from the rowset returned from the database by using the column name, rather than the column position as the previous code sample did. You enable this behaviour by calling the Statement.EnableColumnsProperty() method. Then the results are returned as a Dictionary<string, string> which you key into using the column name:

     using (var readstmt = await db.PrepareStatementAsync(
              "SELECT Id, Name, City, Contact FROM Customer WHERE Id = " + customerId))
    {
         // Enable the columns property
         statement.EnableColumnsProperty();

       if (await readstmt.StepAsync() == true)
        { 
            // it isn't super useful as all columns are returned as text and must be parsed
            var columns = statement.Columns;
            var customer = new CustomerViewModel()
              {
      Id = int.Parse(columns["Id"]),
                 Name = columns["Name"],
            City = columns["City"],
          Contact = columns["Contact"]
        }; 
        }
    } 

This does impose a slight performance overhead, and every value returns as a string, so you have to parse the string to get the correct value type, as shown here for the Id column.

Update

Update is simple enough. This sample uses anonymous parameters in the SQL statement, rather then the named parameters we saw when discussing the Insert operation. Note that anonymous parameters are 1-based, not 0-based like just about every other collection in .NET!

 // See if the customer already exists
var existingCustomer = await GetCustomerAsync(customer.Id); 
if (existingCustomer != null) 
{    using (var custstmt = await db.PrepareStatementAsync(
        "UPDATE Customer SET Name = ?, City = ?, Contact = ? WHERE Id=?")) 
    { 
        // NOTE when using anonymous parameters the first has an index of 1, not 0. 
        custstmt.BindTextParameterAt(1, customer.Name);
   custstmt.BindTextParameterAt(2, customer.City);
    custstmt.BindTextParameterAt(3, customer.Contact);
    custstmt.BindIntParameterAt(4, customer.Id);
        await custstmt.StepAsync();
   }
}

Delete

Delete is simple enough:

   string sql = @"DELETE FROM Customer WHERE Id={0}"; 
  sql = string.Format(sql, customerId);

  // Can use ExecuteStatementAsync to run non row returning statements
  await App.db.ExecuteStatementAsync(sql);

Summary

That’s an introduction to getting started with SQLite-WinRT. In the next posts, I will talk some more about Foreign Key constraints and about working with Read-only databases.