yasovardhan: Store And Retrive image to database in asp.net

At a times you might need to insert or read am image stored in a SQL Server in your project. So in this article we are going to learn how to insert and read an image from a database (SQL Server) in our applications.

To insert an image we first need to create a table in the SQL Server database; the following is the coding.

create table Emp
(
EmpId int identity(100,1),
EmpName varchar(20) not null,
EmpAdd varchar(20) not null,
imPhoto image
)

We have Emp table with EmpId as identity column and Name, Add and photo as the remaining columns.

Store image in database:

Design. Code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

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

<title>Save Retrieve Images</title>

</head>

<body>

<div>

<asp:Label ID="lblEmpName" runat="server" Text="Employee Name"></asp:Label>

<asp:TextBox ID="txtEName" runat="server"></asp:TextBox>

<br />

<asp:Label ID="lblImage" runat="server" Text="Employee Image"></asp:Label>

<asp:FileUpload ID="imgUpload" runat="server" />

<br />

<asp:Button ID="btnSubmit" runat="server" onclick="btnSubmit_Click"

Text="Submit" />

       &nbsp <asp:Label ID="lblResult" runat="server" ForeColor="#0066FF"></asp:Label>

<br />

</div>

</form>

</body>

</html>

For the button click event type the following code:

Aspx code:

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

using System.IO;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

SqlConnection con = new SqlConnection("Data Source=YESH-PC\\SQLEXPRESS;Initial Catalog=myfirstdatabase;Integrated Security=True");

protected void btnSubmit_Click(object sender, EventArgs e)

{

//SqlConnection connection = null;

try

{

FileUpload img = (FileUpload)imgUpload;

Byte[] imgByte = null;

if (img.HasFile && img.PostedFile != null)

{

//To create a PostedFile

HttpPostedFile File = imgUpload.PostedFile;

//Create byte Array with file len

imgByte = new Byte[File.ContentLength];

//force the control to load data in array

File.InputStream.Read(imgByte, 0, File.ContentLength);

}

// Insert the employee name and image into db

//string conn = ConfigurationManager.ConnectionStrings["EmployeeConnString"].ConnectionString;

//connection = new SqlConnection(conn);

con.Open();

string sql = "INSERT INTO EmpDetails(empname,empimg) VALUES(@enm, @eimg) SELECT @@IDENTITY";

SqlCommand cmd = new SqlCommand(sql, con);

cmd.Parameters.AddWithValue("@enm", txtEName.Text.Trim());

cmd.Parameters.AddWithValue("@eimg", imgByte);

int id = Convert.ToInt32(cmd.ExecuteScalar());

lblResult.Text = String.Format("Employee ID is {0}", id);

}

catch

{

lblResult.Text = "There was an error";

}

finally

{

con.Close();

}

You'll find that after running the application your image and other details are being inserted into the database.

Retrive image code:

Desgin code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

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

<title>Untitled Page</title>

</head>

<body>

<div>

<asp:Label ID="Label1" runat="server" Text="enter id"></asp:Label>

<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

<asp:Button ID="Button1" runat="server" Text="click" onclick="Button1_Click" />

<asp:image ID="Image1" runat="server"

Height="142px" Width="176px"/>

</div>

</form>

</body>

</html>

Aspx.code

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

public partial class Default2 : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

SqlConnection con = new SqlConnection("Data Source=YESH-PC\\SQLEXPRESS;Initial Catalog=myfirstdatabase;Integrated Security=True");

SqlCommand cmd=null;

protected void Button1_Click(object sender, EventArgs e)

{

//con = new SqlConnection(dbcon);

con.Open();

int a=Convert .ToInt32 (TextBox1 .Text );

cmd = new SqlCommand("Select * from EmpDetails where empid=@id", con);

cmd.Parameters.AddWithValue("@id",a );

SqlDataReader dr = cmd.ExecuteReader();

dr.Read();

if (dr.HasRows)

{

//Label1.Text = dr[1].ToString();

//Label2.Text = dr[2].ToString();|

Image1.ImageUrl = "~/Handler.ashx?id=" + a;

}

con.Close();

}

Now In order to retrieve the image from the database and display it in the image control we need to create a handler (ashx) file. Right-click your solution explorer -> add new item - >Select Generic Handler from it. Following is the code for the same.

Handler.ashx code:

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

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.IO;

using System.Data.SqlClient;

public class Handler : IHttpHandler

{

public void ProcessRequest(HttpContext context)

{

SqlConnection con = new SqlConnection("Data Source=YESH-PC\\SQLEXPRESS;Initial Catalog=myfirstdatabase;Integrated Security=True");

//SqlCommand cmd = null;

if (context.Request.QueryString["id"] != null)

{

// context.Response.Write(context.Request.QueryString["id"]);

//string dbcon = ConfigurationManager.ConnectionStrings["AdvWorks"].ConnectionString;

//SqlConnection con = new SqlConnection(dbcon);

con.Open();

SqlCommand cmd = new SqlCommand("Select empimg from EmpDetails where empid=@empid", con);

cmd.Parameters.AddWithValue("@empid", context.Request.QueryString["id"].ToString());

SqlDataReader dr = cmd.ExecuteReader();

dr.Read();

context.Response.BinaryWrite((byte[])dr["empimg"]);

dr.Close();

con.Close();

}

else

{

context.Response.Write("No Image Found");

}

public bool IsReusable {

get {

return false;

}

yasovardhan

Friday, 20 September 2013

Store And Retrive image to database in asp.net

No comments:

Post a Comment

About Me