Introduction:-In this article i will show you how to insert images in database and retrieve data from database in images.
Implementation:- In this article firstly i will convert the data in binary format and store them in database and for retrieve the information from database a take a generic handler it is mainly used for converting binary data.
Now below i am giving database script you can copy and run it in your sqlserver. The name of the table is picture i take three column.
1.Pid int autoincreament.
2.Pname Varchar(50)
3.Pimage Image
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Picture]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[images](
[Pid] [int] IDENTITY(1,1) NOT NULL,
[Pname] [varchar](50) NULL,
[Pimage] [image] NULL,
CONSTRAINT [PK_images] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
Now i am giving source code for Default.aspx Page.Copy and Paste it in your Default.aspx page.
<%@ 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>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:FileUpload ID="FileUpload1" runat="server" /><br />
<br />
<asp:Button ID="Upload" runat="server" OnClick="Upload_Click"
Text="Upload" Height="26px" /><br />
<br />
<asp:Label ID="lblMessage" runat="server"></asp:Label><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" DataKeyNames="ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="PID" HeaderText="ID"
InsertVisible="False" ReadOnly="True"
SortExpression="PID" />
<asp:BoundField DataField="PName" HeaderText="ImageName"
SortExpression="ImageName" />
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:Image ID="Image1" runat="server" Height="100px"
Width="100px" ImageUrl='<%# "Handler.ashx?ID=" + Eval("PID")%>'/>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [PID], [PName], [PImage]
FROM [Picture]"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
Below i am giving Default.aspx.cs 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.IO;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlConnection cn = new SqlConnection();
protected void Page_Load(object sender, EventArgs e)
{
cn.ConnectionString = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
}
protected void Upload_Click(object sender, EventArgs e)
{
string strImageName = TextBox1.Text.ToString();
if (FileUpload1.PostedFile != null && FileUpload1.PostedFile.FileName != "")
{
byte[] imgSize = new byte[FileUpload1.PostedFile.ContentLength];
HttpPostedFile uploadedImage = FileUpload1.PostedFile;
uploadedImage.InputStream.Read(imgSize, 0, (int)FileUpload1.PostedFile.ContentLength);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "INSERT INTO picture(pName,pImage) VALUES (@pName,@pImage)";
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@pname", strImageName);
cmd.Parameters.AddWithValue("@pimage", imageSize);
con.Open();
cmd.ExecuteNonQuery();
GridView1.DataBind();
}
}
}
Now i am giving handler.ashx page:---
<%@ WebHandler Language="C#" Class="Handler" %>
using System;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
public class Handler : IHttpHandler
{
public void ProcessRequest (HttpContext context)
{
SqlConnection con = new SqlConnection();
cn.ConnectionString = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select pName,pImage from picture where pID =@pID";
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = cn;
cmd.Parameters.AddWithValue("@pID", context.Request.QueryString["pID"]);
cn.Open();
SqlDataReader dReader = cmd.ExecuteReader();
dReader.Read();
context.Response.BinaryWrite((byte[])dReader["pImage"]);
dReader.Close();
cn.Close();
}
public bool IsReusable
{
get
{
return false;
}
}
}
