如何利用VB.NET与adodb连接在mysql数据库中插入图像

Question

我想在VB.NET2008中使用adodb连接将一个图像插入到mysql数据库中。

我正在使用一个选择查询插入数据到数据库，这里是我的代码添加或保存数据。

    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

我想将数据库中的图像添加到字段图片中，我使用blob作为它的数据类型，我还想从数据库中检索图像并将其显示在PictureBox中...有人能帮我解决一下我的问题吗。

事先谢谢...

Answer 1

创建具有blob字段的表，如下所示

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

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

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.")

Answer 2

无论使用什么数据访问技术或数据库，都需要先将am图像转换为字节，然后将其保存。检索时，将字节数组转换回图像。

要保存：

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

要检索：

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

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