How do I read out a blob back to byte array from sqlite?

Question

I have an OpenFileDialog which allows users to select a single .png image. I store this into a property as such:

public byte[] HeroesDBThumbnailImage ...
...
if(OFD.ShowDialog() == true)
{
    HeroesDBThumbnailImage = File.ReadAllBytes(OFD.FileName);
    HeroesDBThumbnailPath = OFD.SafeFileName;
}

Once the value is set there, I am Inserting the entry to the DB as follows:

query = ....blahblah...
query += "'" Convert.ToBase64String(HeroesDBThumbnailImage) + "','" + Convert.ToBase64String(HeroesDBFeaturedThumbnailImage) + "'); ";

It appears to insert data to the DB, so I was happy.

Then, I read it back as so:

new Hero(
...
HThumbnailImage: (byte[])Row["ThumbnailImage"]
) ...

in the XAML, I have an image, for testing, which I bound to the original byte array. As soona s the user selects an image from the file selector, the image source, bound to that property, displays that image.

However, when I read the data back into the other property, another test image, bound to that byte array does not display anything. All bindings are correct, and I tried to debug at runtime and the byte array is being read back out, however it appears to be coming back a different size from the original... Original was 899 bytes, once read back out from DB it appears to be about 940 ish...

The field in SQLite DB is BLOB by the way.

Thanks

EDIT: Extra Info provided as requested:

OK, so within my Hero Class, I have this:

public static ObservableCollection<Hero> GetAllHeroes()
{
    ObservableCollection<Hero> Heroes = new ObservableCollection<Hero>();

    try
    {
        var db = new SQLiteDatabase();
        DataTable dt = db.GetDataTable("SELECT * FROM Heroes");
        foreach (DataRow Row in dt.Rows)
        {
            Heroes.Add(new Hero(
                HID: Convert.ToInt32(Row["ID"]),
                HName: Row["Name"].ToString(),
                HGold: Convert.ToInt32(Row["Gold"]),
                HPlatinum: Convert.ToInt32(Row["Platinum"]),
                HDBTag: Row["DBTag"].ToString(),
                HReleaseDate: Convert.ToDateTime(Row["ReleaseDate"]),
                HAvailable: Convert.ToBoolean(Row["Available"]),
                HSale: Convert.ToBoolean(Row["Sale"]),
                HFeatured: Convert.ToBoolean(Row["Featured"]),
                HThumbnailImage: (byte[])Row["ThumbnailImage"],
                HFeaturedThumbnailImage: (byte[])Row["FeaturedThumbnailImage"]
                ));
        }
    }
    catch (Exception err)
    {
        System.Windows.Forms.MessageBox.Show(err.Message);
    }

    return Heroes;
}

and the CTOR it relates to is:

public Hero(int HID, string HName, int HGold, int HPlatinum, string HDBTag, DateTime HReleaseDate, bool HAvailable, bool HSale, bool HFeatured, byte[] HThumbnailImage, byte[] HFeaturedThumbnailImage)
{
    ID = HID;
    Name = HName;
    Gold = HGold;
    Platinum = HPlatinum;
    DBTag = HDBTag;
    ReleaseDate = HReleaseDate;
    Available = HAvailable;
    Sale = HSale;
    Featured = HFeatured;
    ThumbnailImage = HThumbnailImage;
    FeaturedThumbnailImage = HFeaturedThumbnailImage;
}

I have the following Property and function in my viewmodel:

private ObservableCollection<Hero> heroesDBHeroes;
public ObservableCollection<Hero> HeroesDBHeroes
{
    get
    {
        return heroesDBHeroes;
    }
    set
    {
        heroesDBHeroes = value;
        OnPropertyChanged("HeroesDBHeroes");
    }
}
private void HeroesDBAddHeroes()
{
    if(HeroesDBHeroes != null)
    {
        HeroesDBHeroes.Clear();
    }
    HeroesDBHeroes = Hero.GetAllHeroes();
}

HeroesDBAddHeroes is called before it is ever needed.

I then have a Datagrid, bound to this ObservableCollection. Datagrid represented:

<DataGrid extra:UIElementAttached.IsBubblingMouseWheelEvents="True" ItemsSource="{Binding HeroesDBHeroes}" IsTabStop="False" KeyboardNavigation.TabNavigation="None" Grid.Row="1" IsReadOnly="True">
    <DataGrid.Columns>
        <extra:DataGridTemplateColumn extra:DataGridColumnAttached.CanUserHideColumn="True" AutomationProperties.Name="Image" CanUserSort="True">
            <extra:DataGridTemplateColumn.Header>
                <TextBlock Text="Image" HorizontalAlignment="Center" FontWeight="Bold" FontSize="16" Margin="10,0" />
            </extra:DataGridTemplateColumn.Header>

            <extra:DataGridTemplateColumn.CellTemplate>
                <DataTemplate>
                    <StackPanel Orientation="Horizontal">
                        <TextBlock Text="{Binding Name}" />
                        <Image Source="{Binding ThumbnailImage}" />
                    </StackPanel>
                </DataTemplate>
            </extra:DataGridTemplateColumn.CellTemplate>
        </extra:DataGridTemplateColumn>
    </DataGrid.Columns>
