Save and retrieve image to and from database in asp.net
Introduction
In this article I am going to demostrate how we can store and retrieve image to and from database in asp.net.
To save image in database we must first convert the image in byte array in C# code and then save it to database table.
In database, column of the the table which will store the image byte array, must be of type binary/varbinary.
Lets create a table that will store the image
CREATE TABLE ProfileImage
(
ImgId INT IDENTITY(1,1),
ImgBody VARBINARY(MAX)
)
The above code will create a ProfileImage table which will have an auto increamented id, ImgId and ImgBody of type varbinary that will contain image byte array.
Now create a stored procedure to save the image in ProfileImage table in database.
CREATE PROC dbo.usp_SaveProfileImage
(
@ImgBody VARBINARY(MAX)
)
AS
BEGIN
INSERT INTO ProfileImage(ImgBody) SELECT @ImgBody
SELECT @@IDENTITY
END
Now create a stored procedure to retrieve image byte array from database.
CREATE PROC usp_GetProfileImage
(
@ImgId INT
)
AS
BEGIN
SELECT * FROM ProfileImage WHERE ImgId=@ImgId
END
At this point we have done with database part for storing and retreiving image to/from database.
Now lets design the user interface to browse the image and save it into database.
Html code in aspx page
<div>
<fieldset>
<legend>Browse & Save image in database </legend>
<div>
Browse Image :
<asp:FileUpload ID="FileUpload1" runat="server" />
</div>
<div>
<asp:Button ID="btnUploadImage" runat="server" Text="Upload" OnClick="btnUploadImage_Click" />
</div>
</fieldset>
<br />
<fieldset>
<legend>Retrieved image from database </legend>
<div>
<asp:Image ID="imgProfile" Visible="false" runat="server" />
</div>
</fieldset>
</div>
Methods in .cs file.
Write method to get uploaded image and convert it in byte array.
private byte[] ConvertImageToByteArray(FileUpload fuImage)
{
byte[] ImageByteArray;
try
{
MemoryStream ms = new MemoryStream(fuImage.FileBytes);
ImageByteArray = ms.ToArray();
return ImageByteArray;
}
catch (Exception ex)
{
return null;
}
}
Now create a method to save byte array in database
protected int SaveImageToDB(byte[] ImageByteArray)
{
SqlConnection conn = null;
try
{
string strConn = ConfigurationManager.ConnectionStrings["strConn"].ConnectionString.ToString();
conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.CommandText = "usp_SaveProfileImage";
cmd.Parameters.Add("@ImgBody", SqlDbType.Image, 0).Value = ImageByteArray;
conn.Open();
int ImageId = Convert.ToInt16(cmd.ExecuteScalar());
conn.Close();
if (ImageId == 0)
Response.Write("Error occured while saving image in database.");
return ImageId;
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
return 0;
}
finally
{
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}
}
To retrieve the image and show on web page, create a generic handler file and name it as ImageHandler.ashx. The response type of this generic handle should be "image/jpeg" that ensures that the response will be an image. And this generic page will become an image dynamically.
Write the code below in the generic handle page;
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "image/jpeg";
if (context.Request.QueryString["imgId"] != null)
{
int imgId = 0;
imgId = Convert.ToInt16(context.Request.QueryString["imgId"]);
MemoryStream memoryStream = new MemoryStream(GetImageFromDB(imgId), false);
System.Drawing.Image imgFromGB = System.Drawing.Image.FromStream(memoryStream);
imgFromGB.Save(context.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);
}
}
private byte[] GetImageFromDB(int ImgId)
{
SqlConnection conn = null;
byte[] ImageByteArray = null;
try
{
conn = new SqlConnection(
ConfigurationManager.ConnectionStrings["strConn"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.CommandText = "usp_GetProfileImage";
cmd.Parameters.Add("@ImgId", SqlDbType.Int, 0).Value = ImgId;
SqlDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
ImageByteArray = (byte[])rd["ImgBody"];
}
rd.Close();
conn.Close();
return ImageByteArray;
}
catch (Exception ex)
{
return null;
}
finally
{
if (conn != null)
conn.Close();
}
}
In the above GetImageFromDB(int ImgId) method, returns byte array fetched from database. And then using this byte array dynamically creates image and writes on page as response.
Now write below code in click event of btnUplaodImage to save browsed image in database and then retrieved it from database and show on web page.
protected void btnUploadImage_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
byte[] ImageByteArray = null;
ImageByteArray = ConvertImageToByteArray(FileUpload1);
int imageId = SaveImageToDB(ImageByteArray);
imgProfile.ImageUrl = "ImageHandler.ashx?imgid=" + imageId.ToString();
imgProfile.Visible = true;
}
}
OutPut
Stay tuned for more......... till then......Happy reading..........