T SQL - Playing around with Image Type ( Create, Insert, Select )

This post briefly describes about creating tables containing image, storing and retrieving images in SQL Server

1) Create a table containing image type

CREATE TABLE dbo.MyImages

(ImageId    INT IDENTITY(1,1) NOT NULL,

 uid             BIGINT NULL, 

 picture       IMAGE NULL

 PRIMARY KEY(ImageId ))

2) Insert an image into the DataBase table. 

INSERT INTO MyImages(picture)

SELECT * FROM OPENROWSET(BULK N'C:\images\emp001.png', SINGLE_BLOB) AS myrow

OR you can go with the full row insert as:

INSERT INTO MyImages(uid, picture)

SELECT 2000, PicColumn FROM OPENROWSET(BULK N'C:\images\emp001.png', SINGLE_BLOB) AS myrow

Here, you need to specify the path to the image in the drive, for it to be captured by the insert.

3) Retrieving for display

It's best that you have a handler for retrieving images from DB. For an example could use a generic handler (.ashx file).

3.1
In the handler's (.ashx file's) ProcessRequest() method set

        context.Response.Clear();
        context.Response.ContentType = "image/jpeg";

3.2
In the ashx file create a method to retrieve image from DB. The query string would be straight forward.

Eg: "SELECT uid, picture FROM dbo.MyImages WHERE" + uid

Capture the resulting row may be in a DataTable.

3.3
get the image into a byte array

byte[] imageByteArray=null;
imageByteArray=(byte[])sampleDataTable.Rows[0][1];

3.4
Include the byte[] to  memory stream, and set it for output

MemoryStream memStream = new MemoryStream(imageByteArray, false);
System.Drawing.Image img = System.Drawing.Image.FromStream(memStream);
img .Save(context.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Png);

3.5
In the aspx page's calling the method ( Eg: inside an item data bound method ).
Set the image url by pointing to the handler

myImage.ImageUrl = "/pathtothe ashpage/sample.ashx?uid=" + uid;