Category Archives: SQLite

MS Network 6: Neum, Bosnia and Herzegovina

Thanks to everyone who came to my talks at MS network 6 conference.

Here are the slides and demo files from my presentations:

Building an Adaptive UI for UWP apps        Slides     Demos

Data in UWP Apps: XML, JSON, SQLite, Entity Framework Core    Slides     Demos

Remember: to run the demos using SQLite, you must first go to Visual Studio ToolsUpdates and Extensions, select the Online category and search for SQLite for Universal Windows Apps. Install that package.

You must also go into the Project References for the projects, and if the SQLite for Universal Windows App Platform is listed but there is a yellow bang icon over it, delete the reference and then re-add it through Add Reference – you will find SQLite for Universal Windows Platform listed in the Extensions list under Universal Windows.

Once again – thanks for hosting me in Bosnia and Herzogovina Smile

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

Using SQLite-WinRT in UWP apps

There are a number of SQLite wrapper APIs out there. In the recent MVA A Developer’s Guide to Windows 10, in module 10  I covered SQLite Local Database and walked through an example of using the SQLite-PCL wrapper from MSOpenTech. There are many other wrappers out there, the most popular of which (and which offers a very nice LINQ API) is SQLite-NET.

However, the one I support is called SQLite-WinRT which has a small but faithful band of followers, and I’ve been getting some queries about using it in Windows 10 UWP apps. For those who haven’t used it, SQLite-WinRT is a lightweight wrapper around the SQLite ‘C’ API and so operates using SQL statements – there are a number of other posts on this blog talking about it such as this one: SQLite-WinRT: Database programming on Windows Phone and Windows 8

So I settled down today to make sure it worked on UWP. Now most of the time, libraries written for Windows 8.1 Store apps or for Windows Phone 8.1 (WinRT) just work in UWP apps – but not this time. For some reason, when you try to new up an instance of the SQLiteWinRT Database object, we get a FileNotFoundException. That just seems a bit weird, and in these pre-RTM times, I’m not going to burn loads of hours investigating it. So instead, I built a new WinRT component specifically targeting Windows 10 UWP and tested it out – that works!

However, after some hours of trying things out, it appears that currently there’s no way of building a NuGet package that contains unique libraries for Windows 8.1/Windows Phone 8.1 *and* for UWP. That support will come with an update to NuGet as we get nearer to RTM.

So for now, to use SQLite-WinRT in a UWP app you will have to add the project for the wrapper API to your solution. You can download a sample project with the SQLite-WinRT component source included here:

Also, don’t forget to go to Tools – Extensions and Updates in Visual Studio 2015, click Online in the left panel and then search for and install SQLite for Universal  App Platform. Then in your client project, add a reference to SQLite for Universal App Platform from the Add Reference wizard:

AddRef

Don’t forget, if your app is targeting Windows 8.0/8.1 or Windows Phone 8.1 (WinRT) or 8.0/8.1(Silverlight) you can still just use the existing SQLite-WinRT NuGet package.

SQLiteWinRT: Opening databases shipped as app content or from an SD Card

Recently, I was asked about opening a database file inserted into a device on an SD card. I had to make a slight change to the SQLWinRT wrapper to enable it, so this post explains how, and also looks at opening databases shipped in the app package as content.

Opening databases shipped as content

You can include a prepopulated SQLite database in your Windows 8.x or Windows Phone app package. When the user installs the app, the database file ends up in the App Install folder where you can open it from your code – but only in a read-only fashion:

public static async void LoadDatabase()
{
    // Get a reference to the SQLite database
    db = new SQLiteWinRT.Database(
        Windows.ApplicationModel.Package.Current.InstalledLocation, 
        "customers.sqlite");

    await db.OpenAsync(SQLiteWinRT.SqliteOpenMode.OpenRead);

    // PRAGMA temp_files=2 causes temporary files to be created in memory
    // rather than in a physicalfile in the same folder as the database
    // Note that temp files only really get used if you are going to do 
    // complicated JOINs etc
    await db.ExecuteStatementAsync("PRAGMA temp_files=2");
}

If you want to have read-write access to the database, you must copy it from the install folder to the local folder first.

async Task CopyDatabaseAsync()
{
    // Has the database been copied already?
    try
    {
        await ApplicationData.Current.LocalFolder.GetFileAsync("customers.sqlite");
        // No exception? It exists...
        return;
    }
    catch (System.IO.FileNotFoundException)
    {   // Expected response - no-op 
    }

    StorageFile dbfile = await StorageFile.GetFileFromApplicationUriAsync(
        new Uri("ms-appx:///customers.sqlite"));
    await dbfile.CopyAsync(ApplicationData.Current.LocalFolder);
}

Opening a Database on an SD Card

A database file that you open from an SD Card is related to the previous case in that it is read-only. You cannot write to any file on an SD Card from any app in Windows Phone 8.

