[HOWTO] Convert Image File to SQL data type varbinary using PowerShell

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

4 thoughts on “[HOWTO] Convert Image File to SQL data type varbinary using PowerShell

Add yours

  1. 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?

    Like

    1. 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.

      Liked by 1 person

    1. 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:

      // LogoBase64 is a property that is accessible in the corresponding razor page
      LogoBase64 = Convert.ToBase64String(logo.Data);
      

      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.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: