Retrieve images from SQL Server database and bind to Gridview

In the previous blog post I have explained how to upload images to SQL Server Database. In this post I am going to show you how you can retrieve images stored in SQL Server Database and bind it to gridview. I am going to handler to bind image in the gridview. Start by adding a handler at the root directory of your project and call it as ShowPhoto.ashx. We are going to use the same table that we used to store images in the database in the previous post. Additionally, a couple of stored procedures are needed to bind images to gridview. Refer previous post for tables

Stored procedure to get picture by Id.

CREATE PROCEDURE [dbo].[sprocGetPictureById]
@ImageId int
AS
BEGIN
SELECT [Picture]
  FROM [Pictures] WHERE ImageId = @ImageId
END

Stored procedure to get all pictures.

CREATE PROCEDURE [dbo].[sprocGetPictures]

AS
BEGIN
SELECT [ImageId]
      ,[ImageName]
      ,[ImageSize]
  FROM [Pictures]
END

ShowPhoto.ashx

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

using System;
using System.Web;
using System.IO;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Drawing.Imaging;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public class ShowPhoto : IHttpHandler
{
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    object img;
    public void ProcessRequest(HttpContext context)
    {
        int imageId, newWidth = 100, newHeight = 100;
        double scale = 0;
        if (context.Request.QueryString["id"] != null)
            imageId = Convert.ToInt32(context.Request.QueryString["id"]);
        else
            throw new ArgumentException("No parameter specified");

        //Code to Read Thumbnail Image from the database
        System.Drawing.Image image = System.Drawing.Image.FromStream(ShowPicture(imageId));
        if (image.Height < image.Width)
        {
            scale = ((double)newHeight) / image.Width;
        }
        else
        {
            scale = ((double)newWidth) / image.Height;
        }

        // Set new width and height.
        newWidth = (int)(scale * image.Width);
        newHeight = (int)(scale * image.Height);

        // create a new image from original
        System.Drawing.Image thumbnail = new Bitmap(image, newWidth, newHeight);
        System.Drawing.Graphics graphic = System.Drawing.Graphics.FromImage(thumbnail);

        graphic.InterpolationMode = InterpolationMode.HighQualityBicubic;
        graphic.SmoothingMode = SmoothingMode.HighQuality;
        graphic.PixelOffsetMode = PixelOffsetMode.HighQuality;
        graphic.CompositingQuality = CompositingQuality.HighQuality;

        graphic.DrawImage(image, 0, 0, newWidth, newHeight);

        ImageCodecInfo[] info;
        info = ImageCodecInfo.GetImageEncoders();
        EncoderParameters encoderParameters;
        encoderParameters = new EncoderParameters(1);
        encoderParameters.Param[0] = new EncoderParameter(Encoder.Quality, 100L);
        context.Response.ContentType = "image/jpeg";
        thumbnail.Save(context.Response.OutputStream, info[1], encoderParameters);
    }

    public Stream ShowPicture(int imageId)
    {
        SqlCommand cmd = new SqlCommand("sprocGetPictureById", sqlcon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ImageId", imageId);
        sqlcon.Open();
        try
        {
            img = cmd.ExecuteScalar();
            return new MemoryStream((byte[])img);
        }
        catch
        {
            return null;
        }
        finally
        {
            sqlcon.Close();
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}

Next, right-click the root of your project and click Add new item -> Web Form and give it a name (in our case RetrieveImage.aspx)

RetrieveImage.aspx

<div>
    <asp:GridView ID="gvPictures" runat="server" AutoGenerateColumns="False"
        EnableModelValidation="True">
        <Columns>
            <asp:BoundField DataField="ImageId" HeaderText="ID" />
            <asp:BoundField DataField="ImageName" HeaderText="Image Name" />
            <asp:BoundField DataField="ImageSize" HeaderText="Image Size(Kb)" />
            <asp:TemplateField HeaderText="Picture">
                <ItemTemplate>
                    <img id="Img1" alt='<%#Eval("ImageName") %>' runat="server" src='<%# "~/ShowPhoto.ashx?id=" + Eval("ImageId") %>' />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
</div>

RetrieveImage.aspx.cs

SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            gvPictures.DataSource = BindPictures();
            gvPictures.DataBind();
        }
    }


private DataTable BindPictures()
    {
        DataTable dtPictures = new DataTable();
        SqlDataAdapter adpPictures = new SqlDataAdapter("sprocGetPictures", sqlcon);
        adpPictures.SelectCommand.CommandType = CommandType.StoredProcedure;
        try
        {
            adpPictures.Fill(dtPictures);
            return dtPictures;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            sqlcon.Close();
        }
    }