How to Effectively Store and Retrieve Images from a Database Blob?

Store Image in Database Blob and Retrieve It for Display

Problem

Storing and retrieving images from a database is a common task in programming. While it may seem straightforward, issues can arise if the code is not implemented correctly. In this case, a user encountered problems when saving images to a database and displaying them in a Picturebox control.

Solution

To address the issue, let's examine the user's code and identify potential problems:

Saving to Database:

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

The code fails to initialize rawdata with the correct dimensions. To store the image bytes correctly, it should be redimensioned to match the length of the image stream:

ReDim rawdata(CInt(ImageStream.Length))

Retrieving from Database:

Dim ad As New System.IO.MemoryStream(100000)
Dim im As Image = Image.FromStream(ad) * "error occurs here" (see below)

The error arises because the ad memory stream is not correctly positioned or has insufficient capacity. The following code fixes this issue:

ad.Position = 0
Dim im As Image = Image.FromStream(ad)

Working Code for Saving Image to Database

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

Working Code for Retrieving Image from Database

Dim adapter As New MySqlDataAdapter
adapter.SelectCommand = Cmd

data = New DataTable

adapter = New MySqlDataAdapter("select picture from [yourtable]", conn)
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()

The above is the detailed content of How to Effectively Store and Retrieve Images from a Database Blob?. For more information, please follow other related articles on the PHP Chinese website!