代码之家 › 专栏 › 技术社区 › madlan

从SQL Server保存二进制文件

sql-server-2008 vb.net

madlan · 技术社区 · 14 年前

我试图用savedialog保存一个存储在SQL数据库中的二进制文件,函数retrievefile以字节数组的形式从数据库中检索指定的文件,以下是目前为止我所拥有的:

Private Shared Function RetrieveFile(ByVal filename As String) As Byte()
    Dim connection As New SqlConnection("Data Source=SERVER\SQL2008;Initial Catalog=NorthPole;Integrated Security=True")
    Dim command As New SqlCommand("SELECT pcfFile FROM Items WHERE pcfFileName=@Filename", connection)
    command.Parameters.AddWithValue("@Filename", filename)
    connection.Open()
    Dim reader As SqlDataReader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)
    reader.Read()
    Dim memory As New MemoryStream()
    Dim startIndex As Long = 0
    Const ChunkSize As Integer = 256
    While True
        Dim buffer As Byte() = New Byte(ChunkSize - 1) {}
        Dim retrievedBytes As Long = reader.GetBytes(1, startIndex, buffer, 0, ChunkSize)
        memory.Write(buffer, 0, CInt(retrievedBytes))
        startIndex += retrievedBytes
        If retrievedBytes <> ChunkSize Then
            Exit While
        End If
    End While
    connection.Close()
    Dim data As Byte() = memory.ToArray()
    memory.Dispose()
    Return data


End Function


Private Sub SaveAsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveAsToolStripMenuItem.Click
    Dim saveFileDialog1 As New SaveFileDialog()
    saveFileDialog1.Filter = "PCF File|*.pcf|"
    saveFileDialog1.Title = "Save an pcf File"
    saveFileDialog1.ShowDialog()

    If saveFileDialog1.FileName <> "" Then
        Dim fs As System.IO.FileStream = CType(RetrieveFile("FakePCF.pcf"), System.IO.FileStream)
                    fs.Close()
    End If
End Sub

文件在数据库中保存为“sqldbtype.varbinary”。我得到:“索引在数组边界之外。”在:

   Dim retrievedBytes As Long = reader.GetBytes(1, startIndex, buffer, 0, ChunkSize)

memoryStream似乎没有检索数据,但SQL Sytax是正确的。我做错什么了?

1 回复 | 直到 13 年前

Ivan FeriÄ 14 年前

首先,您的方法返回byte[],并尝试将其强制转换为filestream。您应该将处理程序更改为:

Private Sub SaveAsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveAsToolStripMenuItem.Click
    Dim saveFileDialog1 As New SaveFileDialog()
    saveFileDialog1.Filter = "PCF File|*.pcf|"
    saveFileDialog1.Title = "Save an pcf File"
    saveFileDialog1.ShowDialog()

    If saveFileDialog1.FileName <> "" Then
        Dim fs As New System.IO.FileStream (saveFileDialog1.FileName, System.IO.FileMode.Create, System.IO.FileAccess.Write)
        Dim data As Byte() = RetrieveFile("FakePCF.pcf")
        fs.Write(data, 0, data.Length)
        fs.Flush()
        fs.Close()
    End If
End Sub