How to get the image from the sql server database and display in gridview?

Posted by Prabu_Spark under ASP.NET on 11/3/2012 | Points: 10 | Views : 18179 | Status : [Member] | Replies : 5
Hi sir,
I need the asp.net program to display the image in gridview. Image is stored in [datatype:image] in sql server 2005. Please
need solution for this.




With regards,
J.Prabu

With regards,
J.Prabu.
[Email:prbspark@gmail.com]



Responses

Posted by: Saratvaddilli on: 11/3/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
we need a webhandler to do this
kindly go through these links
http://geekswithblogs.net/dotNETvinz/archive/2009/04/24/faq-displaying-image-from-database-to-gridview-control.aspx
http://msdn.microsoft.com/en-us/library/aa479350.aspx
http://www.dotnetcurry.com/ShowArticle.aspx?ID=129

Thanks and Regards
V.SaratChand
Show difficulties that how difficult you are

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sandhyab on: 11/3/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

The Below Code is used to retrieve the Saved Images from the database inside the Grid view.
Write the following Code in Your 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>

In Code Behind:
string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindFiles();
}
}

private void BindFiles()
{
DataTable table = new DataTable();

using (SqlConnection conn = new SqlConnection(_connStr))
{

string sql = "SELECT AutoId, FileName, FileContent, Active FROM Files Order By AutoID ASC";

using (SqlCommand cmd = new SqlCommand(sql, conn))
{

using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
{
ad.Fill(table);
}

}
}
GridView1.DataSource = table;
GridView1.DataBind();
}

In the above cod, you can see that we have a GridView where we have specified BoundField and TemplateField on the .aspx page that is being bounded with the records from the database. Apart from FileContent, other fields can be specified as Text so to show the FileContent field that is of image type, we have used img html element and its src attribute has been specified as a Generic Handler (ShowImage.ashx file) that is being passed with the AutoId as the querystring. Below is the code snippet for the ShowImage.ashx generic handler.

Write thiscode in SHOWIMAGE.ASHX FILE (GenericHandler):
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;
}
}
}
Here, IsReUsable just notifies the application that the same instance of the handler should be reused or not. I hope this will help you.

Thanks & Regards,
Sandhya




Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nkkppp on: 11/4/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Prabhu,

This can be done using and without using handler.

USING HANDLER

<div>
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="sid" HeaderText="Student Id"
SortExpression="sid" />
<asp:BoundField DataField="sname" HeaderText="Student Name"
SortExpression="sname" />
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:ImageButton ID="Image1" runat="server"
ImageUrl='<%# "Handler.ashx?SID=" + Eval("SID")%>'/>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="Data Source=PRATHAPG\SQLEXPRESS;Initial Catalog=Prathap;Integrated Security=True"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Student]"></asp:SqlDataSource>
</div>

public class Handler2 : IHttpHandler {

public void ProcessRequest (HttpContext context) {
SqlConnection con = new SqlConnection("server=PRATHAPG\\SQLEXPRESS;database=prathap;trusted_connection=true");
SqlCommand cmd = new SqlCommand("Select photo from student where SID =@SID",con);

SqlParameter sid = new SqlParameter("@SID", System.Data.SqlDbType.Int);
sid.Value = context.Request.QueryString["SID"];
cmd.Parameters.Add(sid);
con.Open();
SqlDataReader dReader = cmd.ExecuteReader();
dReader.Read();
context.Response.BinaryWrite((byte[])dReader["photo"]);
dReader.Close();
con.Close();
}

public bool IsReusable {
get {
return false;
}
}

Without Using Handler


SqlConnection cn = new SqlConnection("server=prathapg\\sqlexpress;database=prathap;trusted_connection=true");

SqlCommand cmd = new SqlCommand("select Data from savedoc where docid=@id", cn);
cmd.Parameters.Add("@id", SqlDbType.Int).Value =int.Parse(txt.Text);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
byte[] bytes = (byte[])dr["Data"];
string base64String = Convert.ToBase64String(bytes, 0, bytes.Length);

// In place of Image1 you can get the id of the Image control in gridview using Findcontrol method and implement the below code
Image1.ImageUrl = "data:image/png;base64," + base64String;
Image1.Visible = true;
}





Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vforvijay on: 11/4/2012 [Member] Starter | Points: 25

Up
0
Down
Hi...

Refer the below link to get the image from database..

http://www.dotnetcode.in/2011/06/how-to-retrieve-uploaded-images-from.html

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Satyapriyanayak on: 1/24/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
First create a table student
Create table student (sid varchar(50),sname varchar(50),saddress varchar(50), smarks int,pic nvarchar (50))

Store the picture in the program folder and save there name in pic column of the table with there extension like (.gif,.jpg,.bmp).

Default.aspx code

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
AllowPaging="True" PageSize="1" BackColor="#FF9999"
onpageindexchanging="GridView1_PageIndexChanging">
<HeaderStyle BackColor="#FFCC99" />
<Columns>
<asp:TemplateField HeaderText="Image" SortExpression="pic">
<ItemTemplate>
<asp:Image ID="Image1" Height="60" Width="60" ImageUrl='<%# Bind("pic") %>' runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Id" SortExpression="sid">

<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("sid") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Name" SortExpression="sname">

<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("sname") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>


<asp:TemplateField HeaderText="Address" SortExpression="saddress">

<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("saddress") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Marks" SortExpression="smarks">

<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("smarks") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>

Default.aspx.vb code

Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
Inherits System.Web.UI.Page
Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
Dim con As New SqlConnection(strConnString)
Dim com As SqlCommand
Dim sqlda As SqlDataAdapter
Dim ds As DataSet
Dim str As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
bindgrid()
End Sub

Sub bindgrid()
con.Open()
str = "SELECT * FROM student"
com = New SqlCommand(str, con)
sqlda = New SqlDataAdapter(com)
ds = New DataSet()
sqlda.Fill(ds, "student")
GridView1.DataSource = ds
GridView1.DataMember = "student"
GridView1.DataBind()
con.Close()
End Sub

Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging
GridView1.PageIndex = e.NewPageIndex
bindgrid()
End Sub
End Class


If this post helps you mark it as answer
Thanks

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response