</DataGrid>

The textblock bound to Name is showing correctly, however the image bound to ThumbnailImage (which is a byte[] also within Hero) is not showing anything.

what do you mean by this - "when I read the data back into the other property, another test image, bound to that byte array does not display anything" — Nikita Shrivastava, Sep 22 '15 at 02:20
It means that I have a property, which I read the blob using the code shown into a property set by HThumbnailImage. I have an in my xaml, where source is bound to this property. It never shows an image, even though the data made it out of the DB. — pingu2k4, Sep 22 '15 at 07:48
So you are using byte[] as Image. Source??? that won't work because you need an Image out of it.Is this what you want? — Nikita Shrivastava, Sep 22 '15 at 07:52
It should work, because I have another bound to a byte[] property, which is set when I select the image vis OpenFileDialog — pingu2k4, Sep 22 '15 at 07:54
No converters. What xaml would be useful? The image tags are simple - just image, where source is bound to property of type byte[]. The first byte[] is set from the image selected in OpenFileDialog, whilst the second is read back from BLOB in SQLite DB (which was previously set by the first byte[]) — pingu2k4, Sep 22 '15 at 08:03
What about the datacontext, that could be different in both case resulting no data — Nikita Shrivastava, Sep 22 '15 at 08:04
Also when the value of the binded property changes, have you implement INotifyPropertyChanged interface? — Nikita Shrivastava, Sep 22 '15 at 08:05
The first one works, the second one I have tried two seperate ways. The Datacontext is set by Datagrid, so I am taking the image from each result in ObservableCollection of type . Contains the byte[] data taken from SQLite DB, and it should be binding correctly as I am binding to another property within Hero and getting that data back out correctly. I am displaying the name property from each element in the ObservableCollection, but binding the image source to the byte[] does nothing. Also tried outside of DG binding image source to the ObservableCollection [0].Prop Byte[] — pingu2k4, Sep 22 '15 at 08:07
you have two properties? one HeroesDBThumbnailImage & HThumbnailImage? — Nikita Shrivastava, Sep 22 '15 at 08:12
I added more code, xaml etc to the top. The property where the image will not display from is ThumbnailImage, set in the Heroes class. — pingu2k4, Sep 22 '15 at 08:14
So, your ThumbnailImage has the new value but not the UI, did you try with Binding modes & UpdateSourceTrigger? Rest looks fine to me. — Nikita Shrivastava, Sep 22 '15 at 08:23
No luck with that. The problem seems to be that the data I put into the database is not equal to the data I get back out. Setting a breakpoint, with 1 specific image, When I select it from OpenFileDialog and set it to the initial byte[], it is 189,976 bytes. When I take it out from DB however, it is 253,304 bytes. I dont know why it would be different? I am converting to Base64String to save to blob field, then reading back out I (byte[]) cast it... — pingu2k4, Sep 22 '15 at 08:44
check this - http://stackoverflow.com/questions/625029/how-do-i-store-and-retrieve-a-blob-from-sqlite — Nikita Shrivastava, Sep 22 '15 at 09:02
I don't know how to do the parameters thing - My SQLite class doesnt have a anything accepting parameters in that way, and I wouldn't know where to start with changing it so that it does... — pingu2k4, Sep 22 '15 at 09:15

score 1 · Accepted Answer · answered Sep 22 '15 at 08:57

1

Before storing the data, you encode it using Convert.ToBase64String(), but when reading the data you seem to miss the decoding part, Convert.FromBase64String(), ie:

byte[] base64Data = (byte[])Row["ThumbnailImage"];
string base64String = System.Text.Encoding.ASCII.GetString(base64Data);
byte[] imageData = Convert.FromBase64String(base64String);

PS. You could probably skip the base64 encoding on both storing and reading, and store the actual binary data directly.

answered Sep 22 '15 at 08:57

C.Evenhuis

25,996
2
58
72

I will try that now, thanks. Relating to your coment on skipping the encoding... When I was building the SQL query and appending the byte array to the string, it was showing as something like "blah..., System.Byte[], blah..." which was the reason I was converting it. Was I doing something wrong with that? – pingu2k4 Sep 22 '15 at 09:14
That conversion worked great - thanks! Would still be interested in how I might go about doing this without the conversion however :) – pingu2k4 Sep 22 '15 at 09:50
@MatthewParker you should then use a parameterized query (using an `SQLiteCommand` with `SQLiteParameter`s) and directly assign the `byte[]` data as its value. – C.Evenhuis Sep 22 '15 at 11:34

How do I read out a blob back to byte array from sqlite?

1 Answers1