Startseite > Allgemein > Read multiple images/files from folder and insert as blobs in DB table (for SSRS usage)

Read multiple images/files from folder and insert as blobs in DB table (for SSRS usage)

In SSRS you can integrate picture-resources from different sources:


External      = URL to an existing image stored on a webserver
Embedded = serialized blob of image will be stored in RDL
Database    = Get image blob from a dataset

Personally i would always recommend to store pictures in database, instead of embedding them in each report. This approach supports central management accross multiple reports.
By storing the pictures in database you also insure that your RDL files stay “small”, if you have an RDL File with multiple images embedded you will get “huge” RDLs which decreases performance at report rendering time.

Step1: Import pictures from filesystem into DB-Table
To make the import and update of images easy i created a “helper” procedure which can be executed as follow:

EXEC dbo.ST_ReportImagesImport      

The procedure uses SQL-internal procedure “dbo.xp_DirTree” to get recursive list of all files in specified folder, read the files and store them in a  “ReportImages” table.

Step2: Create Dataset in Report and lookup your image via expression

This table can then be used as an dataset in SSRS. To get a picture from this dataset you can use the Lookup() formula:

For example use following expression in “Image Properties” dialog to get the picture with ID 1
=Lookup(Fields!ImageBlob.Value),1, IIF(IsNothing(Convert.ToBase64String(Fields!ImageBlob.Value)),"",Convert.ToBase64String(Fields!ImageBlob.Value)), "DsReportImages")


TSQL-Code for helper procedure:


— ——————————–
— Description:
–this proc imports/syncs imagefiles from a folder in filesystem into table dbo.ReportImages
–If the image already exists in table it will be updated otherwise a new row in table will be generated
— ——————————–
— Changes:
–   2012-09-21 JJuelke initial
EXEC ST_ReportImagesImport @PictureFolder=’D:\TEMP\SUP\types\‘,@IncludeSubFolders=1

CREATE PROC ST_ReportImagesImport
      @PictureFolder nvarchar(2000) = ‚D:\TEMP\SUP\types\‘ –define input directory here which contains your image-files
     ,@IncludeSubFolders int = 1
     ,@OutputFileList int = 1
     ,@OutputTargetTable int = 1


–check if directory exists
    declare @file_results table (file_exists int,file_is_a_directory int,parent_directory_exists int)
    insert into @file_results (file_exists, file_is_a_directory, parent_directory_exists) exec master.dbo.xp_fileexist @PictureFolder
    IF NOT EXISTS (select * from @file_results WHERE file_is_a_directory=1)
        Print ‚WARNING! Directory "’+@PictureFolder+’" does not exist at local file system of sql server! Please check variable @PictureFolder and run script again!‘
        RETURN -1

–create target table if not exists already
    CREATE TABLE [dbo].[ReportImages](
        [ReportImagesID] [int] IDENTITY(1,1) NOT NULL,
        [Filename] nvarchar(2000) NULL,
        [ImageBlob] [image] NOT NULL,
        [MimeType] nvarchar(200) default(‚Image/jpeg‘) NULL,
        CreateDT datetime default(getdate()),
        UpdateDT datetime NULL)

DECLARE @files table (ID int identity, FileFQN nvarchar(2000), FileName nvarchar(200), mimetype nvarchar(100), depth int, [isFile] int, blob [image] )
DECLARE @filename nvarchar(2000), @mimetype nvarchar(100), @FileFQN nvarchar(2000)

–now get folder content (recursive) and store this information in @files table
    INSERT INTO @files (FileName,depth,isfile)
    EXEC master.dbo.xp_DirTree @PictureFolder,@IncludeSubFolders,1
    DELETE FROM @files WHERE isFile = 0
    UPDATE @files set
        FileFQN   = @PictureFolder + FileName
        , mimetype= CASE LTRIM(RTRIM(RIGHT(filename, LEN(Filename) – CHARINDEX(‚.‘,filename)  )))
                                         WHEN ‚jpg‘ THEN ‚Image/jpeg‘
                                         WHEN ‚jpeg‘ THEN ‚Image/jpeg‘
                                         WHEN ‚bmp‘ THEN ‚Image/bmp‘
                                         WHEN ‚gif‘ THEN ‚Image/gif‘
                                         WHEN ‚png‘ THEN ‚Image/png‘
                                         ELSE LTRIM(RTRIM(RIGHT(filename, LEN(Filename) – CHARINDEX(‚.‘,filename)  )))
    –outpuf read filelist                                    
    IF @OutputFileList=1 select * from @files

–now loop/read each file and import
DECLARE @insertString nvarchar(3000)
    DECLARE row_cursor CURSOR FOR SELECT FileFQN, FileName,mimetype FROM @Files
        OPEN row_cursor
        FETCH NEXT FROM row_cursor INTO @FileFQN,@filename,@mimetype
        WHILE @@FETCH_STATUS = 0
              SET @insertString = N’WITH Source (BulkColumn,Filename,mimetype) as (                               
                                    SELECT BulkColumn,“’+@FileName+“‘ as FileName, “’+@mimetype+“‘ as mimetype
                                    FROM OPENROWSET(BULK N“‘ + @FileFQN + “‘, SINGLE_BLOB) as tempImg)
                                    MERGE into dbo.ReportImages t
                                        USING Source s ON s.FileName = t.FileName
                                        WHEN MATCHED
                                        THEN UPDATE SET
                                                 t.ImageBlob = s.BulkColumn
                                                ,t.mimetype = s.mimetype
                                                ,t.UpdateDT = getdate()
                                        WHEN NOT MATCHED by target then
                                            INSERT  (FileName,ImageBlob,mimetype)
                                            VALUES  (s.FileName,s.BulkColumn,s.mimetype);‘
                EXEC SP_ExecuteSQL @statement=@insertString
            FETCH NEXT FROM row_cursor INTO @FileFQN,@filename,@mimetype
    CLOSE row_cursor
    DEALLOCATE row_cursor

–output target table
IF @OutputTargetTable=1 select * from dbo.ReportImages


Kategorien:Allgemein Schlagwörter: ,
  1. Es gibt noch keine Kommentare.
  1. 9. August 2014 um 06:49

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

Du kommentierst mit Deinem Abmelden / Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: