Purvi's Blog: insert images into database and how to retrieve

how to insert images into database and how to retrieve and bind images to gridview using asp.net (or) save and retrieve images from database using asp.net

Introduction

Here I will explain how insert and retrieve images from database and how to bind images to gridview using asp.net.

Description:

I have worked on one social networking site at that time we save all the images in to directory folder and we save image path into database at that time I got idea to implement concept like inserting images into database and retrieving the images from database and binding images to gridview using asp.net for that we need follow below steps

First Design table like this in your SQL Server database and give name as Image

Column Name	Data Type	Allow Nulls
ImageId	Int(set identity property=true)	No
ImageName	Varchar(50)	Yes
Image	image	Yes

After that Design your aspx page like this

<title>Inserting images into databse and displaying images with gridview</title>

.Gridview

{

font-family:Verdana;

font-size:10pt;

font-weight:normal;

color:black;

width:500px;

}

</style>

</head>

<body>

<div>

<table>

<tr>

<td>

Image Name:

</td>

<td>

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

</td>

</tr>

<tr>

<td>

Upload Image:

</td>

<td>

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

</td>

</tr>

<tr>

<td>

</td>

<td>

<asp:Button ID="btnUpload" runat="server" Text="Upload" onclick="btnUpload_Click" />

</td>

</tr>

</table>

</div>

<div>

<asp:GridView ID="gvImages" CssClass="Gridview" runat="server" AutoGenerateColumns="False"

HeaderStyle-BackColor="#7779AF" HeaderStyle-ForeColor="white">

<asp:BoundField HeaderText = "Image Name" DataField="imagename" />

<asp:TemplateField HeaderText="Image">

<asp:Image ID="Image1" runat="server" ImageUrl='<%# "ImageHandler.ashx?ImID="+ Eval("ImageID") %>'Height="150px" Width="150px"/>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

After that add using System.IO; using System.Data.SqlClient; and using System.Configuration;namespaces and write the following code in code behind

string strcon = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGridData();

}

/// <summary>

/// btnUpload_Click event is used to upload images into database

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

protected void btnUpload_Click(object sender, EventArgs e)

{

//Condition to check if the file uploaded or not

if (fileuploadImage.HasFile)

{

//getting length of uploaded file

int length = fileuploadImage.PostedFile.ContentLength;

//create a byte array to store the binary image data

byte[] imgbyte = new byte[length];

//store the currently selected file in memeory

HttpPostedFile img = fileuploadImage.PostedFile;

//set the binary data

img.InputStream.Read(imgbyte, 0, length);

string imagename = txtImageName.Text;

//use the web.config to store the connection string

SqlConnection connection = new SqlConnection(strcon);

connection.Open();

SqlCommand cmd = new SqlCommand("INSERT INTO Image (ImageName,Image) VALUES (@imagename,@imagedata)", connection);

cmd.Parameters.Add("@imagename", SqlDbType.VarChar, 50).Value = imagename;

cmd.Parameters.Add("@imagedata", SqlDbType.Image).Value = imgbyte;

int count = cmd.ExecuteNonQuery();

connection.Close();

if (count == 1)

{

BindGridData();

txtImageName.Text = string.Empty;

ScriptManager.RegisterStartupScript(this, this.GetType(), "alertmessage", "javascript:alert('" + imagename + " image inserted successfully')", true);

}

/// <summary>

/// function is used to bind gridview

/// </summary>

private void BindGridData()

{

SqlConnection connection = new SqlConnection(strcon);

SqlCommand command = new SqlCommand("SELECT imagename,ImageID from [Image]", connection);

SqlDataAdapter daimages = new SqlDataAdapter(command);

DataTable dt = new DataTable();

daimages.Fill(dt);

gvImages.DataSource = dt;

gvImages.DataBind();

gvImages.Attributes.Add("bordercolor", "black");

}

Here we need to restrict user to upload only image formats in file upload control for that validaiton check this post how to validate file type in file upload control using javascript

After Completion of above code we need to add HTTPHandler file to our project to retrieve images from database because we save our images in binary format getting the binary format of data from database it’s easy but displaying is very difficult that’s why we will use HTTPHandler to solve this problem.

Here HTTPHandler is a simple class that allows you to process a request and return a response to the browser. Simply we can say that a Handler is responsible for fulfilling requests from the browser. It can handle only one request at a time, which in turn gives high performance.

Right Click on your project add new HTTPHandler.ashx file and give name as ImageHandler.ashx and write the following code in pagerequest method like this

string strcon = ConfigurationManager.AppSettings["ConnectionString"].ToString();

public void ProcessRequest(HttpContext context)

{

string imageid = context.Request.QueryString["ImID"];

SqlConnection connection = new SqlConnection(strcon);

connection.Open();

SqlCommand command = new SqlCommand("select Image from Image where ImageID=" + imageid, connection);

SqlDataReader dr = command.ExecuteReader();

dr.Read();

context.Response.BinaryWrite((Byte[])dr[0]);

connection.Close();

context.Response.End();

}

Here don’t forgot to set the connection string in web.config file here I am getting database connection from web.config file for that reason you need to set the connectionstring in web.config file like this