In one of the projects I’m currently working on, I needed to insert a PNG file into a database column of type varbinary.
I failed with my first attempts, but thanks to some blog posts and some threads on StackOverflow, I was able to come up with the following solution by assembling some of the information I found on the Internet.
# to create a hex-string from a PNG file execute the following commands in PowerShell
# replace C:\PATH\TO\FILE.png with the path to the file you want to convert
[Byte[]]$bytes = Get-Content -Encoding Byte C:\PATH\TO\FILE.png
$hexString = ($bytes | ForEach-Object ToString X2) -join ''
$valueToBeInserted = '0x' + $hexString
$valueToBeInserted | clip
After the execution of the above commands, the value to be inserted into the database column of type varbinary is on the clipboard. To add the value to the database column either directly insert it using Microsoft SQL Server Management Studio or by executing the following insert into statement:
-- replace TABLE_NAME, COLUMN_NAME and PASTE_VALUE_TO_BE_INSERTED_HERE
INSERT INTO [dbo].[TABLE_NAME] ([COLUMN_NAME])
VALUES (PASTE_VALUE_TO_BE_INSERTED_HERE);
rufers blog 
Works out of the box, Thanks Marc for sharing! To validate the hex output I used a random online tool, maybe this could be done in PowerShell as well?
LikeLike
Hi Michael. There is for sure a way to do so in PowerShell. However it was not necessary in my case as I could test it with an application that consumed the values from the database.
LikeLiked by 1 person
Hi marc, how would you recover the png from the saved varbinary?
LikeLike
In our case, we access the database with a .NET Core backend application using
EntityFrameworkCore.Entity class defines a property of type
byte[]that is mapped to the corresponding database column. The data is then served by a controller method over REST.In the frontend (Blazor application), the image data (
byte[]) gets converted to a base 64 string:The base 64 string is used as follows:
<img src="@String.Format("data:image/png;base64,{0}", @ViewModel.LogoBase64)" />I hope that answers your question.
LikeLike