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 http://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 http://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);

                   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);


    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 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(() =>
        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:

        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 http://www.sqlite.org/c3ref/c_abort.html.

CRUD operations


To create records in the database, use code similar to this:

    // 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.


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

       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 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 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);


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 (16)

  1. Peter says:

    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.).

  2. Nii Laryea says:

    Andy, what do you say to this guy? He seems to have a valid point!

  3. Madhu Kumar says:

    Will this work for only string or double format also? I am struggling a lot to work for double format with this.

  4. Thomas says:

    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.

  5. Rohit says:


    I want to use SQLite bulk insert, can any one gide me please?

  6. Enzo Contini says:

    I tried to run your SqlitePhoneSample because I'm evaluating to use your wrapper: however I have some problem!

    Because the SQLite.WP80 is no more at the release 3.7.17 but at the release 3.8, compiling your example there is the following error:"The imported project "C:Program Files (x86)Microsoft SDKsWindows Phonev8.0ExtensionSDKsSQLite.WP803.7.17DesignTimeCommonConfigurationNeutralSQLite.WP80.props" was not found."

    So I downloaded from the codeplex repository the latest version of the sqlite-winrt. Then I copied from tsqlwinrt_49f8079f900dSQLiteWinRTPhone the script Update_Project_SQLite_SDK_References.ps1 into the directory 444.SqlitePhoneSample_195D42FBSQLiteWinRTPhone of your example and I run it with powerShell. At this point the compilation was OK.

    However I saw tha the newest release from codeplex I downloaded (sqlwinrt.codeplex.com/…/latest) is more recent from the one in the example (5/10/2013 instead of 24/5/2013) and it has also some more files (ex. NativeBuffer.h).

    So I substituite all the SQLiteWinRTPhone directory of your example with the analogous of the last codeplex project. Then compiling I have the error:"Error 1 The type or namespace name 'SQLiteWinRTPhone' could not be found (are you missing a using directive or an assembly reference?) C:DatiprojectsLocal DBsqlite-winrt5444.SqlitePhoneSample_195D42FBSqliteUsingSQLiteWinRTViewModelsTableViewModelBase.cs 14 52 SqliteUsingSQLiteWinRT."

    I tried also to to delete the SQLiteWinRTPhone reference in the SqliteUsingSQLiteWinRT project and recreate the reference to the solution project SQLiteWinRTPhone.vcxproj. The same error.

    So I tried to run the script Update_Project_SQLite_SDK_References.ps1 present in the SQLiteWinRTPhone folder. After that I have the following error: "C:DatiprojectsCODEPLEXsqlwinrt_49f8079f900dSQLiteWinRTPhoneSQLiteWinRTPhone.vcxproj(167,3): The project file could not be loaded. '<', hexadecimal value 0x3C, is an invalid attribute character. Line 167, position 3."

    At this point I wonder if the latest version of sqlite-winrt downloadable from sqlwinrt.codeplex.com/…/latest is still working for WindowsPhone … and if this wrapper will be supported also in the future for Windows Phone!

    Please let me know as soon as you can because I need to make a decision on what kind of solution I have to use to access a local DB on muy WP8 app.

    Kind regards

    Enzo Contini

  7. Bruno Vieira says:

    It was the only wrapper for SQLite that worked (almost) out of the box in Visual Studio Express 2013 and Windows 8.1. It gets the job done and it would be a pity if the devs turn this out

  8. Jerry Problem with VB says:

    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

  9. Jerry Horton says:

    Hi, I got the code working with VB , the only problem is that the data in the SQLite file will not persist in the emulator from execution to execution and I am not turning the emulator off. Your example shows the case where the file is created – does the code work for saving the file in the emulator and executing over again.

    I did get data to persist if I use file stream IO.

  10. Jerry Horton says:

    I did get over the persisting of data problem , but I cannot figure the work around for reads the wrapper does , reading the data base requires two await statements – one for the prepare and one for the step , once I execute a read , my code does not pause to wait until the reads are done , it continues on , I need to wait until all the reads are done and all the data has been retrieved before going on , how do I do that?


  11. Jitendra kushwah says:

    Thank you…It is very useful for me now i'm trying to create a new and want to use that……

  12. hrmph says:

    y no linq?

  13. Andy Wigley says:

    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.

  14. Question on code sample says:

    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.

  15. Sanjay says:


    Every time i insert as

    await 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