Store image to database blob; retrieve from db int

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

Tested Code for Store and Retrieve Images using MySQL and VB.NET

Public Class FMImage

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    If OpenFileDialog1.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
        TextBox1.Text = OpenFileDialog1.FileName

        Dim filename As String = TextBox1.Text
        Dim FileSize As UInt32

        Dim Conn As MySql.Data.MySqlClient.MySqlConnection
        Conn = New MySql.Data.MySqlClient.MySqlConnection

        Try
            If Conn.State = ConnectionState.Open Then Conn.Close()
            Conn.ConnectionString = MySQLConnectionString
            Conn.Open()

        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Connect")
        End Try


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

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

        'CREATE TABLE `actors` ( `actor_pic` longblob,`filesize` bigint(20) default NULL,`filename` varchar(150) default NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

        sql = "insert into actors (actor_pic, filesize, filename) VALUES(@File, @FileName, @FileSize)"

        Try

            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)
        End Try


        Dim adapter As New MySql.Data.MySqlClient.MySqlDataAdapter
        adapter.SelectCommand = New MySql.Data.MySqlClient.MySqlCommand("SELECT actor_pic, filesize, filename FROM actors", Conn)

        Dim Data As New DataTable
        'adapter = New MySql.Data.MySqlClient.MySqlDataAdapter("select picture from [yourtable]", Conn)

        Dim commandbuild As New MySql.Data.MySqlClient.MySqlCommandBuilder(adapter)
        adapter.Fill(Data)
        MsgBox(Data.Rows.Count)


        Dim lb() As Byte = Data.Rows(Data.Rows.Count - 1).Item("actor_pic")
        Dim lstr As New System.IO.MemoryStream(lb)
        PbPicture.Image = Image.FromStream(lstr)
        PbPicture.SizeMode = PictureBoxSizeMode.StretchImage
        lstr.Close()

    End If
End Sub

Public Function ConvertImageFiletoBytes(ByVal ImageFilePath As String) As Byte()
    Dim _tempByte() As Byte = Nothing
    If String.IsNullOrEmpty(ImageFilePath) = True Then
        Throw New ArgumentNullException("Image File Name Cannot be Null or Empty", "ImageFilePath")
        Return Nothing
    End If
    Try
        Dim _fileInfo As New IO.FileInfo(ImageFilePath)
        Dim _NumBytes As Long = _fileInfo.Length
        Dim _FStream As New IO.FileStream(ImageFilePath, IO.FileMode.Open, IO.FileAccess.Read)
        Dim _BinaryReader As New IO.BinaryReader(_FStream)
        _tempByte = _BinaryReader.ReadBytes(Convert.ToInt32(_NumBytes))
        _fileInfo = Nothing
        _NumBytes = 0
        _FStream.Close()
        _FStream.Dispose()
        _BinaryReader.Close()
        Return _tempByte
    Catch ex As Exception
        Return Nothing
    End Try
End Function

Public Function ConvertBytesToMemoryStream(ByVal ImageData As Byte()) As IO.MemoryStream
    Try
        If IsNothing(ImageData) = True Then
            Return Nothing
            'Throw New ArgumentNullException("Image Binary Data Cannot be Null or Empty", "ImageData")
        End If
        Return New System.IO.MemoryStream(ImageData)
    Catch ex As Exception
        Return Nothing
    End Try
End Function

Public Function ConvertImageFiletoMemoryStream(ByVal ImageFilePath As String) As IO.MemoryStream
    If String.IsNullOrEmpty(ImageFilePath) = True Then
        Return Nothing
        ' Throw New ArgumentNullException("Image File Name Cannot be Null or Empty", "ImageFilePath")
    End If
    Return ConvertBytesToMemoryStream(ConvertImageFiletoBytes(ImageFilePath))
End Function

End Class

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

Answer 3

I am having problem using mr @dMo 's program its showing me an error "Column 'picture' cannot be null"

here is my code.

Private Sub ButtonSave_Click(sender As Object, e As EventArgs) Handles ButtonSave.Click
    Dim filename As String = TextBoxSave.Text + ".jpg"
    Dim FileSize As UInt32

    conn.Close()

    Dim mstream As New System.IO.MemoryStream()
    PictureBoxSave.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 employeedetails  (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
End Sub

P.S. Im sorry for posting this adnand answer I have no enough reputation to comment to this post

Answer 4

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