Excel-fil er beskadiget efter udlæsning fra database
HejJeg vil godt kunne gemme filer i en SQL-database samt at kunne udlæse filer fra databasen med VB.Net kode (koden har jeg fundet på nettet).
Jeg kan godt gemme en Excel fil i databasen, men når jeg prøver at udlæse filen, så skriver Excel en fejlmeddelelse om, at filen er beskadiget.
Håber på, at der er en ekspert i dette forum, som kan hjælp mig med at rette koden, så jeg kan gemme og udlæse filer.
******************************************************
*** Oprettelse af tabel (SQL) ***
******************************************************
CREATE TABLE BLOBTest
(
TestID int IDENTITY(1,1),
BLOBName varChar(50),
BLOBData varBinary(MAX)
)
******************************************************
*** Indsætter fil i tabel (SQL) ***
******************************************************
INSERT INTO BLOBTest
(BLOBName, BLOBData)
SELECT 'First test file',
BulkColumn FROM OPENROWSET(
Bulk 'C:\test\test.xlsx', SINGLE_BLOB) AS BLOB
******************************************************
*** Udlæsning af fil (VB.Net) ***
******************************************************
Public Sub fileStream()
Dim connection As SqlConnection = New SqlConnection("Data Source=(local);Initial Catalog=Test;Integrated Security=True")
Try
connection.Open()
TextBox1.Text += ("Database er åben!")
connection.Close()
Catch ex As Exception
TextBox1.Text = +("Database kunne ikke åbnes!")
End Try
' Assumes that connection is a valid SqlConnection object.
Dim command As SqlCommand = New SqlCommand("SELECT * FROM BLOBTest", connection)
' Writes the BLOB to a file.
Dim stream As FileStream
' Streams the binary data to the FileStream object.
Dim writer As BinaryWriter
' The size of the BLOB buffer.
Dim bufferSize As Integer = 100
' The BLOB byte() buffer to be filled by GetBytes.
Dim outByte(bufferSize - 1) As Byte
' The bytes returned from GetBytes.
Dim retval As Long
' The starting position in the BLOB output.
Dim startIndex As Long = 0
' Open the connection and read data into the DataReader.
connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader(CommandBehavior.SequentialAccess)
Do While reader.Read()
' Get the publisher id, which must occur before getting the logo.
'pubID = reader.GetString(0)
' Create a file to hold the output.
stream = New FileStream("c:\test\" & "test" & ".xlsx", FileMode.OpenOrCreate, FileAccess.Write)
writer = New BinaryWriter(stream)
' Reset the starting byte for a new BLOB.
startIndex = 0
' Read bytes into outByte() and retain the number of bytes returned.
retval = reader.GetBytes(2, startIndex, outByte, 0, bufferSize)
' Continue while there are bytes beyond the size of the buffer.
Do While retval = bufferSize
writer.Write(outByte)
writer.Flush()
' Reposition start index to end of the last buffer and fill buffer.
startIndex += bufferSize
retval = reader.GetBytes(2, startIndex, outByte, 0, bufferSize)
Loop
'Write the remaining buffer.
writer.Write(outByte, 0, retval - 1)
writer.Flush()
' Close the output file.
writer.Close()
stream.Close()
Loop
' Close the reader and the connection.
reader.Close()
connection.Close()
End Sub