[RESOLVED] Working with SQLite and image files? Save the file or the file path?

[The_Hobbyist]

I've been reading about how best to work with image files using VB.NET and SQLite and how to either save them directly to the database or how to save their file location to the database. Frankly, I haven't a clue how to do either yet and before I start flailing around I thought it best to start a discussion on which approach may be best for my needs. There seems to be mixed reviews on the subject and from what I can tell, those reviews seem to have swayed course over the years. At least, that is how I've come to understand it. The general consensus though seems to be that saving images directly to the database is not the best approach, please correct me if I am wrong. This was my initial intention but I am having second thoughts about it.

I have read through some of the pros and cons and have come to believe the following:

• When saving an image file directly to the database, it is most efficient if the file is stored as a byte array and then upon retrieval, converted back to a binary image object before displaying it in a picture box.
• Saving an image file directly to the database can allow for read/write speeds roughly 1/3 faster than retrieving the image via it's file path.
• Saving an image's file path to the database makes for a smaller database which tends to be more efficient

My project is essentially a catalogue of different geological specimens (picture a rock encyclopedia) where each specimen has a detailed record consisting of many different data points. Aside from still wanting to add a personal inventory or collection tracker into the database & project, I also want to include a photo reference for each specimen. This will include a photo specimen for not only the encyclopedia side of the application but for each piece in the personal inventory (collection) as well. Is there one method of image handling that I should focus on over the other and why? I can't imagine the database growing to more than 1000 records but I also can't predict what the file size of each image used might be (user defined images). Or can I? Could I or should I limit the file size in an effort to reduce the database swell?

I have looked at other examples but for a few days now I have been focused on jmc's code example here where he saves the image file as a byte array into the database and then converts it to a binary image object for display. I have come to understand that while rather blunt, to the point and bordering on harsh at times, jmc is an extremely well versed and knowledgeable programmer. One dare say top shelf even. For the latter reason, I suspect his example would be one of the better ones to follow assuming the method is best suited to my needs. This is yet to be determined. I haven't yet managed to get his sample code to work successfully for me and before I spend too much time on it, I felt it best that I start a discussion on which direction might be best for me to travel in and why - save the file or the file path to the database?

[The_Hobbyist]

OK. Enough of that chit chat...

I've decided, based on the fact that my DB is bound to be relatively small that I will go ahead and try to use jmc's method. While working with his code example, I came across a snag.

First off, I didn't recognize any code within the example that would allow the user to open an image file so that it could be saved into the DB. So, I think I implemented a way to do that. I think...

When I run the following code to save an image to the DB, I don't seem to get any errors. When I look at the database in DB Browser for SQLite, I can see that a single record is present. When I look at the file size for the database file however, it hasn't seemed to change from before I ran the code. I presume that the DB file should increase in size according to the image's file size? That would make sense to me and I suspect that the image file is not being saved correctly into the DB, Perhaps I should start here?

Why does my image file not appear to be saving correctly in the DB file? The have the database's table value type set to BYTE. Am I way off base with this or am I close?

To be clear, at the moment I'm focused on just getting the user selected image file converted to a byte array so as to be stored into the DB. Once that hurdle has been navigated, I'll try to move forward.

Imports System.Data.SQLite

Public Class frmMain

    Private dbCon As New SQLiteConnection("Data Source=" & Application.StartupPath & "\Database.db;" & "Version=3;New=False;Compressed=True;")

    Private Sub btnSave2DB_Click(sender As Object, e As EventArgs) Handles btnSave2DB.Click

        Dim command As New SQLiteCommand("UPDATE MyTable SET Picture = @Picture WHERE ID = 1", dbCon)
        Dim FilePath As String

        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            Me.OpenFileDialog1.InitialDirectory = Application.StartupPath & "\FX\"
            Me.OpenFileDialog1.Filter = "Image|*.jpg"
            FilePath = OpenFileDialog1.FileName
        End If

        Using picture As Image = Image.FromFile(FilePath)
            Using stream As New IO.MemoryStream
                picture.Save(stream, Imaging.ImageFormat.Jpeg)

                Command.Parameters.Add("@Picture", SqlDbType.VarBinary).Value = stream.ToArray()
            End Using
        End Using

        dbCon.Open()
        command.ExecuteNonQuery()
        dbCon.Close()

    End Sub

End Class

[wes4dbt]

