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.
To insert data from front end to back end we need to create a procedure; the following is the code for it.
create procedure prcInsert
(
@name varchar(20),
@add varchar(30),
@photo image
)
as
begin
insert into Emp values(@name,@add,@photo)
end
This procedure will accept name, add and photo from the user and click on a button to add the data to the database.
Now we need to design a form for accepting data from the user; the following is the design:
<%@ 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">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table width="70%">
<tr>
<td style="width: 109px; height: 21px">
Enter Emp Name :</td>
<td style="width: 16px; height: 21px">
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 109px">
Enter Emp Add :</td>
<td style="width: 16px">
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 109px">
Select Photo :</td>
<td style="width: 16px">
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Label ID="StatusLabel" runat="server"></asp:Label></td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Save" /></td>
</tr>
</table>
</div>
</form>
</body>
</html>
For the button click event type the following code:
using System;
using System.Data;
using System.Configuration;
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.Data.SqlClient;
using System.IO;
public partial class _Default : System.Web.UI.Page
{
string dbcon = ConfigurationManager.ConnectionStrings["AdvWorks"].ConnectionString;
SqlConnection con;
SqlDataAdapter da;
DataSet ds;
SqlCommand cmd;
byte[] raw;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
if ((FileUpload1.FileName != ""))
{
//to allow only jpg gif and png files to be uploaded.
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
if (((extension == ".jpg") || ((extension == ".gif") || (extension == ".png"))))
{
FileStream fs = new FileStream(FileUpload1.PostedFile.FileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
raw = new byte[fs.Length];
fs.Read(raw, 0, Convert.ToInt32(fs.Length));
con = new SqlConnection(dbcon);
cmd = new SqlCommand("prcInsert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", TextBox1.Text);
cmd.Parameters.AddWithValue("@add", TextBox2.Text);
cmd.Parameters.AddWithValue("@photo", raw);
con.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
string script = "<script>alert('Data Added Successfully')</script>";
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "Added", script);
TextBox1.Text = "";
TextBox2.Text = "";
}
else
{
string script = "<script>alert('Error Adding Data')</script>";
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "Error", script);
}
}
else
{
StatusLabel.Text = "Only Jpg,gif or Png files are permitted";
}
}
else
{
StatusLabel.Text = "Kindly Select a File.....";
}
con.Close();
}
catch (Exception e1)
{
}
}
}
You'll find that after running the application your image and other details are being inserted into the database.
After clicking the save button you'll get a message box stating that your data is saved.
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.
<%@ 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)
{
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 imPhoto from Emp where Empid=@empid", con);
cmd.Parameters.AddWithValue("@empid", context.Request.QueryString["id"].ToString());
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
context.Response.BinaryWrite((byte[])dr["imPhoto"]);
dr.Close();
con.Close();
}
else
{
context.Response.Write("No Image Found");
}
}
public bool IsReusable {
get {
return false;
}
}
}
Design a new web form…
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ShowDetails.aspx.cs" Inherits="ShowDetails" %>
<!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>
<table>
<tr>
<td style="height: 74px">
Enter a Employee ID To be Searched
</td>
<td style="height: 74px">
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="EmpId" DataValueField="EmpId">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdvWorks %>"
SelectCommand="SELECT EmpId, EmpName, EmpAdd, imPhoto FROM Emp"></asp:SqlDataSource>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Search" />
</td>
</tr>
<tr>
<td>
Employee Image :
</td>
<td valign="top">
<asp:Image ID="Image1" runat="server" ImageUrl='<%# "~/Handler.ashx?ID=" + Eval("EmpID")%>' /></td>
</tr>
<tr>
<td>
Employee Name :
</td>
<td>
<asp:Label ID="Label1" runat="server"></asp:Label></td>
</tr>
<tr>
<td>
Employee Address :
</td>
<td>
<asp:Label ID="Label2" runat="server"></asp:Label></td>
</tr>
</table>
</div>
</form>
</body>
</html>
Following is the code behind for the same.
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.Data.SqlClient;
public partial class ShowDetails : System.Web.UI.Page
{
string dbcon = ConfigurationManager.ConnectionStrings["AdvWorks"].ConnectionString;
SqlConnection con;
SqlCommand cmd;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
con = new SqlConnection(dbcon);
con.Open();
cmd = new SqlCommand("Select * from Emp where Empid=@id", con);
cmd.Parameters.AddWithValue("@id", DropDownList1.Text);
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
if (dr.HasRows)
{
Label1.Text = dr[1].ToString();
Label2.Text = dr[2].ToString();|
Image1.ImageUrl = "~/Handler.ashx?id=" + DropDownList1.Text;
}
con.Close();
}
}
Now run the application and select an empid from it and click the search button.
Hope you might have liked the above application and it might will help you in your project. Kindly post your queries/reply.