從SQLServer讀取影像時出現“System.OutOfMemoryException:'Outofmemory.'”

我為 VB.NET 表單中的每個按鈕分配了影像，影像來自 SQL Server。資料型別是varbinary(MAX).

這是我的代碼：

Using con As New SqlConnection("con string")
    Dim sql As String = "SELECT * FROM Inventory WHERE ID=@ID"
    Using cmd As New SqlCommand(sql, con)
        cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = 3
        con.Open()
        Using myreader As SqlDataReader = cmd.ExecuteReader()
            If myreader.Read() AndAlso Not DBNull.Value.Equals(myreader("Image")) Then
                Boton3.Text = myreader("Item")
                Boton3.Enabled = myreader("ONOFF")
                Dim ImgSql() As Byte = DirectCast(myreader("Image"), Byte())
                Using ms As New MemoryStream(ImgSql)
                    Boton3.BackgroundImage = Image.FromStream(ms)
                    con.Close()
                End Using
            Else
                Boton3.Text = myreader("Item")
                Boton3.BackgroundImage = Nothing
                Boton3.Enabled = myreader("ONOFF")
            End If
        End Using
    End Using
End Using

平臺是64位的。我認為這可能與處理不當有關，但我不確定，因為我是編碼新手。

編輯顯示新代碼以及我如何檢索多個記錄：

Private Sub Button12_Click(sender As Object, e As EventArgs) Handles Button12.Click
        Dim dt As DataTable
        Try
            dt = GetInventoryDataByID(1)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Exit Sub
        End Try
        If dt.Rows.Count > 0 Then
            Boton1.Text = dt.Rows(0)("Articulo").ToString
            Boton1.Enabled = CBool(dt.Rows(0)("ONOFF"))
            If Not DBNull.Value.Equals(dt.Rows(0)("Imagen")) Then
                Dim ImgSql() As Byte = DirectCast(dt.Rows(0)("Imagen"), Byte())
                Using ms As New MemoryStream(ImgSql)
                    Boton1.BackgroundImage = Image.FromStream(ms)
                End Using
            Else
                Boton1.BackgroundImage = Nothing
            End If
        Else
            MessageBox.Show("No records returned")
        End If
        Dim dt2 As DataTable
        Try
            dt2 = GetInventoryDataByID(2)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Exit Sub
        End Try
        If dt2.Rows.Count > 0 Then
            Boton2.Text = dt2.Rows(0)("Articulo").ToString
            Boton2.Enabled = CBool(dt2.Rows(0)("ONOFF"))
            If Not DBNull.Value.Equals(dt2.Rows(0)("Imagen")) Then
                Dim ImgSql() As Byte = DirectCast(dt2.Rows(0)("Imagen"), Byte())
                Using ms As New MemoryStream(ImgSql)
                    Boton2.BackgroundImage = Image.FromStream(ms)
                End Using
            Else
                Boton2.BackgroundImage = Nothing
            End If
        Else
            MessageBox.Show("No records returned")
        End If
    End Sub
    Private Function GetInventoryDataByID(id As Integer) As DataTable
        Dim dt As New DataTable
        Dim sql As String = "SELECT Imagen, Articulo, ONOFF FROM Inventario WHERE ID=@ID"
        Using con As New SqlConnection("CON STRING"),
            cmd As New SqlCommand(sql, con)
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id
            con.Open()
            Using myreader As SqlDataReader = cmd.ExecuteReader()
                dt.Load(myreader)
            End Using
        End Using
        Return dt
    End Function
End Class

uj5u.com熱心網友回復：

您不希望在更新用戶界面時保持連接打開。將用戶界面代碼與資料庫代碼分開。

如果在外部 Using 塊的第一行末尾放置一個逗號，則命令和連接都包含在同一個塊中。節省一點縮進。

您正在向@ID引數傳遞一個整數，但您已將其設定SqlDbType為 a VarChar。看起來有問題。我將 更改SqlDbType為Int。

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim dt As DataTable
    Try
        dt = GetInventoryDataByID(3)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Exit Sub
    End Try
    If dt.Rows.Count > 0 Then
        Boton3.Text = dt.Rows(0)("Item").ToString
        Boton3.Enabled = CBool(dt.Rows(0)("ONOFF"))
        If Not DBNull.Value.Equals(dt.Rows(0)("Image")) Then
            Dim ImgSql() As Byte = DirectCast(dt.Rows(0)("Image"), Byte())
            Using ms As New MemoryStream(ImgSql)
                Boton3.BackgroundImage = Image.FromStream(ms)
            End Using
        Else
            Boton3.BackgroundImage = Nothing
        End If
    Else
        MessageBox.Show("No records returned")
    End If
End Sub

Private Function GetInventoryDataByID(id As Integer) As DataTable
    Dim dt As New DataTable
    Dim sql As String = "SELECT * FROM Inventory WHERE ID=@ID"
    Using con As New SqlConnection("con string"),
            cmd As New SqlCommand(sql, con)
        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id
        con.Open()
        Using myreader As SqlDataReader = cmd.ExecuteReader()
            dt.Load(myreader)
        End Using
    End Using
    Return dt
End Function

編輯在影像上添加處置

    If Not DBNull.Value.Equals(dt.Rows(0)("Image")) Then
        Dim ImgSql() As Byte = DirectCast(dt.Rows(0)("Image"), Byte())
        Using ms As New MemoryStream(ImgSql)
            If Boton3.BackgroundImage IsNot Nothing Then
                Boton3.BackgroundImage.Dispose()
            End If
            Boton3.BackgroundImage = Image.FromStream(ms)
        End Using
    Else
        If Boton3.BackgroundImage IsNot Nothing Then
            Boton3.BackgroundImage.Dispose()
        End If
    End If

uj5u.com熱心網友回復：

我通過不使用按鈕解決了這個問題。相反，我使用圖片框作為按鈕并解決了問題。我猜問題是按鈕不允許像圖片框一樣多的記憶體。

標籤：sql-server 网络

上一篇：%在vb.net/sql中使用嗎？

下一篇：BluetoothLEDevice.FromIdAsync回傳Uncastable__ComObject