Problem displaying image data from SQL Server

Hello,

I'm having problems saving and then displaying binary data in sql server.

I have a form that takes a file specified by the user and inserts this into sql server:

protected void btnUploadFile_Click(object sender, EventArgs e)
{
if (theFile.PostedFile != null)
{
if (theFile.PostedFile.ContentLength > 0)
{
byte[] docBuffer = new byte[theFile.PostedFile.ContentLength];
Response.Write(theFile.PostedFile.ContentType.ToString());

if (docBuffer.Length > 0)
{
// save to db
DbAccess dbAccess = new DbAccess(); // my helper function for all db access etc

try
{
dbAccess.BuildCommand("Incentives_SaveDocument");
dbAccess.Parameters.Add("@.docImage", SqlDbType.Image).Value = docBuffer;
dbAccess.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
}
}
}

Stored proc:

ALTER PROCEDURE Incentives_SaveDocument
@.docImage image
AS
SET NOCOUNT ON

INSERT INTO Table1 (theData) VALUES (@.docImage)

RETURN

This appears to work fine. I store the binary data in a image column and if I query the db it shows the row as <Binary>.

The problem I have is with retrieving the data and saving it to a file. The file saves OK but when I open it is contains lots of "squares" that I suppose are the binary - it doesn't show the text.

The code for retrieving/displaying the doc is:

protected void btnView_Click(object sender, EventArgs e)
{
DbAccess dbAccess = new DbAccess();
byte[] byteArray = null;

try
{
dbAccess.BuildCommand("Incentives_RetrieveDocument");
dbAccess.Parameters.Add("@.id", SqlDbType.Int).Value = 6; // id for the doc to return
SqlDataReader reader1 = dbAccess.ReturnDataReader();

while (reader1.Read())
{
if (reader1.HasRows)
{
byteArray = (byte[])reader1["theData"];
}
}
reader1.Close();

FileStream fs = new FileStream("file1", FileMode.CreateNew, FileAccess.Write);
fs.Write(byteArray, 0, byteArray.Length);
fs.Flush();
fs.Close();

FileInfo fileInfo = new FileInfo("file1");

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileInfo.Name);
HttpContext.Current.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
HttpContext.Current.Response.ContentType = "application/msword";
HttpContext.Current.Response.WriteFile(fileInfo.FullName);
HttpContext.Current.Response.End();

}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{
dbAccess.CloseDbConnection();
}

The stored proc:

ALTER PROCEDURE Incentives_RetrieveDocument
@.id int
AS
SET NOCOUNT ON

SELECT * FROM Table1 WHERE id = @.id

RETURN

I would be grateful for any advice on this - its the first time I've worked with BLOB data.

Thanks

SiWhen you recreate a file from the image stored on the database, does it have the same extension?|||

Hello,

I've worked out what is was. I was uploading a byte array without the binary data in it. I needed the following to get it to work:

Stream dataStream = theFile.PostedFile.InputStream;
byte[] docBuffer = new byte[theFile.PostedFile.ContentLength];
int n = dataStream.Read(docBuffer, 0, theFile.PostedFile.ContentLength);

Si