Storing Images into database and retrieve to show in Web Application

Tweet

This post explains how to store an image into the database and retrieve to show in aspx page.

Here I have used Sql Server 2008 server with Northwind database. (I also will be updating the post for Oracle, MySql database)

At brief idea of what required to be done to store the image into database is:

1. Declare an image data type field in database table.
   
   
2. While storing the values into database add the following code for storing image
   

   // get the image stream into array of byte type
   byte[] image = new byte[fileName.PostedFile.ContentLength];
   
   fileName.PostedFile.InputStream.Read(image, 0, (int)fileName.PostedFile.ContentLength);
   

   And assign the same into parameter like following
   

   SqlParameter parameter = new SqlParameter(parameterName, SqlDbType.Image, image.Length);
   parameter.Value = image;
   
   command.Parameters.Add(parameter);
   

   And call the execute method of command to store into database.
   
3. To retrieve the image from the database, you are required to create a GenericHandler or a separate aspx file which returns the image as response.
   So where ever you want to show the image, all you need to do is to call the ashx or aspx file with an url by passing required parameter to find the image into the database instead of directly mentioning the image path. So ashx/aspx file act an an handler to find the image into database and assign the image stream to the calling place.
   
   For example, If you are using Generic Handler, the ProcessRequest event could be as follows:
   

   using (SqlConnection connection = new 
   SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
   {
       SqlCommand command = new SqlCommand(
           "select ProductImage from Products where ProductID = '" + 
            context.Request.QueryString["ProductID"] + "'", connection);
   
       connection.Open();
       SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
       while (dr.Read())
       {
           if (dr["ProductImage"].ToString().Length > 0)
           {
               context.Response.BinaryWrite((byte[])dr["ProductImage"]);
           }
       }
   }
   

   If you are using a separate aspx file, to Page_Load even as follows:
   

   using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
   {
       SqlCommand command = new SqlCommand(
           "select ProductImage from Products where ProductID = '" 
            Request.QueryString["ProductID"] + "'", connection);
   
       connection.Open();
       SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
       while (dr.Read())
       {
           if (dr["ProductImage"].ToString().Length > 0)
           {
               Response.BinaryWrite((byte[])dr["ProductImage"]);
           }
       }
   }
   

   As you seen here, the image as retrieved to the calling place as a response from this aspx or ashx. So we don’t have to write any other code as response other then image.
   
   Also I am passing ProductID as query string to get aparticular product image.
   
   
4. To call the aspx page from the C# Code,
   

   imgProduct.Src = 
   string.Format("GetImagePage.aspx?ProductID={0}", ProductID.ToString());
   

   or
   

   imgProduct.Src = 
   string.Format("GetImageHandler.ashx?ProductID={0}", ProductID.ToString());
   

   Here GetImagePage.aspx, GetImageHandler.ashx are the file name I used for placing the code.
   
   When the actual page gets rendered, the runtime will call the aspx or ashx file to get the image. To call from the html script itself, call like
   

   <img src="GetImagePage.aspx?ProductID=5" />
   

   or
   

   <img src="GetImageHandler.ashx?productid=5" />
   

   
   (Here I hardcoded product Id for understanding purpose only)
   

You can also look at another post, which concentrate on a fully functional example for understanding how to store and retrieve image in database.