Save XLSM file to Database column and retrieve ?

Question

The code previously implemented takes in the xls file saves it on to a column in a table using the stream i use the same method but the only change is the the file saved is a xlsm or an xlsx type file it saves to the column in the database

When I try and get the contents from the database and throw the saved xlsm file or xlsx file I get an error "Excel file found unreadable content do you want to recover the contents of this work book ?"

Here's the code to save the xlsm or the xlsx file

System.IO.Stream filestream = System.IO.File.Open(file, System.IO.FileMode.Open);
int fileLength = (int)filestream.Length;
byte[] input = new byte[fileLength];
filestream.Read(input, 0, fileLength);
string Sql = "insert into upload values(@contents)";
con.Open();
System.Data.SqlClient.SqlCommand c = new System.Data.SqlClient.SqlCommand(Sql, con);
c.Parameters.Add("@contents", System.Data.SqlDbType.Binary);
c.Parameters["@contents"].Value = input;
c.ExecuteNonQuery();

To retrieve and send to user

SqlCommand comm = new SqlCommand("select contents from upload order by id desc", con);
SqlDataReader reader = comm.ExecuteReader();
int bufferSize = 32768;                   
        byte[] outbyte = new byte[bufferSize];  
        long retval;                           
        long startIndex = 0;                    
        startIndex = 0;
        retval = reader.GetBytes(0, startIndex, outbyte, 0, bufferSize);
        while (retval > 0)
        {
            System.Web.HttpContext.Current.Response.BinaryWrite(outbyte);
            startIndex += bufferSize;
            if (retval == bufferSize)
            {
                retval = reader.GetBytes(2, startIndex, outbyte, 0, bufferSize);
            }
            else
            {
                retval = 0;
            }
        }

Answer 1

A couple of things strike me as possibilities.

Firstly, you are not calling reader.Read().

Secondly, there is not need for the check on retval == bufferSize - just call GetBytes again and it will return 0 if no bytes were read from the field.

Thirdly, as you are writing to the HttpResponse you need to make sure that you call Response.Clear() before writing the bytes to the output, and Response.End() after writing the file to the response.

The other thing to try is saving the file to the hard drive and comparing it to the original. Is it the same size? If it is bigger then you are writing too much information to the file (see previous comments about HttpResponse). If it is smaller then you are not writing enough, and are most likely exiting the loop too soon (see comment about retval).

Answer 2

I believe I've got a similar problem, if you let Excel recover the data does it work properly? In my case it does and when Icompare the results I can see that in the "corrupt" document it has UTF8 BOM-markers at the start of every inner document, e.g. sheets. When Excel repairs it the BOMs are gone. However, I've yet to figure out how to use BinaryWrite without getting these pesky markers.

Answer 3

I couldn't help but notice the number of places where your code failed to wrap an IDisposable in a using block, like the following:

using (SqlConnection con = new SqlConnection(connectionString))
{
    byte[] input;
    using (System.IO.Stream filestream = System.IO.File.Open(file, System.IO.FileMode.Open))
    {
        int fileLength = (int)filestream.Length;
        input = new byte[fileLength];
        filestream.Read(input, 0, fileLength);
    }
    const string Sql = "insert into upload values(@contents)";
    con.Open();
    using (System.Data.SqlClient.SqlCommand c = new System.Data.SqlClient.SqlCommand(Sql, con))
    {
        c.Parameters.Add("@contents", System.Data.SqlDbType.Binary);
        c.Parameters["@contents"].Value = input;
        c.ExecuteNonQuery();
    }

    using (SqlCommand comm = new SqlCommand("select contents from upload order by id desc", con))
    {
        using (SqlDataReader reader = comm.ExecuteReader())
        {
            int bufferSize = 32768;
            byte[] outbyte = new byte[bufferSize];
            long retval;
            long startIndex = 0;
            startIndex = 0;
            retval = reader.GetBytes(0, startIndex, outbyte, 0, bufferSize);
            while (retval > 0)
            {
                System.Web.HttpContext.Current.Response.BinaryWrite(outbyte);
                startIndex += bufferSize;
                if (retval == bufferSize)
                {
                    retval = reader.GetBytes(2, startIndex, outbyte, 0, bufferSize);
                }
                else
                {
                    retval = 0;
                }
            }
        }
    }
}