mysql - 尝试将图片插入sql数据库

Question

我想将图像插入到我的 SQL 数据库中。我当前有这段代码，它正在图像应该在的位置输入 Null。为什么会出现这种情况？

我在这里使用一个简单的 addquestion 类。

您可以在下面找到我使用的代码:

Imports System.IO
Imports System.Data.SqlClient

Public Class addquestion
    Dim con As New System.Data.Odbc.OdbcConnection("DRIVER={MySQL ODBC 5.2 ANSI Driver};SERVER=localhost;PORT=3306;DATABASE=physicsapp;USER=root;PASSWORD=root;OPTION=3;")
    Dim rs As Odbc.OdbcDataReader

Private Sub addquestion_Load(sender As Object, e As EventArgs) Handles MyBase.Load

End Sub

Private Sub QuestionButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles QuestionButton.Click
    Dim myFileDlog As New OpenFileDialog


    myFileDlog.InitialDirectory = "c:/"


    myFileDlog.Filter = "All Files (*.*)|*.*" & _"|Picture Files (*.png)|*.png"


    myFileDlog.FilterIndex = 1


    myFileDlog.RestoreDirectory = True


    If myFileDlog.ShowDialog() = _
        DialogResult.OK Then
        If Dir(myFileDlog.FileName) <> "" Then
            MsgBox("File Exists: " & _
                   myFileDlog.FileName, _
                   MsgBoxStyle.Information)
        Else
            MsgBox("File Not Found", _
                   MsgBoxStyle.Critical)
        End If
    End If


    qnametextbox.Text = myFileDlog.FileName
End Sub

    Dim ans as Char = "B"
    Dim file As String = qnametextbox.Text
    Dim question As Image
    question = Image.FromFile(file)

    Dim ms As New MemoryStream()
    question.Save(ms, System.Drawing.Imaging.ImageFormat.Png)
    Dim data As Byte() = ms.GetBuffer()

    Dim s As String = "INSERT INTO tests VALUES (@Question ,'" & ans & "', 200)"

    Dim sql As New Odbc.OdbcCommand(s, con)
    sql.Parameters.Add("@Question", Odbc.OdbcType.VarBinary).Value = data
    con.Open()
    sql.ExecuteNonQuery()
    con.Close()

这是表的定义

CREATE TABLE `tests` (
  `Question` blob NOT NULL,
  `CorrectAnswer` varchar(8) DEFAULT NULL,
  `QuestionNumber` int(8) NOT NULL AUTO_INCREMENT,
  KEY `QuestionNumber` (`QuestionNumber`)
) ENGINE=MyISAM AUTO_INCREMENT=201 DEFAULT CHARSET=utf8

任何建议都可以提供帮助。

谢谢。

Answer 1

无论您使用什么数据访问技术或数据库，您都需要先将Image转换为Byte，然后保存。检索时，您将 Byte 数组转换回 Image。

保存:

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