Tag Archives: SQLite

Working with SQLite in UWP

*Post will be updated with explanation soon!*

The following samples have been tested on Desktop, Mobile and IoT (Raspberry Pi 2). They require that you have the 10586 SDK installed (Visual Studio 2015 Update 1 or later).

UWP using Entity Framework 7 – use SQLite for persistent storage: EF7Sample

UWP using SQLite-NET, second example using SQLite-WinRT NuGet.
The SQLite-NET sample also demonstrates accessing a SQLite database from a background task (Click the Background Task link on the command bar menu to navigate to a page where you can register and signal (i.e. start) the background task).
*Check the SQLite Extension SDK references in the foreground projects. The samples were built with 3.10.0 but if you have a different SQLite version installed, you will need to remove and then re-add the SQLite extension reference*
SQLiteSamplesUWP

How to massively improve SQLite Performance (using SqlWinRT)

Use ConfigureAwait(false) on calls to SqlWinRT async methods to improve INSERT performance up to 145 times, and SELECT performance up to 80 times 

Sample project: SQLitePerformance.zip

I have been seeing a few comments by people complaining of poor performance when executing SELECT statements against a SQLite database using the SQLWinRT wrapper, or when adding new records using multiple consecutive INSERT statements.

With many relational databases, poor performance on INSERT can often be rectified just by wrapping all your operations within a single transaction. If you don’t explicitly declare a transaction when programming against SQLite, the database engine creates an implicit transaction for each statement execution and that has an overhead associated with it, so by wrapping all your INSERTs inside a single transaction, you would expect a performance boost.

With SELECT operations, poor performance is often just down to not having the correct keys defined. If you have a statement such as SELECT * FROM CUSTOMER WHERE NAME = ‘CONTOSO’ and you execute it on a database where you have not defined a key on the NAME column, the only way the database engine can find the required record is to start at the first record and read sequentially down the table until it reaches the end in order to select the rows to return (called a ‘Table Scan’). If you simply want to select all the records in a table (SELECT * FROM CUSTOMER), then you can’t avoid the Table Scan – in fact that’s what you want – so you would expect that there wasn’t very much you could do to improve performance in that case.

Well – that’s the theory. I set about building a simple demo to demonstrate these points, but it didn’t turn out quite how I expected! It turns out that, important though the points already mentioned are, the most important factor BY FAR is how you call the async methods of the SQLWinRT API! Read on…

The Test Program

For my test program, I was inspired by this post on Stack Overflow: How do I improve the performance of SQLite. That post is quite old and is talking about C++ dev, so not directly relevant to Windows Phone or Windows Store app development using managed code, but I liked the test case, which was to take a large dataset from the City of Toronto’s publically available transportation data. Unfortunately, the exact same dataset used in the original post isn’t available anymore, so I couldn’t do a direct comparison, but they did have the Transportation Trips dataset, containing 128982 records that we can first insert into a SQLite database, and then select them out again.

SQLite Bulk Insert

The dataset is in the form of a CSV text file, representing 128982 rows, each containing 8 columns. The code to read the file and extract the values  looks like this:

SQLiteWinRT.Database db;