I've only played around with retrieving/saving images so I don't know a lot. But I did get it to work.

        If MessageBox.Show("Save pic", "Save Pic", MessageBoxButtons.YesNo) = DialogResult.Yes Then
            Dim m As New IO.MemoryStream

            '- Get the image from the picture control
            Me.PictureBox1.Image.Save(m, System.Drawing.Imaging.ImageFormat.Jpeg)

            '- Convert the stream to the byte array 
            Dim pic As Byte() = m.ToArray()

            Dim cmd As New SQLiteCommand("Update ScientificData  Set Photo =@photo WHERE ID = 1", con)
            cmd.Parameters.Add("@photo", DbType.Binary).Value = pic

            Dim var = cmd.ExecuteNonQuery()
            MessageBox.Show("Inserted " & var)
        End If

I'm saving an image from a Picturebox.

The SQLite field "photo" has a data type of Blob. Not Byte
The Update Command parameter is DBType.Binary. Not SQLDbType.VarBinary. That wasn't even one of the choices for SQLite.

Other than that, they are basically the same.

Than all you have to do is bind the Picturebox to the bindingsource,

Me.PictureBox1.DataBindings.Add("Image", scienceBindingsource, "Photo", True)

If you want to know if your database has been modified, check the data time stamp.

[The_Hobbyist]

Thank you! I am now able to open an image file and save it to the database. I was using VarBinary as the data type because it was used in the example. Originally I was trying to use Byte. Obviously not knowing what I was doing lead me down that path. And, Yup! I physically typed VarBinary into the option window in SQLite, without realizing that it doesn't work that way. I'm happy to have learned that along the way tonight as well, so thank you again!

This has helped me immensely. I'll report back once I get a chance to try loading the database image back into the picture box. For now, my lunch break at work has ended.

Cheers!

[wes4dbt]

This has helped me immensely. I'll report back once I get a chance to try loading the database image back into the picture box. For now, my lunch break at work has ended.

It's one line of code. lol

Though don't forget to add the Picture field to your "Select" statement.

[jmcilhinney]

First off, I didn't recognize any code within the example that would allow the user to open an image file so that it could be saved into the DB.

That's not true, given that the first post contains this:

Using picture As Image = Image.FromFile("file path here")
    'Create an empty stream in memory.
    Using stream As New IO.MemoryStream
        'Fill the stream with the binary data from the Image.
        picture.Save(stream, Imaging.ImageFormat.Jpeg)
 
        'Get an array of Bytes from the stream and assign to the parameter.
        command.Parameters.Add("@Picture", SqlDbType.VarBinary).Value = stream.ToArray()
    End Using
End Using

It seems that what you actually mean is a way for the user to select a file. That's got nothing to do with this topic directly though, which is why it wasn't included. The file path can come from anywhere at all, e.g. a TextBox, a file, etc. An OpenFileDialog is the standard way for the user to select ANY file for ANY purpose in WinForms, so that's pretty much a given. If you had asked that question in its own thread, that's the answer you would have gotten.

It's also worth noting that that code is actually more verbose than it needs to be. If you actually need an Image object, e.g. to display in a PictureBox, then you should create an Image object. If all you need is the data in order to save it to the database, you can just call File.ReadAllBytes to get that data. This will actually be more efficient too, because it creates one copy of the data instead of three (Image, Stream and array).

[jmcilhinney]

The problem appears to be that you are using the wrong enumeration here:

Command.Parameters.Add("@Picture", SqlDbType.VarBinary).Value = stream.ToArray()

You are using SqlDbType, which is generally only for the SqlClient provider for SQL Server. It appears that that SQLite provider uses the DbType enumeration. Enums are really just numbers under the hood, so you are using the numeric value for SqlDbType.VarBinary and the provider is interpreting that as being a DbType value and that same number will correspond to some other type so your data will be saved incorrectly.

This suggests that you have Option Strict Off, which is unfortunately the default. You should turn Option Strict On immediately in the project properties. That would have flagged that you were using the wrong type there. You should also turn it On in the VS options, so it is On by default in all future projects. ALWAYS have Option Strict On at the project level and then only turn it Off at the file level where you explicitly need to use late binding. Even then, use partial classes to keep the code in those files to an absolute minimum.

[The_Hobbyist]

This is a lot to take in jmc. Thank you.

[jmcilhinney]

This is a lot to take in jmc. Thank you.

We definitely don't have enough head holes to get all the required information in.

[Zvoni]

As to your original question: https://www.vbforums.com/showthread....=1#post5525350
Might shed some light on your approach

[The_Hobbyist]

We definitely don't have enough head holes to get all the required information in.

