Search code examples
sqlazureazure-sql-databaseazure-blob-storage

How do I add an image to Azure SQL Database?

I've been rewriting my database recently and wanted it to be able to accept images. However in case of Azure it's a lot more tricky, since adding images directly from PC is impossible and ends up with this error:

INSERT INTO [Lesson_Content] (Lesson_Language, Lesson_Text, Id_Lesson, Lesson_Image) SELECT 'Polish','sample text', 4, BulkColumn
FROM OPENROWSET
(
    Bulk 'C:\Users\ogryz\Desktop\swedish.png', SINGLE_BLOB
)AS Lesson_Image

Cannot bulk load because the file "C:\Users\ogryz\Desktop\swedish.png" could not be opened. Operating system error code 997(Overlapped I/O operation is in progress.).

So I had to resort to storage blobs, however I have no clue how to add an image to them via SQL rather than throught website.

So, I'd actually have two questions:

  1. How to add an image to blob storage throught sql?
  2. Is there some better method of adding an image to Azure SQL Database rather than using previously mentioned blobs?
Solution

  • How do I add an image to Azure SQL Database?

    As per Microsoft Document There is no support for loading files from on-premises computers to Azure SQL Database.

    enter image description here

    Agreed with CSharpRocks, the image should be saved to blob storage, and the database should also contain the blob URL of that image, or you can store the image in blob storage and then read it in SQL database.

    To achive this,

    • First upload your image in blob storage. enter image description here
    • Then create external DataSource for that blob storage and insert the image from blob storage to Azure sql.

    Sample Code:

    --create a master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword';
GO

--create a scoped credential

CREATE DATABASE SCOPED CREDENTIAL sampleblobcred1
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = 'SAS Token';

--create a external data source

CREATE EXTERNAL DATA SOURCE samplestorageblob
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://StorageAccountName.blob.core.windows.net'
          , CREDENTIAL= sampleblobcred1
);

--Insert image from blob storage

INSERT INTO DatabaseImageTable (col1, col2)VALUES (1, (Select BulkColumn FROM OPENROWSET( 
BULK 'image.jpg',
DATA_SOURCE = 'samplestorageblob', SINGLE_BLOB) AS ImageFile));