ballardchalmers - home

Contact us 01342 410223

Blog

Loading Images Into SQL Server

A requirement arose recently to import a folder full of JPEG images into a SQL Server table. I ideally wanted to achieve this using pure T-SQL code; that is without using CLR code, external stored procedures or references to OLE objects.

The first challenge was to find a way to obtain a list of all the files in a particular operating system folder. To do this I decided to use xp_cmdshell to execute the DIR *.jpg command. The results returned from the EXEC statement can then be stored in a database table using the INSERT … EXEC statement. The required code is:

INSERT INTO ImportedImages(FileName)
EXEC xp_cmdshell'dir c:\work\images\*.jpg /B'

DELETE
FROM ImportedImages

WHERE FileName IS NULL

The DELETE is used to necessary to clear out a NULL file name returned by DIR.

The next challenge is, given a file name, to import the contents of the file into a column in a record in the database. The OPENROWSET function has a BULK option that can do this and the required command is:

SELECT bulkcolumn
FROM OPENROWSET(BULK 'C:\work\Images\1001.jpg', SINGLE_BLOB) AS I

This can be combined with an update to directly update the image column in the database as follows:

UPDATE ImportedImages

SET ImageData =(SELECT bulkcolumn
FROM OPENROWSET(BULK 'C:\work\Images\1001.jpg', SINGLE_BLOB) AS I)
WHERE FileName = 'C:\work\Images\1001.jpg'

The rest of the code just requires a cursor to loop through each file name and load the images one by one using dynamic SQL.

The full code including creating the database table is:

-- Create the table to hold the images

CREATE TABLE ImportedImages(
      FileName sysname NULL, ImageData varbinary(max) NULL)
GO

-- Get images names from folder
INSERT INTO ImportedImages(FileName)
EXEC xp_cmdshell'dir c:\work\images\*.jpg /B'

DELETE FROM ImportedImages

WHERE FileName IS NULL
GO

-- Import all impages from folder

DECLARE C CURSOR FOR
      (SELECT FileName FROM ImportedImages WHERE ImageData IS NULL)
DECLARE @FileName sysname
DECLARE @Path sysname
DECLARE @SQL varchar(max)

-- Loop through each file loading the images one by one

OPEN C
FETCH NEXT FROM C INTO @FileName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  SET @Path = 'C:\work\Images\' + @FileName
  SET @SQL =
  'UPDATE ImportedImages
      SET ImageData = (SELECT bulkcolumn FROM OPENROWSET(
      BULK '''
+ @Path + ''', SINGLE_BLOB) AS IMAGE)
      WHERE FileName = ''' + @FileName + ''''
  EXEC (@sql)
  FETCH NEXT FROM C INTO @FileName
END
CLOSE C
DEALLOCATE C

 

By Geoff Ballard, Chief Technical Officer

 

Have a comment? Let us know below, or send an email to enquiries@ballardchalmers.com

 

Comment on this article

About

Ballard Chalmers is one of the UK’s leading enterprise software engineering companies, specialising in development for the Microsoft Enterprise Application Platform. We are principally dedicated to software development and integration for the Microsoft Cloud, as well as on-premises and hybrid systems. Our expertise is in Microsoft .NET, SQL Server, BizTalk Server and SharePoint Server.

Let's talk

  01342 410223

Find out more

Thank you for contacting us.

We will be in touch with you shortly.