Working Display images in DataList from Database

Display images in DataList from Database

In one of my previous article I have shown you how you can save images in database as binary data using File Upload control. I have received many requests to write an article on displaying binary images from the database in a data bound control such as DataList or GridView. In this tutorial I will show you how you can achieve this with only few lines of code.

Display Images in DataList from Database

To complete this article, I assume that you have already stored images in database as binary data and if you are confused how to do this read my article Saving images in database using ASP.NET FileUpload Control. I saved some laptops with their images in my database for the purpose of this article.

I am using DataList control for this article but you can easily use same technique for Repeater or GridView control. First we need is to set up a simple DataList control that shows products from the database. Following is the HTML source for the DataList control. 
<asp:DataList ID="DataList1" runat="server" RepeatColumns="3" RepeatDirection="Horizontal"
   Width="100%" BorderColor="#336699" BorderStyle="Solid" BorderWidth="2px">
  
   <ItemTemplate>
      <asp:Label ID="Label1" runat="server" Text='<%# Eval("ProductName") %>' Font-Bold="True"
         Font-Size="10pt" ForeColor="#336699" Width="100%" />
      <br />
      <asp:Image ID="Image1" runat="server"
         ImageUrl='<%# "GetImage.aspx?id=" + Eval("ProductID") %>' />
   </ItemTemplate>
   <ItemStyle HorizontalAlign="Center" VerticalAlign="Top"  />
</asp:DataList>
The above code looks straight forward as it is using ItemTemplate of DataList control to display Product Name and Product Image. The interesting line for you is the Image control that has ImageUrl property set to another asp.net page which will actually generate images from the database.

You need to bind the above DataList control by using the code shown below. The code is simple ADO.NET code in which I fill one DataTable object and bind DataList control with the data table. 
if (!Page.IsPostBack)
{
   string constr = "Server=SampleServer; Database=SampleDB; uid=test; pwd=test";
   string query = "SELECT ProductID, ProductName FROM Products";

   SqlDataAdapter da = new SqlDataAdapter(query, constr);
   DataTable table = new DataTable();

   da.Fill(table);
   DataList1.DataSource = table;
   DataList1.DataBind();
}
Now we are moving to interesting part of this jigsaw puzzle, GetImage.aspx page. As I already told you that GetImage.aspx page main job is to read your binary image from the database and render it on the Image control. I am passing the Product ID to this page as query string as you have seen above in the Image control in the DataList ItemTemplate. 
protected void Page_Load(object sender, EventArgs e)
{
   string id = Request.QueryString["id"];

   string constr = "Server=SampleServer; Database=SampleDB; uid=test; pwd=test";
   string query = "SELECT ProductImage FROM Products WHERE ProductID = @ProductID";

  SqlConnection con = new SqlConnection(constr);
  SqlCommand com = new SqlCommand(query, con);

  com.Parameters.Add("@ProductID", SqlDbType.Int).Value = Int32.Parse(id);

  con.Open();
  SqlDataReader r = com.ExecuteReader();
 
  if (r.Read())
  {
     byte[] imgData = (byte[])r["ProductImage"];
     Response.BinaryWrite(imgData); 
  }
  con.Close();
}
In the above code I passed product id as parameter in the SELCT query to load particular product image from the database. The main trick is happening in the if condition where I retrieved binary image as byte array and write byte array to the response stream using BinaryWrite method of Response object.

I hope this article will help many others who thought that loading and displaying images in ASP.NET controls required lot of code.