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

Sheonarayan
Posted by in ADO.NET category on for Beginner level | Points: 250 | Views : 40386 red flag
Rating: 4 out of 5  
 3 vote(s)

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. 

Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com

Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Bhanubysani on: 8/26/2011 | Points: 25
article is very good..there is any alternate with out using SHOWIMAGE.ASHX FILE this ashx file..y it is used.

i heard that in realtime images will be saved in local folders.plz post that type of article its more useful.how to save the image in local folder and how to dispaly the file..

plz post taht article
Posted by: Shiebas on: 1/8/2012 | Points: 25
Hi this article is very good, i would appreciate it if you can follow up on this article by providing code for edit or replace the picture.
thanks in advance
Posted by: Sheonarayan on: 2/2/2012 | Points: 25
@Bhanubysani,

Saving images into the hard disk of the server and storing the file name with path into the database and then showing in the GridView is very easy. There are articles on this website for that, here is one I quickly found http://www.dotnetfunda.com/articles/article976-displaying-clickable-image-in-a-gridview.aspx.

Hope this will help.

@Shiebas,

To edit/update images, it is same as updating records using GridView, please follow this article http://www.dotnetfunda.com/articles/article29.aspx.

Thanks!

Keep browsing and learning stuffs.
Posted by: Raj.Trivedi on: 10/1/2012 | Points: 25
Hello Sir,

The article is awesome,i want to know how can i use one handler to bind images from 2 different tables,
I have 2 pages one for rent and another for sale
i want images from rent table when i use the rent radio button and search and images from sale table when i search using sale radio button
i want to do this in one handler

Regard's
Raj

Login to post response

Comment using Facebook(Author doesn't get notification)