Search This Blog

Tuesday, February 21, 2012

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


In my pervious article
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




4 comments :