private async void Button_Start_Click(object sender, RoutedEventArgs e)
{
    int Route_ID;
    int Service_ID;
    int Trip_ID;
    string Trip_Headsign;
    int Direction_ID;
    int Block_ID;
    int Shape_ID;
    bool Wheelchair_Accessible;

    Button_Start.IsEnabled = false;

    /*********************************************/
    /* Open the Database and create the Schema */
    await LoadDatabase();

    Stopwatch sw = Stopwatch.StartNew();
    int n = 0;

    // Prepare statement
    using (var statement = await db.PrepareStatementAsync(
        @"INSERT INTO TTC (id, Route_ID, Service_ID, Trip_ID, 
                           Trip_Headsign, Direction_ID, Block_ID, 
                           Shape_ID, Wheelchair_Accessible) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"))
    {
        // Start transaction
        await db.ExecuteStatementAsync("BEGIN TRANSACTION");

        /*********************************************/
        /* Open input file and import into Database*/
        var inputfile = await Windows.ApplicationModel.Package.Current.
            InstalledLocation.GetFileAsync("trips.txt");
        using (var inputStream = await inputfile.OpenSequentialReadAsync())
        {
            using (StreamReader rdr = 
                new StreamReader(inputStream.AsStreamForRead()))
            {
                while (true)
                {
                    string inputline = await rdr.ReadLineAsync();
                    if (inputline == null)
                        break;

                    // Discard line 0 - header line
                    if (n > 0)
                    {
                        string[] fields = inputline.Split(new char[] { ',' });

                        Route_ID = Int32.Parse(fields[0]); ;
                        Service_ID = Int32.Parse(fields[1]);
                        Trip_ID = Int32.Parse(fields[2]);
                        Trip_Headsign = fields[3];
                        Direction_ID = Int32.Parse(fields[4]);
                        Block_ID = Int32.Parse(fields[5]);
                        Shape_ID = Int32.Parse(fields[6]);
                        Wheelchair_Accessible = fields[7] == "1" ? true : false;

                        /* Bind parameter values and Insert */
                        statement.Reset();
                        statement.BindIntParameterAt(1, n);
                        statement.BindIntParameterAt(2, Route_ID);
                        statement.BindIntParameterAt(3, Service_ID);
                        statement.BindIntParameterAt(4, Trip_ID);
                        statement.BindTextParameterAt(5, Trip_Headsign);
                        statement.BindIntParameterAt(6, Direction_ID);
                        statement.BindIntParameterAt(7, Block_ID);
                        statement.BindIntParameterAt(8, Shape_ID);
                        statement.BindIntParameterAt(9, Wheelchair_Accessible ? 1 : 0);

                        await statement.StepAsync();
                    }

                    n++;

                    if (n % 100 == 0)
                    {
                        progressBar.Value = n;
                    }
                }
            }
        }

        // Commit transaction
        await db.ExecuteStatementAsync("COMMIT TRANSACTION");
    }

    db.Dispose();
    db = null;

    var report = String.Format(
        "Inserted {0:d} records in {1:0.00} seconds", 
        n - 1, 
        (double)sw.ElapsedMilliseconds / 1000);
    MessageBox.Show(report);

    Button_Start.IsEnabled = true;
}

private async Task LoadDatabase()
{
    db = new SQLiteWinRT.Database(
        ApplicationData.Current.LocalFolder, "sqliteperf.db");

    await db.OpenAsync();

    string sql = @"
        CREATE TABLE IF NOT EXISTS TTC 
            (id INTEGER PRIMARY KEY, 
                Route_ID INTEGER, 
                Service_ID INTEGER, 
                Trip_ID INTEGER, 
                Trip_Headsign TEXT,
                Direction_ID INTEGER, 
                Block_ID INTEGER, 
                Shape_ID INTEGER, 
                Wheelchair_Accessible BOOL)";

    await db.ExecuteStatementAsync(sql);
}

Using this code, and by commenting out lines here and there as appropriate, we can test three scenarios:

  • Processing the input file but not inserting records in the database (this just gives us the time for the file processing)
  • Uncomment the call to StepAsync() to Insert each record into the database individually
  • Uncomment the lines executing the BEGIN TRANSACTION, END TRANSACTION statements so all records are inserted inside a single transaction

testrunner  Transaction

And the results? Well, not great, to be honest:

Test Total Time (seconds) – 128982 records Insert Time per record (ms)
Process records – no insert 7
Insert records individually 6307 49
Insert records within a containing transaction 3177 25

Although wrapping all the inserts into a single transaction halves the time it takes to insert the 128982 records compared to separate operations (where the database engine will activate an implicit transaction for every insert), we are still only getting 25ms per insert, or 40 operations per second. That’s not too impressive.

Trying ConfigureAwait(false)

Those performance figures are disappointing and make bulk insert operations in a Windows Phone or Windows Store app impractical. However, look what happens when we make a subtle change to the main loop of code:

Stopwatch sw = Stopwatch.StartNew();
int n = 0;

// Prepare statement
using (var statement = await db.PrepareStatementAsync(
    @"INSERT INTO TTC (id, Route_ID, Service_ID, Trip_ID, Trip_Headsign, 
                        Direction_ID, Block_ID, Shape_ID, Wheelchair_Accessible) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"))
{
    // Start transaction
    await db.ExecuteStatementAsync("BEGIN TRANSACTION");

    /*********************************************/
    /* Open input file and import into Database*/
    var inputfile = await Windows.ApplicationModel.Package.Current.
        InstalledLocation.GetFileAsync("trips.txt");
    using (var inputStream = await inputfile.OpenSequentialReadAsync())
    {
        using (StreamReader rdr = new StreamReader(inputStream.AsStreamForRead()))
        {
            while (true)
            {
                string inputline = await rdr.ReadLineAsync();
                if (inputline == null)
                    break;

                // Discard line 0 - header line
                if (n > 0)
                {
                    string[] fields = inputline.Split(new char[] { ',' });

                    Route_ID = Int32.Parse(fields[0]); ;
                    Service_ID = Int32.Parse(fields[1]);
                    Trip_ID = Int32.Parse(fields[2]);
                    Trip_Headsign = fields[3];
                    Direction_ID = Int32.Parse(fields[4]);
                    Block_ID = Int32.Parse(fields[5]);
                    Shape_ID = Int32.Parse(fields[6]);
                    Wheelchair_Accessible = fields[7] == "1" ? true : false;

                    /* Bind parameter values and Insert */
                    statement.Reset();
                    statement.BindIntParameterAt(1, n);
                    statement.BindIntParameterAt(2, Route_ID);
                    statement.BindIntParameterAt(3, Service_ID);
                    statement.BindIntParameterAt(4, Trip_ID);
                    statement.BindTextParameterAt(5, Trip_Headsign);
                    statement.BindIntParameterAt(6, Direction_ID);
                    statement.BindIntParameterAt(7, Block_ID);
                    statement.BindIntParameterAt(8, Shape_ID);
                    statement.BindIntParameterAt(9, Wheelchair_Accessible ? 1 : 0);

                    await statement.StepAsync().AsTask().ConfigureAwait(false);
                }

                n++;
                if (n % 100 == 0)
                {
                    Dispatcher.BeginInvoke(()=>progressBar.Value = n);
                }
            }
        }
    }

    // Commit transaction
    await db.ExecuteStatementAsync("COMMIT TRANSACTION");
}

db.Dispose();
db = null;

Dispatcher.BeginInvoke(() =>
    {
        var report = String.Format(
            "Inserted {0:d} records in {1:0.00} seconds", 
            n - 1, (double)sw.ElapsedMilliseconds / 1000);
        MessageBox.Show(report);

        Button_Start.IsEnabled = true;
    });
}

The main – and significant change above – is that the StepAsync() method is now called in a different way:

await statement.StepAsync().AsTask().ConfigureAwait(false);

In addition, the code that ‘touches’ the UI, such as the MessageBox.Show() call, and the code setting the ProgressBar is now called using Dispatcher.BeginInvoke(Action a). This is necessary because the change to how StepAsync() is called has a knock-on effect of causing the code to end up executing on a background thread – I will explain more on this shortly.

What does this do to the timings?:

Test Total Time (seconds) – 128982 records Insert Time per record (ms)
Process records – no insert 7
Insert records individually 6307 49
Insert records within a containing transaction 3206 25
Insert individually with ConfigureAwait(false) 2791 21
Insert within a transaction with ConfigureAwait(false) 55 0.43

TransactionWithConfigureAwait

WHOA! What happened there? If you insert records within a transaction and use ConfigureAwait(false), you can achieve insert performance of one insert every 0.43 ms, or 2345 records per second. Now we’re talking! That means the performance of inserting records individually without using ConfigureAwait(false) is more than 144 times slower than the best case.

Why such a dramatic improvement? Well, the Task.ConfigureAwait(bool continueOnCapturedContext) method is used to configure the awaiter used to await the result of the asynchronous operation. An awaiter is an object you don’t see or program directly as a developer, but simply by using the await keyword when calling an asynchronous method, the compiler will generate code that uses an awaiter to handle the suspension and reactivation of your calling method while it waits for some asynchronous operation to complete. When the asynchronous method has completed, the awaiter resumes execution of your code which by default happens on the same context (think ‘thread’) you were on when you called the async method. But if you set ConfigureAwait(false), it doesn’t do that but instead continues on whatever context the async method executed on. There’s a performance advantage to this, which is why the advice to authors of library code is that you should always make async calls within your library code using ConfigureAwait(false) – the caller of your library method can then make the decision on whether to return to their original context by choosing whether or not to use ConfigureAwait(false) on the call to your async library method.

Which is what we are doing here – by setting ConfigureAwait(false) on our call to SqlWinRT:Statement.StepAsync() we are saying “don’t bother continue on the originating context, stay on whatever context you are executing on”. And as you can see, the performance gain is spectacular! To be honest, I was very surprised just how spectacular the performance improvement is. Normally, you would expect a modest performance gain, but I guess that because this call is happening between managed code and a WinRT component, the overhead of ConfigureAwait(true) – the default – is particularly significant.

One consequence of using ConfigureAwait(false) is that our code after the await call resumes on a background thread, so when we want to interact with the UI, such as to set the ProgressBar or to display the message at the end, we have to use the Dispatcher to make sure the code that touches the UI executes back on the UI thread:

Dispatcher.BeginInvoke(() =>
    {
        var report = String.Format("Inserted {0:d} records in {1:0.00} seconds", 
            n, (double)sw.ElapsedMilliseconds / 1000);
        MessageBox.Show(report);

        Button_Start.IsEnabled = true;
    });

If we don’t do that, you get an exception.

SELECT Performance

What about the performance of reading records out of the database? The warnings I gave in the introduction to this piece about having appropriate keys configured if you are doing a SELECT with a filter on a column that is not the primary key are still valid. But even if you are simply reading all the records out of a table, whether you use ConfigureAwait(false) again has a huge impact.

Here’s the code you would probably write to read all the rows in our table and to load them into in-memory objects in an ObservableCollection<T> ready to display on the UI:

private async void Button_Select_Click(object sender, RoutedEventArgs e)
{
    if (db == null)
    {
        db = new SQLiteWinRT.Database(
            ApplicationData.Current.LocalFolder, "sqliteperf.db");

        await db.OpenAsync();
    }


    Stopwatch sw = Stopwatch.StartNew();
    int n = 0;
    Button_Select.IsEnabled = false;

    string sql = @"
        SELECT
            id , 
                Route_ID , 
                Service_ID, 
                Trip_ID, 
                Trip_Headsign,
                Direction_ID, 
                Block_ID, 
                Shape_ID, 
                Wheelchair_Accessible
            FROM TTC";
    var stmt = await db.PrepareStatementAsync(sql);

    ObservableCollection<TransportationTrip> tripCollection =
        new ObservableCollection<TransportationTrip>();

    while (await stmt.StepAsync())
    {
        var trip = new TransportationTrip()
        {
            ID = stmt.GetIntAt(0),
            RouteID = stmt.GetIntAt(1),
            ServiceID = stmt.GetIntAt(2),
            TripID = stmt.GetIntAt(3),
            TripHeadsign = stmt.GetTextAt(4),
            DirectionID = stmt.GetIntAt(5),
            BlockID = stmt.GetIntAt(6),
            ShapeID = stmt.GetIntAt(7),
            WheelchairAccessible = 
                stmt.GetIntAt(8) == 1 ? true : false,
        };

        tripCollection.Add(trip);
        n++;
    }

    var report = String.Format("Selected {0:d} records in {1:0.00} seconds",
        n, (double)sw.ElapsedMilliseconds / 1000);
    MessageBox.Show(report);

    Button_Select.IsEnabled = true;
}

Running this gives the following result:

SELECT-no-Await

2838 seconds! That sucks – big time.

Then do it again, but make these subtle changes:

while (await stmt.StepAsync().AsTask().ConfigureAwait(false))
{
    var trip = new TransportationTrip()
    {
        ID = stmt.GetIntAt(0),
        RouteID = stmt.GetIntAt(1),
        ServiceID = stmt.GetIntAt(2),
        TripID = stmt.GetIntAt(3),
        TripHeadsign = stmt.GetTextAt(4),
        DirectionID = stmt.GetIntAt(5),
        BlockID = stmt.GetIntAt(6),
        ShapeID = stmt.GetIntAt(7),
        WheelchairAccessible = stmt.GetIntAt(8) == 1 ? true:false,
    };

    tripCollection.Add(trip);
    n++;
}

Dispatcher.BeginInvoke(() =>
    {
        var report = String.Format("Selected {0:d} records in {1:0.00} seconds", 
            n, (double)sw.ElapsedMilliseconds / 1000);
        MessageBox.Show(report);

        Button_Select.IsEnabled = true;
    });

Which gives this result:

SELECT-await

That’s some improvement! Comparing the two:

Test Total Time (seconds) – SELECT 128981 records SELECT Time per record (ms) Records per second
SELECT 2838 22 45
SELECT with ConfigureAwait(false) 32.4 0.25 3980

That’s a huge improvement!

[WARNING: The timings quoted here are for running on the emulator. You should expect times on a real device to be very substantially slower.]

Conclusions

Some golden rules emerge from this:

  1. Use ConfigureAwait(false) when calling SqlWinRT async methods, particularly within repetitive looping such as INSERT or SELECT of a significant number of records. Remember that when you do so, your code will not resume on the same context it started on, so take appropriate measures to handle this.
  2. In addition to using ConfigureAwait(false), wrap large numbers of INSERTs in a single transaction to get the best performance.

The last learning from this is not specific to SqlWinRT: if you call async methods on a WinRT component, there is a significant overhead in switching back to the originating context. Try calling such a component using ConfigureAwait(false) – it may yield significant performance benefits.

Lastly – this was a test program. If you ever find yourself trying to read 128000 records into memory, I would suggest you have another think about your app design and whether you really need all those objects in memory Smile .

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:

[Updated 21-Oct-2013 – SQLite 3.8.2]

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

image_thumb3

After you’ve installed that, the native sqlite3 dlls are installed into a folder under C:Program Files (x86)Microsoft SDKsWindows Phonev8.0ExtensionSDKsSQLite.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 SDKsWindows Phonev8.0ExtensionSDKsSQLite.WP803.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.

image_thumb7

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 void Application_Launching(object sender, LaunchingEventArgs e)
    {
      LoadDatabase();
    }

    private async void LoadDatabase()
    {
      // Get a reference to the SQLite database
      db = new SQLiteWinRT.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(
        SQLiteWinRT.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():

static SQLiteWinRT.Database db;
    static ManualResetEvent DBLoaded = new ManualResetEvent(false);

    public static Task<SQLiteWinRT.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 (Exception ex)
{
var result = 
    SQLiteWinRT.Database.GetSqliteErrorCode(ex.HResult);
throw new ApplicationException("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

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 = App.db;     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 (Exception) 
{ … }

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)
{
    CustomerViewModel customer = null;
    using (var readstmt = await App.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 App.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 App.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.