VB.net - 从 mysql 数据库直接向/从图片框插入/检索图片

VB.net - insert/retrieve picture from mysql Database directly to/from a Picturebox

提问by dMO

I'm am having a heck of a time finding a code snippet that works for this. I have got to the point where it appears the picture is stored as a blob (perhaps incorrectly) by using this code.

我正忙着寻找适用于此的代码片段。我已经到了使用此代码将图片存储为 blob（可能不正确）的地步。

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)

but on retrieving 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()

I get the error "parameter not valid" in the area noted. FYI If anyone even has some better (working) code examples than this that I can plug in versus debugging this mess that would be great too.

我在指出的区域中收到错误“参数无效”。仅供参考，如果有人甚至有一些比这更好的（工作）代码示例，我可以插入而不是调试这种混乱，那也很棒。

回答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 once picture in picturebox so obvoiusly 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 user1122321

Well, You are trying to cast the type MemoryStream when the 'Image.FromStream' is expecting System.IO.Stream

好吧，当“Image.FromStream”期待 System.IO.Stream 时，您正试图转换类型 MemoryStream

回答by GESU

I just wrote this code as part of my contribution to this forum though not a member but love to help. This code search for multiple records and their corresponding images as you keep searching for record instead of search for one record and close the app to search again. This code allows you to search a record, clear the fields enter the search criteria and search again & again.

我只是写了这段代码，作为我对这个论坛的贡献的一部分，虽然不是会员，但很乐意提供帮助。此代码搜索多条记录及其对应的图像，因为您一直在搜索记录而不是搜索一条记录，然后关闭应用程序再次搜索。此代码允许您搜索记录，清除字段输入搜索条件并再次搜索。

    If TextBox3.Text = "" Then ' This is the search field to be used it could be any field from your database that will match the value from the database. Either firstname, phone or email etc
        MsgBox("Nothing to search for from the database", MsgBoxStyle.OkOnly + MsgBoxStyle.Exclamation, "Oop!")
    End If
    Try
        conn.Open()
        Dim data As New MySqlDataAdapter("SELECT * FROM users_data WHERE phoneno = '" & TextBox3.Text & "' ", conn)

        Dim dTable As New DataTable
        data.Fill(dTable)

        If dTable.Rows.Count > 0 Then
            TextBox1.Text = dTable.Rows(0).Item("firstname")
            TextBox2.Text = dTable.Rows(0).Item("lastname")

            'Fetching the corresponding image to this member
            Dim arrImage As Byte()
            Dim myMS As New IO.MemoryStream
            If Not IsDBNull(dTable.Rows(0).Item("myimage")) Then
                arrImage = dTable.Rows(0).Item("myimage")
                For Each ar As Byte In arrImage
                    myMS.WriteByte(ar)
                Next
                PictureBox1.Image = System.Drawing.Image.FromStream(myMS)
            End If

        Else
            MsgBox("No record found for this Phone No: " & TextBox3.Text & " Enter a valid Phone No or consult the Admin Manager", MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "Record not found")
            clear()
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
        Exit Sub
    Finally
        conn.Close()
    End Try

This code could work for MYSQL Server database and Microsoft SQL databases as well. The only difference is is changing this statement Dim data As New MySqlDataAdapterwhich is for MYSQL Server to Dim data As New SqlDataAdapterfor Microsoft SQL server. Good evening all StackOverflowers

此代码也适用于 MYSQL Server 数据库和 Microsoft SQL 数据库。唯一的区别是将用于 MYSQL Server 的Dim data As New MySqlDataAdapter语句更改为用于 Microsoft SQL Server 的Dim data As New SqlDataAdapter。晚上好所有 StackOverflowers

回答by Dlorko

VB.NET - insert/retrieve picture from MySQL Database directly to/from a Picturebox

VB.NET - 从 MySQL 数据库直接向/从图片框插入/检索图片

Example Procedure VISUAL BASIC. NET 2017: I'm sharing: 1° Configuration of the table MySQL and variables. 2° Visual design 3° code.

示例程序 VISUAL BASIC。NET 2017：我正在分享：1° 表 MySQL 和变量的配置。2° 视觉设计 3° 代码。

Overview of the procedure: The user put an imagen in a picturebox. The imagen located in picturebox is SAVED in MySQL table. Another option is give the ID of the image (textbox1.text) and then LOAD the image from the MySQL table. There is another button to clear the picturebox but is not strictly necessary. It is not the perfect procedure it has being made only to understand the flow of images load/save to MySQL table AND I SHARE my knoledge with you.

过程概述：用户将图像放入图片框中。位于图片框中的图像保存在 MySQL 表中。另一种选择是给出图像的 ID (textbox1.text)，然后从 MySQL 表中加载图像。还有另一个按钮可以清除图片框，但不是绝对必要的。这不是一个完美的过程，它只是为了理解图像加载/保存到 MySQL 表的流程，我与你分享我的知识。

1. Configure the table in MySQL server. Example: table used: "imagenes" variables:

1. 在 MySQL 服务器中配置表。示例：使用的表：“imagenes”变量：

"idimagen" as INT. (Primary key)

“idimagen”作为INT。（首要的关键）

"imagen" as LONGBLOB (allow null) ------->>>> Container of the image.

"imagen" as LONGBLOB (allow null) ------->>>> 图像的容器。

Mysql table configuration example:

Mysql表配置示例：

1. DESIGN IN VB.NET

2. 在 VB.NET 中设计

Designed form used:

使用的设计形式：

1. Code used:

3. 使用的代码：

