Save Image to Sql Server in ASP.net and Retrieve it Using Generic Handler

1. Create a Website Project in visual studio

Add File Upload control.

File Upload control doesn't have an event associated with it to post a file to server, so add button a to the page

2. Create a database in SQLSEVER to store your image for example lets create a table named 'employee' where we store Employee's image with other relevant information.

Employee table contains the following fields:

Employeeid Int,

Employeename Varchar(50),

EmpImg Image. (look here the datatype is image/blob).

3.Add Generic Handler

Generic Handler file is an asp.net file with ashx extension similar to a normal web file with aspx extension. Normal aspx pages is used to handle "light duties" .But the ashx files can handle ‘Heavy duty’ like dynamically loading pictures from database .

Just as aspx files can be compiled on the fly (just-in-time), so can be handlers. Generic handlers have an extension of ashx. They're equivalent to custom handlers written in C Sharp or Visual Basic.NET in that they contain classes that fully implement IHttpHandler. They're convenient in the same way as of aspx files. You simply surf to them and they're compiled automatically.

The following example illustrates the implementation of a "generic handler". Here the name given is GetImage.

Add a "generic" handler to the Web site. Go to the Solution Explorer, right-click on the CustomHandler Web site node and select Add New Item. Select Generic Handler from the templates. Name the handler GetImage.ashx:

Generic Handler Code GetImage.ashx is given below.

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

using System;

using System.Web;

using System.Data.SqlClient;

using System.IO;

public class GetImage : IHttpHandler {

public void ProcessRequest (HttpContext context) {

try

{

string empno;

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

empno = context.Request.QueryString["id"].ToString();//Get Employee Id from Query String

else

throw new ArgumentException("No parameter specified");

context.Response.ContentType = "image/jpeg";

Stream strm = GetImageData(empno);

byte[] buffer = new byte[4096];

int byteSeq = strm.Read(buffer, 0, 4096);//Read Image data from Db to byte array

while (byteSeq > 0)

{

context.Response.OutputStream.Write(buffer, 0, byteSeq);

byteSeq = strm.Read(buffer, 0, 4096);

}

}

catch (Exception ex)

{

}

}

public Stream GetImageData(string employeeid)

{

SqlConnection connection = new SqlConnection("Data Source=.;Database=Images;Trusted_Connection=True");//ReplaceitWith Your Conncetion String

connection.Open();

string sql = "SELECT img FROM EmployeePhoto WHERE employeeid = @ID";

SqlCommand cmd = new SqlCommand(sql, connection);

cmd.Parameters.AddWithValue("@ID", employeeid);

object img = cmd.ExecuteScalar();

try

{

return new MemoryStream((byte[])img);

}

catch

{

return null;

}

finally

{

connection.Close();

}

}

public bool IsReusable {

get {

return false;

}

}

}

Add Employees Photo Page(Default.aspx here)

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;

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

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

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);

}

// Using File Stream to read data from file

//SqlConnection connection=new SqlConnection("server=.;uid=sa;pwd=sa;Database=Images");//SQL SErver Authentication

SqlConnection connection = new SqlConnection("Data Source=C.;Database=Images;Trusted_Connection=True");//Windows Authentication

connection.Open();

SqlCommand addImage = new SqlCommand("insert into EmployeePhoto values(@employeeid,@employeename,@img)", connection);

addImage.Parameters.Add("@employeeid", SqlDbType.Int).Value = TextBox1.Text;

addImage.Parameters.Add("@employeename", SqlDbType.VarChar).Value = TextBox2.Text;

addImage.Parameters.Add("@img", SqlDbType.Image).Value=imgByte;//

addImage.ExecuteNonQuery();

connection.Close();

}

catch(Exception ex)

{

Response.Write(ex.Message);

}

}

public void ClearPage()

{

foreach (Control c in form1.Controls)

{

if (c.GetType().ToString() == "System.Web.UI.WebControls.TextBox")

{

((TextBox)c).Text = String.Empty;

}

}

}

}

ViewEmployees Page

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 ViewEmployees : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

SqlConnection connection = new SqlConnection("Data Source=.; Database=Images;Trusted_Connection=True");

connection.Open();

DropDownList1.Items.Clear();

DropDownList1.Items.Add("---------Select--------");

SqlCommand getemplyeeids = new SqlCommand("select employeeid,

employeename from EmployeePhoto", connection);

SqlDataReader Read_Name;

Read_Name = getemplyeeids.ExecuteReader();

while (Read_Name.Read())

{

ListItem LI = new ListItem();

LI.Text = Read_Name.GetValue(1).ToString();

LI.Value = Read_Name.GetValue(0).ToString();

DropDownList1.Items.Add(LI);

}

Read_Name.Close();

connection.Close();

}

}

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

{

Image1.ImageUrl = "~/GetImage.ashx?id=" + DropDownList1.SelectedItem.Value;//Invokes Generic Handler Implemented in GetImage

}

}

If all have done your coding, run the Project and add some employees,View Employee image by selecting Name from Dropdownlist

Enjoy!!!

Download Sample Project