我正在使用FILESTREAM将文件保存到SQL server 2008(Express)数据库中,但一些文件似乎在此过程中出现了损坏。
例如,如果我以较新的格式(docx或xslx)保存Word或Excel文档,则尝试打开该文件时会收到错误消息,提示数据已损坏,是否要尝试恢复。如果我选择是,Office就能够“恢复”数据并在兼容模式下打开文件。
但是,如果我先压缩文件,然后提取内容,那么我就能够毫无问题地打开文件。奇怪的是,如果我将mp3文件保存到数据库中,我会遇到相反的问题,我可以毫无问题地打开该文件,但是如果我保存了mp3的压缩版本,则无法提取该zip的内容。当我尝试保存pdf或PowerPoint文件时,我也遇到了类似的问题(只有将pdf压缩后才能阅读,而ppt根本无法阅读)。
更新:这是我用来写入数据库和读取的代码:
写入数据库:
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();
并阅读:
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();