FAQ: Displaying Image from Database to GridView Control

I wrote this demo because I observed that lots of people in the forum (forums.asp.net) always ask questions on how to display Image that was stored in the database to GridView control.

Before reading this example, be sure that you have already know how to upload image to the database. If you are not familiar with it then I would suggest you to read my previous example about “Uploading and Storing Images to Database in ASP.NET”.

In this demo, we are going to use a Handler.ashx file for fetching the binary data from the database and stream it.

What is a Handler?

A handler is responsible for fulfilling requests from a browser. Requests that a browser manages are either handled by file extension (or lack thereof) or by calling the handler directly. Only one handler can be called per request. A handler does not have any HTML static text like .aspx or .ascx files. A handler is a class that implements the IHttpHandler interface. If you need to interact with any Session information, you will also need to implement IRequiresSessionState. If you want to make an asynchronus handler, you will need to implement the IHttpAsyncHandler interface instead of the IHttpHandler interface.

STEP 1: Creating a Handler file (.ashx)

If you are not familiar creating a handler file in Visual Studio then follow these few steps below :

* Right Click on the Project and select “Add New Item”

* On the popup window, select the “Generic Handler” file. See the screen shot below

* Click Add

STEP 2: Setting up the GridView

For the simplicity of this demo, I set up the GridView like below in the WebForm:

ASPX:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
        <Columns>
            <asp:BoundField  DataField="Id" ReadOnly="true" HeaderText="Image ID"/>
            <asp:BoundField  DataField="ImageName" HeaderText="File Name"/>
            <asp:BoundField  DataField="ImageType" HeaderText="Image File Type"/>
            <asp:BoundField  DataField="ImageSize" HeaderText="Image Size (bytes)"/>
            <asp:TemplateField HeaderText="Image View" ControlStyle-Width="100px" ControlStyle-Height="100px">
                <ItemTemplate>
                    <asp:Image ID="Image" runat="server" ImageUrl='<%# "Handler.ashx?id=" + Eval("Id")  %>' />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
</asp:GridView>

Noticed that we are using an Image control within the TemplateField column of the GridView for displaying the images. You will also observe that the ImageUrl property of the Image Control are being set declaratively with the path of the Handler file that we have just created in STEP 1 and pass the Id as the querystring value.

STEP 3: Streaming the Image Binary file from the database

Here’s the code block for streaming the image file in the handler file.

<%@ WebHandler Language="C#" Class="Handler" %>

 
using System;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Collections.Specialized;
 
public class Handler : IHttpHandler {

    public string GetConnectionString()
    {
        //sets the connection string from your web config file "ConnString" is the name of your Connection String
        return System.Configuration.ConfigurationManager.ConnectionStrings["MyConsString"].ConnectionString;
    }

    public void ProcessRequest(HttpContext context)
    {
        string id = context.Request.QueryString["id"]; //get the querystring value that was pass on the ImageURL (see GridView MarkUp in Page1.aspx)

        if (id != null)
        {

            MemoryStream memoryStream = new MemoryStream();
            SqlConnection connection = new SqlConnection(GetConnectionString());
            string sql = "SELECT * FROM TblImages WHERE Id = @id";
            
            SqlCommand cmd = new SqlCommand(sql, connection);
            cmd.Parameters.AddWithValue("@id", id);
            connection.Open();

            SqlDataReader reader = cmd.ExecuteReader();
            reader.Read();

            //Get Image Data
            byte[] file = (byte[])reader["Image"];

            reader.Close();
            connection.Close();
            memoryStream.Write(file, 0, file.Length);
            context.Response.Buffer = true;
            context.Response.BinaryWrite(file);
            memoryStream.Dispose();
        }
    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }
}

The code above fetches the image data from the database based from the Id that was passed through the querystring and then streams the image data using the MemoryStream object.

STEP 4: Binding GridView

Here are the code blocks for binding the Grid with image data from the database.

   public string GetConnectionString(){
        //sets the connection string from your web config file "ConnString" is the name of your Connection String
        return System.Configuration.ConfigurationManager.ConnectionStrings["MyConsString"].ConnectionString;
    }

 
    private void BindGrid()
    {

        DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection(GetConnectionString());
        try
        {
            connection.Open();
            string sqlStatement = "SELECT * FROM TblImages";
            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

            sqlDa.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
    }

    protected void Page_Load(object sender, EventArgs e){
        if (!Page.IsPostBack){
            BindGrid();
        }
    }