Show an image saved in a SQL Table on an ASP.Net Image Control

In a previous entry I have shown, how to store images to a database table. Now I will show you how to retrieve it and show it on a ASP.Net Image control. There is no straight forward method showing it like “ImageControl.Image = ImageStream….” How ever it can be achieved using a Generic Handler.

Add a ‘Generic Handler’ to your ASP.Net web application. And in this example I will name it as ‘getImageFromDB.ashx’. By default IHttpHandler will be implemented. (ProcessRequest and IsReusable methods will be implemented). And I will add another method called ‘GetImage’ and alter the ‘ProcessRequest’ method. And the finished handler should similar to this:

   1: using System;

   2: using System.Drawing;

   3: using System.Drawing.Imaging;

   4: using System.IO;

   5: using System.Web;

   6: using System.Data;

   7: using System.Data.SqlClient;

   8:  

   9: namespace MyWebApplication

  10: {

  11:    

  12:     public class getImageFromDB : IHttpHandler

  13:     {

  14:         public void ProcessRequest(HttpContext context)

  15:         {

  16:             context.Response.Clear();

  17:  

  18:             if (!String.IsNullOrEmpty(context.Request.QueryString["empID"]))

  19:             {

  20:                 int id = Int32.Parse(context.Request.QueryString["empID"]);

  21:  

  22:                 Image image = GetImage(id);

  23:  

  24:                 context.Response.ContentType = "image/jpeg";

  25:                 image.Save(context.Response.OutputStream, ImageFormat.Jpeg);

  26:             }

  27:             else

  28:             {

  29:                 context.Response.ContentType = "text/html";

  30:                 context.Response.Write("<p>Need a valid id</p>");

  31:             }

  32:         }

  33:  

  34:         public bool IsReusable

  35:         {

  36:             get

  37:             {

  38:                 return false;

  39:             }

  40:         }

  41:  

  42:         private Image GetImage(int empID)

  43:         {

  44:  

  45:             MemoryStream memoryStream = new MemoryStream();

  46:             //Retrieve image from Database to a memeory stream. If you are using a different method, use it and assign the data to the 'memoryStream' variable.

  47:  

  48:             string connectionString = "Password=PWD;Persist Security Info=True;User ID=USER;Initial Catalog=SampleDatabase;Data Source=SQLSERVER";

  49:             using (SqlConnection sqlConnection = new SqlConnection(connectionString))

  50:             {

  51:                 using (SqlCommand sqlCommand = new SqlCommand("SELECT emp_id, emp_name, emp_image FROM Employee where emp_id = " + empID.ToString(), sqlConnection))

  52:                 {

  53:                     sqlConnection.Open();

  54:                     SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

  55:  

  56:                     if (sqlDataReader.HasRows)

  57:                     {

  58:                         sqlDataReader.Read();

  59:                         byte[] btImage = (byte[])sqlDataReader["emp_image"];

  60:  

  61:                         memoryStream = new MemoryStream(btImage, false);

  62:                     }

  63:                 }

  64:                 sqlConnection.Close();

  65:             }

  66:             return Image.FromStream(memoryStream);

  67:         }

  68:     }

  69: }

And you can call the handler and display the image, using the following syntax :

   1: private void GetImageFromDatabase(int empID)

   2: {

   3:     imageControl.ImageUrl = "getImageFromDB.ashx?empID=" + empID.ToString();

   4: }

Saving an image to a SQL Database Table

Sometimes it’s not the best of method, to store images in to the database, since it’ll take lot of database space. But there are times, that it’s the only option on your list.
To this sample I will be using the following SQL Table.

   1: CREATE TABLE [dbo].[Employee](

   2:     [emp_id] [int] NOT NULL,

   3:     [emp_name] [varchar](50) NOT NULL,

   4:     [emp_image] [image] NULL

   5: ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

When inserting data use the following syntax:

   1: string fileName = @"D:\MyImage.jpg";

   2: string connectionString = "Password=PWD;Persist Security Info=True;User ID=USER;Initial Catalog=DATABASE;Data Source=SQLSERVER";

   3: using (SqlConnection sqlConnection = new SqlConnection(connectionString))

   4: {

   5:  

   6:     FileInfo finfo = new FileInfo(fileName);

   7:  

   8:     byte[] btImage = new byte[finfo.Length];

   9:     FileStream fStream = finfo.OpenRead();

  10:  

  11:     fStream.Read(btImage, 0, btImage.Length);

  12:     fStream.Close();

  13:  

  14:  

  15:     using (SqlCommand sqlCommand = new SqlCommand("INSERT INTO Employee (emp_id, emp_name, emp_image) VALUES(@emp_id, @emp_name, @emp_image)", sqlConnection))

  16:     {

  17:  

  18:         sqlCommand.Parameters.AddWithValue("@emp_id", 2);

  19:         sqlCommand.Parameters.AddWithValue("@emp_name", "Employee Name");

  20:         SqlParameter imageParameter = new SqlParameter("@emp_image", SqlDbType.Image);

  21:         imageParameter.Value = btImage;

  22:  

  23:         sqlCommand.Parameters.Add(imageParameter);

  24:  

  25:  

  26:         sqlConnection.Open();

  27:         sqlCommand.ExecuteNonQuery();

  28:         sqlConnection.Close();

  29:     }

  30:  

  31: }