Tuesday, October 11, 2011

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");

   //opening the database connection

   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    


   //Clears buffer for this stream and causes any buffered data to be written to the disc
   //Closing the FileStream
   //Setting the ImageUrl to the image file path
   Image1.ImageUrl = "\\TempImages\\"+Session.SessionID+".jpeg";               


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

No comments :

Post a Comment