Add reference: MySQL.data 6.10.5.0 You need to import: MySql.Data.MySqlClient AND Imports System.IO

添加参考：MySQL.data 6.10.5.0 你需要导入：MySql.Data.MySqlClient AND Imports System.IO

Public Class Form1



 Dim Server As String = "XX.XX.XXX.XXX"
    Dim UserID As String = "XXXXXXXXXXXXXX"
    Dim Password As String = "PASSWORD"
    Dim Database As String = "DATABASE NAME"
    Dim Port As Integer = 3306
    Dim AllowUserVariables As Boolean = True

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim ULTIMOID As Integer = 0
        Try
            Dim xxxxcon As New MySqlConnectionStringBuilder()
            xxxxcon.Database = Database
            xxxxcon.Server = Server
            xxxxcon.UserID = UserID
            xxxxcon.Password = Password
            xxxxcon.Port = 3306
            xxxxcon.AllowUserVariables = True
            Dim con As New MySqlConnection(xxxxcon.ToString)
            Dim dbsql As String = "SELECT MAX(idimagen) AS 'ULTIMOID' FROM imagenes;"
            Dim cmdMy As New MySqlCommand(dbsql, con)
            con.Open()
            cmdMy.ExecuteNonQuery()
            Try
                ULTIMOID = Convert.ToInt32(cmdMy.ExecuteScalar())
            Catch ex As Exception
                ULTIMOID = 0
            End Try
            con.Close()
        Catch ex As Exception
            MsgBox("Problemas leyendo la BASE para obtener el último ID. Error: " & ex.Message)
        End Try
               Try
            Dim xxxxcon As New MySqlConnectionStringBuilder()
            xxxxcon.Database = Database
            xxxxcon.Server = Server
            xxxxcon.UserID = UserID
            xxxxcon.Password = Password
            xxxxcon.Port = 3306
            xxxxcon.AllowUserVariables = True
            Dim FileSize As UInt32
            Dim con As New MySqlConnection(xxxxcon.ToString)
            Dim mstream As New System.IO.MemoryStream()
            PictureBox1.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 imagenes (idimagen, imagen) VALUES(@id, @imagen)"
            Try
                con.Open()
                With sqlcmd
                    .CommandText = sql
                    .Connection = con
                    .Parameters.AddWithValue("@id", ULTIMOID + 1)
                    .Parameters.AddWithValue("@imagen", arrImage)
                    .ExecuteNonQuery()
                End With
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                con.Close()
            End Try

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        OpenFileDialog2.Filter = "image file (*.jpg, *.bmp, *.png) | *.jpg; *.bmp; *.png| all files (*.*) | *.* "
        If OpenFileDialog2.ShowDialog <> Windows.Forms.DialogResult.Cancel Then
            PictureBox1.Image = Image.FromFile(OpenFileDialog2.FileName)
        End If
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        PictureBox1.Image = Nothing
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Dim xxxxcon As New MySqlConnectionStringBuilder()
        xxxxcon.Database = Database
        xxxxcon.Server = Server
        xxxxcon.UserID = UserID
        xxxxcon.Password = Password
        xxxxcon.Port = 3306
        xxxxcon.AllowUserVariables = True

        Dim con As New MySqlConnection(xxxxcon.ToString)
        Try
            Dim ds As New DataSet

            Dim dbsql As String = "SELECT * FROM imagenes WHERE idimagen = " & TextBox1.Text & ";"

            Dim cmdMy As New MySqlCommand(dbsql, con)
            con.Open()
            Dim da As New MySqlDataAdapter(dbsql, con)
            da.Fill(ds, "Imagenes")
            con.Close()
            If ds.Tables("imagenes").Rows.Count > 0 Then
                Dim bytes As [Byte]() = ds.Tables("imagenes").Rows(0).Item(1)
                Dim ms As New MemoryStream(bytes)
                PictureBox1.Image = Image.FromStream(ms)
            Else
                MsgBox("No record found for this Phone No: " & TextBox1.Text)

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try
    End Sub
End Class

回答by Chris Haas

What happens if you switch this:

如果你切换这个会发生什么：

Dim ad As New System.IO.MemoryStream(100000)

to:

到：

Dim ad As New System.IO.MemoryStream()

EDIT

编辑

VB array sizes are different than other programming languages, I think you need to do a minus 1:

VB数组大小与其他编程语言不同，我认为你需要做一个负1：

rawData = New Byte(FileSize - 1) {}

EDIT 2

编辑 2

Alright, lets look over what you have for raw binary data. All JPGs should start with FFD8and end with FFD9. Insert the following after you set the rawDataarray. If it throws an error then your JPEG information is corrupt.

好的，让我们看看你有什么原始二进制数据。所有 JPG 都应FFD8以FFD9. 设置rawData数组后插入以下内容。如果它抛出错误，则您的 JPEG 信息已损坏。

    If (rawData(0) = &HFF) AndAlso (rawData(1) = &HD8) Then
        Trace.WriteLine("File start OK")
    Else
        Throw New ApplicationException("Invalid jpg header")
    End If

    If (rawData(rawData.Length - 2) = &HFF) AndAlso (rawData(rawData.Length - 1) = &HD9) Then
        Trace.WriteLine("File end OK")
    Else
        Throw New ApplicationException("Invalid jpg footer")
    End If

EDIT 3

编辑 3

We're going to need to see what the first few bytes of the data look like. Run this and post what gets outputed:

我们将需要查看数据的前几个字节是什么样的。运行它并发布输出的内容：

    For I = 0 To 20
        Trace.Write(Convert.ToString(rawData(I), 16).ToUpperInvariant())
    Next