Store image to database blob; retrieve from db into Picturebox

Question

Hi I posted this earlier and got some help but still no working solution. I have determined thanks to the last q & a that there is something wrong with my "save to db" code as well as my "retrieve to picture" code. Even If I manually save the pic in the db it stil wont retreive. This is code i patched together from 3 or 4 examples around the net. Ideally if someone had some known good code and could direct me to it that would be the best.

    Dim filename As String = txtName.Text + ".jpg"
    Dim FileSize As UInt32
    Dim ImageStream As System.IO.MemoryStream

    ImageStream = New System.IO.MemoryStream
    PbPicture.Image.Save(ImageStream, System.Drawing.Imaging.ImageFormat.Jpeg)
    ReDim rawdata(CInt(ImageStream.Length - 1))
    ImageStream.Position = 0
    ImageStream.Read(rawdata, 0, CInt(ImageStream.Length))
    FileSize = ImageStream.Length

    Dim query As String = ("insert into actors (actor_pic, filename, filesize) VALUES    (?File, ?FileName, ?FileSize)")
    cmd = New MySqlCommand(query, conn)
    cmd.Parameters.AddWithValue("?FileName", filename)
    cmd.Parameters.AddWithValue("?FileSize", FileSize)
    cmd.Parameters.AddWithValue("?File", rawData)

    cmd.ExecuteNonQuery()

    MessageBox.Show("File Inserted into database successfully!", _
    "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

![enter image description here][1]

'*****retieving to the picturebox using the following code:

  Private Sub GetPicture()
    'This retrieves the pictures from a mysql DB and buffers the rawdata into a memorystream 

    Dim FileSize As UInt32
    Dim rawData() As Byte

    Dim conn As New MySqlConnection(connStr)


    conn.Open()
    conn.ChangeDatabase("psdb")


    Dim cmd As New MySqlCommand("SELECT actor_pic, filesize, filename FROM actors WHERE actor_name = ?autoid", conn)
    Cmd.Parameters.AddWithValue("?autoid", Actor1Box.Text)

    Reader = cmd.ExecuteReader
    Reader.Read()

    'data is in memory 

    FileSize = Reader.GetUInt32(Reader.GetOrdinal("filesize"))
    rawData = New Byte(FileSize) {}

    'get the bytes and filesize 

    Reader.GetBytes(Reader.GetOrdinal("actor_pic"), 0, rawData, 0, FileSize)

    Dim ad As New System.IO.MemoryStream(100000)
    ' Dim bm As New Bitmap

    ad.Write(rawData, 0, FileSize)

    Dim im As Image = Image.FromStream(ad) * "error occurs here" (see below)
    Actor1Pic.Image = im


    Reader.Close()


    conn.Close()
    conn.Dispose()

    ad.Dispose()

Answer 1

Well since getting no help i bashed away at the problem and got it to work finally. Here is my working code.

SAVE TO MySQL out of Picturebox (pbPicture)

    Dim filename As String = txtName.Text + ".jpg"
    Dim FileSize As UInt32

    conn.Close()

    Dim mstream As New System.IO.MemoryStream()
    PbPicture.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
    Dim arrImage() As Byte = mstream.GetBuffer()

    FileSize = mstream.Length
    Dim sqlcmd As New MySqlCommand
    Dim sql As String
    mstream.Close()

    sql = "insert into [your table]  (picture, filename, filesize) 
                               VALUES(@File, @FileName, @FileSize)"

    Try
        conn.Open()
        With sqlcmd
            .CommandText = sql
            .Connection = conn
            .Parameters.AddWithValue("@FileName", filename)
            .Parameters.AddWithValue("@FileSize", FileSize)
            .Parameters.AddWithValue("@File", arrImage)

            .ExecuteNonQuery()
        End With
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        conn.Close()
    End Try

LOAD from MySQL db Back to Picturebox

   Dim adapter As New MySqlDataAdapter
    adapter.SelectCommand = Cmd

    data = New DataTable

    adapter = New MySqlDataAdapter("select picture from [yourtable]", conn)

NOTE!! can only put one picture in picturebox so obviously this query can only return one record for you

    commandbuild = New MySqlCommandBuilder(adapter)
    adapter.Fill(data)

    Dim lb() As Byte = data.Rows(0).Item("picture")
    Dim lstr As New System.IO.MemoryStream(lb)
    PbPicture.Image = Image.FromStream(lstr)
    PbPicture.SizeMode = PictureBoxSizeMode.StretchImage
    lstr.Close()

Answer 2

The accepted and upvoted answer may work, but it is suboptimal and quite wasteful:

1. If the image to save is on disk, there is no reason to use a UI control and a MemoryStream to get the image into a byte array.
2. The code also appears to reuse a single global connection object; these should be created and disposed of rather than reused.
3. Consider saving just the file name to the database, perhaps hashed, with the image saved to a special folder. Saving image data bloats the DB and takes longer to convert.
4. Finally, .GetBuffer() is quite incorrect:
   The memstream buffer will often include unused, allocated bytes. With a 25k test file, ToArray() returns 25434 bytes - the correct size of the image - while GetBuffer() returns 44416. The larger the image, the more empty bytes there will be.

---

This uses MySQL provider objects since it is so-tagged, but the data provider (MySQL, SQLServer, OleDB etc) used doesnt matter: they all work the same.

In cases where the image source is a PictureBox, use a MemoryStream:

Dim picBytes As Byte()
Using ms As New MemoryStream()
    picBox1.Image.Save(ms, imgFormat)
    picBytes = ms.ToArray()        ' NOT GetBuffer!
End Using

Since the image had to come from somewhere, if it is a file, this is all you need:

picBytes = File.ReadAllBytes(filename)

Once you have the image as bytes, to save:

Dim SQL = "INSERT INTO <YOUR TBL NAME> (picture, filename, filesize) VALUES(@Pic, @FileName, @FileSize)"

Using conn As New MySqlConnection(connstr)
    Using cmd As New MySqlCommand(SQL, conn)
        conn.Open()

        cmd.Parameters.Add("@Pic", MySqlDbType.Blob).Value = picBytes
        cmd.Parameters.Add("@FileName", MySqlDbType.String).Value = filename
        cmd.Parameters.Add("@FileSize", MySqlDbType.Int32).Value = FileSize

        cmd.ExecuteNonQuery()

    End Using
End Using            ' close and dispose of Connection and Command objects

Loading Image from DB

Dim imgData As Byte()

'... open connection, set params etc
Using rdr As MySqlDataReader = cmd.ExecuteReader

    If rdr.HasRows Then
        rdr.Read()
        imgData = TryCast(rdr.Item("Image"), Byte())
        ' in case this record has no image
        If imgData IsNot Nothing Then
             ' ToDo: dispose of any previous Image

            ' create memstream from bytes
            Using ms As New MemoryStream(imgData)
                ' create image from stream, assign to PicBox
                picBox1.Image = CType(Image.FromStream(ms), Image)

            End Using
        End If
    End If
End Using

Note that Bitmaps and Images must be disposed of. If you repeatedly create new images as the user browses the database your app will leak and eventually crash. If you convert back and forth a lot, you can write a helper or extension method to convert images to bytes and vice versa.

DBConnection and DBCommand objects also need to be disposed of. The Using block does this for us.

References, Resources:

• Reuse or Dispose DB Connections? sample GetConnection helper
• Connection Pooling
• MemoryStream.GetBuffer()
• Can we stop using AddWithValue()