Introduction

When working with databases, there may be instances where you need to store images, such as product photos or team member headshots. In this article, we will explore how to store and retrieve images in SQL Server using various techniques.

Requirements

• SQL Server 2016 or later with SSMS installed
• SSDT 2015 installed (or earlier versions)
• SSRS installed

Inserting a Single Image

To insert a single image into SQL Server, we first need to create a table with a column of type varbinary(max) to store the image data. Here’s an example:

CREATE TABLE myimages (
  id int,
  img varbinary(max)
)

Once the table is created, we can use the INSERT INTO statement to insert the image into the table. For example:

INSERT INTO myimages
VALUES (1, (SELECT * FROM OPENROWSET(BULK N'C:\img\1.png', SINGLE_BLOB) as T1))

This statement inserts an image named 1.png from the C:\img folder into the myimages table.

Storing Multiple Images

If you have multiple images that you want to store in SQL Server, you can use PowerShell to simplify the task. First, truncate the myimages table to remove any existing data:

TRUNCATE TABLE myimages

Next, use PowerShell to iterate through the images in a folder and insert them into the table. Here’s an example:

$files = Get-ChildItem -Path C:\img

$counter = 1

foreach ($file in $files) {
  Invoke-Sqlcmd -Query "INSERT INTO myimages VALUES ($counter, (SELECT * FROM OPENROWSET(BULK N'C:\img\$file', SINGLE_BLOB) as T1))"
  $counter++
}

This script retrieves all the files in the C:\img folder, assigns a unique ID to each image, and inserts them into the myimages table.

Retrieving Images

To verify that the images were successfully inserted, we can use SQL Server Reporting Services (SSRS). Open SSDT and create a new project. Use the Report Server Project Wizard to create a report that displays the images from the myimages table.

Once the report is created, you can add an image control and configure it to display the images from the database. Here’s how:

1. Add the myimages table to the report
2. Select the columns id and img
3. Choose the tabular report type
4. Design the table layout
5. Configure the image control to use the img column as the image source
6. Select the appropriate MIME type for the images (e.g., image/png)
7. Specify the image size

Once the report is run, you should see the images retrieved from the myimages table.

Conclusion

SQL Server provides various methods for storing and retrieving images. In this article, we learned how to insert a single image and multiple images into a SQL Server table using T-SQL and PowerShell. We also explored how to view the inserted images using SQL Server Reporting Services. If you have any questions, feel free to leave a comment!