|
|
|
|
|
Inserting Images into Database and Display it in GridView through Handler.ashx |
|
Posted by
Moderator1
on
7/5/2007 11:31:47 AM
|
Category:
ADO.NET |
|
|
Total Views :
31907 |
|
Adding to your Favorites....
|
|
|
 |
|
|
|
|
|
Introduction |
|
This article explicate the method of inserting images and pictures into SQL Server
database table and display it in an Asp.Net GridView control with the help of Handler.aspx. |
|
|
|
Description |
Have you seen any web application or website without images? No, you cannot. Images
played a major role in web application development. Either it’s a static html website
or an advanced RAD application, everything is build along with images. If your application
is an E-Commerce based or Image Gallery portal, definitely you have to suffer lot
on saving the images in different location with different sizes and types. And it’s
not an easiest job to manage those unwanted and outdated images to be removed from
your file server, then making backup of those images from one server location to
another location. So it is clearly time consuming and hectic.
|
|
|
To make your task easier, this article explains you the methods of storing the images
into data source. There are many advantages of saving the images into database.
The main advantage is easy management of images. You can control the number and
size of images stored in your server. You can remove all unnecessary images from
the database in a single sql query and you can backup the image data easily. On
the other hand, you should be generous of keeping sufficient memory store in your
database server. |
|
|
|
Inserting Image into Database
To start with, let me explain the SQL Server database table structure we are going
to use to insert the image. The table you are going to create to store the image
must contain a column of data type IMAGE. This image data
type is a Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647)
bytes. To store the image into this column we are going to convert it into binary
string with the help of some IO classes and then insert into the table. For demonstration,
we are going to create a table named ImageGallery with four columns in the following
structure
|
Column Name |
Description |
Data Type |
|
Img_Id |
Identity column for Image Id |
int |
|
Image_Content |
Store the Image in Binary Format |
image |
|
Image_Type |
Store the Image format (i.e. jpeg, gif, png, etc.) |
varchar |
|
Image_Size |
Store the Image File Size |
bigint |
After we create table in the database, we can start the coding part.
1. Open your web application in Visual Studio 2005, drag and drop File Upload control
and a Button control into the web page.
2. In the code-behind, add the namespace System.IO.
3. In the Button’s Button1_Click event, write the following code
|
if (FileUpload1.PostedFile != null
&& FileUpload1.PostedFile.FileName != "")
{
byte[] myimage = new byte[FileUpload1.PostedFile.ContentLength];
HttpPostedFile Image = FileUpload1.PostedFile;
Image.InputStream.Read(myimage, 0, (int)FileUpload1.PostedFile.ContentLength);
SqlConnection myConnection = new SqlConnection(“Your Connection String”);
SqlCommand storeimage = new SqlCommand("INSERT INTO ImageGallery "
+"(Image_Content, Image_Type, Image_Size) "
+" values (@image, @imagetype, @imagesize)", myConnection);
storeimage.Parameters.Add("@image", SqlDbType.Image, myimage.Length).Value = myimage;
storeimage.Parameters.Add("@imagetype", SqlDbType.VarChar, 100).Value
= FileUpload1.PostedFile.ContentType;
storeimage.Parameters.Add("@imagesize", SqlDbType.BigInt, 99999).Value
= FileUpload1.PostedFile.ContentLength;
myConnection.Open();
storeimage.ExecuteNonQuery();
myConnection.Close();
}
|
To upload the image from any location (your local drive) to the server, we have
to use HttpPostedFile object. Point the uploaded file to HttpPostedFile
object. Then the InputStream.Read method will read the
content of the image by a sequence of bytes from the current stream and advances
the position within the stream by the number of bytes it read. So myimage contains the image as binary data. Now we have
to pass this data into the SqlCommand object, which will insert it into the database
table.
|
Display the Image in a GridView with Handler.ashx
So far, the article explains the way to insert images into the database. The Image
is in the database in binary data format. Retrieving this data in an ASP.NET web
page is fairly easy, but displaying it is not as simple. The basic problem is that
in order to show an image in an apsx page, you need to add an image tag that links
to a separate image file through the src attribute or you need to put an Image control
in your page and specify the ImageUrl.
For example:
|
|
<asp:Image ID="Image1" runat="server" ImageUrl="YourImageFilePath" /> |
|
|
|
Unfortunately, this approach will not work if you need to show image data dynamically.
Although you can set the ImageUrl attribute in code, you have no way to set the
image content programmatically. You could first save the data to an image file on
the web server’s hard drive, but that approach would be dramatically slower, wastes
space, and raises the possibility of concurrency errors if multiple requests are
being served at the same time and they are all trying to write the same file.
In these situations, the solution is to use a separate ASP.NET resource that returns
the binary data directly from the database. Here HTTP Handler class comes to center
stage.
|
|
|
|
What is Handler?
An ASP.NET HTTP Handler 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. A handler class
implements the IHttpHandler interface.
For this article demonstration, we are going to display the image in the GridView
control along with the data we stored in the table. Here are the steps required
to accomplish this:
1. Create a Handler.ashx file to perform image retrieval.
This Handler.ashx page will contain only one method called ProcessRequest. This
method will return binary data to the incoming request. In this method, we do normal
data retrieval process and return only the Image_Content field as bytes of array.
The sample code follows |
public void ProcessRequest (HttpContext context)
{
SqlConnection myConnection = new SqlConnection(“YourConnectionString”);
myConnection.Open();
string sql = "Select Image_Content from ImageGallery where Img_Id=@ImageId";
SqlCommand cmd = new SqlCommand(sql, myConnection);
cmd.Parameters.Add("@ImageId", SqlDbType.Int).Value = context.Request.QueryString["id"];
cmd.Prepare();
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
context.Response.ContentType = dr["Image_Type"].ToString();
context.Response.BinaryWrite((byte[])dr["Image_Content"]);
dr.Close();
myConnection.Close();
}
|
|
2. Place a GridView control in your aspx page, with one TemplateField
column, add an Image control into the TemplateField's ItemTemplate section. Specify the ImageUrl property as
|
|
<asp:TemplateField>
<ItemTemplate>
<asp:Image ID="Image1" runat="server" ImageUrl='<%#
"Handler.ashx?id=" + Eval("Img_Id") %>' />
</ItemTemplate>
</asp:TemplateField> |
|
3. Now we can bind the GridView control to display all the records in the table
as follows
|
GridView1.DataSource = FetchAllImagesInfo();
GridView1.DataBind(); |
|
Before you bind the GridView, you should write the FetchAllImagesInfo method to
return all the records with their image data from the table and then you have to
load the images into the GridView control. The code for FetchAllImagesInfo is
public DataTable FetchAllImagesInfo())
{
string sql = "Select * from ImageGallery";
SqlDataAdapter da = new SqlDataAdapter(sql, "Your Connection String");
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
} |
That's it. When you
run your project,
you can see the images got loaded into the GridView control.
This is a very simple explanation to store images into the data source and to retrieve
it back to display in the webpage. You can make the logic even simpler and even
elaborate it upto your requirements.
To view our sample application, click here. |
|
|
|
|
|
|
|
| | | | |