Reading and writing images from/to database


Hi,
This post will show you how to save/load images to/from a database. This approach lets you do it without having to save the image on to disk. On a real hosting computer, you probably will not have write permissions. Therefore, it is sometimes vital to do image saving/loading on the fly.

In this post we will work on a database named Personnel, in which there is a table called Personnel:

create table Personnel
(
id int identity(1,1) not null primary key,
Fillname varchar(100) not null,
Picture image null
)

We are going to store out personnel’s name and picture into this table. On the application side, create a .aspx page with the following four controls:
1-a TextBox
2-a RequiredFieldValidator
3-a FileUpload control
4-a Button
5-a DataList control

Your page will look like this:

As the above image indicates, we will save each person’s full name and picture to database and the personnel information will be viewd in a DetailList control.

Behind the AddRecord button, we read the image into a Byte[] object. Then transmit the bytes to database. I strongly recommend you to use a stored procedure (if you are working with ADO.NET) because you won’t face any problems with converting bytes to string.

In Asp.NET there is a class claeed HttpPostedFile. This class lets us get full information about the file that is about to upload.  The code bellow show how we read the file:

if (FileUpload1.HasFile)
{
HttpPostedFile myFile = FileUpload1.PostedFile;
int Length = myFile.ContentLength;
string ContentType = myFile.ContentType.ToUpper();

if (Length == 0)
throw new Exception(“File size must be greater than zero!”);
if (ContentType.CompareTo(“IMAGE/PJPEG”) != 0 && ContentType.CompareTo(“IMAGE/JPEG”) != 0)
throw new Exception(“Only JPEG files are welcome!”);

Byte[] myFileBytes = new byte[Length];
myFile.InputStream.Read(myFileBytes, 0, Length);

In the above code I just allow JPEG files to be uploaded. Actually, I wanted to show you how to control the image type and infact, you can upload any file type.

As the last line of this code shows, HttpPostedFile class contains an inner stream from which we can read the image bytes. After reading the image file, we simply save it into db. I have created a stored procedure for doing this and I call this procedure from my C# code:

ALTER PROCEDURE dbo.SavePersonnel
@FullName Varchar(100),
@Picture Image

AS
Insert into Personnel (FullName,Picture) Values (@FullName,@Picture )
Return

C#:

string cnStr = ConfigurationManager.ConnectionStrings[“ConnectionString”].ToString();
using (SqlConnection connection = new SqlConnection(cnStr))
{
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = “dbo.SavePersonnel”;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“FullName”, txtFullName.Text.Trim());
cmd.Parameters.AddWithValue(“Picture”, myFileBytes);
connection.Open();
cmd.ExecuteNonQuery();
}

Well, now how to read the image and show it using a System.Web.UI.WebControls.Image control. Unfortunately, despite System.Drawing.Image class, ASP.NET Image class does not provide a public stream. Therefore we can not create the image content withouth an image url.

Thanks to ASP.NET Generic Handlers, we can overcome this problem easily. We may develop a .aspx or .ashx (generic handler) file and use it to read the file from database and write the image bytes into the response output stream. A generic handler (.ashx) file is much more light weight than a .aspx file. Therefore, I will develop a .ashx file. We will pass the ID of each person to this .ashx file. The generic handler then finds the person at DB, reads the image and writes it to the current HttpContext instance :

Here is the full source code of the .ashx file:

<%@ WebHandler Language=”C#” Class=”GetPersonnelImage” %>

using System;
using System.Web;
using System.Data.SqlClient;

public class GetPersonnelImage : IHttpHandler {

public void ProcessRequest (HttpContext context) {
context.Response.ContentType = “image/jpeg”;
if (context.Request.QueryString[“id”] == null || context.Request.QueryString[“id”] == “”)
return;
else
{
string cnStr = System.Configuration.ConfigurationManager.ConnectionStrings[“ConnectionString”].ToString();
using (SqlConnection connection = new SqlConnection(cnStr))
{
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = “Select Picture from dbo.Personnel Where Id=” + context.Request.QueryString[“id”];
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
byte[] image = reader.GetValue(0) as byte[];
System.IO.MemoryStream ms = new System.IO.MemoryStream(image);
ms.WriteTo(context.Response.OutputStream);
ms.Flush();
context.Response.OutputStream.Flush();
}
}
}
}

public bool IsReusable {
get {
return true;
}
}
}

As is clear, we read the person’s record with a SqlDataReader and then get the image bytes using it’s GetValue method.

To let a Image control show the image we have to set it’s ImageUrl’s property in ths way:

Image img = new Image();

img.ImageUrl = “~/GetPersonnelImage.ashx?id=10″;

Inside a GridView or DataList, we can create a Template field with an Image control and bind the Image control’s ImageUrl property to the mentioned generic handler. For example:

<asp:Image ID=”Image1″ runat=”server” ImageUrl='<%#Eval(“id”,”GetPersonnelImage.ashx?id={0}”)%>’

You can download a full sample program from here. To run the project, you must have Sql Server 2005 Express edition on your machine. If you don’t have SQL Server 2005 Express, change the connection string existing in Web.config file to your own database.

Advertisements

11 thoughts on “Reading and writing images from/to database

  1. What really makes you shinning at the prom party night is to choose the right
    prom gown in a right color that flatters to your skin color and hair tone.
    Allow a fabulous expression by donning at long precious stone danglers with
    it for one chic look. All these made the short dresses more chic and charming.

  2. Any moisture in the frame or on the glass can damage your picture so you
    want to be certain it’s had time to dry before putting it back.
    Keep in mind that the size of frame or frames that you choose to use is what dictates the size of your finished recycled paper.
    Kids tend to accumulate a lot of “stuff,” so this can be a
    fun and creative little project for them.

  3. I almost never comment, but i did some searching
    and wound up here Reading and writing images from/to database | Aspguy’s Weblog.
    And I actually do have 2 questions for you if you tend not to mind.
    Is it just me or does it seem like a few of these responses look like coming from
    brain dead visitors? 😛 And, if you are posting at additional places,
    I’d like to keep up with everything new you have to post.
    Would you list of every one of your public pages like your Facebook page, twitter feed,
    or linkedin profile?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s