SQLite WinRT Wrapper with Lightweight APIs
SQLite WinRT Wrapper for Windows Phone 8 provides an ability to access the SQLite database functionality using lightweight Windows Runtime (WinRT) APIs. The wrapper provides functions for various database actions as listed below
- 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
The above mentioned operations follow the async pattern that means they perform operations on a worker thread, which helps make your database apps fast and fluid. The wrapper also provide support for exception based programming and is mainly designed to provide a very thin wrapper over the SQLite functionality.
SQLite WinRT wrapper 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. In order to make use of this feature, you should call EnableColumnsProperty() method.\
var statement = await db.PrepareStatementAsync("SELECT rowid, CityName FROM Cities;");
In order to fix the problems associated with read only databases, you can either make use of temp_store pragma to force SQLite to use in-memory temporary tables and indices or copy the database from the install folder to the isolated storage folder on the first run of your app. While the first option is the easy to use, the second option uses more disk space and will take little time to initiate the first boot. However, you will not notice any undue delay if the database file is small. \
"Another feature that needs to be avoided with read-only databases is the journal_mode pragma with a value of WAL (write-ahead logging)," said Peter Torr, Program Manager, Windows Phone Developer Platform.
Shane Hastie on Distributed Agile Teams, Product Ownership and the Agile Manifesto Translation Program
Shane Hastie Apr 17, 2015