Retrieving Image from SQL server database

In this example i will be showing a example to retrieve stored image from the SQL Server database. 

This example is suitable only to the scenario where we need to retrieve a single image in one call.

As i have shown in the previous example that i stored the image in a table named tblImage. I am copying the same create table statement from my previous example for better understanding.

SQL Server supports a datatype named as image for storing the image. A brief description about image datatype is as follows:-

image : Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

The following query can be used to create a table:

CREATE TABLE [dbo].[tblImage] (

        [ID] [int] IDENTITY ,

        [Image_Name] [varchar] (500) NULL ,

        [Image] [image] NULL

)

In the create table statement, ID is declared as IDENTITY column.

I created a sample page with a Image control and a button (Button Text -  Retrieve Image). OnClick of "Retrieve Image" button, the image will be displayed/loaded in the Image box.

Following c# code will be used to retrieve image from the database table:

//defining the connection string for the SqlConnection object

SqlConnection connection = new SqlConnection(@"server=MyPC;database=master;uid=sa;pwd=password1");

try

{

   //opening the database connection

   connection.Open();

   SqlCommand cmd = new SqlCommand();

   //select statement creation as a commandText

   cmd.CommandText = "Select Image from tblImage where ID=1";

   cmd.Connection = connection;

   //executing the query to retrive the byte array

   // the retrieved binary data should be converted to byte array type

   byte[]  byteArray = (byte[])cmd.ExecuteScalar();

   //The project structure contains a folder called TempImages

   // which will be used to create and store image files

   string path = Server.MapPath("TempImages");

   

   //Using the FileStream class creating a jpeg file from byteArray   

   FileStream fs = new    

   FileStream(path+"\\"+Session.SessionID+".jpeg",FileMode.CreateNew,FileAccess.Write);

   fs.Write(byteArray,0,byteArray.Length);

   //Clears buffer for this stream and causes any buffered data to be written to the disc

   fs.Flush();

   //Closing the FileStream

   fs.Close();

   

   //Setting the ImageUrl to the image file path

   Image1.ImageUrl = "\\TempImages\\"+Session.SessionID+".jpeg";               

}

finally

{

   if(connection!=null)

     connection.Close();

}

It is always better to use Session.SessionID for maintaining the uniqueness of any id.

Later i will be showing another example of retrieving multiple images in one database call and binding it to the grid control.

Happy coding....