Steam of Bytes and Base64 Encoded
I needed to add some code using Dapper to retrieve images stored in a database for a webby client. The stockItems table has a column for a photo but they were all null…
CREATE TABLE [Warehouse].[StockItems](
[StockItemID] [int] NOT NULL,
[StockItemName] [nvarchar](100) NOT NULL,
[SupplierID] [int] NOT NULL,
[ColorID] [int] NULL,
[UnitPackageID] [int] NOT NULL,
[OuterPackageID] [int] NOT NULL,
[Brand] [nvarchar](50) NULL,
[Size] [nvarchar](20) NULL,
[LeadTimeDays] [int] NOT NULL,
[QuantityPerOuter] [int] NOT NULL,
[IsChillerStock] [bit] NOT NULL,
[Barcode] [nvarchar](50) NULL,
[TaxRate] [decimal](18, 3) NOT NULL,
[UnitPrice] [decimal](18, 2) NOT NULL,
[RecommendedRetailPrice] [decimal](18, 2) NULL,
[TypicalWeightPerUnit] [decimal](18, 3) NOT NULL,
[MarketingComments] [nvarchar](max) NULL,
[InternalComments] [nvarchar](max) NULL,
[Photo] [varbinary](max) NULL,
[CustomFields] [nvarchar](max) NULL,
[Tags] AS (json_query([CustomFields],N'$.Tags')),
[SearchDetails] AS (concat([StockItemName],N' ',[MarketingComments])),
[LastEditedBy] [int] NOT NULL,
[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
[ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_Warehouse_StockItems] PRIMARY KEY CLUSTERED
(
[StockItemID] ASC
)
I uploaded images of three different colours of sellotape dispensers with the following SQL
UPDATE Warehouse.StockItems
SET [Photo] =(SELECT * FROM Openrowset( Bulk 'C:\Users\BrynLewis\Pictures\TapeDispenserBlue.jpg', Single_Blob) as MyImage) where StockItemID =
-- 203 Tape dispenser (Black)
-- 204 Tape dispenser (Red)
-- 205 Tape dispenser (Blue)
There are two options for downloading the image. The first is as a stream of bytes
[HttpGet("{id}/image")]
public async Task<ActionResult> GetImage([Range(1, int.MaxValue, ErrorMessage = "StockItem id must greater than 0")] int id)
{
Byte[] response;
try
{
using (SqlConnection db = new SqlConnection(this.connectionString))
{
response = await db.ExecuteScalarAsync<byte[]>(sql: @"SELECT [Photo] as ""photo"" FROM [Warehouse].[StockItems] WHERE StockItemID=@StockItemId", param: new { StockItemId = id }, commandType: CommandType.Text);
}
if (response == default)
{
logger.LogInformation("StockItem:{0} image not found", id);
return this.NotFound($"StockItem:{id} image not found");
}
}
catch (SqlException ex)
{
logger.LogError(ex, "Looking up a StockItem:{0} image", id);
return this.StatusCode(StatusCodes.Status500InternalServerError);
}
return File(response, "image/jpeg");
}

The second is a Base64 encoded stream of bytes
[HttpGet("{id}/base64")]
public async Task<ActionResult> GetBase64([Range(1, int.MaxValue, ErrorMessage = "Stock item id must greater than 0")] int id)
{
Byte[] response;
try
{
using (SqlConnection db = new SqlConnection(this.connectionString))
{
response = await db.ExecuteScalarAsync<byte[]>(sql: @"SELECT [Photo] as ""photo"" FROM [Warehouse].[StockItems] WHERE StockItemID=@StockItemId", param: new { StockItemId = id }, commandType: CommandType.Text);
}
if (response == default)
{
logger.LogInformation("StockItem:{0} Base64 not found", id);
return this.NotFound($"StockItem:{id} image not found");
}
}
catch (SqlException ex)
{
logger.LogError(ex, "Looking up a StockItem withID:{0} base64", id);
return this.StatusCode(StatusCodes.Status500InternalServerError);
}
return Ok("data:image/jpeg;base64," + Convert.ToBase64String(response));
}

I lost an hour from my life I will never get back figuring out that a correctly formatted/spelt content types “image/jpeg” and “data:image/jpeg;base64” were key to getting the webby client to render image.