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

3 thoughts on “SQLiteWinRT: Now BLOB columns and Windows 8.1 supported

    1. andywigley

      I don’t agree that performance of simple selects is horrible. My sample app is doing selects, and it works just fine, though admittedly the sample data set is small.
      Usually, if Select performance is poor, it’s because you haven’t configured appropriate indexes to do an efficient lookup. If you don’t have an index, all the database engine can do to find matching records is to start with record 1 and read down the entire table until it reaches the end! [Apologies if you knew that – but it’s surprising how many people don’t!].

      Reply
  1. Justin Markwell

    Well I am glad I found this, I updated to new SQLite and about 50 errors i’ve never seen before came up in my VS and to top it off its a solution I had just submitted to release lol.
    I noticed the reference for SQLiteWinRTPhone (8.0.1 or something close) was the wrong one I updated it with SQLiteWinRTPhone with no (somenumber) after it then just changed every SQLiteWinRTPhone name to SQLiteWinRT and it will work again. Was worried there Andy thanks for the post

    again that was the first thing I renamed with no success until I changed the reference to the one without the ()

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *