ASP.NET - Image handler to fetch and show Images from varbinary(MAX)

Imagine you want to get images from [Production].[ProductProductPhoto] table in AdventureWorks SQL sample database. The following code can be in a generic handler (.ashx) file.

public class ImgHandler : IHttpHandler
{
private string sqlCnStr =
   ConfigurationManager.ConnectionStrings["CnStrAdvWrk"].ConnectionString;

public void ProcessRequest(HttpContext context)
{
   string productId = context.Request.QueryString["PrdId"];
   if (string.IsNullOrEmpty(productId))
      productId = "1";

   using (SqlConnection sqlCn = new SqlConnection(sqlCnStr))
   {
   StringBuilder cmd = new StringBuilder();
   cmd.Append("SELECT PrdPhoto.[ThumbNailPhoto] as PrdPhoto ");
   cmd.Append(" FROM [Production].[Product] as Prd ");
   cmd.Append(" JOIN [Production].[ProductProductPhoto] as PPrdPhoto ");
   cmd.Append(" ON Prd.ProductID = PrdPrdPhoto.ProductID ");
   cmd.Append(" JOIN [Production].[ProductPhoto] as PPhoto ");
   cmd.Append(" ON PPrdPhoto.ProductPhotoID = PPhoto.ProductPhotoID ");
   cmd.Append(" WHERE Prd.[ProductID] = " + productId);

   sqlCn.Open();
   SqlCommand sqlCmd = new SqlCommand(cmd.ToString(), sqlCn);
   SqlDataReader sqlDtaRdr = sqlCmd.ExecuteReader();
   sqlDtaRdr.Read();
   context.Response.BinaryWrite( (byte[]) sqlDtaRdr[0] );
   sqlCn.Close();
   context.Response.End();
   }
}

   public bool IsReusable
   {
   get { return false; }
   }
}

Now you just need to call the handler.

If you have an image control and you know the ProductId:

<asp:Image ID="Image1" runat="server"
ImageUrl="~/ImgHandler.ashx?PrdId=1" />

or if you have a GridView and you want to have a column with product images, then you can use TemplateField with and Image control inside the ItemTemplate:

<asp:TemplateField HeaderText="Image">
   <ItemTemplate>
      <asp:Image ID="Image1" runat="server"
      ImageUrl='<%# "ImgHandler.ashx?PrdId=" + Eval("PrdId") %>' />'
   </ItemTemplate>
</asp:TemplateField>

ASP.NET - Image handler to fetch and show Images from varbinary(MAX)