This is part 2 of a 2 part article. In Part 1 I explained how to insert a file image into a SQL database using Microsoft SQL Server and the Microsoft .NET Framework. In this article I explain how to retrieve the file image from the database and display it to the application user. One way to do this is to use ASP .NET as a front end layer. This is probably the easiest way to handle the task. For this example we have a ASP .NET web page called "Open.aspx". Open.aspx and Open.aspx.cs Recall from part 1, that the database table where the file images is stored contains a unique identifier (a.k.a a GUID) field. This is the value the Open.aspx page will use to request the file from the database. In the code behind file, Open.aspx.cs there is a page load event as shown below.
protected void Page_Load(object sender, EventArgs e)
{
try
{
string strFileId = Request.QueryString["FileId"].ToString();
if(!string.IsNullOrEmpty(strFileId))
{
LoadAttachment(strFileId);
}
}
catch
{
//Do nothing
}
}
When the page loads it reads in the query string value "FileId". Once the FileId is known the page then calls the local method "LoadAttachment". The LoadAttachment method makes a call to our custom class which handles database operations. Below is the code. Method LoadAttachment()
private void LoadAttachment(string FileId)
{
try
{
Attachment MyAttachment = null;
MyAttachment = Attachment.GetOne(new Guid(FileId));
//The real magic starts here:
//Transform page from HTML into the content of the file
Response.Clear();
Response.ClearContent();
Response.BufferOutput = true;
Response.AddHeader("Content-Disposition", "attachment;
filename=" + MyAttachment.FileName);
Response.ContentType = "application/octet-stream";
//Now write the contents of the file into the page
Response.OutputStream.Write(MyAttachment.FileImage, 0
MyAttachment.FileImage.Length);
//Close the Response stream
Response.Flush();
Response.Close();
//At this point the File Download dialog will appear.
}
catch(Exception ex)
{
response.Write("Error: " + ex.Message);
}
}
The LoadAttachment method causes the File Download dialog box to appear.
The last little bit of code to share is the custom Attachment class method GetOne. The GetOne method is called by the Open.aspx.cs LoadAttachment method. Static method: Attachment.GetOne()
public static Attachment GetOne(Guid FileId)
{
Attachment MyAttachment = null;
SqlConnection sqlCon = new SqlConnection(ConnectionString);
sqlCmd = new SqlCommand(@"
select file_id, file_name, file_image
from Attachments
where file_id=@file_id", sqlCon);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Parameters.Add("@file_id",
SqlDbType.UniqueIdentifier).Value = FileId;
sqlCon.Open();
SqlDataReader Reader = sqlCmd.ExecuteReader();
if (Reader.Read())
{
MyAttachment.FileId = new Guid(Reader["file_id"].ToString());
MyAttachment.FileName = Reader["file_name"].ToString();
MyAttachment.FileImage = (byte[])Reader["file_image"];
}
Reader.Close();
return MyAttachment;
}
Note that when the SQL Data Reader accesses the file_image field it is required to cast the content into a byte[] array. That's all that's needed to read a file from the database and display it. Please note that the code samples shown have been simplified for demonstration purposes. You'll need to be sure to add proper error handling if you choose to sample the code found in this article.