inject image from db into sql report query

Question

If I'm creating simple report query against mssql db

if not exists(select * from DeliveryTemplate where TemplateId=2)
    begin 
       select u.usersCode, 1,2,'User {UsersCode}',' hello {Username},', null, dt.DeliveryCode 
       from User u
          left join DeliveryTemplate dt on u.Id = dt.UserId
    end

how can extend this query in order to insert image from the database where this image is represented as byte[] inside DeliveryTemplate

public class DeliveryTemplate{
   ...
   public virtual byte[] MyImage { get; set; }
}

Just to make clear I know to transform byte[] to Image but I don't know how to inject image in above query.

can you clarify the question? does including `dt.MyImage` not work? (where `MyImage` is the name of the `varbinary(max)` column) — Marc Gravell, Jan 11 '16 at 09:40
When this report is generated I need to display actual image, like — user1765862, Jan 11 '16 at 10:49
Html is a UI concern, not a TSQL concern. Generate that at the UI. As for how to write an inline image - base-64 is your friend; see examples http://stackoverflow.com/questions/1207190/embedding-base64-images and http://www.greywyvern.com/code/php/binary2base64 — Marc Gravell, Jan 11 '16 at 11:21

Reversed Engineer · Answer 1 · 2016-02-04T11:46:43.260

The following T-SQL work for me:

UPDATE DeliveryTemplate SET MyImage = 
(SELECT * FROM OPENROWSET (BULK N'C:\Images\Image.jpg', SINGLE_BLOB) rs) 
WHERE UserId = identity_value

Edit: Create the image field as follows:

ALTER TABLE DeliveryTemplate ADD MyImage VARBINARY(MAX) NULL

Note that you have to store the image in a folder to which SQL Server has access rights. If it's a custom folder (as above), you'll need to give persmissions to SQL Server for that (I think it's user MSSQL$MSSQLSERVER2012 depending on your SQL version).

You can check this by checking the permissions on the SQL data folder or wherever your database's MDF file is stored (in SSMS, right click the Database > Properties > Files). On my machine, databases are stored in C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA.

Of course you don't want to mess around in that folder too much seeing as your databases are actually stored there. But you can use it for testing the above SQL code and getting an image into the database. Under no circumstances do you want to delete any of the MDF or LDF files there!!! (Deleting an MDF file is a "Resume Generating Event" to quote Christopher Harrison from the excellent MS Virtual Academy Videos)

inject image from db into sql report query

1 Answers1