Oh! God.... I don't need anymore holes in my head. Those hole would be just as likely to let stuff out as they are to let stuff in... I already struggle terribly with short term memory less.

Anyway...

Using a mini-project to work on this, I am now able to open an image file with an OpenFileDialog, save it to the database using your code example jmc and then bind it to a picturebox which allows me to view the image by selecting the row in the DGV. I am now trying to get it all to work within my main project and (surprise) I am having some issues. I have some questions but I don't want to be too hasty and will attempt to figure them out on my own. For now I'm going to sleep on it and work on this some more tomorrow. I don't want to flag this thread as resolved just yet.

My issue revolves around setting up the adapter command parameters, specifically this line .Parameters.Add("@Picture", DbType.Binary).Value = ThePhoto. All of my adapters are setup in a cluster in a different part of the code where as I have your code example written to set the adapter up all within the button click event (Save Button). When I put the line of code above into my cluster of adapter command parameters, I get an error.

I might be making an assumption that I can move the command parameter line up into my cluster of command parameters but it seems reasonable to me that I should be able to. Whatever the case, if I can't resolve the issue in the next day or two, I'll come back and try to form an intelligent question.

Thanks again.

[The_Hobbyist]

As to your original question: https://www.vbforums.com/showthread....=1#post5525350
Might shed some light on your approach

Thanks. I'll give this a read tomorrow. I appreciate your time.

Cheers!

[jmcilhinney]

I get an error.

If only there were some way we could know what that error was.

[wes4dbt]

Oh! God.... I don't need anymore holes in my head. Those hole would be just as likely to let stuff out as they are to let stuff in... I already struggle terribly with short term memory less.

Anyway...

Using a mini-project to work on this, I am now able to open an image file with an OpenFileDialog, save it to the database using your code example jmc and then bind it to a picturebox which allows me to view the image by selecting the row in the DGV. I am now trying to get it all to work within my main project and (surprise) I am having some issues. I have some questions but I don't want to be too hasty and will attempt to figure them out on my own. For now I'm going to sleep on it and work on this some more tomorrow. I don't want to flag this thread as resolved just yet.

My issue revolves around setting up the adapter command parameters, specifically this line .Parameters.Add("@Picture", DbType.Binary).Value = ThePhoto. All of my adapters are setup in a cluster in a different part of the code where as I have your code example written to set the adapter up all within the button click event (Save Button). When I put the line of code above into my cluster of adapter command parameters, I get an error.

I might be making an assumption that I can move the command parameter line up into my cluster of command parameters but it seems reasonable to me that I should be able to. Whatever the case, if I can't resolve the issue in the next day or two, I'll come back and try to form an intelligent question.

Thanks again.

Telling us your getting an error doesn't do much good. Tell us what the error is and where it's happening, then post your current code. It's probably something minor and could be solved quickly. No need to struggle for days.

[Zvoni]

On a sidenote to the OP (and in particular to my link above):
If you want that Image-Column to be in the same table as the rest of the data, be very careful with "SELECT * FROM"
(nevermind that you should avoid "SELECT * FROM" in production code anyway)

[wes4dbt]

I had never used databinding with images before so I got some good news and some bad news for you.

Good news, using databinding makes this an incredibly simple process.

Bad news, you don't use any of the code you have for storing/retrieving photos. But it's always good to learn new things so it's not real bad news.

All I did was,

Add the Photo field to my Dataadapter Insert command and to it's Update command.
Add a Button under the PictureBox called SelectImageButton
then in the Click event

    Private Sub SelectImageButton_Click(sender As Object, e As EventArgs) Handles SelectImageButton.Click
        Using ofd As New OpenFileDialog
            ofd.InitialDirectory = "C:\AJunk2019\Pictures"
            ofd.Filter = "Image|*.jpg"
            If ofd.ShowDialog = DialogResult.OK Then
                Me.PictureBox1.Image = Image.FromFile(ofd.FileName)
            End If
        End Using
    End Sub

That's it, done. The databinding and DataAdapter do all the rest for you.

[The_Hobbyist]

If only there were some way we could know what that error was.

Yes, jmc, I intentionally didn't tell you what the error was because I'm attempting to figure this out on my own. It's unlikely at this point that I will but I'm making an effort. At the very least, I'm trying to show that I am putting the effort in and not just begging for handouts every time I get stuck. I'm trying to make the code God's here happy....lol. Stay tuned... Its coming...

