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:

image

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      
         @PictureFolder=‚D:\TEMP\YourImagesFolder\‘,@IncludeSubFolders=1

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

image

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
——————————–
/*Testcase
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

as

–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)
    BEGIN
        Print ‚WARNING! Directory "’+@PictureFolder+’" does not exist at local file system of sql server! Please check variable @PictureFolder and run script again!‘
        RETURN -1
    END ELSE
BEGIN

–create target table if not exists already
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES a where a.TABLE_NAME = ‚ReportImages‘)
BEGIN
    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)
END

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)  )))
                                         END
    –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
            BEGIN
              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
            END
    CLOSE row_cursor
    DEALLOCATE row_cursor

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

END

Kategorien:Allgemein Schlagwörter: ,
  1. Es gibt noch keine Kommentare.
  1. No trackbacks yet.

Schreibe einen Kommentar

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

WordPress.com-Logo

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

Twitter-Bild

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

Facebook-Foto

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: