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

Tweet

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.