views:

159

answers:

3

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();
    }
+1  A: 

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.

Chris Lively
No antivirus/firewall on server.
MadBoy
+2  A: 

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

marc_s
I've replace the code with yours and it still errors out. I've changed buffersize to 4096 too. Also i think `writer.Write(outByte, 0, (int) retval - 1);` was taking care of the missing 73 bytes. The code is based on http://msdn.microsoft.com/en-us/library/87z0hy49%28VS.80%29.aspx
MadBoy
@madboy: same error? Do you get any additional info? is the resulting file size on disk an exact multiple of 100 resp. 4096 bytes??
marc_s
@madboy: yeah, true - I overlooked that last `writer.Write` statement. Yes, that would take care of the last remaining bytes...
marc_s
I've now did another test. The orginal file says: 48,0 KB (bajtów: 49 225) as size, and 52,0 KB (bajtów: 53 248) as size on disk (Win 7 properties show this). While the file taken out of db is size 52,0 KB (bajtów: 53 248) and size on disk 52,0 KB (bajtów: 53 248). Keep in mind i have win 7 x64 maybe something that makes it go bad?
MadBoy
I've updated main poste with working code (2nd part of the read function). Seems like using BinaryWriter isn't the best for this. Not sure why thou :-)
MadBoy
@marc_s Also added my own answer to this question which shows the working way without `BinaryWriter` with proper usage of `using`. If you find something wrong in it please let me know.
MadBoy
A: 

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

            }
        }
    }
MadBoy