Results 1 to 24 of 24

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

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    137

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

    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?

  2. #2

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    137

    Re: Working with SQLite and image files? Save the file or the file path?

    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.

    Code:
    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

  3. #3
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,040

    Re: Working with SQLite and image files? Save the file or the file path?

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

    Code:
            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,
    Code:
    Me.PictureBox1.DataBindings.Add("Image", scienceBindingsource, "Photo", True)
    If you want to know if your database has been modified, check the data time stamp.
    Last edited by wes4dbt; Jan 24th, 2023 at 09:33 PM.

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    137

    Re: Working with SQLite and image files? Save the file or the file path?

    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!

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,040

    Re: Working with SQLite and image files? Save the file or the file path?

    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.

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,263

    Re: Working with SQLite and image files? Save the file or the file path?

    Quote Originally Posted by The_Hobbyist View Post
    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:
    vb.net Code:
    1. Using picture As Image = Image.FromFile("file path here")
    2.     'Create an empty stream in memory.
    3.     Using stream As New IO.MemoryStream
    4.         'Fill the stream with the binary data from the Image.
    5.         picture.Save(stream, Imaging.ImageFormat.Jpeg)
    6.  
    7.         'Get an array of Bytes from the stream and assign to the parameter.
    8.         command.Parameters.Add("@Picture", SqlDbType.VarBinary).Value = stream.ToArray()
    9.     End Using
    10. 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).

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,263

    Re: Working with SQLite and image files? Save the file or the file path?

    The problem appears to be that you are using the wrong enumeration here:
    vb.net Code:
    1. 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.

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    137

    Re: Working with SQLite and image files? Save the file or the file path?

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

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,263

    Re: Working with SQLite and image files? Save the file or the file path?

    Quote Originally Posted by The_Hobbyist View Post
    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.

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,552

    Re: Working with SQLite and image files? Save the file or the file path?

    As to your original question: https://www.vbforums.com/showthread....=1#post5525350
    Might shed some light on your approach
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    137

    Re: Working with SQLite and image files? Save the file or the file path?

    Quote Originally Posted by jmcilhinney View Post
    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.

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    137

    Re: Working with SQLite and image files? Save the file or the file path?

    Quote Originally Posted by Zvoni View Post
    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!

  13. #13
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,263

    Re: Working with SQLite and image files? Save the file or the file path?

    Quote Originally Posted by The_Hobbyist View Post
    I get an error.
    If only there were some way we could know what that error was.

  14. #14
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,040

    Re: Working with SQLite and image files? Save the file or the file path?

    Quote Originally Posted by The_Hobbyist View Post
    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.

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,552

    Re: Working with SQLite and image files? Save the file or the file path?

    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)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  16. #16
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,040

    Re: Working with SQLite and image files? Save the file or the file path?

    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

    Code:
        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.

  17. #17

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    137

    Re: Working with SQLite and image files? Save the file or the file path?

    Quote Originally Posted by jmcilhinney View Post
    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...


    Quote Originally Posted by wes4dbt View Post
    ...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

    Code:
        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):
    Code:
        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:
    Code:
    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):

    Quote Originally Posted by wes4dbt View Post
    Code:
            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
    Last edited by The_Hobbyist; Jan 25th, 2023 at 04:13 PM.

  18. #18
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,040

    Re: Working with SQLite and image files? Save the file or the file path?

    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.

  19. #19

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    137

    Re: Working with SQLite and image files? Save the file or the file path?

    Quote Originally Posted by wes4dbt View Post
    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:

    Code:
    .Add("@Picture", DbType.Binary, 0, "Picture")
    Now I'm going to read Zvoni's thread.

    Cheers!

  20. #20
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,040

    Re: Working with SQLite and image files? Save the file or the file path?

    Quote Originally Posted by The_Hobbyist View Post
    Thank you! I got it now... You folks are the best! This is how to add the adapter parameter properly:

    Code:
    .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.

  21. #21

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    137

    Re: Working with SQLite and image files? Save the file or the file path?

    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).

  22. #22
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,263

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

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

  23. #23
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,263

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

    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.

  24. #24

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    137

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

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

    Cheers!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width