File gets corrupted when reading/writting from SQLServer 2005/2008 from C#?

Question

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();
    }

Answer 1

Probably nothing is wrong with the code. I'd look at what virus scanning software you have on that server and promptly uninstall it. I've seen Norton and McAfee both clobber files with NO indication they changed anything.

Answer 2

You have an error in your databaseFileRead method.

Consider this: you have a bufferSize of 100 bytes (really really small - I'd recommend 4096 bytes minimum!) and your loop look like this:

while (retval == bufferSize) 
{
   writer.Write(outByte);
   writer.Flush();
   startIndex += bufferSize;
   retval = sqlQueryResult.GetBytes(0, startIndex, outByte, 0, bufferSize);
}

OK, this works fine, until there are only 73 bytes left in your file to process - in that case, the last call to

retval = sqlQueryResult.GetBytes(0, startIndex, outByte, 0, bufferSize);

will return "73" in retval and since that is not == bufferSize, you will abort. So with this, you're always skipping the last couple of bytes.....

What you need to do is this:

while (retval > 0) 
{
   writer.Write(outByte);
   writer.Flush();
   startIndex += retval;
   retval = sqlQueryResult.GetBytes(0, startIndex, outByte, 0, bufferSize);
}

With this, when reading the last 73 bytes, you'll get retval=73 and you'll write out those last 73 bytes, and then the next call to sqlQueryResult should return retval=0 and then terminate your loop.

Try it - I'm pretty sure that's the cause of this error.

Marc

Answer 3

Following code seems to work without errors. Skipping usage of BinaryWriter made it write the file correctly. Why using BinaryWriter was breaking the file i don't know :-)

    public static void databaseFileRead(string varID, string varPathToNewLocation) {
        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()) {
                if (sqlQueryResult != null) {
                    sqlQueryResult.Read();
                    byte[] blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                    sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
                    using (FileStream fs = new FileStream(varPathToNewLocation, FileMode.Create, FileAccess.Write)) {
                        fs.Write(blob, 0, blob.Length);
                    }
                }

            }
        }
    }