How to retrieve saved images from SQL Server database and show on the Page?

Introduction

ADO.NET is a mechanism to communicate with the database. It allows us to work in connected (database connection is alive while the operation is performed) as well as disconnected architecture (database connection is closed and operation can be performed on the data).

Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.

You might have a requirement where you have to save the Images into the database rather than on the server in file systems. I have already covered How to save an Image into the database using Stored Procedure in ASP.NET?, In this article we shall learn retrieving saved image from the database and showing on the page. 

To demonstrate that I have created a web page with GridView and I shall show the image into the GridView.

Below is my GridView code on ASPX Page page

ASPX PAGE

<asp:GridView ID="GridView1" runat="server" EnableViewState="false" 
AutoGenerateColumns="false">
        <Columns>
            <asp:BoundField HeaderText="AutoId" DataField="AutoId" />
            <asp:BoundField HeaderText="File Name" DataField="FileName" />
            <asp:TemplateField HeaderText="File">
                <ItemTemplate>
                    <img src="ShowImage.ashx?autoId=<%# Eval("AutoId") %>" alt="<%#
Eval("FileName") %>" />
               </ItemTemplate>
           </asp:TemplateField>
           <asp:TemplateField HeaderText="IsActive">
               <ItemTemplate>
                   <%# Eval("Active").ToString().Equals("True") ? "Yes" : "No" %>
               </ItemTemplate>
          </asp:TemplateField>
 
    </Columns>
</asp:GridView>

Where we have kept AutoId, FileName fields of the Database table as BoundField and File as the TemplateField (under which we have kept html img tag with src as the ShowImage.ashx ie. generic handler and passing the AutoId as the parameter, AutoId is the primary key of this table). The last column is the Active field where we are displaying Yes, if database value is True otherwise No.

CODE BEHIND

    string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
 
protected void Page_Load(object sender, EventArgs e)
    {
       if (!IsPostBack)
       {
           BindFiles();
       }
  }
 
  /// <summary>
  /// Binds the files.
  /// </summary>
  private void BindFiles()
  {
      DataTable table = new DataTable();
 
      // get the connection
      using (SqlConnection conn = new SqlConnection(_connStr))
      {
         // write the sql statement to execute
         string sql = "SELECT AutoId, FileName, FileContent, Active FROM Files Order
By AutoID ASC";
            // instantiate the command object to fire
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
               // get the adapter object and attach the command object to it
               using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
               {
                   // fire Fill method to fetch the data and fill into DataTable
                   ad.Fill(table);
                }
               // DataAdapter doesn't need open connection, it takes care of opening and
closing the database connection
            }
       }
       GridView1.DataSource = table;
       GridView1.DataBind();
   }

Above code is simple where we are retrieving records from the database using ADO.NET and populating to the GridView.

Below is the code snippet for the ShowImage.ashx generic handler that is being used as src of the img tag inside GridView..

SHOWIMAGE.ASHX FILE

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
 
public class ShowImage : IHttpHandler {
 
   public void ProcessRequest (HttpContext context)
   {
       if (context.Request.QueryString["autoId"] == null) return;
 
       string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
       string autoId = context.Request.QueryString["autoId"];
       using (SqlConnection conn = new SqlConnection(connStr))
       {
           using (SqlCommand cmd = new SqlCommand("SELECT FileContent FROM Files WHERE
AutoID = @autoId", conn))
            {
               cmd.Parameters.Add(new SqlParameter("@autoId", autoId));
               conn.Open();
               using (SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                     reader.Read();
 
context.Response.BinaryWrite((Byte[])reader[reader.GetOrdinal("FileContent")]);
                    reader.Close();
                }
            }
        }
   }
 
   public bool IsReusable {
       get {
           return true;
       }
    }
}

In the above code snippet, we have first checked for the “autoId” querystring, if it is being passed to this handler page or not. If not, return and does nothing else get the connection string from the web.config file, retrieve the autoId from the querystring and fire the Sql statement (in real time project, you can use Stored Procedure) to get the FileContent field from the database. As this field content is of Image type so we need to write this in the array of bytes on the page so we have used BinarWriter method of Response object and writing the arrays of bytes. As this array of bytes are nothing but the image content so the image gets displayed on the page.

Do not worry about IsReusable method, this just notifies the application that the same instance of the handler should be reused or not.

OUTPUT

In case your requirement is not to list the images from the database to GridView, simply use the img tag used in the GridView where you want to display the image and follow the same steps.

Hope this article would be useful for those looking for listing saved images from the database. I have been writing about frequently faced problem solutions in ASP.NET. Stay tuned for more articles like this.