I haven’t researched it thoroughly, but you should be able to open a database file on removable storage in a Windows 8.x Store app. You will have to request the Removable Storage capability in the app manifest, and declare the file extensions you want to access in the app manifest as well. Then you should be able to get a StorageFile reference to the database file using techniques similar to those discussed here: http://lunarfrog.com/blog/2012/10/27/external-storage-devices/   . When I’ve tried it, I’ll update this post!

What I have tried though, is doing this on a Windows Phone 8 device that has an SD card slot, such as the Nokia Lumia 820. As with Windows 8, you have to declare the file extensions you want to access in the manifest. For example, add the following after the closing </Tokens> tag:

    <Extensions>
      <FileTypeAssociation Name="sqlite" TaskID="_default" NavUriFragment="fileToken=%s">
        <SupportedFileTypes>
          <FileType ContentType="application/sqlite">.sqlite</FileType>
        </SupportedFileTypes>
      </FileTypeAssociation>
    </Extensions>

Then in your code, you can open a read-only connection to the database as follows:

public static async void LoadDatabase()
{
    // Get a reference to the SQLite database
    ExternalStorageDevice _sdCard = 
        (await ExternalStorage.GetExternalStorageDevicesAsync())
        .FirstOrDefault();

    if (_sdCard == null)
    {
        MessageBox.Show("No SD Cards found");
        return;
    }
    else
    {
        ExternalStorageFile dbStoragefile = 
            await _sdCard.GetFileAsync("customers.sqlite");
        db = new SQLiteWinRT.Database(dbStoragefile.Path);
    }

    await db.OpenAsync(SQLiteWinRT.SqliteOpenMode.OpenRead);

    // PRAGMA temp_files=2 causes temporary files to be created in memory
    // rather than in a physicalfile in the same folder as the database 
    // Note that temp files only really get used if you are going to do 
    // complicated JOINs etc
    await db.ExecuteStatementAsync("PRAGMA temp_files=2");
}

Important: I had to modify the wrapper code posted up on http://sqlwinrt.codeplex.com in order to enable this usage. I added a new override of the Database object constructor that just takes the path to the database file as a string. Earlier versions had one override of the constructor that took a Windows.Storage.StorageFile parameter and another that took a Windows.Storage.StorageFolder and the filename as string, but neither of those were usable since the ExternalStorageFile object is not as you might expect a derivative of Windows.Storage.StorageFile, but is instead in the Microsoft.Phone.Storage namespace – hence I had to create a new constructor.

Download the latest version of the wrapper code from http://sqlwinrt.codeplex.com to get this update – note that this is built against SQLite version 3.8.2 – make sure you update to this version using the Visual Studio – Tools – Extensions and Updates wizard.

Download the sample project here

Note that it covers both scenarios covered in this post. At the top of app.xaml.cs there is a compile time symbol #define USE_SDCARD – comment this out to test the ‘database shipped as content’, or leave it in to test the SD card access. You’ll need a real device for the latter and before testing the app use your PC to copy the customers.sqlite file from the project onto the sd card and insert into your phone. Sadly, the emulator does not emulate a removable storage card!

SQLiteWinRT: Now BLOB columns and Windows 8.1 supported

I’ve posted some updates to the SQLite WinRT API at http://sqlwinrt.codeplex.com. The SQLite WinRT API offers lightweight Windows Runtime (WinRT) APIs you can use to access the most common SQLite database functionality by using SQL statements, rather than a LINQ-style API.

Support for Windows 8.1

One change is to add an implementation for Windows 8.1. So if you download the source code now (there’s no msi or NUGet package for this as yet), you’ll get three projects which are the WinRT components for Windows 8.0, Windows 8.1 and Windows Phone 8.0. Make sure you have installed the SQLite SDK for your target platform on the Tools – Extensions and Updates menu and then Just include the appropriate project in your own solution, and you’re good to go. See my earlier post for an introduction to programming with the API: http://andywigley.com/2013/06/06/sqlite-winrt-database-programming-on-windows-phone-and-windows-8/

Namespaces Standardised

The other relatively minor change is that I’ve standardised the namespace used to SQLWinRT for all platforms. It just makes it a little easier to share code across platforms that way. That does mean that the blog post referenced in the previous paragraph shows code using the old SQLiteWinRTPhone namespace – though the sample accompanying that post has been updated.

Support for BLOB columns

There is a major update in functionality though – support for BLOB columns, which was requested to help store byte arrays – or Images – directly in the database.

You can define a table in the database with a BLOB column using code similar to this:

// Get a reference to the SQLite database
db = new SQLiteWinRT.Database(ApplicationData.Current.LocalFolder, "sqliteblobdemo.db");

await db.OpenAsync();

string sql = @"
    CREATE TABLE IF NOT EXISTS
        Pictures(Id     INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                 Name   VARCHAR(140),
                 Image  BLOB);";
await db.ExecuteStatementAsync(sql);

Then, to store a record in this table, you could do something like:

