sql-server - 错误：填充：selectcommand.connection 属性尚未得到

Question

我正在尝试从数据库中检索二进制数据。

我收到此错误：“错误：填充：selectcommand.connection 属性尚未得到”。我找不到错误。

Public Shared Function BinaryData(ByVal sFileName As String) As Byte()
            Dim strSql As String
            Dim binaryFile As Byte() = Nothing
            Dim dt As DataTable
            Dim myCommand As New SqlCommand
            Dim sqlConn As New SqlConnection
            sqlConn = New SqlConnection("Data Source=xxx;Initial Catalog=xx;Persist Security Info=True;User ID=wxx;Password=xx;MultipleActiveResultSets=True;Application Name=EntityFramework")
            sqlConn.Open()
            myCommand.Connection = sqlConn
            strSql = "SELECT Data  FROM tbldrive WHERE Filename = '" + sFileName + "'"
            Dim scmd As New SqlCommand(strSql, sqlConn)

            dt = DataComponent.DataTableQuery(DataComponent.SqlConn, strSql)

            If dt.Rows.Count > 0 Then
                Try
                    binaryFile = DirectCast(dt.Rows(0).Item("Data"), Byte())
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try

            End If
            Return binaryFile
        End Function

标签： sql-servervb.net

Answer 1

It looks like you've tried a few things in that code but accidentally left the remains of some attempts in there.

There are some things you could do a bit differently: as you're only after one item from the database, you can use ExecuteScalar; and when the code has finished with the SQL connection and command, they should have .Dispose() called on them - the Using statement will take care of that for you even if something goes wrong. Finally, you should always use SQL parameters to pass parameters to an SQL query - it makes it more secure and avoids having to worry about things like apostrophes in the value.

Public Shared Function BinaryData(ByVal sFileName As String) As Byte()
    Dim sql As String = "SELECT Data FROM tbldrive WHERE Filename = @fname"
    Dim connStr = "Data Source=xxx;Initial Catalog=xx;Persist Security Info=True;User ID=wxx;Password=xx;MultipleActiveResultSets=True;Application Name=EntityFramework"

    Dim binaryFile As Byte() = Nothing

    Using conn As New SqlConnection(connStr),
            cmd As New SqlCommand(sql, conn)

        cmd.Parameters.Add(New SqlParameter With {
                           .ParameterName = "@fname",
                           .SqlDbType = SqlDbType.NVarChar,
                           .Size = 255,
                           .Value = sFileName})

        conn.Open()

        Dim obj As Object = cmd.ExecuteScalar()

        If obj IsNot Nothing Then
            Try
                binaryFile = DirectCast(obj, Byte())
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        End If

    End Using

    Return binaryFile

End Function

(You may need to adjust the .SqlDbType and .Size parameters: they need to match the column type and size in the database. Also, you probably don't need MultipleActiveResultSets.)