Some files corrupted by SQL Server FileStream

Question

I save files to a SQL Server 2008 (Express) database using FILESTREAM, the problem is that some files seem to get corrupted in the process.

For example, if I save the word or excel document in one of the new formats (docx or xslx), when I try to open the file, I get an error message saying that the data is damaged and I like the word / succeed to try to recover it. If I click "yes", he will be able to "restore" the data and open the file in compatibility mode.

However, if I first archive the file and then extract the contents, I can open the file without any problems. Strange If I save the mp3 file in the database, then I have the opposite problem, I can open the file without problems, but if I saved the zipped version of mp3, I could not even extract the contents of this zip code. When I tried to save the file in pdf or power-point format, I encountered similar problems (I could only read pdf if I archived it first, but I could not read ppt at all).

Update: here is my code that I use to write to the database and read

To write to the database:

SQL = "SELECT Attachment.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Activity " + "WHERE RowID = CAST(@RowID as uniqueidentifier)"; transaction = connection.BeginTransaction(); command.Transaction = transaction; command.CommandText = SQL; command.Parameters.Clear(); command.Parameters.Add(rowIDParam); SqlDataReader readerFS = null; readerFS= command.ExecuteReader(); string path = (string)readerFS[0].ToString(); byte[] context = (byte[])readerFS[1]; int length = context.Length; SqlFileStream targetStream = new SqlFileStream(path, context, FileAccess.Write); int blockSize = 1024 * 512; //half a megabyte byte[] buffer = new byte[blockSize]; int bytesRead = sourceStream.Read(buffer, 0, buffer.Length); while (bytesRead > 0) { targetStream.Write(buffer, 0, bytesRead); bytesRead = sourceStream.Read(buffer, 0, buffer.Length); } targetStream.Close(); sourceStream.Close(); readerFS.Close(); transaction.Commit(); 

And read:

  SqlConnection connection = null; SqlTransaction transaction = null; try { connection = getConnection(); connection.Open(); transaction = connection.BeginTransaction(); SQL = "SELECT Attachment.PathName(), + GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Activity" + " WHERE ActivityID = @ActivityID"; SqlCommand command = new SqlCommand(SQL, connection); command.Transaction = transaction; command.Parameters.Add(new SqlParameter("ActivityID", activity.ActivityID)); SqlDataReader reader = command.ExecuteReader(); string path = (string)reader[0]; byte[] context = (byte[])reader[1]; int length = context.Length; reader.Close(); SqlFileStream sourceStream = new SqlFileStream(path, context, FileAccess.Read); int blockSize = 1024 * 512; //half a megabyte byte[] buffer = new byte[blockSize]; List<byte> attachmentBytes = new List<byte>(); int bytesRead = sourceStream.Read(buffer, 0, buffer.Length); while (bytesRead > 0) { bytesRead = sourceStream.Read(buffer, 0, buffer.Length); foreach (byte b in buffer) { attachmentBytes.Add(b); } } FileStream outputStream = File.Create(outputPath); foreach (byte b in attachmentBytes) { byte[] barr = new byte[1]; barr[0] = b; outputStream.Write(barr, 0, 1); } outputStream.Close(); sourceStream.Close(); command.Transaction.Commit(); 

Answer 1

Invalid read code:

  while (bytesRead > 0) { bytesRead = sourceStream.Read(buffer, 0, buffer.Length); foreach (byte b in buffer) { attachmentBytes.Add(b); } } 

If bytesRead is less than buffer.Length, you still add the entire buffer to attachementBytes. This way you always damage the document returned by adding some garbage at the end of the last message in the bytesRead buffer.

Other than that, let me really have a WTF moment. Reading a stream as a List<byte> ?? Come on! Firstly, I don’t see the reason why you need to read the intermediate storage in memory first. You can simply read the buffer with the buffer and write each buffer directly to outputStream. Secondly, if you must use intermediate storage in memory, use a MemoryStream , not a List<byte> .

Answer 2

I had the exact problem a few months ago and it turned out that I was adding an extra byte at the end of the file when reading this from FILESTREAM.