MySQL 将图像存储到数据库 blob；从数据库检索到图片框

Store image to database blob; retrieve from db into Picturebox

提问by dMO

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.

嗨，我早些时候发布了这个并得到了一些帮助，但仍然没有可行的解决方案。感谢上次问答，我确定我的“保存到数据库”代码和“检索图片”代码有问题。即使我手动将图片保存在数据库中，它也不会检索。这是我从网络上的 3 或 4 个示例拼凑而成的代码。理想情况下，如果有人有一些已知的好代码并且可以指导我使用它，那将是最好的。

    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]

![在此处输入图像描述][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()

回答by dMO

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)

从图片框保存到 MySQL (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

从 MySQL db 加载返回图片框

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

回答by ??ssa P?ngj?rdenlarp

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 anda MemoryStreamto 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.

1. 如果要保存的图像在磁盘上，则没有理由使用 UI 控件和aMemoryStream将图像放入字节数组中。
2. 该代码似乎还重用了单个全局连接对象；这些应该被创建和处理，而不是重复使用。
3. 考虑只将文件名保存到数据库中，可能是散列的，图像保存到一个特殊的文件夹中。保存图像数据会使数据库膨胀并需要更长的时间来转换。
4. 最后，这.GetBuffer()是非常不正确的：
   memstream 缓冲区通常包含未使用的、已分配的字节。对于 25k 测试文件，ToArray()返回 25434 字节 - 图像的正确大小 - 而GetBuffer()返回 44416。图像越大，空字节就越多。

---

---

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.

这使用 MySQL 提供程序对象，因为它是这样标记的，但使用的数据提供程序（MySQL、SQLServer、OleDB 等）并不重要：它们都工作相同。

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

如果图像源是 PictureBox，请使用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 Bitmapsand Imagesmust 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.

注意Bitmaps和Images必须处理掉。如果您在用户浏览数据库时反复创建新图像，您的应用程序将泄漏并最终崩溃。如果您来回转换很多，您可以编写一个帮助程序或扩展方法来将图像转换为字节，反之亦然。

DBConnectionand DBCommandobjects also need to be disposed of. The Usingblock does this for us.

DBConnection和DBCommand对象也需要处理。该Using块做到这一点对我们来说。

References, Resources:

参考资料，资源：

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

• 重用或处置数据库连接？样本GetConnection助手
• 连接池
• MemoryStream.GetBuffer()
• 我们可以停止使用 AddWithValue()

回答by DDD

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

我在使用 @dMo 先生的程序时遇到问题，它向我显示错误“列'图片'不能为空”

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

PS我很抱歉发布这个和答案我没有足够的声誉来评论这篇文章

回答by Derek Mundondo

The code below inserts a record of vehicle information in a table. The Image of the car selected is converted into memory stream and saved to the Database as Varbinary.

下面的代码在表中插入车辆信息记录。所选汽车的图像被转换为​​内存流并作为 Varbinary 保存到数据库中。

A function is used to convert the image to memory stream.

一个函数用于将图像转换为内存流。

  ' Upload new vehicle image

    Private Sub BtnUpload_Click(sender As Object, e As EventArgs) Handles 
    BtnUpload.Click

    Dim imgBinary As Byte()

    With Me.OpenFileDialog1
        .FileName = ""
        .Filter = "Image Files(*.BMP;*.JPG;*.JEPG;*.GIF)|*.BMP;*.JPG;*.JEPG;*.GIF|All files (*.*)|*.*"
        .RestoreDirectory = True
        .ValidateNames = True
        .CheckFileExists = True
        If .ShowDialog = Windows.Forms.DialogResult.OK Then
            'Me.PicImage.Image.Dispose()
            Me.PicImage.Image = System.Drawing.Image.FromFile(.FileName)
        End If
    End With

    imgBinary = ConvertImage(PicImage.Image)

    Dim command As New SqlCommand("insert into MyCars(CarId, Manufacture, CarModel, CarClass, CarImage) values(@CarId, @Manufacture, @CarModel, @CarClass, @CarImage)", connection)

    command.Parameters.Add("@CarId", SqlDbType.VarChar).Value = CInt(TxtID.Text)
    command.Parameters.Add("@Manufacture", SqlDbType.VarChar).Value = TxtManufacturer.Text
    command.Parameters.Add("@CarModel", SqlDbType.VarChar).Value = TxtModel.Text
    command.Parameters.Add("@CarClass", SqlDbType.VarChar).Value = TxtClass.Text
    command.Parameters.Add("@CarImage", SqlDbType.VarBinary).Value = imgBinary

    Try
        connection.Open()
        If command.ExecuteNonQuery() = 1 Then
            MessageBox.Show("Car # " & TxtID.Text & " successfully added to database.")
        Else
            MessageBox.Show("Car not added!")
        End If

    Catch ex As Exception
        MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
    Finally
        connection.Close()
    End Try

End Sub

The following function converts the image into memory stream.

以下函数将图像转换为内存流。

' Convert Image from Memory Stream
Public Function ConvertImage(ByVal myImage As Image) As Byte()

    Dim mstream As New MemoryStream
    myImage.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)

    Dim myBytes(mstream.Length - 1) As Byte
    mstream.Position = 0

    mstream.Read(myBytes, 0, mstream.Length)

    Return myBytes

End Function

The code below is used to display the image from the database. Use the ID (as an integer) to display the image.

下面的代码用于显示数据库中的图像。使用 ID（作为整数）显示图像。

    Private Sub BtnShowImg_Click(sender As Object, e As EventArgs) Handles BtnShowImg.Click

    Dim command As New SqlCommand("select * from MyCars where CarId = @CarId", connection)
    command.Parameters.Add("CarId", SqlDbType.VarChar).Value = TxtID.Text

    Dim table As New DataTable()
    Dim adapter As New SqlDataAdapter(command)

    adapter.Fill(table)

    If table.Rows.Count <= 0 Then

        MessageBox.Show("No Image for the Selected Id")

    Else

        TxtID.Text = table.Rows(0)(0).ToString()                ' Col 0 = CarId
        TxtManufacturer.Text = table.Rows(0)(1).ToString()      ' Col 1 = Manufacturer
        TxtModel.Text = table.Rows(0)(2).ToString               ' Col 2 = Model
        TxtClass.Text = table.Rows(0)(3).ToString()             ' Col 3 = Vehicle Class

        Dim img() As Byte
        img = table.Rows(0)(4)  ' Col 4 = Img

        Dim ms As New MemoryStream(img)

        PicImage.Image = Image.FromStream(ms)

    End If

End Sub

Interface - Hope It Helps

界面 - 希望有帮助

回答by Derek Mundondo

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

使用 MySQL 和 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