Working with Image, Sql Server and ASP.NET 2.0

In this article, I discuss a simple application to store and retrieve images in Sql Server Database using Asp.NET 2.0.

The following are what we use in this project:

Sql Server 2005 Express Edition
FileUpload control, Image control, GridView control with SqlDataSource control

Follow the steps given below:

First create a new website either with Visual Studio.Net 2005 or Visual Web Develoer Express Edition 2005 using File -> New Website. Give any name you like to this new project.

Connect to MSDB database in Sql Server 2005 Express edition using Database Explorer/Server Explorer. You can use any other database or standard/enterprise edition. In this case change connection string accordingly.

Create a table called PERSONS with the following structure.

create table Persons
( name  varchar(30) primary key,
  photo  image
)

Add a new ASP.NET page with name AddPerson.aspx and select the language as C#.

The following is the source code of Addperson.aspx.

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.IO "%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

   protected void Button1_Click(object sender, EventArgs e)
   {
    Stream imagestream;
   
    int len = FileUpload1.PostedFile.ContentLength; // get length of the file
    imagestream = FileUpload1.PostedFile.InputStream; // get stream for the image
    Byte [] imagecontent = new Byte[len];  // create an array of bytes to hold image data
    imagestream.Read(imagecontent, 0, len); // read image into array

    SqlConnection con = new SqlConnection("Data Source=localhost\\sqlexpress;Initial Catalog=msdb;Integrated Security=True");
    con.Open();
    SqlCommand cmd = new SqlCommand("insert into persons values (@name,@photo)",con);
    cmd.Parameters.Add("@name", SqlDbType.VarChar, 20).Value = TextBox1.Text;
    cmd.Parameters.Add("@photo", SqlDbType.Image).Value = imagecontent;
    cmd.ExecuteNonQuery(); // insert
    con.Close();
    Response.Write("Person Added Successfully");
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Add Person</title>
</head>
<body>
<h2>Add Person</h2>
    <form id="form1" runat="server">
    <div>
    <table>
        <tr>
            <td>Person Name 
            </td>
            <td>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>Person's Photo
            </td>
            <td>
                <asp:FileUpload ID="FileUpload1" runat="server" />
            </td>
        </tr>
    </table>
    <p />
    <asp:Button ID="Button1" runat="server" Text="Add Person" OnClick="Button1_Click" />
        <p />
        <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="persons.aspx">Show Persons</asp:HyperLink>
        </div>
    </form>
</body>
</html>

This allows user to enter person name and upload jpg/gif file that contains photo of the person. When you click on Add Person button, name and photo are inserted into PERSONS table in Sql Server database.

When your click on link Show Persons then it will display name and photo of each person stored in PERSONS table using persons.aspx. So add persons.aspx file to your project. This page contains SqlDataSource and GridView controls.

Persons.aspx uses SQLDataSource control to retrieve data from PERSONS table. The data is displayed using GridView with a simple bound column and a template column. Template column contains itemtemplate, which contains Image web control of ASP.NET.

The following is the code for persons.aspx.

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
    String GetUrl(Object name)
    {
        return "getphoto.aspx?name=" + name.ToString();
    }

    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <h2>List Of Persons </h2>
        &nbsp;<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="name"
            DataSourceID="SqlDataSource1" CellPadding="5" CellSpacing="1" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
            <Columns>
                <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" />
                <asp:TemplateField HeaderText="Photo">
                  <ItemTemplate>
                    <asp:Image ImageUrl='<%# GetUrl(Eval("name"))%>'   runat="server"  Width="100" Height="100"/>
                  </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <HeaderStyle BackColor="#FF8080" />
        </asp:GridView>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:msdbConnectionString %>"
            SelectCommand="SELECT [name], [photo] FROM [persons]"></asp:SqlDataSource>
    </form>
</body>
</html>

GridView displays image taken from getphoto.aspx file in each image control of the gird row. GetPhoto.aspx is used to write image using BinaryWrite method of the Response object. Name of the person is passed as parameter to getphoto.aspx.

The following is the code for getphoto.aspx file.

<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
      
    protected void Page_Load(object sender, EventArgs e)
    {
        String name = Request.QueryString["name"];

        SqlConnection myConnection = new SqlConnection("Data Source=localhost\\sqlexpress;Initial Catalog=msdb;Integrated Security=True");
        myConnection.Open();
        SqlCommand myCommand = new SqlCommand("select photo from persons where name ='" + name + "'", myConnection);
        SqlDataReader myDataReader;
        myDataReader = myCommand.ExecuteReader();
        myDataReader.Read(); // goto first row
        Response.BinaryWrite( (byte[]) myDataReader["photo"]);
        myConnection.Close();
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
    </form>
</body>
</html>

Try running this application. Add a few persons using AddPerson.aspx. Then click on link to show persons. You must see person name and photo.

Keep Learning,
Srikanth.