In this tutorial I will explain how to save Image in Sql Server Database in Binary format and then displaying the image inside the Grid View control. In asp.net there are two options either you can store images inside the folder and storing its path in the database or second way is store image as binary image inside the database.
In this example I am storing sample student records inside the database along with image in binary format and then bind those records in a Grid View as shown in the diagram below:
Steps to Store and Bind Binary Image in ASP.NET using SQL Server Database:
Step 1: First create table and stored procedure in database using the script given below:
Run the Table Script Given Below:
Add the Following Code in the Code Behind File:
Code in C#:
Step 4: Run the application and then insert some record in the database and see the final result.
If you get any problem while saving the image into database as binary then mention your problem in the comments.
In this example I am storing sample student records inside the database along with image in binary format and then bind those records in a Grid View as shown in the diagram below:
Steps to Store and Bind Binary Image in ASP.NET using SQL Server Database:
Step 1: First create table and stored procedure in database using the script given below:
Run the Table Script Given Below:
CREATE TABLE [dbo].[STUDENT_RECORD](
[STU_ID] [int] IDENTITY(1,1) NOT NULL,
[STU_NAME] [varchar](100) NULL,
[CLASS] [varchar](50) NULL,
[EMAIL] [varchar](50) NULL,
[NAME] [varchar](50) NULL,
[ContentType] [varchar](50) NULL,
[Image_File] [varbinary](max) NULL,
CONSTRAINT[PK_STUDENT_RECORD] PRIMARY KEY CLUSTERED
(
[STU_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]
GO
SET ANSI_PADDING OFF
GO
Stored Procedure Script:
CREATE PROCEDURE [dbo].[Insert_Record]
(
@STU_NAME varchar(100),
@CLASS varchar(50),
@EMAIL varchar(50),
@Name varchar(50),
@ContentType varchar(50),
@Image_File varbinary(max)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNTON;
-- Insert statements for procedure here
Insert intoSTUDENT_RECORD (STU_NAME,CLASS,EMAIL,Name,ContentType,Image_File) values(@STU_NAME,@CLASS,@EMAIL,@Name,@ContentType,@Image_File)
END
Step 2: In the Front End Add a New Web Form and add the following code:
Design View Code:
<form id="form1" runat="server">
<div>
<table border="0" cellpadding="0"width="100%"cellspacing="0">
<tr><td width="10%"></td><td width="10%"></td><td></td></tr>
<tr><td width="10%"></td><td width="10%">Enter Name</td><td>
<asp:TextBox ID="txtName"runat="server"></asp:TextBox></td></tr>
<tr><td width="10%"></td><td width="10%">Enter Class</td><td><asp:TextBox ID="txtClass"runat="server"></asp:TextBox></td></tr>
<tr><td width="10%"></td><td >Email Id</td><td>
<asp:TextBox ID="txtEmail"
runat="server"></asp:TextBox></td></tr>
<tr><td width="10%"></td><td >Upload Image</td><td>
<asp:FileUpload ID="FileUpload1"runat="server"/></td></tr>
<tr><td width="10%"></td><td >
</td><td>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" />
<asp:HiddenField ID="hdid" runat="server"Value="0"/>
</td></tr>
<tr><td> </td></tr>
<tr><td colspan="3">
<asp:GridView ID="GridView1" runat="server" Width="70%"
AutoGenerateColumns="False"DataKeyNames="STU_ID"
AllowPaging="True"PageSize="4"
style="margin-top: 0px" onrowdeleting="GridView1_RowDeleting"
onpageindexchanging="GridView1_PageIndexChanging">
<Columns>
<asp:BoundField DataField="STU_NAME"HeaderText="STUDENT NAME"
SortExpression="STU_NAME"/>
<asp:BoundField DataField="CLASS"HeaderText="CLASS"SortExpression="CLASS"/>
<asp:BoundField DataField="EMAIL"HeaderText="Email Id" SortExpression="CLASS" />
<asp:ImageField DataImageUrlField = "STU_ID"
DataImageUrlFormatString ="ImageCSharp.aspx?ImageID={0}"
ControlStyle-Width = "100" ControlStyle-Height= "100"
HeaderText = "Preview Image" ItemStyle-HorizontalAlign="Center"/>
</Columns>
</asp:GridView></td></tr>
</table>
</div>
</form>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web.Script.Serialization;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.IO;
public partial class Test_Grid : System.Web.UI.Page
{
SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString());
static int image_height;
static int image_width;
static int max_height;
static int max_width;
static byte[] data;
static stringfilename;
static stringcontenttype;
protected voidPage_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGrid();
}
}
//Bind Data to Grid View
private voidBindGrid()
{
try
{
con.Open();
string qry = "Select *from STUDENT_RECORD";
SqlDataAdapter adp = newSqlDataAdapter(qry, con);
DataTable dt = newDataTable();
adp.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
con.Close();
}
catch (Exceptionex)
{
var message = new JavaScriptSerializer().Serialize(ex.Message);
var script = string.Format("alert({0});", message);
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", script, true);
}
finally
{
con.Close();
}
}
//On Submit button save data to database
protected voidbtnSubmit_Click(object sender, EventArgs e)
{
InsertData(txtName.Text.Trim(), txtClass.Text.Trim(),txtEmail.Text.Trim());
ClearControl();
}
//Function to insert record in database
private voidInsertData(string studentname, string stuclass, stringessay)
{
con.Open();
try
{
if (FileUpload1.HasFile)
{
// Read the file and convert it to Byte Array
string filePath = FileUpload1.PostedFile.FileName;
filename = Path.GetFileName(filePath);
string ext = Path.GetExtension(filename);
contenttype = String.Empty;
//Set the contenttype based on File Extension
switch (ext)
{
case ".doc":
contenttype = "application/vnd.ms-word";
break;
case ".docx":
contenttype = "application/vnd.ms-word";
break;
case ".xls":
contenttype = "application/vnd.ms-excel";
break;
case ".xlsx":
contenttype = "application/vnd.ms-excel";
break;
case ".jpg":
contenttype = "image/jpg";
break;
case ".png":
contenttype = "image/png";
break;
case ".gif":
contenttype = "image/gif";
break;
case ".pdf":
contenttype = "application/pdf";
break;
}
System.Drawing.Image image_file = System.Drawing.Image.FromStream(FileUpload1.PostedFile.InputStream);
if (contenttype != string.Empty)
{
image_height = image_file.Height;
image_width = image_file.Width;
max_height = 120;
max_width = 160;
image_height = (image_height * max_width) / image_width;
image_width = max_width;
if (image_height > max_height)
{
image_width = (image_width * max_height) / image_height;
image_height = max_height;
}
else
{
}
Bitmap bitmap_file = new Bitmap(image_file, image_width, image_height);
System.IO.MemoryStream stream = new System.IO.MemoryStream();
bitmap_file.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);
stream.Position = 0;
data = new byte[stream.Length + 1];
stream.Read(data, 0, data.Length);
}
}
else
{
var message = newJavaScriptSerializer().Serialize("Please Upload an image");
var script = string.Format("alert({0});", message);
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", script, true);
return;
}
//Stream fs = FileUpload2.PostedFile.InputStream;
//BinaryReader br = new BinaryReader(fs);
//Byte[] bytes = br.ReadBytes((Int32)fs.Length);
SqlCommand cmd = newSqlCommand("Insert_Record", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@STU_NAME", txtName.Text.Trim());
cmd.Parameters.AddWithValue("@CLASS", txtClass.Text.Trim());
cmd.Parameters.AddWithValue("@EMAIL ", txtEmail.Text.Trim());
cmd.Parameters.AddWithValue("@Name", filename);
cmd.Parameters.AddWithValue("@ContentType", contenttype);
cmd.Parameters.AddWithValue("@image_file", data);
cmd.ExecuteNonQuery();
con.Close();
BindGrid();
}
catch (Exceptionex)
{
}
}
}
Step 3: Add a New Web Form With the name ImageCSharp.aspx and add the given code in the code behind file:
protected void Page_Load(object sender, EventArgse)
{
if (Request.QueryString["ImageID"] != null)
{
string strQuery = "select Name, ContentType, image_file from" +
" STUDENT_RECORD where STU_ID=@STU_ID";
String strConnString = System.Configuration.ConfigurationManager
.ConnectionStrings["con"].ConnectionString;
SqlCommand cmd = newSqlCommand(strQuery);
cmd.Parameters.Add("@STU_ID", SqlDbType.Int).Value
= Convert.ToInt32(Request.QueryString["ImageID"]);
SqlConnection con = newSqlConnection(strConnString);
SqlDataAdapter sda = newSqlDataAdapter();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
DataTable dt = newDataTable();
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
}
catch
{
dt = null;
}
finally
{
con.Close();
sda.Dispose();
con.Dispose();
}
if (dt != null)
{
Byte[] bytes = (Byte[])dt.Rows[0]["image_file"];
Response.Buffer = true;
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = dt.Rows[0]["ContentType"].ToString();
Response.AddHeader("content-disposition", "attachment;filename="
+ dt.Rows[0]["Name"].ToString());
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}
}
}
0 comments:
Post a Comment