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:
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.)
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.
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.
Comments
- Anonymous
June 06, 2013
Very Helpful - Anonymous
June 25, 2013
Message to all Devs: Don't use this wrapper. It will be dead in no time. Even MSFT devs like Tim Heuer prefer the SQLite wrapper made by the Mono Team. I can't see why this mediocre wrapper will be well maintained (bug-fixes, new features.). - Anonymous
July 22, 2013
Andy, what do you say to this guy? He seems to have a valid point! - Anonymous
July 24, 2013
Will this work for only string or double format also? I am struggling a lot to work for double format with this. - Anonymous
August 04, 2013
I would use this wrapper if it would not have horrible performance :(Either the wrapper is bad or the whole OS has some serious issues. - Anonymous
September 28, 2013
Hi,I want to use SQLite bulk insert, can any one gide me please? - Anonymous
October 14, 2013
The comment has been removed - Anonymous
October 21, 2013
The comment has been removed - Anonymous
November 13, 2013
Hi , I have implemented this solution in my VB project , I now have two projects in my solution - one containing the wrapper in c# and one my vb project , I also have the reference to Windows Phone SQLite correctly references - problem is that when I try to convert the C# examples to vb , my vb project cannot reference database , I do not have an import for referring to the C# wrapper - how do I use the wrapper with vb? thank you by the way , this is very useful if I can get it to work - Anonymous
November 17, 2013
The comment has been removed - Anonymous
November 20, 2013
The comment has been removed - Anonymous
March 24, 2014
Thank you...It is very useful for me now i'm trying to create a new and want to use that...... - Anonymous
May 13, 2014
y no linq? - Anonymous
May 14, 2014
Sorry I've been slow to answer some of these.Yes, I support this on 'best effort' basis - but it's open source. You can get all the code on CodePlex. The 'support' aspect of it is more about helping people who are having any problems using it more than the complexity of the wrapper itself (there's very little complexity - it's a simple wrapper around the SQLite C++ API).I will post here again soon with more news about this wrapper and updates for Windows Phone 8.1.hrmph - there's no Linq support because SQLite-net does that. This was always intended as an alternative. - Anonymous
October 12, 2014
Hi Andy, question on your above code example.Under "CRUD OPERATIONS", you have this statement:SQLiteWinRT.Database db = await App.GetDatabaseAsync();Shouldn't that be SQLiteWinRTPhone.Database db = await App.GetDatabaseAsync(); ?I see the SQLiteWinRTPhone name in the remainder of the sample. - Anonymous
February 25, 2015
Hi,Every time i insert asawait db.ExecuteStatementAsync(str_statement);it gives Exception An exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.ni.dll and wasn't handled before a managed/native boundary)But when i try same query in mozilla sqlite manager it works for same db.Could not understand why