DOCX FILE CORRUPTED WHEN RETRIEVED FROM SQL2005

[=?Utf-8?B?U1VOTlk=?=]

Hi, i am uploading a .docx file into sql2005 and later when i retrieve the
file from the database and i open it, i get a error message "The file is
corrupted and cannot be open". I am not facing this issue when i store and
retrieve files of type .doc

i am storing the file in a column of datatype image.
Here is the code where i insert the file into database. Am sure the code is
working. its just not working for .docx

protected void Button1_Click(o bject sender, EventArgs e)
{
if (FileUpload1.Ha sFile && FileUpload1.Pos tedFile != null)
{
int ilength =
Convert.ToInt32 (FileUpload1.Po stedFile.Conten tLength);
string content = FileUpload1.Pos tedFile.Content Type;
string file_name = FileUpload1.Fil eName;
Byte[] bytecontent = new Byte[ilength];

con = new SqlConnection() ;
con.ConnectionS tring = "Server=W2RZYFV 603\\SQLEXPRESS ;
Database=master ; Trusted_Connect ion=True";
con.Open();

string sql = "insert into
files(file_data ,name,content_t ype,file_size)V ALUES(@file, @name, @content,
@size)";
try
{
SqlCommand cmd = new SqlCommand(sql, con);
FileUpload1.Pos tedFile.InputSt ream.Read(bytec ontent, 0,
FileUpload1.Pos tedFile.Content Length);
cmd.Parameters. AddWithValue("@ file", bytecontent);
cmd.Parameters. AddWithValue("@ name", file_name);
cmd.Parameters. AddWithValue("@ content", content);
cmd.Parameters. AddWithValue("@ size", ilength);
int i = cmd.ExecuteNonQ uery();
TextBox1.Text = i.ToString();
}
catch (Exception ex)
{
TextBox1.Text = ex.Message;
}
finally
{
con.Close();
}

}
Here is the code to retrieve the data from the database.
protected void Page_Load(objec t sender, EventArgs e)
{
try
{

con = new SqlConnection() ;
con.ConnectionS tring = "Server=W2RZYFV 603\\SQLEXPRESS ;
Database=master ; Trusted_Connect ion=True";
con.Open();

}
catch (Exception ex)
{

}
string qid = "select max(id) from files";
int id;
SqlCommand cmd1 = new SqlCommand(qid, con);
id = Convert.ToInt32 ( cmd1.ExecuteSca lar());
string query = "select * from files where id="+ id.ToString();
//string query = "select * from images where id=4";
SqlCommand cmd = new SqlCommand(quer y, con);
SqlDataReader dr = cmd.ExecuteRead er();

if (dr.Read())
{
byte[] imagecontent = (byte[])(dr[1]);
Response.Conten tType = dr[3].ToString();
Response.AddHea der("Content-Disposition",
"attachment;fil ename="+dr[2].ToString());
Context.Respons e.BinaryWrite(i magecontent);

}
cmd = null;
dr.Close();
con.Close();

}

Please let me know as to why is this not working for .docx files.
--
SUNNY

[Marc Gravell]

Well, one thought would be: are you sure that there are no artefacts
before/after the BLOB? I would recommend using a handler (not an aspx)
for download pages so you know exactly what is in the stream. It might
be that Word is OK at ignoring rubbing in the binary, but a docx might
appear malformed... equally, it would be wise to set things like the
content-length.

It might also depend on the size of the file; I tend to use chunking for
reading/writing BLOBs, but this isn't as easy... but it would be worth
verifying how much data you are reading from the database vs how much is
there (datalength). There may be a limit to what SqlDataReader will read
in one hit, since an overly-big byte[] buffer is never a good thing...

Marc

[=?Utf-8?B?U1VOTlk=?=]

Hi Marc,
the data type of column where i am storing the file is of type Image.
I checked and i see that the amount of data i am reading from the database
and the datalength is same.
The size of file i am using for testing purpose is just 25 kb, so it
shouldnt be a problem.

if i can get more help, it would be appreciated.
--
SUNNY
"Marc Gravell" wrote:

Well, one thought would be: are you sure that there are no artefacts
before/after the BLOB? I would recommend using a handler (not an aspx)
for download pages so you know exactly what is in the stream. It might
be that Word is OK at ignoring rubbing in the binary, but a docx might
appear malformed... equally, it would be wise to set things like the
content-length.

It might also depend on the size of the file; I tend to use chunking for
reading/writing BLOBs, but this isn't as easy... but it would be worth
verifying how much data you are reading from the database vs how much is
there (datalength). There may be a limit to what SqlDataReader will read
in one hit, since an overly-big byte[] buffer is never a good thing...

Marc

[Marc Gravell]

So the next step is to use something like Fiddler to see what *actually*
comes down the wire. If this is an aspx I wouldn't be in the least bit
surprised to see a lot of contamination. You can get aspx to render a
clean stream, but it is a lot simpler to just use a handler (or ashx) to
start with...

Marc

[Ignacio Machin ( .NET/ C# MVP )]

On Aug 7, 6:42*am, SUNNY <SUNNYwrote:

Hi, i am uploading a .docx file into sql2005 and later when i retrieve the
file from the database and i open it, i get a error message "The file is
corrupted and cannot be open". I am not facing this issue when i store and
retrieve files of type .doc

i am storing the file in a column of datatype image.
Here is the code where i insert the file into database. Am sure the code is
working. its just not working for .docx

protected void Button1_Click(o bject sender, EventArgs e)
* * {
* * * * if (FileUpload1.Ha sFile && FileUpload1.Pos tedFile != null)
* * * * {
* * * * * * int ilength =
Convert.ToInt32 (FileUpload1.Po stedFile.Conten tLength);
* * * * * * string content = FileUpload1.Pos tedFile.Content Type;
* * * * * * string file_name = FileUpload1.Fil eName;
* * * * * * Byte[] bytecontent = new Byte[ilength];

* * * * * * con = new SqlConnection() ;
* * * * * * con.ConnectionS tring = "Server=W2RZYFV 603\\SQLEXPRESS ;
Database=master ; Trusted_Connect ion=True";
* * * * * * con.Open();

* * * * * * string sql = "insert into
files(file_data ,name,content_t ype,file_size)V ALUES(@file, @name, @content,
@size)";
* * * * * * try
* * * * * * {
* * * * * * * * SqlCommand cmd = new SqlCommand(sql, con);
* * * * * * * * FileUpload1.Pos tedFile.InputSt ream.Read(bytec ontent, 0,
FileUpload1.Pos tedFile.Content Length);
* * * * * * * * cmd.Parameters. AddWithValue("@ file", bytecontent);
* * * * * * * * cmd.Parameters. AddWithValue("@ name", file_name);
* * * * * * * * cmd.Parameters. AddWithValue("@ content", content);
* * * * * * * * cmd.Parameters. AddWithValue("@ size", ilength);
* * * * * * * * int i = cmd.ExecuteNonQ uery();
* * * * * * * * TextBox1.Text = i.ToString();
* * * * * * }
* * * * * * catch (Exception ex)
* * * * * * {
* * * * * * * * TextBox1.Text = ex.Message;
* * * * * * }
* * * * * * finally
* * * * * * {
* * * * * * * * con.Close();
* * * * * * }

* * * * }

Here is the code to retrieve the data from the database.
protected void Page_Load(objec t sender, EventArgs e)
* * {
* * * * try
* * * * {

* * * * * * con = new SqlConnection() ;
* * * * * * con.ConnectionS tring = "Server=W2RZYFV 603\\SQLEXPRESS ;
Database=master ; Trusted_Connect ion=True";
* * * * * * con.Open();

* * * * }
* * * * catch (Exception ex)
* * * * {

* * * * }
* * * * string qid = "select max(id) from files";
* * * * int id;
* * * * SqlCommand cmd1 = new SqlCommand(qid, con);
* * * * id = Convert.ToInt32 ( cmd1.ExecuteSca lar());

* * * * string query = "select * from files where id="+ id.ToString();
* * * * //string query = "select * from images where id=4";
* * * * SqlCommand cmd = new SqlCommand(quer y, con);
* * * * SqlDataReader dr = cmd.ExecuteRead er();

* * * * if (dr.Read())
* * * * {
* * * * * * byte[] imagecontent = (byte[])(dr[1]);
* * * * * * Response.Conten tType = dr[3].ToString();
* * * * * * Response.AddHea der("Content-Disposition",
"attachment;fil ename="+dr[2].ToString());
* * * * * * Context.Respons e.BinaryWrite(i magecontent);

* * * * }
* * * * cmd = null;
* * * * dr.Close();
* * * * con.Close();

* * }

Please let me know as to why is this not working for .docx files.
--
SUNNY

Hi,

I would recommend you to use a console or a win test prog. to see if
the file is going out ok from the db

[Jon Skeet [C# MVP]]

<=?Utf-8?B?U1VOTlk=?= <SUNNY>wrote:

Hi, i am uploading a .docx file into sql2005 and later when i retrieve the
file from the database and i open it, i get a error message "The file is
corrupted and cannot be open". I am not facing this issue when i store and
retrieve files of type .doc

i am storing the file in a column of datatype image.
Here is the code where i insert the file into database. Am sure the code is
working. its just not working for .docx

Aside from anything else, you're assuming that you'll be able to read
the whole of the data from the web request in a single call to Read.
Never a good idea.

See http://pobox.com/~skeet/csharp/readbinary.html

--
Jon Skeet - <sk***@pobox.co m>
Web site: http://www.pobox.com/~skeet
Blog: http://www.msmvps.com/jon.skeet
C# in Depth: http://csharpindepth.com

[=?UTF-8?B?QXJuZSBWYWpow7hq?=]

SUNNY wrote:

Hi, i am uploading a .docx file into sql2005 and later when i retrieve the
file from the database and i open it, i get a error message "The file is
corrupted and cannot be open". I am not facing this issue when i store and
retrieve files of type .doc

i am storing the file in a column of datatype image.
Here is the code where i insert the file into database. Am sure the code is
working. its just not working for .docx

Since the code treats the files as bytes, then it is obviously not .doc
versus .docx that makes the difference, since bytes is bytes no matter
what content.

You need to look for other differences.

int ilength =
Convert.ToInt32 (FileUpload1.Po stedFile.Conten tLength);
string content = FileUpload1.Pos tedFile.Content Type;
string file_name = FileUpload1.Fil eName;
Byte[] bytecontent = new Byte[ilength];

con = new SqlConnection() ;
con.ConnectionS tring = "Server=W2RZYFV 603\\SQLEXPRESS ;
Database=master ; Trusted_Connect ion=True";
con.Open();

string sql = "insert into
files(file_data ,name,content_t ype,file_size)V ALUES(@file, @name, @content,
@size)";
try
{
SqlCommand cmd = new SqlCommand(sql, con);
FileUpload1.Pos tedFile.InputSt ream.Read(bytec ontent, 0,
FileUpload1.Pos tedFile.Content Length);

I would check the number of bytes actually read here !

In fact you should really read in a while loop.

cmd.Parameters. AddWithValue("@ file", bytecontent);
cmd.Parameters. AddWithValue("@ name", file_name);
cmd.Parameters. AddWithValue("@ content", content);
cmd.Parameters. AddWithValue("@ size", ilength);
int i = cmd.ExecuteNonQ uery();

con = new SqlConnection() ;
con.ConnectionS tring = "Server=W2RZYFV 603\\SQLEXPRESS ;
Database=master ; Trusted_Connect ion=True";
con.Open();

string qid = "select max(id) from files";

If you used the same connection, then you could just use
SCOPE_IDENTITY( ).

I know this is just test, but SELECT MAX(id) is a very bad habit.

int id;
SqlCommand cmd1 = new SqlCommand(qid, con);
id = Convert.ToInt32 ( cmd1.ExecuteSca lar());

string query = "select * from files where id="+ id.ToString();
//string query = "select * from images where id=4";
SqlCommand cmd = new SqlCommand(quer y, con);
SqlDataReader dr = cmd.ExecuteRead er();

if (dr.Read())
{
byte[] imagecontent = (byte[])(dr[1]);
Response.Conten tType = dr[3].ToString();
Response.AddHea der("Content-Disposition",
"attachment;fil ename="+dr[2].ToString());
Context.Respons e.BinaryWrite(i magecontent);

You should test imagecontent.Le ngth here and compare:
* files actual size
* size when inserted
* size when retrieved

Arne