Updated November 7, 2014 11:28 pm - This post was written by Dave Risney, Software Design Engineer in the Operating Systems Group
Note: There are two existing projects listed below that wrap SQLite in WinRT. You may be able to use these instead of writing your own wrapper. See if they expose the functionality you require and if their license works for you before writing your own. The solution discussed in this post came about specifically to avoid licensing issues.
- SQLite-WinRT on CodePlex: Read more about it in this SQLite-WinRT blog post.
- SQLite3-WinRT on GitHub: Read more about it on their github page.
- Install the SQLite Visual Studio extensions.
- Create a WinRT component universal apps project.
- Write the SQLite wrapper code.
- Write common app code to use the SQLite WinRT wrapper.
We’ll follow this with a look at the implementation of the sample app, a note on converting IndexedDB code to use SQLite, and other related resources.
For the sample app, we started with the IndexedDB samplefor Windows 8.1, made it a universal app, and followed the steps described below.
Install SQLite Visual Studio extensions
The SQLite Development Team has published a SQLite for Windows Runtime (Windows 8.1) Visual Studio extension, making it incredibly easy to add SQLite to your Windows 8.1 Store app solution. Follow the link above, press the Download link, and open the VSIX file to install the extension in Visual Studio.
Similarly, the SQLite Development Team has published a SQLite for Windows Phone 8.1 Visual Studio extension. Follow the same steps to install that extension as well.
Create a WinRT component universal apps project
Open your app’s solution in Visual Studio and add a new Windows Runtime Component (Universal Apps) project, found under Visual C++ > Store Apps > Universal Apps. This will add Windows, Windows Phone, and Shared projects to your solution for the new WinRT component.
Next, to use the new WinRT component you must add references from the app projects to the corresponding WinRT component projects. Add a reference from the app Windows 8.1 project to the WinRT component Windows 8.1 project, and from the app Windows Phone 8.1 project to the WinRT component Windows Phone 8.1 project.
Now the app can use the WinRT component, but the WinRT component projects still need references to the corresponding SQLite extensions. Add another pair of references, this time from the WinRT component Windows 8.1 project to the SQLite for Windows Runtime (Windows 8.1) extension and from the WinRT component Windows Phone 8.1 project to the SQLite for Windows Runtime (Windows Phone 8.1) extension. The extensions can be found in the Add Reference dialog under Windows (Phone) 8.1, Extensions.
Write the SQLite wrapper code
In our case, our app requires opening and closing a database, creating tables, inserting data, and querying data under transactions. The schema required for the sample app is very simple, so our WinRT wrapper contains only a Databaseobject that can open and close a database and execute SQL statements. To make it easy to insert data without worry about escaping, we support binding parameters to my SQL statements. To obtain queried data, we return an array of row objects from our execute method. All of our methods are asynchronous so they don’t block our app’s UI thread while using the database.
Your own project may well require wrapping additional SQLite APIs; our example is a simple demonstrative sample that doesn’t take advantage of advanced SQLite features.
Sample app implementation details
Here’s an overview of the various methods in the WinRT SQLite wrapper we’ve made in the sample app.
The SQLite API is written in C and primarily uses UTF-8 char* strings and return values to report errors. WinRT, on the other hand, generally uses UTF-16 Platform::String types and reports errors via exceptions. In the util.* files we implement ValidateSQLiteResult which turns error codes returned from SQLite functions into WinRT exceptions, or passes the return value through for non-error results. Also in util.* are two functions to convert between UTF-8 std::string types we can use with the SQLite APIs and UTF-16 Platform::Stringtypes that we can use with C++/CX and WinRT.
In the Database.* files we implement the Database WinRT class which has only a handful of methods. Here’s the class definition from Database.h:
The static OpenDatabaseInFolderAsync method is the only public mechanism to create a Database object. It returns an IAsyncOperation<Database^>^ meaning it is an asynchronous method and provides the newly created or opened Database object as its asynchronous result. In the implementation we ensure that the SQLite temporary path is configured as described in SQLite documentation, and then we wrap sqlite3_open_v2 using the functions from util.*. We implement the asynchronous operation using PPL create_async. From Database.cpp here’s the definition of OpenDatabaseInFolderAsync:
Database::ExecuteAsync is also asynchronous, this time returning IAsyncOperationWithProgress< IVector<ExecuteResultRow^>^, ExecuteResultRow^>, in which the asynchronous result is a vector of any ExecuteResultRows queried by the executed SQL statement and additionally provides progress notifications containing the same queried rows but provided as soon as they are selected and one at a time. We wrap sqlite3_exec, which is made slightly complicated by sqlite3_exec’s callback parameter mechanism used to provide the queried rows. From Database.cpp, here’s the definition of ExecuteAsync and the callback function provided to sqlite3_exec SQLiteExecCallback:
In order to support SQL parameter binding we also implement Database::BindAndExecuteAsync, which has the same return value as Database::ExecuteAsync but accepts an additional parameter that is a vector of strings that should be bound to the SQL statement. One interesting note is that the IVector<String^>^ parameter is tied to the calling thread and so instead we create a copy of the list of strings but as a std::vector<String^>. This we capture in our create_async lambda and can use on another thread. Because sqlite3_exec doesn’t support parameter binding we cannot use that convenience function and instead we perform the sqlite3_prepare, sqlite3_bind, sqlite3_step, and sqlite3_finalize sequence explicitly. Here’s the definition of BindAndExecuteAsync from Database.cpp:
In the ExecuteResultRow.* files we implement ExecuteResultRow and ColumnEntry that contain the results of queries to the database. However these all exist simply to expose this data in a reasonable way to the WinRT caller, and there’s no actual interaction with SQLite APIs here. The most interesting aspect of ExecuteResultRow is merely in how it is used by the Database::*ExecuteAsyncmethods.
The runPromisesInSerial function takes an array of promises and ensures they run one after another to make it easy to run a series of asynchronous ExecuteAsync commands.
The executeAsTransactionAsync function starts a transaction, runs the function provided by the caller, and then ends the transaction. The only interesting aspect is that the function is asynchronous and to end the transaction we must intercept both asynchronous success and failure of that function, end the transaction, but be sure to still return the same success result or throw the same error value.
The executeStatementsAsTransactionAsync and bindAndExecuteStatementsAsTransactionAsync combine the previous two functions to make it easy to provide an array of strings containing SQL statements that should be executed serially and as a transaction.
Later you can see these functions are used to easily execute a list of SQL statements asynchronously and in serial:
Moving from IndexedDB to SQLite
It may be the case that you have an existing Windows 8.1 app that uses IndexedDB and you want to make into a universal Windows app. To do this, after completing the steps we describe above, you’ll need to change your app code from using IndexedDB to using the WinRT SQLite wrapper.
Accordingly, the converted code in the sample app looked very different from the original IndexedDB sample app. Generally we were able to maintain the high-level goal of what each database-related method in the sample app did, but it was not a totally straightforward conversion. If you have a lot of existing IndexedDB code you can consider writing your WinRT wrapper so that its interface more closely resembles that of IndexedDB. Hopefully your app’s database code is well separated from the rest of your app or easy to convert.