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
– Marc Gravell, Commented Jan 11, 2016 at 9:40
When this report is generated I need to display actual image, like <img src=... So, how would you do that? Yes, MyImage is varbinary(max) on db representation. — user1765862
– user1765862, Commented Jan 11, 2016 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 stackoverflow.com/questions/1207190/embedding-base64-images and greywyvern.com/code/php/binary2base64 — Marc Gravell
– Marc Gravell, Commented Jan 11, 2016 at 11:21

Reversed Engineer · Accepted Answer · 2016-02-04 11:46:43Z

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)

Collectives™ on Stack Overflow

inject image from db into sql report query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related