473,288 Members | 1,771 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

File corruption when retrieving files from a SQL 2005 database

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!

Mar 26 '07 #1
5 2283
Anyone have any ideas? Or am I posting in an inappropriate group?
Mar 26 '07 #2
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

<dj****@gmail.coma écrit dans le message de news:
11**********************@n59g2000hsh.googlegroups. com...
Anyone have any ideas? Or am I posting in an inappropriate group?


Mar 26 '07 #3
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)

dj****@gmail.com wrote:
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!
Mar 26 '07 #4
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
<dj****@gmail.comwrote in message
news:11**********************@y80g2000hsf.googlegr oups.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!
Mar 27 '07 #5
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!

Mar 27 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Fred MacMurray | last post by:
Hello, all. So we're all happy with Access. Or Access is "good enough" with most of our applications. I built a split database here at work that has about seven users who are on pretty much...
5
by: Upendra Agnihotram | last post by:
Hi, I am having Client-Server Access application. The source db resides on WinNT Server and some of the client systems are on Windows 2000 Professional. These days i am experiencing frequent...
9
by: John | last post by:
Hi All, I have encountered a problem with the ldb file of a database. When making changes to the backend databases I need exclusive access to make changes. I usually do this at night when all...
3
by: Scott | last post by:
Hi anyone!!! I have an access database file which is corupted after a computer crash. I have tried a compact and repair which failed. I have purchased an Access Repair package which can only...
2
by: Tom van Stiphout | last post by:
Hi all, I have a fairly large Access2000 application (FE/BE). One of the subforms (in datasheet) has a dropdown list named BackSplash. It's a ValueList, not LimitToList, bound to the BackSplash...
4
by: Brian K. | last post by:
I am trying to split up and secure a database that we've been using on a network for a few years, following the procedure listed in Q304932 from microsoft. I create a new blank database for...
13
by: Bob Darlington | last post by:
I have a repair and backup database routine which runs when a user closes down my application. It works fine in my development machine, but breaks on a client's at the following line: If...
2
by: nepdae | last post by:
Please forgive me, this is a long one. My 11-user Access 2000 database is having recurring corruption problems. The symptoms include the following: 1) corrupted fields in recently created or...
3
by: Stephan | last post by:
Hi all, I am new to access and I face the following "issue": I would like to create a database, to which users can upload files (=pdf, doc, xls...). The files shall be stored locally on a...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.