Retrieve Images from a SQL database for display in an Asp.Net Datagrid

In our last article, Saving Images in a SQL database, we covered how to upload client images into a SQL database. In this article we will demonstrate how to retrieve the images (BLOB data) from a SQL database and display them in a ASP.Net datagrid. Our first step is to read the data from the Images table we have already created.

SELECT ImageKey, ImageName, Images, ImageType FROM Images

Now, let’s take a look at our datagrid:

<asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False">
   <Columns>
     <asp:BoundColumn DataField="ImageIdentity" HeaderText="ImageIdentity"

Visible=False></asp:BoundColumn>
     <asp:BoundColumn DataField="ImageName" HeaderText="Image

Name"></asp:BoundColumn>
     <asp:BoundColumn DataField="ImageType" HeaderText="Image

Type"></asp:BoundColumn>
     <asp:TemplateColumn  HeaderText="Image">
         <ItemTemplate >
            <img runat="server"
                 src='<%# "getImage.aspx?ID=" + DataBinder.Eval(Container.DataItem,
                 "ImageIdentity")  %>' ID="Img1"/>
         </ItemTemplate>
     </asp:TemplateColumn>
   </Columns>

</asp:DataGrid>

The first three columns: ImageIdentity, ImageName, and ImageType, are perfectly simple – these are bound columns, and all we need to do is set the DataField property to the column name in the datasource for the grid. The last column (Img1) is more interesting since you cannot bind a column to an image data type. Images are traditionally displayed in web pages using the HTML<IMG> tag, and that is what we are doing within our templated column as well. However, instead of having a static path to an image on the web server, the source of the image is a web page. 

The first thing we need to do in getImage.aspx is retrieve the image and ContentType of the image we want to display. By passing the key field for this record in the QueryString we can use the code below.

      System.Data.SqlClient.SqlDataReader Reader=null;
      string ImageIdentity = Request.QueryString["ID"].ToString();
      SqlConnection sqlConnection = new SqlConnection(@"initial catalog=Articles;user

id=sa;password=;server=myServer");
      sqlConnection.Open();    
      SqlCommand sqlCommand = new SqlCommand("Select Images,ImageType from Images where

imageKey="+ImageIdentity  ,sqlConnection);
      Reader= sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
      Reader.Read();

Our next step is to read the image into a byte array

      byte [] byteArray = (byte[]) Reader["Images"];

All that is left for us to do is specify the ContentType for the page, and use a BinaryWrite to move data from our byte array to the output of the page.

      Response.ContentType=Reader["ImageType"].ToString();
      Response.BinaryWrite(byteArray);

Since this is a datagrid column however, maybe we want to display a thumbnail of the image. To draw a thumbnail we are going to create a System.Drawing.Image object which allows us to resize the image into something more appropriate for display in our datagrid.

System.IO.MemoryStream mstream = 
     new System.IO.MemoryStream(byteArray,0,byteArray.Length);
System.Drawing.Image dbImage = 
     System.Drawing.Image.FromStream( new System.IO.MemoryStream(byteArray));
System.Drawing.Image thumbnailImage = 
     dbImage.GetThumbnailImage(100,100,null,new System.IntPtr());
thumbnailImage.Save(mstream,dbImage.RawFormat);
Byte[] thumbnailByteArray = new Byte[mstream.Length];
mstream.Position = 0;
mstream.Read(thumbnailByteArray, 0, Convert.ToInt32(mstream.Length));
Response.Clear();
Response.ContentType=Reader["ImageType"].ToString();
Response.BinaryWrite(thumbnailByteArray);

Now we want to get a little more ambitious with our page. We want to be able to click on the datagrid image column which contains the thumbnail of our database image, and view the full size image.

<ItemTemplate >
  <a href='<%# "getImage.aspx?ThumbNail=\"N\"&ID=" + DataBinder.Eval(Container.DataItem, 
                   "ImageIdentity")  %>'>
      <img runat="server" src='<%# "getImage.aspx?ID=" + DataBinder.Eval  
       (Container.DataItem, "ImageIdentity")%>' ID="Img1"/>
  </a>
</ItemTemplate>

Since we already have the getImage.aspx page coded, all we do is pass an additional parameter in the query string, so that the page knows whether to display the full size image or the thumbnail.

Login to Download code

by Llama Lopon