Working With Images In ADO.NET and ASP.NET

In this blog, I will show you how to insert a row that contains an image into database. Then i will show you how to display rows along with image that is stored in SQL Server database.

Creation Of Table
Program to load image into database
Program to take image from database an display it
Program to display details of all rows along with images

Creation Of Table

In order to show how to load image into database and then retreive and display image, we use the following table in SQL Server.

create table students
( 
  id    int identify (1,1) not null,
  name  varchar(20),
  email varchar(30) unique,
  photo image
);

ID column is an autoincrement column. With every insert the value of this column will be incremented by one.

PHOTO colum is used to store an image.

Program to load image into database - LOADSTUDENT.ASPX

The following ASP.NET page is used to take information from user and inserts a row into STUDENTS table. The information contains name, email address and file that contains the photo of the student. The file containing photo of the student is uploaded using FILE control of HTML.

<%@ Page Language="c#" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

public void insertrow(object sender, EventArgs e)
{
	// Create an array of bytes from the input file
	int len = upload.PostedFile.ContentLength;

	byte[] pic = new byte[len + 1];

	Upload.PostedFile.InputStream.Read(pic, 0, len);

	// Insert the image and comment into the database
	SQLConnection con = new SQLConnection("database=pubs;uid=sa");
	con.Open();
	SQLCommand cmd = new SQLCommand("insert into students values (@name,@email,@photo)", con);

	cmd.Parameters.Add("@name", txtname.text);
	cmd.Parameters.Add("@email", txtemail.text);
	cmd.Parameters.Add("@photo", pic);

	cmd.ExecuteNonQuery();

	con.Close();

	lblmsg.text = "Students Details Inserted Successfully!";
}

</script>
<html>
<head>
<form enctype="multipart/form-data" runat="server">
<body>
  <h2>Student's Details </h2>

    <table>
        <tbody>
        <tr>
                <td>
                    Student's Name</td>
                <td>
                    <asp:TextBox id="txtname" RunAt="server"></asp:TextBox>
                </td>
            </tr>

            <tr>
                <td>
                    Email Address</td>
                <td>
                    <asp:TextBox id="txtemail" RunAt="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Image File Name</td>
                <td>
                    <input id="Upload" type="file" runat="server" /></td>
            </tr>
            </table>
            <p>

            <asp:Button id="Button1" onclick="insertrow" RunAt="server" Text="Insert"></asp:Button>
            <p>
            <asp:label id="lblmsg" runat=server />
 </form>
</body>
</html>

Run the above programs. Enter name,email of the student. Then select the gif file that contains the photo of the student and click on Insert button.

The following are the important steps in the above program:

Using CONTENTLENGTH property of POSTEDFILE we find out the length of the file that is posted.
We create an array of bytes of the required length
Using READ method of INPUTSTREAM property of the posted file we read content of the file into byte array.
We copy the byte array into @photo parameter so that it is copied to PHOTO column of STUDENTS table.

Program to display an image - DISPLAYSIMAGE.ASPX

The following program is used to take ID of a student as parameter and display the photo of the student. It generates the page that contains only one GIF image.

It reads the content of PHOTO column of the row where ID of the column is equivalent to ID parameter passed to it.

<%@ Page Language="c#" %>
<%@ import Namespace="system.data.sqlclient,system.drawing,system.drawing.imaging,system.io" %>
<script runat="server">

public void page_load()
{
	SQlConnection con = new SQlConnection("uid=sa;database=pubs");
	con.open();
	SQLCommand cmd = new SQLCommand("select photo from students where id = '" + request.querystring("id") + "'", con);

	MemoryStream stream = new MemoryStream();
	byte[] image = cmd.ExecuteScalar();
	stream.Write(image, 0, image.Length);
	Bitmap bitmap = new Bitmap(stream);
	Response.ContentType = "image/gif";
	bitmap.Save(Response.OutputStream, ImageFormat.Gif);
	con.Close();
	stream.Close();
}
</script>

In the above program,we read image from database and then write the content of the image into an object of MemoryStream .

Then an object Bitmap is created from the Stream that contains the image.

Then content of Bitmap object is written to OutputStream of the Response object. ContentType property of Response object must be set to image/gif to specify that output is not normal HTML and instead an image.

Program to display details of Students - DISPLAYSTUDENTS.ASPX

The following program displays the details of all students. Each students contains id, name , email and photo.

We use DISPLAYIMAGE.ASPX to render photo of the student. Output of displayimage.aspx can be used with SRC attribute of <IMG> tag.

<%@ Page Language="C#" %>
<%@ import namespace="system.data.sqlclient,system.data"%>
<script runat="server">

public void page_load()
{
	SQLconnection con = new SQLconnection("database=pubs;uid=sa");
	con.open();
	sqlcommand cmd = new sqlcommand("select id,name,email from students", con);
	sqldatareader dr = default(sqldatareader);

	dr = cmd.executereader();

	repeater1.datasource = dr;
	repeater1.databind();
	con.close();
}
</script>
<html>
<head>
</head>
<body>

    <form runat="server">
     <asp:repeater runat=server id=repeater1>
       <headertemplate>
          <h2>Details of Students </h2>
       </headertemplate>
       <itemtemplate>
         Id : <%# Container.DataItem("id") %>
         <br>
         Name   : <%# Container.DataItem("name") %>
         <br>
         Email  : <%# Container.DataItem("email") %>
         <br>
        <img src=displayimage.aspx?id=<%# Container.DataItem("id")%>> 
         <hr>
       </itemtemplate>
     </asp:repeater>
    </form>
</body>
</html>

In the above program we use a repeater to display information of students.

Id, name and email address are displayed normally. But to display photo, we have to use DISPLAYIMAGE.ASPX file. It takes student's id as parameter and returns the photo of the student in the form of a GIF. The output of the DISPLAYIMAGE.ASPX is used as source for IMG tag.

The above three programs demonstrate how to use images with ASP.NET using SQL Server database.

I hope these programs will help you to understand how to use images in ASP.NET.