I've wrote two little methods to save and load .docx (and later on other type of files) files into database (SERVER 2005/2008 with VarBinary(MAX) as column). Everything seems nice but when i read the file back it's created but Word complains that it's corrupted but finally opens it up with everything in it. What's wrong with the code?
public static void databaseFileRead(string varID, string varPathToNewLocation) {
const int bufferSize = 100;
byte[] outByte = new byte[bufferSize];
using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetailsDZP))
using (var sqlQuery = new SqlCommand(@"SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = @varID", varConnection)) {
sqlQuery.Parameters.AddWithValue("@varID", varID);
using (var sqlQueryResult = sqlQuery.ExecuteReader(CommandBehavior.Default))
while (sqlQueryResult != null && sqlQueryResult.Read()) {
using (FileStream stream = new FileStream(varPathToNewLocation, FileMode.OpenOrCreate, FileAccess.Write)) {
using (BinaryWriter writer = new BinaryWriter(stream)) {
long startIndex = 0;
long retval = sqlQueryResult.GetBytes(0, startIndex, outByte, 0, bufferSize);
while (retval == bufferSize) {
writer.Write(outByte);
writer.Flush();
startIndex += bufferSize;
retval = sqlQueryResult.GetBytes(0, startIndex, outByte, 0, bufferSize);
}
writer.Write(outByte, 0, (int) retval - 1);
writer.Flush();
writer.Close();
}
stream.Close();
}
}
}
}
public static void databaseFilePut(string varFilePath) {
FileStream stream = new FileStream(varFilePath, FileMode.Open, FileAccess.Read);
BinaryReader reader = new BinaryReader(stream);
byte[] file = reader.ReadBytes((int) stream.Length);
reader.Close();
stream.Close();
using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetailsDZP))
using (var sqlWrite = new SqlCommand("INSERT INTO Raporty (RaportPlik) Values(@File)", varConnection)) {
sqlWrite.Parameters.Add("@File", SqlDbType.Binary, file.Length).Value = file;
sqlWrite.ExecuteNonQuery();
}
}
Edit:
I've changed the code as per suggestion to have bufferSize of 4096 but it still isn't a go.
The orginal file says: 48,0 KB (bytes: 49 225) as size, and 52,0 KB (bytes: 53 248) as size on disk (Win 7 properties show this). While the file taken out of db is size 52,0 KB (bytes: 53 248) and size on disk 52,0 KB (bytes: 53 248).
It all happens on development machine with Win 7 x64, i have uninstalled Eset Smart Security just to be sure.
Edit2:
So I have added another "way" to do it from webpage and it seems to do the trick. The only noticable diffrence is lack of using BinaryWriter and a bit weird definition for Byte[] blob. Weird isn't it ?
public static void databaseFileRead(string varID, string varPathToNewLocation) {
const int bufferSize = 4096;
byte[] outByte = new byte[bufferSize];
using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetailsDZP))
using (var sqlQuery = new SqlCommand(@"SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = @varID", varConnection)) {
sqlQuery.Parameters.AddWithValue("@varID", varID);
using (var sqlQueryResult = sqlQuery.ExecuteReader())
while (sqlQueryResult != null && sqlQueryResult.Read()) {
using (FileStream stream = new FileStream(varPathToNewLocation, FileMode.OpenOrCreate, FileAccess.Write))
using (BinaryWriter writer = new BinaryWriter(stream)) {
long startIndex = 0;
long retval = sqlQueryResult.GetBytes(0, startIndex, outByte, 0, bufferSize);
while (retval > 0) {
writer.Write(outByte);
writer.Flush();
startIndex += retval;
retval = sqlQueryResult.GetBytes(0, startIndex, outByte, 0, bufferSize);
}
}
}
}
Byte[] blob = null;
FileStream fs = null;
const string sConn = Locale.sqlDataConnectionDetailsDZP;
SqlConnection conn = new SqlConnection(sConn);
SqlCommand cmd = new SqlCommand("SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = " + varID, conn);
conn.Open();
SqlDataReader sdr = cmd.ExecuteReader();
sdr.Read();
blob = new Byte[(sdr.GetBytes(0, 0, null, 0, int.MaxValue))];
sdr.GetBytes(0, 0, blob, 0, blob.Length);
sdr.Close();
conn.Close();
fs = new FileStream("c:\\Builder.docx", FileMode.Create, FileAccess.Write);
fs.Write(blob, 0, blob.Length);
fs.Close();
}