File corruption when retrieving files from a SQL 2005 database

[djhexx]

Hello. I have an ASP.NET application (C#) that I allow users to
upload files. Files get stored in a SQL2005 database. The file data
is stored in a varbinary(max) column.

When the user uploads the file, I store it in a database. When a user
requests to download a file, the file is retrieved from the database
and then sent to them.

The files go in there ok. This is how I store them:

(note FileInfo is my own custom struct data type that I wrote to keep
track of File \ record info....NOT the System.IO class)

protected FileInfo StoreTempFile(FileInfo fileInfo)
{
byte[] fileData;
Guid gFileID;
Stream stream;
HttpPostedFile postedFile;
Database db = DatabaseFactory.CreateDatabase();

try
{
postedFile = this.FileUpload1.PostedFile;
fileData = new byte[fileInfo.FileSize];
stream = postedFile.InputStream;

stream.Read(fileData, 0, fileInfo.FileSize);
stream.Close();

FileStream filestream = new FileStream("C:\\test.txt",
FileMode.Create);
filestream.Write(fileData, 0, fileInfo.FileSize);
filestream.Close();

gFileID =
(Guid)db.ExecuteScalar("ContractsFilesInsert", fileInfo.ContractID,
fileInfo.FileName, fileInfo.FileSize, fileData, System.DateTime.Now);

fileInfo.FileID = gFileID;

return (fileInfo);
}
catch (Exception error)
{
return (fileInfo);
}
}



As you can see, I have some code to write the file to disk to double
check it's integrity (for debugging). The file gets uploaded fine and
is not corrupt before going into the database.


Here is the code used to retrieve the file from the database:


protected void SendFile(FileInfo fileInfo)
{
Database db = DatabaseFactory.CreateDatabase();
byte[] fileData = new byte[fileInfo.FileSize];

fileData =
(byte[])db.ExecuteScalar("ContractsFilesSelectByID", fileInfo.FileID);

FileStream filestream = new FileStream("C:\\test.txt",
FileMode.Create);
filestream.Write(fileData, 0, fileInfo.FileSize);
filestream.Close();

Response.Flush();
Response.ClearContent();
Response.ClearHeaders();
Response.AppendHeader("Content-Disposition", "attachment;
filename=" + fileInfo.FileName);
Response.ContentType = "text/plain";
Response.BinaryWrite(fileData);
Response.Flush();
Response.End();
Response.Close();
}


Here I am writing out the file to disk before I send the file out to
the user to double check it's integrity and eliminate the download
process as being the problem. The file that outputs is corrupt. Yes
I know the contentType will change according to the file...but I just
hardcoded "text/plain" temporarily as I am testing with text files.

Now...the odd part about all this, is JPG images seem to come out of
the database and sent to the downloader completely intact. Anything
else like zips, exe's, or even plain text files are corrupt.

Is the problem that I am using the ExecuteScaler method and casting
the results to a byte[]? If so, what's the preferred way to retrieve
files?

I'll be happy to include any additional information you need. Thanks
in advance!

 

[djhexx]

Anyone have any ideas? Or am I posting in an inappropriate group?

 

[Patrice]

Have you tried ? :
- never catch an exception and do nothing. It could hide a problem in your
code.
- I would save both file and would start to compare their length ; if they
have the same length you can use the " fc" command to find out the first
mismatch. You can also use a well know pattern buffer (such as index mod 256
at offset "index"). It makes really easy to unit test you stream
reading/writing routine.

Good luck.

Patrice

 

[bruce barker]

execute scaler just return the first column of the the first row
returned from the query, so as long as this column is a varbinary you
should be ok.

check that the proc parameters are varbinary. also you use the saved
buffer size rather than the actual. executescaler is returnning a newly
allocated buffer, so there is no reason to allocate first.

hex dump a file and compare to the contents is sql as a hex string.

-- bruce (sqlwork.com)

Hello. I have an ASP.NET application (C#) that I allow users to
upload files. Files get stored in a SQL2005 database. The file data
is stored in a varbinary(max) column.

When the user uploads the file, I store it in a database. When a user
requests to download a file, the file is retrieved from the database
and then sent to them.

The files go in there ok. This is how I store them:

(note FileInfo is my own custom struct data type that I wrote to keep
track of File \ record info....NOT the System.IO class)

protected FileInfo StoreTempFile(FileInfo fileInfo)
{
byte[] fileData;
Guid gFileID;
Stream stream;
HttpPostedFile postedFile;
Database db = DatabaseFactory.CreateDatabase();

try
{
postedFile = this.FileUpload1.PostedFile;
fileData = new byte[fileInfo.FileSize];
stream = postedFile.InputStream;

stream.Read(fileData, 0, fileInfo.FileSize);
stream.Close();

FileStream filestream = new FileStream("C:\\test.txt",
FileMode.Create);
filestream.Write(fileData, 0, fileInfo.FileSize);
filestream.Close();

gFileID =
(Guid)db.ExecuteScalar("ContractsFilesInsert", fileInfo.ContractID,
fileInfo.FileName, fileInfo.FileSize, fileData, System.DateTime.Now);

fileInfo.FileID = gFileID;

return (fileInfo);
}
catch (Exception error)
{
return (fileInfo);
}
}



As you can see, I have some code to write the file to disk to double
check it's integrity (for debugging). The file gets uploaded fine and
is not corrupt before going into the database.


Here is the code used to retrieve the file from the database:


protected void SendFile(FileInfo fileInfo)
{
Database db = DatabaseFactory.CreateDatabase();
byte[] fileData = new byte[fileInfo.FileSize];

fileData =
(byte[])db.ExecuteScalar("ContractsFilesSelectByID", fileInfo.FileID);

FileStream filestream = new FileStream("C:\\test.txt",
FileMode.Create);
filestream.Write(fileData, 0, fileInfo.FileSize);
filestream.Close();

Response.Flush();
Response.ClearContent();
Response.ClearHeaders();
Response.AppendHeader("Content-Disposition", "attachment;
filename=" + fileInfo.FileName);
Response.ContentType = "text/plain";
Response.BinaryWrite(fileData);
Response.Flush();
Response.End();
Response.Close();
}


Here I am writing out the file to disk before I send the file out to
the user to double check it's integrity and eliminate the download
process as being the problem. The file that outputs is corrupt. Yes
I know the contentType will change according to the file...but I just
hardcoded "text/plain" temporarily as I am testing with text files.

Now...the odd part about all this, is JPG images seem to come out of
the database and sent to the downloader completely intact. Anything
else like zips, exe's, or even plain text files are corrupt.

Is the problem that I am using the ExecuteScaler method and casting
the results to a byte[]? If so, what's the preferred way to retrieve
files?

I'll be happy to include any additional information you need. Thanks
in advance!

 

[Steve C. Orr [MCSD, MVP, CSM, ASP Insider]]

Here I've covered how to upload files into a SQL Server database and how to
successfully download them again to the client:
http://SteveOrr.net/articles/EasyUploads.aspx

--
I hope this helps,
Steve C. Orr,
MCSD, MVP, CSM, ASPInsider
http://SteveOrr.net

Hello. I have an ASP.NET application (C#) that I allow users to
upload files. Files get stored in a SQL2005 database. The file data
is stored in a varbinary(max) column.

When the user uploads the file, I store it in a database. When a user
requests to download a file, the file is retrieved from the database
and then sent to them.

The files go in there ok. This is how I store them:

(note FileInfo is my own custom struct data type that I wrote to keep
track of File \ record info....NOT the System.IO class)

protected FileInfo StoreTempFile(FileInfo fileInfo)
{
byte[] fileData;
Guid gFileID;
Stream stream;
HttpPostedFile postedFile;
Database db = DatabaseFactory.CreateDatabase();

try
{
postedFile = this.FileUpload1.PostedFile;
fileData = new byte[fileInfo.FileSize];
stream = postedFile.InputStream;

stream.Read(fileData, 0, fileInfo.FileSize);
stream.Close();

FileStream filestream = new FileStream("C:\\test.txt",
FileMode.Create);
filestream.Write(fileData, 0, fileInfo.FileSize);
filestream.Close();

gFileID =
(Guid)db.ExecuteScalar("ContractsFilesInsert", fileInfo.ContractID,
fileInfo.FileName, fileInfo.FileSize, fileData, System.DateTime.Now);

fileInfo.FileID = gFileID;

return (fileInfo);
}
catch (Exception error)
{
return (fileInfo);
}
}



As you can see, I have some code to write the file to disk to double
check it's integrity (for debugging). The file gets uploaded fine and
is not corrupt before going into the database.


Here is the code used to retrieve the file from the database:


protected void SendFile(FileInfo fileInfo)
{
Database db = DatabaseFactory.CreateDatabase();
byte[] fileData = new byte[fileInfo.FileSize];

fileData =
(byte[])db.ExecuteScalar("ContractsFilesSelectByID", fileInfo.FileID);

FileStream filestream = new FileStream("C:\\test.txt",
FileMode.Create);
filestream.Write(fileData, 0, fileInfo.FileSize);
filestream.Close();

Response.Flush();
Response.ClearContent();
Response.ClearHeaders();
Response.AppendHeader("Content-Disposition", "attachment;
filename=" + fileInfo.FileName);
Response.ContentType = "text/plain";
Response.BinaryWrite(fileData);
Response.Flush();
Response.End();
Response.Close();
}


Here I am writing out the file to disk before I send the file out to
the user to double check it's integrity and eliminate the download
process as being the problem. The file that outputs is corrupt. Yes
I know the contentType will change according to the file...but I just
hardcoded "text/plain" temporarily as I am testing with text files.

Now...the odd part about all this, is JPG images seem to come out of
the database and sent to the downloader completely intact. Anything
else like zips, exe's, or even plain text files are corrupt.

Is the problem that I am using the ExecuteScaler method and casting
the results to a byte[]? If so, what's the preferred way to retrieve
files?

I'll be happy to include any additional information you need. Thanks
in advance!

 

[djhexx]

hahaha! I figured out the problem. The problem wasn't in the code at
all, but in my stored procedure.

Basically, in the stored procedure I was doing a Select File_Data

From Table_Name Where @File_ID = @File_ID. Note the @ symbol in

front of the column name! Bad! So, the end result was a query that
always returned the data for the first record in the table. Not
corrupt data....but the WRONG data. lol.

Thanks for the help!