...Add the Photo field to my Dataadapter Insert command and to it's Update command.
Add a Button under the PictureBox called SelectImageButton
then in the Click event

    Private Sub SelectImageButton_Click(sender As Object, e As EventArgs) Handles SelectImageButton.Click
        Using ofd As New OpenFileDialog
            ofd.InitialDirectory = "C:\AJunk2019\Pictures"
            ofd.Filter = "Image|*.jpg"
            If ofd.ShowDialog = DialogResult.OK Then
                Me.PictureBox1.Image = Image.FromFile(ofd.FileName)
            End If
        End Using
    End Sub

The above is nearly the exact same code that I have in my project Wes, the only difference being that I use the Picturebox_Click event to initiate it. You're right, it works like a champ. My trouble starts when I try to add the Photo field to the data adapter's Update and Insert commands. I must not have the syntax correct or something else minor is off. I just can't quite seem to sort it out. I haven't given up yet but I'll explain:

While only showing the Picture field in my sample below, this is how I set up my data adapter (as you already know):

    Private Sub SetUpAdapter()

        Dim parentInsertCommand As New SQLiteCommand("INSERT INTO Gemstones
                                                        (Picture) VALUES (@Pictures)", dbCon)

        Dim parentUpdateCommand As New SQLiteCommand("UPDATE Gemstones
                                                        SET Picture=@Picture" WHERE Id = @Id", dbCon)

        With parentInsertCommand.Parameters
                       .Add("@photo", DbType.Binary).Value = pic

        With parentUpdateCommand.Parameters
                       .Add("@photo", DbType.Binary).Value = pic

        With adGemstones
            .InsertCommand = parentInsertCommand
            .UpdateCommand = parentUpdateCommand
            .DeleteCommand = parentDeleteCommand
        End With

    End Sub

Lastly, my data binding line is below, which probably doesn't need to be shown here but I'll share it anyway for the sake of completeness:

Me.PictureBox.DataBindings.Add("Image", bsGemstones, "Picture", True)

The code bugs out on the variable "pic" stating that it is not declared. I get that because in your example the variable "pic" is holding the converted image data in a byte array and in my code it is not present at all. I don't know how to make it work like the example you provided where the adapter commands are written a little differently (see below):

        If MessageBox.Show("Save pic", "Save Pic", MessageBoxButtons.YesNo) = DialogResult.Yes Then
            Dim m As New IO.MemoryStream

            '- Get the image from the picture control
            Me.PictureBox1.Image.Save(m, System.Drawing.Imaging.ImageFormat.Jpeg)

            '- Convert the stream to the byte array 
            Dim pic As Byte() = m.ToArray()

            Dim cmd As New SQLiteCommand("Update ScientificData  Set Photo =@photo WHERE ID = 1", con)
            cmd.Parameters.Add("@photo", DbType.Binary).Value = pic

            Dim var = cmd.ExecuteNonQuery()
            MessageBox.Show("Inserted " & var)
        End If

[wes4dbt]

While only showing the Picture field in my sample below, this is how I set up my data adapter (as you already know):

Just look how you have added the parameters to your adapter in your main project. You'll see the problem.

[The_Hobbyist]

Just look how you have added the parameters to your adapter in your main project. You'll see the problem.

Thank you! I got it now... You folks are the best! This is how to add the adapter parameter properly:

.Add("@Picture", DbType.Binary, 0, "Picture")

Now I'm going to read Zvoni's thread.

Cheers!

[wes4dbt]

Thank you! I got it now... You folks are the best! This is how to add the adapter parameter properly:

.Add("@Picture", DbType.Binary, 0, "Picture")

Now I'm going to read Zvoni's thread.

Cheers!

For some reason you were trying to copy the code I used to add a parameter to a Command object. With a DataAdapter, the parameter gets its value from the DataTable row.

[The_Hobbyist]

Yes I was. Its still a little confusing to me but its less confusing than when I first started down this path. Thanks again for the patience and the help (everyone).

[jmcilhinney]

I would recommend that you read this, which provides code examples of various ADO.NET scenarios, and this, which explains how to use parameters.

[jmcilhinney]

On the subject of data-binding and PictureBoxes, if you have a file path stored in your data then you can bind that to the ImageLocation property of the PictureBox and Bob's your uncle. No other code needed. If your data contains Byte arrays that represent images then you can't bind that to a standard PictureBox because there's no property of that type. What you can do is derive your own class from PictureBox and add such a property. You can then put the code to convert the Byte array to an Image via a MemoryStream inside that property and then your application code will be cleaner as you can bind to the PictureBox the same way you would a TextBox or the like.

[The_Hobbyist]

Thanks again jmc. I'll dive into those reads soon.

Cheers!