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

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

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.

Thanks in advance...

解决方案

Regardless of what data access technology or database you use, you need to convert am Image to a Byte first and then save that. On retrieval, you convert the Byte array back to an 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 MemoryStream for the conversion is the same regardless.