为了账号安全,请及时绑定邮箱和手机立即绑定

将图像存储到数据库Blob;从数据库检索到Picturebox

/ 猿问

将图像存储到数据库Blob;从数据库检索到Picturebox

偶然的你 2019-12-06 14:42:59

您好,我在较早之前发布了此内容,并获得了一些帮助,但仍然没有有效的解决方案。由于最后的问与答,我确定我的“保存到数据库”代码以及“检索到图片”代码有问题。即使我手动将图片保存在数据库中,它也不会恢复。这是我从网络上的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)

![在此处输入图片描述] [1]


' * **使用以下代码接收到图片框:


  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)

查看完整描述

3 回答

?
慕哥9229398

好吧,既然没有帮助,我就解决了这个问题,终于使它起作用了。这是我的工作代码。


从Picturebox中保存到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

从MySQL db加载回到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()


查看完整回答
反对 回复 2019-12-06
?
繁花不似锦

可以接受并赞成的答案可能有效,但它不是次优且非常浪费:


如果图像保存在磁盘上,没有理由使用UI控件和一个MemoryStream以获取图像到一个字节数组。

该代码似乎还可以重用单个全局连接对象。这些应该创建和处理而不是重复使用。

考虑将文件名仅保存到数据库(可能是哈希文件),并将图像保存到特殊文件夹中。保存图像数据会使数据库肿,并且转换时间更长。

最后,这.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

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


DBConnection并且DBCommand还需要处理物体。该Using块为我们做到了。


查看完整回答
反对 回复 2019-12-06
?
慕村225694

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


查看完整回答
反对 回复 2019-12-06

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信