Retrieve binary data from SQLite database with dapper

Viewed 367

I'm trying to retrieve the contents of an SQLite database using Dapper ORM and populate a list of objects using it. However, even though I have set the column type in the database to BLOB, I am getting an error which seems to suggest Dapper ORM is interpreting the data as a string.

The database is being populated with a Python script which is configured with the column also set to BLOB type. Within SQLiteStudio, I can view the data as an image preview, so the correct data is in the database.

The error: enter image description here

The code to query the database:

public List<Thumbnail> ReadAll()
{
    var test = _dbConnection.Query<Thumbnail>(
                "Select * FROM thumbcache").ToList();       
    return test;
}

Here is my class definition:

public class Thumbnail
{
    public int id { get; set; }
    public DateTime scantime { get; set; }
    public int imageheight { get; set; }
    public int imagewidth { get; set; }
    public string identifier { get; set; }
    public long offset { get; set; }
    public string signature { get; set; }
    public int entrySize { get; set; }
    public int identifierlen { get; set; }
    public long datasize { get; set; }
    public int paddingsize { get; set; }
    public string datachecksum { get; set; }
    public string headerchecksum { get; set; }
    public string entryhash { get; set; }
    public string cachefile { get; set; }
    public byte[] image { get; set; }   // This is the field that fails to be populated
}

The data in the database: enter image description here

Can anyone spot what I'm doing wrong here? I'd appreciate any suggestions you may have.

In response to @MarcGravell's comment, I am now trying to save the binary as base64 in the database and parse it using C#, however I am getting most images throw invalid base64 errors (even though the base64 in the database is correct), and the images that do show are corrupt/incorrect.

Class definition:

public class Thumbnail
{
    public int id { get; set; }
    ...
    public string cachefile { get; set; }
    public string image { get; set; }   //Changed this to string
}

Current output of my C# (value of base64 string pulled straight from db shown in messagebox): enter image description here

What is interesting to note is that the MessageBox is supposed to show the base64 pulled from the database, yet it seems to show bytes instead?

And this is what the image looks like when you directly convert the base64 in the database into an image using an online tool:

enter image description here

My code to create the image:

private Image create_image(Thumbnail thumb)
{
    string imageBase64 = thumb.image;
    MessageBox.Show(imageBase64);
    try
    {
        byte[] imageBytes = Convert.FromBase64String(imageBase64)
        using (MemoryStream ms = new MemoryStream(imageBytes))
        {
            Image image = Image.FromStream(ms);
            return image;
        }
    }
    catch
    {
        return null;
    }
}

And here is the error I get when I don't use a try-catch: enter image description here

1 Answers

I've managed to fix this issue by moving from using Dapper to SQLite-Net.

https://github.com/praeclarum/sqlite-net/

Not sure what the issue with Dapper was; the data in my database was always showing correctly so I can only assume Dapper must've been doing something weird to the data when it pulled it from the database and populated the objects.

I've stuck with using Base64 encoding for the binary data, I've not tested SQLite-Net with raw binary data.

Related