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

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


No comments :

Post a Comment