SQLite WinRT wrapper for Windows Phone

SQLite WinRT wrapper for Windows Phone:
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:

• Opening a database (wraps the sqlite3_open_v2 function)
• Executing a single SQL statement (wraps the sqlite3_exec function)
• Creating a complex SQL statement (wraps the sqlite3_prepare16_v2 function)
• Binding parameters to a statement (wraps the sqlite3_bind_xyz functions)
• Iterating over statement results (wraps the sqlite3_step and sqlite3_column_xyz functions)

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.

DIGITAL JUICE