In this article, we shall learn how to retrieve the saved images from the SQL Server database and show them 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.