How to Save Image to SQLSERVER Database in ASP.NET ?


My Website: http://www.myacademicproject.com/
Example 1:

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

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 containsSqlDataSource 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.

Example 2:

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.

using 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 toHttpPostedFile 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, Image_Type 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.

Advertisements

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