private async void Insert_RecordAsync(string description, BitmapImage image)
{
    string sql = @"INSERT INTO Pictures (Name, Image) VALUES (@name, @image)";
    var db = await App.GetDatabaseAsync();
    using (var statement = await db.PrepareStatementAsync(sql))
    {
        // Convert BitmapImage to byte array
        byte[] imagebytes = ConvertToBytes(image);
        // .. and from that to an IBuffer
        Windows.Storage.Streams.IBuffer imagebuffer = imagebytes.AsBuffer();

        // set the statement parameters
        statement.BindTextParameterWithName("@name", description);
        statement.BindBlobParameterWithName("@image", imagebuffer);

        await statement.StepAsync();
    }
}

private byte[] ConvertToBytes(BitmapImage bitmapImage)
{
    byte[] data = null;
    using (MemoryStream stream = new MemoryStream())
    {
        WriteableBitmap wBitmap = new WriteableBitmap(bitmapImage);
        wBitmap.SaveJpeg(stream, wBitmap.PixelWidth, wBitmap.PixelHeight, 0, 100);
        stream.Seek(0, SeekOrigin.Begin);
        data = stream.GetBuffer();
    }

    return data;
}

Here’s an example to read data out again:

    protected override PictureViewModel CreateItem(SQLiteWinRT.Statement statement)
    {
      // Read the image from the BLOB column
        Windows.Storage.Streams.IBuffer blobBuffer = null;
        if (statement.GetColumnType(2) != ColumnType.Null)
        {
            blobBuffer = statement.GetBlobAt(2);
        }

        // Convert IBuffer back to a BitmapImage
        byte[] pictureBytes = blobBuffer.ToArray();

        var bitmapSource = new BitmapImage();

        using (MemoryStream ms = new MemoryStream(pictureBytes))
        {
            bitmapSource.CreateOptions = BitmapCreateOptions.None;
            ms.Seek(0, SeekOrigin.Begin);
            bitmapSource.SetSource(ms);
        };

      var c = new PictureViewModel(
        statement.GetIntAt(0),
        statement.GetTextAt(1),
        bitmapSource);

      return c;
    }

Here’s a complete sample app you can study to help implement this functionality in your own apps:

Sample code SQLiteBlobSample.zip

SQLite-WinRT: Support for NULL columns added

Small update to the SQLite WinRT wrapper we published at http://sqlwinrt.codeplex.com a few months ago. This update adds support for storing NULL values into a row through the new methods BindNullParameterAt(int index) and BindNullParameterWithName(string name) added to the Statement class.

The following code sample shows how to store a null value into a column in a row, and then how to check if a null value has been returned to you in the data returned from a SELECT:

var db = new SQLiteWinRTPhone.Database(
    ApplicationData.Current.LocalFolder, "sqlite.db");

await db.OpenAsync();

string sql = @"
    CREATE TABLE IF NOT EXISTS
        Customer(Id     INTEGER PRIMARY KEY NOT NULL,
                    Name   VARCHAR(140),
                    NullableInt INTEGER);";

await db.ExecuteStatementAsync(sql);

try
{
    sql = @"INSERT INTO Customer(Id, Name, NullableInt) 
            VALUES (@id, @name, @nullInt);";
    using (var custstmt = await db.PrepareStatementAsync(sql))
    {
        custstmt.BindIntParameterWithName("@id", 11);
        custstmt.BindNullParameterWithName("@name"); 
        custstmt.BindNullParameterAt(3);
        await custstmt.StepAsync();
    }
}
catch (Exception ex)
{
    var result = Database.GetSqliteErrorCode(ex.HResult);
    Debug.WriteLine("INSERT failed with error " + result);
} 

// Right now read the rows out again
try
{
    sql = "SELECT Id, Name, NullableInt FROM Customer;";
    using (var custstmt = await db.PrepareStatementAsync(sql))
    {
        // Use StepAsync to execute a prepared statement
        while (await custstmt.StepAsync())
        {
            var Id = custstmt.GetIntAt(0);
            var Name = "NULL";
            if (custstmt.GetColumnType(1) != ColumnType.Null)
            {
                Name = custstmt.GetTextAt(1);
            }
            int? NullableInt = null;
            if (custstmt.GetColumnType(2) != ColumnType.Null)
            {
                NullableInt = custstmt.GetIntAt(2);
            }
            Debug.WriteLine("Got Customer: " + Id + " " 
                + Name + " NullableInt: " 
                + (NullableInt.HasValue ? NullableInt.Value.ToString() : "null"));
        }
    }
}
catch (Exception ex)
{
    var result = Database.GetSqliteErrorCode(ex.HResult);
    MessageBox.Show("SELECT failed with error " + result);
} 

Notice that you use BindNullParameterxxx to store a null value – using BindTextParameterWithName(string name, string value) and passing a NULL for the value doesn’t work and causes an exception to be thrown.

Notice also that to check if a column returned to you contains NULL, you use the Statement.GetColumnType(int index) method to find out if the column type is ColumnType.Null.

So, please go and download the source code again from https://sqlwinrt.codeplex.com/SourceControl/latest – sorry, no NuGet package for this. Please give it a try and give us any feedback. For more on how to use the wrapper, see my earlier post SQLite-WinRT: Database programming on Windows Phone and Windows 8.

SQLite Using NULL Demo code: SQLiteNULLdemo.zip

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.