Show images on Grid View from File Stream SQL Server 2008
Background :
In my last post about SQL Server 2008 new feature File Stream (Saving and Retrieving File Using FileStream SQL Server 2008), we did an example of saving an image to the file stream and then retrieve it back and make it available for download.
The result of that example looks like as below.
But, one has to press the button to download the image file. One of my blog reader raise a point that he wants to display the same image instead of Get File button which is going to download.
Introduction :
So, in this post I will explain you, how can we rendered the image before actually downloading it and show that in the grid (Maybe as thumbnail, but this post will not discuss any thing about generating thumbnails).
Note : If you want to know. How to add files to the file stream please see my post Saving and Retrieving File Using FileStream SQL Server 2008
Getting Started:
We will complete this goal by using HttpHandler. lets first alter our gridview.
1: <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
2: onrowcommand="GridView1_RowCommand">
3: <Columns>
4: <asp:BoundField DataField="ID" HeaderText="ID" />
5: <asp:BoundField DataField="SystemNumber" HeaderText="System Id" />
6: <asp:TemplateField>
7: <ItemTemplate>
8: <asp:LinkButton ID="lbGetFile" runat="server" CommandName="GetFile" CommandArgument='<%#Eval("ID") %>' ><img src='<%#Eval("ID") %>.jpg' /></asp:LinkButton>
9: </ItemTemplate>
10: </asp:TemplateField>
11: </Columns>
12:
13: </asp:GridView>
Notice the link button on line no 8. I have now specify an image tag inside Link button and pass the primarykey of tbl_files as the file name along with random “.jpg”. So that, it can finally looks like as follows
8e7af927-cc7e-4515-8409-d94566246de8.jpg
a3de6abb-382f-484c-822c-7f93e0ede0c7.jpg
4ad64bf1-ea6e-4228-bdc0-300a0cd90f5a.jpg
The idea is, I will attach the handler with jpg file type to accommodate the incoming requests.
Now, lets create HttpHandler and name it “imageHandler”
public class imageHandler : IHttpHandler
{
#region IHttpHandler Members
public bool IsReusable
{
get { return false; }
}
public void ProcessRequest(HttpContext context)
{
//Getting file name from incoming request.
string url = Path.GetFileName(context.Request.Path);
Guid FileId;
try
{
//Since we have all our primary keys stored in GUID
//Try parsing the file name to Guid
FileId = new Guid(Path.GetFileNameWithoutExtension(url));
}
catch (FormatException)
{
//If some other JPG file is requested
FileId = Guid.Empty;
}
if (FileId != Guid.Empty) // If the call is for valid Image File Stream
{
SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
objSqlCon.Open();
SqlTransaction objSqlTran = objSqlCon.BeginTransaction();
SqlCommand objSqlCmd = new SqlCommand("FileGet", objSqlCon, objSqlTran);
objSqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter objSqlParam1 = new SqlParameter("@ID", SqlDbType.VarChar);
objSqlParam1.Value = FileId.ToString();
objSqlCmd.Parameters.Add(objSqlParam1);
string path = string.Empty;
string fileType = string.Empty;
using (SqlDataReader sdr = objSqlCmd.ExecuteReader())
{
while (sdr.Read())
{
path = sdr[0].ToString();
fileType = sdr[1].ToString();
}
}
objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);
byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();
SqlFileStream objSqlFileStream = new SqlFileStream(path, objContext, FileAccess.Read);
byte[] buffer = new byte[(int)objSqlFileStream.Length];
objSqlFileStream.Read(buffer, 0, buffer.Length);
objSqlFileStream.Close();
objSqlTran.Commit();
context.Response.AddHeader("Content-disposition", "attachment; filename=" + Path.GetFileName(path) + fileType);
// Here you need to manage the download file stuff according to your need
context.Response.ContentType = "application/octet-stream";
context.Response.BinaryWrite(buffer);
}
else
{ // If the call is for some other JPG file, nothing to do with file stream.
context.Response.WriteFile(context.Request.Path);
}
}
Well, read the comments I wrote in the code. That will of course help you to understand what actually I have done.
And then register the HttpHandler.
<httpHandlers>
<add verb="*" path="*.jpg" type="LearningApp.imageHandler, LearningApp"/>
</httpHandlers>
Conclusion:
There we go, In this way we can show the images stored on file stream in grid view. You can download both VS 2008 and VS 2010 project files.