I have a visual studio sql server database project, and an Images table. I need insert a default image in a PostDeployment script, because this image will be used in all entities with an empty image. How can I store this image in the project, and how can access it in the script?
Insert this image in the database project is a good practice? I have a WindowsService project, and a ASP.NET MVC project with entity framework. Should I insert this image in some kind of initial verification in each one of this other 2 projects?
There are a couple of ways to do this.
Given a table
create table testImage
(id int,
myImage varbinary( max));
You can insert an image from a file with something like:
INSERT INTO testImage (id, myImage)
SELECT 1, bulkcolumn
FROM openrowset(BULK 'D:\x\dZLx1.png', single_blob) as myImage
There are a couple of potential headaches here, you need to keep track of the path to the image in your project somehow, and I think there are a few security-related scenarios where OPENROWSET doesn't work anyway.
It might be more reliable to do this once on your desktop, then SELECT the value out again to use in an insert statement such as
IF NOT EXISTS (SELECT * FROM testIMAGE where ID = 2)
BEGIN
INSERT INTO testImage VALUES
(2,0x89504E470D0A1A0.....)
END
(full script here: https://gist.github.com/gavincampbell/a25431dffd3555563a052c297a32415e)
As you will realise when you try this, the resulting string will be long. I think it would be a good idea to keep this in a separate script and reference it from the main post-deploy script with :r (apologies if you knew this already!). Also remember that you don't need quotes around the binary "string".