Friday, 18 November 2011

How to Show images in the GridView when image stored in the database


This post explains how to show images in the Grid View in a column when the image files are stored in the database.

I am using Products table in the Northwind database. So, make sure to have Northwind database in SQL Server and change the connection string in the Web.Config to run the source code.

In this implementation, the Products table will have an image column for storing the image of each product and the GridView will have a column to show the image of each product.

Preparing the database

Note: This is an option section which tells how to create an image column in Product table in Northwind database. If you have database with image stored in table, please skip this section and refer next section Steps to show the images in the GridView.

  1. Add a column ProductImage in the Products table.
    ALTER TABLE Products ADD ProductImage IMAGE
  2. Updating image of each product in the table

    As the column is added newly there is no image present in the table. So I have added a page (UpdateImage.aspx) file for storing the image in the source code.
    protected void btnSave_Click(object sender, EventArgs e)
    {
        if ((fuImage.PostedFile.FileName.Trim().Length > 0) &&
            (fuImage.PostedFile != null))
        {
            byte[] image = new byte[fuImage.PostedFile.ContentLength];
    
            fuImage.PostedFile.InputStream.Read(image, 0, (int)fuImage.PostedFile.ContentLength);
    
    
            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString);
    
            SqlCommand command = new SqlCommand();
            command.CommandText = "Update Products set ProductImage = @Image where ProductID = '" + txtProductID.Text + "'";
    
            command.CommandType = CommandType.Text;
            command.Connection = connection;
    
            PrepareSQLParameter(command, "@Image", SqlDbType.Image, image.Length, image);
    
            connection.Open();
    
            int result = command.ExecuteNonQuery();
            connection.Close();
    
            txtProductID.Text = "";
        }
    }
    private SqlParameter PrepareSQLParameter(SqlCommand command, string parameterName, SqlDbType parameterType, int parameterLength, object parameterValue)
    {
        SqlParameter parameter = new SqlParameter(parameterName, parameterType, parameterLength);
        parameter.Value = parameterValue;
    
        command.Parameters.Add(parameter);
        return parameter;
    }
    The code is very simple - the web page contains a text box for getting the Product Id of the record and a file upload control in the page. On click of Save button on the page, below code executed.
    <div>
        <table>
            <tr>
                <td style="vertical-align:top">Product ID</td>
                <td><asp:TextBox ID="txtProductID" runat="server" Width="100px"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Image</td>
                <td><asp:FileUpload runat="server" ID="fuImage" Width="400px" /> </td>
            </tr>
            <tr>
                <td colspan="2" style="text-align:center">
                    <asp:Button ID="btnSave" runat="server" Width="80px" Text="Save" 
                        onclick="btnSave_Click" />
                </td>
            </tr>
        </table>
    </div>
    
Steps to show the images in the GridView

  1. To retrieve the image from the database, we required to add a Generic Handler file (GetDBImage.ashx) in the project.
    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"]);
                }
            }
        }
    }
  2. Add a GridView in the Page
    <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="4"
        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>
    The GridView contains a template field which contains an image control. The Generic Handler gets the image and assign to the src property of the image control.
  3. Below is the code for binding the records to the GridView.
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
            BindGrid();
    }
    /// <summary>
    /// Method which binds the data to the Grid
    /// </summary>
    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();
    }
  4. Below is the code for ProductView entity class
    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; }
    }
As you see in this example, there is no direct way to get the image with other data and bind to the GridView. So we are using Generic Handler for getting the image and assigning to the GridView at rendering time by calling with http url.

download the source code in C# here and VB here.

Below is the output of the Page.


4 Responses to “How to Show images in the GridView when image stored in the database”

  • Shwe Nann Thu says:
    12 December 2012 at 09:47

    thanks a lot ...
    it is really helpful to me :)

  • vinod verma says:
    7 February 2013 at 13:50

    thanks

  • Anto Raj says:
    20 August 2013 at 17:46

    I have 10 images in my table.
    when i reterive the images it shows the first image as 10 times.
    can you please help me to find the error...

  • Thirumalai M says:
    21 August 2013 at 14:21

    Hi,

    In the GenericHandler - ProcessRequest event, verify the select query. I believe the Id you are passing from the UI layer is same. Check the below points.

    1. The ProductId (or the unique id passing to GenericHanlder) must be unique across each row.
    2. When selecting rows from database and binding to the grid, it must have the unique id column selected and sent that id while calling GenericHandler call (verify Add a GridView in the Page->11 line).
    3. When the SQL Query been formed in GenericHandler, the query must provide different resultset (including the image column), when you query from SQL Management Studio. (Pls keep a breakpoint and get the query)
    3. Verify is any static method/variable defined in the Generic Handler, or where retrieves the image from db.

    If all those are expected fine, the image stored in the db for each row is same. pls test by storing some other image for some rows.

Post a Comment