How to retrieve byte array from SQLite database in UWP using C#

Question

I've searched for too long but couldn't find any answer.

I'm working on a UWP app which stores BitmapImage data (converted to byte array) into a blob field in an SQLite3 database. However, I can't find a way to retrieve the data once it's saved in the database.

All the solutions I've seen use the GetBytes method from Microsoft.Data.SQLite.SQLiteDataReader but Microsoft's documentation indicates method is "not supported". https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite.sqlitedatareader.getbytes?view=msdata-sqlite-1.1.0

Can anyone please show some sample code how to retrieve the blob from the SQLite 3 database and convert it to a BitmapImage for display?

Thanks in advance for your help.

Try this, learn.microsoft.com/en-us/windows/uwp/data-access/… — Bailey Miller
– Bailey Miller, Commented Aug 3, 2017 at 12:21
Thanks but it doesn't seem to show any info on how I can read back a byte array from the database. — Arthus Le Savage
– Arthus Le Savage, Commented Aug 3, 2017 at 12:41
@ArthusLeSavage The entity framework is a tool for working with objects that translate to tables in a database. Create a class that has a byte array and that creates a table with the proper data type and when you run your queries you get a byte array back. — Bailey Miller
– Bailey Miller, Commented Aug 3, 2017 at 13:36
if you have used byte[] to store the bytes then you don't need to use 'GetBytes'. eg- this is your model: public byte[] ImageAsBytes {get; set;} then you can simply access it like var bytes = db.TableName.ImageAsBytes.FirstOrDefault( r => r.primaryKey == value ); — ravi kumar
– ravi kumar, Commented Aug 4, 2017 at 5:44

Bailey Miller · Accepted Answer · 2017-08-03 14:01:48Z

0

That is a really minimal version of the work required but that gives you the step that will store picture data as btye[]'s also that will retrieve the data as a byte array.

I won't go into full depth of the entity framework because that isn't a part of the question, I believe this link does a good job. Microsoft Documentation.

Disclaimer I have never used the entity framework with UWP, as well I have never used a SQL Lite database, there may be things with the amount of data and all the available types that can be stored in the database.

As for the converting byte[] to BitMap in UWP, look here.

public class ProfilePictures
{
    public int ProfilePicturePK {get;set;}
    public byte[] PictureData {get;set;}
}

... all the other entity setup, see link for entity setup

public byte[] GetPictureData(int id)
{
    return GetPictureDataAsync(id).Wait();
}

//If you want to make things a little more UI and thread friendly
public async Task<byte[]> GetPictureDataAsync(int id)
{
     await Task.Factory.StartNew(()=>{
        using(var db = Context)
        {
            return (from pictures in db.ProfilePictures where pictures.ProfilePicturePK == id select pictures).SingleOrDefault();
        }
     });
}

edited Aug 3, 2017 at 14:01

answered Aug 3, 2017 at 13:41

Bailey Miller

1,5232 gold badges25 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arthus Le Savage Over a year ago

Thanks for all your help. I went with Crowcoder's suggestion to retrieve the BLOB field.

Collectives™ on Stack Overflow

How to retrieve byte array from SQLite database in UWP using C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related