将图片存储到数据库的二进制大对象中；从数据库中检索并显示在Picturebox中。

Question

将图片存储到数据库的二进制大对象中；从数据库中检索并显示在Picturebox中。

4

你好，我之前发布了这个问题并得到一些帮助，但仍然没有解决方案。通过最后一个问答，我已经确定我的“保存到数据库”代码和“检索图片”的代码都有问题。即使我手动将图片保存在数据库中，它仍然无法检索。这是我从网络上的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]

'*****使用以下代码将图像检索到picturebox中：

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

- dMO

5个回答

2

接受并点赞的答案可能有效，但它是次优和非常浪费的：

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

因为标记了这个问题的MySQL提供程序对象，所以使用MySQL提供程序对象，但是使用的数据提供程序（MySQL，SQLServer，OleDB等）并不重要：它们都可以起作用。

如果图片来源是PictureBox，请使用MemoryStream：

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

由于图片必须来自某个地方，如果是文件，则只需要这些：

picBytes = File.ReadAllBytes(filename)

一旦你有了图像的字节，要保存:

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

从数据库加载图像

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

请注意，必须处理位图和图像。如果在用户浏览数据库时反复创建新的图像，您的应用程序将泄漏并最终崩溃。如果来回频繁转换，可以编写帮助程序或扩展方法将图像转换为字节，反之亦然。 DBConnection和DBCommand对象也需要被处理。 Using块可以为我们完成这个任务。 参考资源:

- Ňɏssa Pøngjǣrdenlarp

0

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

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

  ' 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

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

' 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

以下代码用于从数据库中显示图像。使用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

界面 - 希望能帮到你

- Derek Mundondo

0

我在使用@dMo先生的程序时遇到了问题，它显示了一个错误：“列'picture'不能为空”

这是我的代码。

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

附言：很抱歉我发了这个广告并回答了这个问题，因为我没有足够的声望来评论这篇文章。

- DDD

0

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

- user1237720

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- dMO · Accepted Answer

由于没有得到帮助，我自己研究解决了问题，现在分享我的可行代码。

将图片框（pbPicture）中的内容保存到MySQL数据库中：

    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

从MySQL数据库加载回PictureBox

   Dim adapter As New MySqlDataAdapter
    adapter.SelectCommand = Cmd

    data = New DataTable

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

注意！PictureBox只能放置一张图片，因此这个查询显然只能为您返回一条记录。

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