How to store and retrieve the Image data directly from SQL Server Table

1.     Background

The purpose of this article is to describe a way of storing the Image data into SQL Server table as well as retrieval of the image data directly from the table. Here we are going to use OPENROWSET with the BULK provider / SINGLE_BLOB file format for storing and retrieval of the image data.

2.     What is OPENROWSET?

• OPENROWSET is a SQL ROWSET function that basically used for connecting and accessing the remote data.
• We can access table data, file data and image data using OPENROWSET function.
• BULK rowset provider for OPENROWSET is basically used to read data from a file.
• SINGLE_BLOG: Returns the contents of data_file as a single-row, single-column rowset of type varbinary (max).

Syntax:
OPENROWSET

( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'

   | 'provider_string' }

   , {   [ catalog. ] [ schema. ] object

       | 'query'

     }

   | BULK 'data_file' ,

       { FORMATFILE = 'format_file_path' [ <bulk_options> ]

       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }

} )

3.     Steps to store image data into SQL Server table

• We have created a sample stored procedure that is using OPENROWSET function with BULK provider to access the image file from a given location. As it is Image data, we are using file format as SINGLE_BLOG so that entire content come as single row..

Below is the sample stored procedure to store the Image data for “monuments of India“ with their name into SQL Server table:

   /*Table Schema*/

  CREATE TABLE Monuments

   (ID INT IDENTITY,

     Name NVARCHAR(250),

             MImage VARBINARY(MAX))

/*Stored procedure to store the image data*/

/*This Stored procedure can be utilize when we need to upload the an image using Image path and Image Caption(name)*/

CREATE PROC SaveImageDataToDB

 (@Name NVARCHAR(250),

  @ImagePath NVARCHAR(500)

 )

AS

/*Declaring Variable*/

DECLARE @SQLString NVARCHAR(MAX)

/*Setting the OPENROWSET query*/

SET @SQLString = 'SELECT ' + '''' +@Name +'''' + ' AS Name,' +  'MImage

FROM OPENROWSET(BULK N''' + @ImagePath + ''',SINGLE_BLOB) AS MImage(MImage);'

/*Executing the query and inserting the image data*/

INSERT INTO Monuments

(Name,MImage)

EXEC (@SQLString)

  

• Now execute the stored procedure.

            EXEC SaveImageDataToDB 'Sanchi Stupta', 'G:\Image\Sanchi.jpg'

GO

EXEC SaveImageDataToDB 'Howrah Bridge', 'G:\Image\Howrah.jpg'

GO

EXEC SaveImageDataToDB 'India Gate', 'G:\Image\India_Gate.jpg'

GO

EXEC SaveImageDataToDB 'Hawa Mahal', 'G:\Image\Hawa_mahal.jpg'

GO

EXEC SaveImageDataToDB 'Taj Mahal', 'G:\Image\TajMahal.jpg'

GO

• Now see the result. Here we can see the image data in the form of Binary data.

   

4.     Steps to retrieve the image data from table using SSRS report

• Create a simple tabular report that pulls the Monument Name and its image from the Monument table.

Below is the report that is showing #Error in place of Images.

 

• In order to fix this issue, remove the column mapping  for Monument Image:

 

• Right click on the ‘Monument Image’ row and go to the Textbox properties:

• Go to Fill and select Image Source as Database. Select your image column from dropdown as a field. Select MIME type as per your images type.

• Now Preview the report

 

Now we can see the Images from the data successfully.

5.     Conclusion

By using OPENROWSET function and SSRS, we can store and retrieve the image data from SQL server table respectively.

-------------------------------------------------End of Document---------------------------------------------------