Asp.net: Binding Image in gridview using Handler from image column in databse sql2005/2008

In my pervious article

 Asp.net Saving Imagein binary into database table in Sql 2005/2008

you have learn how to save image file in binary format in a database column.

In this  post I going to fetch those (Northwind databse product table) record and bind into a grid view. For this I have created a generic handler for binding the image on gridview image control here the code below

HTML PART

<h2>Show the images in the GridView</h2>

 <asp:GridView ID="grdViewProducts" runat="server"

    AllowPaging="True" AutoGenerateColumns="False" TabIndex="1"

    DataKeyNames="ProductID" Width="100%" BackColor="White"

    CellPadding="3" BorderStyle="Solid" BorderWidth="1px"

    BorderColor="Black" GridLines="Horizontal" PageSize="10"

    onpageindexchanging="grdViewProducts_PageIndexChanging">

    <Columns>

        <asp:TemplateField HeaderText="Photo">

            <ItemStyle Width="10%" HorizontalAlign="Center" />

            <ItemTemplate>

                <img id="imgPhoto" src="GetDBImage.ashx?ProductID=<%# Eval("ProductID") %>"

                    width="125px" height="125px" title="<%# Eval("ProductName") %>" />

            </ItemTemplate>

        </asp:TemplateField>

        <asp:BoundField DataField="ProductName" HeaderText="Product Name" >

            <ItemStyle Width="30%" />

        </asp:BoundField>

        <asp:BoundField DataField="CompanyName" HeaderText="Supplier" >

            <ItemStyle Width="25%" />

        </asp:BoundField>

        <asp:BoundField DataField="CategoryName" HeaderText="Category" >

            <ItemStyle Width="20%" />

        </asp:BoundField>

        <asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit">

            <ItemStyle Width="15%" />

        </asp:BoundField>

        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" DataFormatString="{0:#0.00}">

            <ItemStyle Width="15%" />

        </asp:BoundField>

    </Columns>

    <RowStyle BackColor="White" ForeColor="#333333" />

    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Right" />

    <SelectedRowStyle BackColor="#A5D1DE" Font-Bold="true" ForeColor="#333333" />

    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

    <AlternatingRowStyle BackColor="#E2DED6" ForeColor="#284775" />

</asp:GridView>

C# Code

  protected void Page_Load(object sender, EventArgs e)

        {

            if (!Page.IsPostBack)

                BindGrid();

        }

private void BindGrid()

        {

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))

            {

                SqlCommand command = new SqlCommand(

                        "SELECT ProductID, ProductName, CompanyName, CategoryName, " +

                        "QuantityPerUnit, UnitPrice FROM Products " +

                        "JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID " +

                        "JOIN Categories ON Products.CategoryID = Categories.CategoryID ", connection);

                connection.Open();

                SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);

                IList<ProductView> productViewList = new List<ProductView>();

                while (dr.Read())

                {

                    ProductView productView = new ProductView();

                    productView.ProductID = Convert.ToInt32(dr["ProductID"].ToString());

                    productView.ProductName = dr["ProductName"].ToString();

                    productView.CompanyName = dr["CompanyName"].ToString();

                    productView.CategoryName = dr["CategoryName"].ToString();

                    productView.QuantityPerUnit = dr["QuantityPerUnit"].ToString();

                    productView.UnitPrice = Convert.ToDouble(dr["UnitPrice"].ToString());

                    productViewList.Add(productView);

                }

                grdViewProducts.DataSource = productViewList;

                grdViewProducts.DataBind();

            }

        }

        protected void grdViewProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)

        {

            grdViewProducts.PageIndex = e.NewPageIndex;

            BindGrid();

        }

    }

    public class ProductView

    {

        public int ProductID { get; set; }

        public string ProductName { get; set; }

        public string CompanyName { get; set; }

        public string CategoryName { get; set; }

        public string QuantityPerUnit { get; set; }

        public double UnitPrice { get; set; }

    }

Handler Code for Image Creation from database

public class GetDBImage : IHttpHandler

    {

        public void ProcessRequest(HttpContext context)

        {

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))

            {

                SqlCommand command = new SqlCommand("Select ProductImage from Products where ProductID = '" + context.Request.QueryString["ProductID"] + "'", connection);

                connection.Open();

                SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())

                {

                    if (dr["ProductImage"].ToString().Length > 0)

                    {

                        context.Response.BinaryWrite((byte[])dr["ProductImage"]);

                    }

                }

            }

        }

        public bool IsReusable

        {

            get

            {

                return false;

            }

        }

    }


here is the output

Downloadfull code from here