如何使用 vb.net 和 adodb 连接在 mysql 数据库中插入图像

how to insert image in mysql database using vb.net and adodb connection

提问by user3819206

I want to insert an image into a mysql database using the adodb connection in vb.net 2008.

我想使用 vb.net 2008 中的 adodb 连接将图像插入到 mysql 数据库中。

I am using a select query to insert data into database, here is my code for adding or saving data...

我正在使用选择查询将数据插入数据库，这是我添加或保存数据的代码...

    rs.Open("select * from registration where Debt_ID = '" & txtDebt_ID.Text & "' ", cnn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockPessimistic)
    If rs.RecordCount = 1 Then
        MsgBox("ID already exist!", MsgBoxStyle.Exclamation, vbOK)
        rs.Close()
        cnn.Close() 

        Exit Sub
    Else

        rs.AddNew()
        rs.Fields("Debt_ID").Value = txtDebt_ID.Text
        rs.Fields("LastName").Value = txt_Lastname.Text
        rs.Fields("firstName").Value = txt_Firstname.Text
        rs.Fields("MiddleName").Value = txt_Middlename.Text
        rs.Fields("age").Value = txt_Age.Text
        rs.Fields("birthdate").Value = txt_Birthdate.Text
        rs.Fields("civil_status").Value = txtCivil_status.Text
        rs.Fields("address").Value = txt_Address.Text
        rs.Fields("occupation").Value = txt_Address.Text
        rs.Fields("contact_no").Value = txt_Contact.Text
        'rs.Fields("picture").Value = PictureBox1.Image
        rs.Save()
        rs.Close()
    End If

I wanted to add an image on the database into the field picture and I'm using blob as my datatype for it, I also want to retrieve the image from the database and display it in a picturebox... can someone please help regarding my problem.

我想将数据库上的图像添加到字段图片中，并且我使用 blob 作为我的数据类型，我还想从数据库中检索图像并将其显示在图片框中...有人可以帮助我吗问题。

Thanks in advance...

提前致谢...

回答by jmcilhinney

Regardless of what data access technology or database you use, you need to convert am Imageto a Bytefirst and then save that. On retrieval, you convert the Bytearray back to an Image.

无论您使用何种数据访问技术或数据库，您都需要先将am 转换Image为 aByte然后再保存。在检索时，您将Byte数组转换回Image.

To save:

保存：

Dim connection As New SqlConnection("connection string here")
Dim command As New SqlCommand("UPDATE MyTable SET Picture = @Picture WHERE ID = 1", connection)

'Create an Image object.'
Using picture As Image = Image.FromFile("file path here")
    'Create an empty stream in memory.'
    Using stream As New IO.MemoryStream
        'Fill the stream with the binary data from the Image.'
        picture.Save(stream, Imaging.ImageFormat.Jpeg)

        'Get an array of Bytes from the stream and assign to the parameter.'
        command.Parameters.Add("@Picture", SqlDbType.VarBinary).Value = stream.GetBuffer()
    End Using
End Using

connection.Open()
command.ExecuteNonQuery()
connection.Close()

To retrieve:

检索：

Dim connection As New SqlConnection("connection string here")
Dim command As New SqlCommand("SELECT Picture FROM MyTable WHERE ID = 1", connection)

connection.Open()

Dim pictureData As Byte() = DirectCast(command.ExecuteScalar(), Byte())

connection.Close()

Dim picture As Image = Nothing

'Create a stream in memory containing the bytes that comprise the image.'
Using stream As New IO.MemoryStream(pictureData)
    'Read the stream and create an Image object from the data.'
    picture = Image.FromStream(stream)
End Using

That example is for ADO.NET and SQL Server but the principle of using a MemoryStreamfor the conversion is the same regardless.

该示例适用于 ADO.NET 和 SQL Server，但MemoryStream无论如何使用 a进行转换的原理都是相同的。

回答by Suji

create a table with BLOBfield as follows

创建一个带有BLOB字段的表，如下所示

CREATE TABLE picture (
ID   INTEGER AUTO_INCREMENT,
IMAGE   BLOB, 
PRIMARY KEY (ID)
);

insert into this table using the following query string:

使用以下查询字符串插入此表：

Dim mstream As New System.IO.MemoryStream()
pic_box_save.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
Dim arrImage() As Byte = mstream.GetBuffer()
mstream.Close()
Try
 sql = "INSERT INTO image_in_db(id, image_data) VALUES(@image_id, @image_data)"
 sql_command = New MySqlClient.MySqlCommand(sql, sql_connection)
 sql_command.Parameters.AddWithValue("@image_id", Nothing)
 sql_command.Parameters.AddWithValue("@image_data", arrImage)
 sql_command.ExecuteNonQuery()
Catch ex As Exception
 MsgBox(ex.Message)
 Exit Sub
End Try
MsgBox("Image has been saved.")