SQLite WinRT wrapper for Windows Phone

SQLite WinRT wrapper for Windows Phone

  • Comments 6
  • Likes

The SQLite WinRT wrapper is a CodePlex project that offers lightweight Windows Runtime (WinRT) APIs you can use to access the most common SQLite database functionality. The latest update of the library supports Windows Phone 8 development in addition to Windows 8 development. Download the update to begin accessing SQLite databases in your Windows Phone 8 apps.

Supported functionality

The WinRT API gives you a familiar programming model for implementing the following basic operations:

All operations follow the async pattern – they perform operations on a worker thread, which helps make your database apps fast and fluid. The wrapper also supports exception-based programming, so you don’t need to check the return value from every method call. Note that the API doesn’t support all of the advanced features of SQLite, nor does it attempt to hide the underlying SQL statements, untyped result sets, and so on. It’s designed to provide a very thin wrapper over the SQLite functionality that many people already know and love, rather than to provide an equivalent programming model to LINQ-to-SQL or ADO.NET.

Examples

Here’s a simple example of opening a database named cities.db (in the app’s local folder) and then adding the contents of the Cities table to an ObservableCollection. You can then databind the collection to a ListBox or other control.

C#
  1. async void AddToItemsCollection()
  2. {
  3.     // Get the file from the local folder
  4.     var file = await
  5.       ApplicationData.Current.LocalFolder.GetFileAsync("cities.db");
  6.  
  7.     // Create a new SQLite instance for the file
  8.     var db = new Database(file);
  9.  
  10.     // Open the database asynchronously
  11.     await db.OpenAsync(SqliteOpenMode.OpenRead);
  12.  
  13.     // Prepare a SQL statement to be executed
  14.     var statement = await db.PrepareStatementAsync(
  15.       "SELECT rowid, CityName FROM Cities;");
  16.  
  17.     // Loop through all the results and add to the collection
  18.     while (await statement.StepAsync())
  19.         items.Add(statement.GetIntAt(0) + ": " + statement.GetTextAt(1));
  20. }

Performing a more complex query also is easy – instead of the simple PrepareStatementAsync line in the preceding example, you can use a WHERE clause and a bound parameter (the question mark) to look for cities beginning with the letter ‘c’:

C#
  1. // Prepare a SQL statement to be executed with a parameter
  2. var statement = await db.PrepareStatementAsync(
  3.   "SELECT rowid, CityName FROM Cities WHERE CityName LIKE ?;");
  4.  
  5. // Bind the parameter value to the statement
  6. statement.BindTextParameterAt(1, "c%");
  7.  
  8. // Loop through all the results and add to the collection
  9. while (await statement.StepAsync())
  10.   items.Add(statement.GetIntAt(0) + ": " + statement.GetTextAt(1));

Using bound parameters (instead of something like String.Format) provides a much more secure way of accessing a database that is resilient against SQL injection attacks. In addition to simple numbered parameters (as used above), you can use named parameters as well.

The API also supports collection-based access to the returned result, but it’s not enabled by default because it slows down processing and returns all columns as strings (rather than their underlying type). To use this feature, call the EnableColumnsProperty method like this:

C#
  1. // Prepare a SQL statement to be executed with a parameter
  2. var statement = await db.PrepareStatementAsync(
  3.   "SELECT rowid, CityName FROM Cities;");
  4.  
  5. // Enable access via the 'Columns' property. If you don't do this then
  6. // the Columns property will simply return null
  7. statement.EnableColumnsProperty();
  8.  
  9. // Now you can access the columns by name (they will return strings)
  10. while (await statement.StepAsync())
  11.   items.Add(statement.Columns["rowid"] + ": " + statement.Columns["CityName"]);

Read-only databases

Many apps contain read-only databases - for example, the list of cities used in the example here, or a list of product categories. These databases typically are deployed to the app’s install folder instead of to isolated storage. This can cause problems if SQLite needs to create temporary tables, indices, or logs: SQLite attempts to create the files in the same directory as the database, but the app doesn’t have write access to its installation folder. (See the debugging note at the end of this post.)

You have two options for getting around this problem. The first is to use the temp_store pragma to force SQLite to use in-memory temporary tables and indices. This option is the easiest to use but might not be practical if it causes your app to use too much memory. The second option is to copy the database from the install folder to the isolated storage folder on the first run of your app. This uses more disk space and adds time to your first-boot experience, but if the database file is small it shouldn’t cause a noticeable delay. This also gives you the option of updating the database if you ever need to (after all, new product categories are created all the time!).

Another feature that needs to be avoided with read-only databases is the journal_mode pragma with a value of WAL (write-ahead logging). Although this has some performance benefits, it will also fail for databases in the app’s install folder since SQLite will attempt to create a journal file there (and fail).

Debugging note. During the development process, Windows Phone gives your app write access to its installation folder to facilitate some debugger operations. Developers typically aren’t affected by this because they’re already conditioned not to write files to the install folder, but it can cause issues if you use a read-only database and forget to set the temp_store pragma. The app will appear to work during your pre-submission testing, but as soon as you submit it to the Store the app will fail because it no longer has write access to the folder.

6 Comments
You must be logged in to comment. Sign in or Join Now
  • that is cool, I was actually starting  aPOC and was wondering solution for local storage like this. Thks

  • Adding to Andy's comment: One of the differences between this wrapper and sqlite-net-wp8 is that this wrapper provides a "Modern" API surface area around the core SQLite engine (objects, exceptions, async) whereas sqlite-net-wp8 exposes the raw C-style API (basically a work-around for the lack of P/Invoke) that is needed by sqlite-net. This wrapper is also very small (vs. sqlite-net) so should be easy to incrementally add features that you might need in your own apps. The great news is that developers get to choose which approach they prefer!

  • To find out more about programming with the SQLite WinRT wrapper, my session at TechEd North America is a deep dive on this. Details at http://bit.ly/11FI54K, which is where the recording will be posted after the event.

  • To answer on Peter's behalf: this wrapper is for those developers who prefer to work with SQL statements, and using an API that is very close to the SQLite C/C++ API.

    We hear feedback from some developers that they would prefer to use SQL statements instead of working with LINQ. This wrapper meets that need. If you prefer LINQ, then yes, the github.com/.../sqlite-net-wp8 solution is right for you.

    The Local Database API in the Windows Phone 8 SDK (LINQ to SQL over a SQL CE database) is still a supported solution, but as you point out akshay2000, it is not supported on Windows 8. So if you want a database solution that works on both, the SQLite is for you, either using the SQLite-NET LINQ solution, or this SQLiteWinRT solution.

  • I'm not sure why do I want this. Windows Phone already supports local databases in form of SQL CE, right? It made sense on Windows 8, because there was no official solution provided for local databases. Post should really highlight the advantages of this approach over the recommended approach.

  • Looks great, Peter. Really simple to get going. That said, definitely not enough, at least for apps like ours at http://hiddenpineapple.com . We've been using this lately and while it still lacks in a few areas, it's been really great overall! github.com/.../sqlite-net-wp8