Fiddler Composer with the image field name and upload file button highlighted
The currentimplementation only supports the uploading of one image at a time in a field called “image”.
Fiddler console after succesfull upload
This implementation supports a “Content-Type” of “application/octet-stream” or “image/jpeg”.
[HttpPost("{id}/image")]
public async Task<ActionResult> Upload([FromRoute(Name = "id")][Range(1, int.MaxValue, ErrorMessage = "StockItem id must greater than 0")] int id, [FromForm] IFormFile image)
{
if (image == null)
{
return this.BadRequest("Image image file missing");
}
if (image.Length == 0)
{
return this.BadRequest("Image image file is empty");
}
if ((string.Compare(image.ContentType, "application/octet-stream",true) != 0) && (string.Compare(image.ContentType, "image/jpeg", true) != 0))
{
return this.BadRequest("Image image file content-type is not application/octet-stream or image/jpeg");
}
try
{
using (MemoryStream ms = new MemoryStream())
{
await image.CopyToAsync(ms);
ms.Seek(0, SeekOrigin.Begin);
using (SqlConnection db = new SqlConnection(this.connectionString))
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add("StockItemId", id);
parameters.Add("photo", ms, DbType.Binary, ParameterDirection.Input);
await db.ExecuteAsync(sql: @"UPDATE [WareHouse].[StockItems] SET [Photo]=@Photo WHERE StockItemID=@StockItemId", param: parameters, commandType: CommandType.Text);
}
}
}
catch (SqlException ex)
{
logger.LogError(ex, "Updating photo of StockItem with ID:{0}", id);
return this.StatusCode(StatusCodes.Status500InternalServerError);
}
return this.Ok();
}
After uploading the image I could download it as either a stream of bytes(displayed in Fiddler) or Base64 encoded (this had to be converted to an image)
Fiddler displaying downloaded jpeg image
This implementation doesn’t support the uploading of multiple images or the streaming of larger images but would be sufficient for uploading thumbnails etc.
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");
}
[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.
On a couple of the systems I work on there are a number of queries (often complex spatial searches) which are very resource intensive but are quite readily cached. In these systems we have used HTTP GET and HEAD Request methods together so that the client only re-GETs the query results after a HEAD method indicates there have been updates.
I have been trying to keep the number of changes to my Microsoft SQL Azure World Wide Importers database to a minimum but for this post I have added a rowversion column to the StockGroups table. The rowversion data type is an automatically generated, unique 8 byte binary(12 bytes Base64 encoded) number within a database.
StockGroups table with Version column
Adding a rowversion table to an existing System Versioned table in the SQL Server Management Studio Designer is painful so I used…
ALTER TABLE [Warehouse].[StockGroups] ADD [Version] [timestamp] NULL
To reduce complexity the embedded SQL is contains two commands (normally I wouldn’t do this) one for retrieving the list StockGroups the other for retrieving the maximum StockGroup rowversion. If a StockGroup is changed the rowversion will be “automagically” updated and the maximum value will change.
[HttpGet]
public async Task<ActionResult<IAsyncEnumerable<Model.StockGroupListDtoV1>>> Get()
{
IEnumerable<Model.StockGroupListDtoV1> response = null;
try
{
using (SqlConnection db = new SqlConnection(this.connectionString))
{
var parameters = new DynamicParameters();
parameters.Add("@RowVersion", dbType: DbType.Binary, direction: ParameterDirection.Output, size: ETagBytesLength);
response = await db.QueryAsync<Model.StockGroupListDtoV1>(sql: @"SELECT [StockGroupID] as ""ID"", [StockGroupName] as ""Name""FROM [Warehouse].[StockGroups] ORDER BY Name; SELECT @RowVersion=MAX(Version) FROM [Warehouse].[StockGroups]", param: parameters, commandType: CommandType.Text);
if (response.Any())
{
byte[] rowVersion = parameters.Get<byte[]>("RowVersion");
this.HttpContext.Response.Headers.Add("ETag", Convert.ToBase64String(rowVersion));
}
}
}
catch (SqlException ex)
{
logger.LogError(ex, "Retrieving list of StockGroups");
return this.StatusCode(StatusCodes.Status500InternalServerError);
}
return this.Ok(response);
}
I used Telerik Fiddler to to capture the GET response payload.
The HEAD method requests the maximum rwoversion value from the StockGroups table and compares it to the eTag. In a more complex scenario this could be a call to a local cache to see if a query result has bee refreshed.
[HttpHead]
public async Task<ActionResult> Head([Required][FromHeader(Name = "ETag")][MinLength(ETagBase64Length, ErrorMessage = "eTag length invalid too short")][MaxLength(ETagBase64Length, ErrorMessage = "eTag length {0} invalid too long")] string eTag)
{
byte[] headerVersion = new byte[ETagBytesLength];
if (!Convert.TryFromBase64String(eTag, headerVersion, out _))
{
logger.LogInformation("eTag invalid format");
return this.BadRequest("eTag invalid format");
}
try
{
using (SqlConnection db = new SqlConnection(this.connectionString))
{
byte[] databaseVersion = await db.ExecuteScalarAsync<byte[]>(sql: "SELECT MAX(Version) FROM [Warehouse].[StockGroups]", commandType: CommandType.Text);
if (headerVersion.SequenceEqual(databaseVersion))
{
return this.StatusCode(StatusCodes.Status304NotModified);
}
}
}
catch (SqlException ex)
{
logger.LogError(ex, "Retrieving StockItem list");
return this.StatusCode(StatusCodes.Status500InternalServerError);
}
return this.Ok();
}
I used Fiddler to to capture a HEAD response payload a 304 Not modified.
HTTP/1.1 304 Not Modified
Server: Microsoft-IIS/10.0
X-Powered-By: ASP.NET
Date: Sat, 26 Jun 2021 22:09:02 GMT
I then modified the database and the response changed to 200 OK indicating the local cache should be updated with a GET.
HTTP/1.1 200 OK
Transfer-Encoding: chunked
Server: Microsoft-IIS/10.0
X-Powered-By: ASP.NET
Date: Sat, 26 Jun 2021 22:09:59 GMT
This approach combined with the use of the If-Match, If-Modified-Since, If-None-Match and If-Unmodified-since allows web and client side caches to use previously requested results when there have been no changes. This can significantly reduce the amount of network traffic and server requests.
HTTP/1.1 400 Bad Request
Content-Length: 240
Content-Type: application/problem+json; charset=utf-8
Server: Microsoft-IIS/10.0
X-Powered-By: ASP.NET
Date: Sat, 26 Jun 2021 06:28:11 GMT
This was unlike the helpful validation messages returned by the GET method of the StockItems pagination example code
{
"type":"https://tools.ietf.org/html/rfc7231#section-6.5.1",
"title":"One or more validation errors occurred.",
"status":400,
"traceId":"00-bd68c94bf05f5c4ca8752011d6a60533-48e966211dec4847-00",
"errors":
{
"PageSize":["PageSize must be present and greater than 0"],
"PageNumber":["PageNumber must be present and greater than 0"]
}
}
The lack of diagnostic information was not helpful and I’ll explore this further in a future post. I often work on Fintech applications which are “insert only”, or nothing is deleted just marked as inactive/readonly so this approach is viable.
This StockItemsHistoryController has methods for retrieving a list of StockItems at a point in time specified by an optional query string parameter (if no value is provided the current time is assumed). To show how a temporal query can span multiple tables I included the [Purchasing].[suppliers] table which is also versioned.
[HttpGet]
public async Task<ActionResult<IAsyncEnumerable<Model.StockItemsHistoryListDtoV1>>> Get([FromQuery]DateTime? asAt)
{
IEnumerable<Model.StockItemsHistoryListDtoV1> response = null;
if (!asAt.HasValue)
{
asAt = DateTime.UtcNow;
}
try
{
using (SqlConnection db = new SqlConnection(this.connectionString))
{
response = await db.QueryAsync<Model.StockItemsHistoryListDtoV1>(sql: "[warehouse].[StockItemsHistoryStockItemsListAsAtV1]", param: new { asAt }, commandType: CommandType.StoredProcedure);
}
}
catch (SqlException ex)
{
logger.LogError(ex, "Retrieving list of StockItems");
return this.StatusCode(StatusCodes.Status500InternalServerError);
}
return this.Ok(response);
}
ALTER PROCEDURE [Warehouse].[StockItemsHistoryStockItemsListAsAtV1]
@AsAt DATETIME2(7)
AS
BEGIN
SELECT [StockItems].[StockItemID] as "ID"
,[StockItems].[StockItemName] as "Name"
,[StockItems].[UnitPrice]
,[StockItems].[RecommendedRetailPrice]
,[StockItems].[TaxRate]
,[StockItems].[CustomFields]
,[Suppliers].[SupplierID]
,[Suppliers].[SupplierName]
FROM [Warehouse].[StockItems] FOR SYSTEM_TIME AS OF @AsAt as StockItems
INNER JOIN [Purchasing].[Suppliers] FOR SYSTEM_TIME AS OF @AsAt as Suppliers ON (StockItems.SupplierID = [Suppliers].SupplierID)
END
The query also returns the custom fields (often what was changed in StockItem history), the supplier Id and Supplier name.
The detailed history of a StockItem can be queried to illustrate how the _Archive(history) table works
ALTER PROCEDURE [Warehouse].[StockItemsHistoryStockItemHistoryListV1]
@StockItemID int
AS
BEGIN
SELECT[StockItems_Archive].[StockItemID] as "ID"
,[StockItems_Archive].[StockItemName] as "Name"
,[StockItems_Archive].[UnitPrice]
,[StockItems_Archive].[RecommendedRetailPrice]
,[StockItems_Archive].[TaxRate]
,[StockItems_Archive].[CustomFields]
,[StockItems_Archive].[ValidFrom]
,[StockItems_Archive].[ValidTo]
FROM [Warehouse].[StockItems_Archive]
WHERE [StockItems_Archive].[StockItemID] = @StockItemId
ORDER BY [ValidFrom] DESC
END
[HttpGet("{id}/history")]
public async Task<ActionResult<IEnumerable<Model.StockItemHistoryListDtoV1>>> GetHistory([Range(1, int.MaxValue, ErrorMessage = "Stock item id must greater than 0")] int id)
{
IEnumerable<Model.StockItemHistoryListDtoV1> response = null;
try
{
using (SqlConnection db = new SqlConnection(this.connectionString))
{
response = await db.QueryAsync<Model.StockItemHistoryListDtoV1>(sql: "[Warehouse].[StockItemsHistoryStockItemHistoryListV1]", param: new { StockItemID = id }, commandType: CommandType.StoredProcedure);
if (response == default)
{
logger.LogInformation("StockItem:{0} not found", id);
return this.NotFound($"StockItem:{id} not found");
}
}
}
catch (SqlException ex)
{
logger.LogError(ex, "Retrieving up a StockItem with Id:{0}", id);
return this.StatusCode(StatusCodes.Status500InternalServerError);
}
return this.Ok(response);
}
The state of a StockItem plus the associated Supplier and PackageTypes tables can also be queried at a point in time (if no value is provided the current time is assumed).
[HttpGet("{id}")]
public async Task<ActionResult<Model.StockItemGetDtoV1>> Get([Range(1, int.MaxValue, ErrorMessage = "Stock item id must greater than 0")] int id, [FromQuery] DateTime? asAt)
{
Model.StockItemGetDtoV1 response = null;
if ( !asAt.HasValue)
{
asAt = DateTime.UtcNow;
}
try
{
using (SqlConnection db = new SqlConnection(this.connectionString))
{
response = await db.QuerySingleOrDefaultAsync<Model.StockItemGetDtoV1>(sql: "[Warehouse].[StockItemsHistoryStockItemLookupAsAtV1]", param: new { asAt, stockItemID=id }, commandType: CommandType.StoredProcedure);
if (response == default)
{
logger.LogInformation("StockItem:{0} not found", id);
return this.NotFound($"StockItem:{id} not found");
}
}
}
catch (SqlException ex)
{
logger.LogError(ex, "Retrieving StockItem with Id:{0}", id);
return this.StatusCode(StatusCodes.Status500InternalServerError);
}
return this.Ok(response);
}
ALTER PROCEDURE [Warehouse].[StockItemsHistoryStockItemLookupAsAtV1]
@StockItemID int,
@AsAt DATETIME2(7)
AS
BEGIN
SELECT[StockItem].[StockItemID] as "ID"
,[StockItem].[StockItemName] as "Name"
,[StockItem].[UnitPrice]
,[StockItem].[RecommendedRetailPrice]
,[StockItem].[TaxRate]
,[StockItem].[typicalWeightPerUnit]
,[StockItem].[QuantityPerOuter]
,[UnitPackage].[PackageTypeName] as "unitPackageName"
,[OuterPackage].[PackageTypeName] as "outerPackageName"
,[Supplier].[SupplierID]
,[Supplier].[SupplierName]
FROM [Warehouse].[StockItems] FOR SYSTEM_TIME AS OF @AsAt as StockItem
INNER JOIN[Warehouse].[PackageTypes] FOR SYSTEM_TIME AS OF @AsAt as UnitPackage ON ([StockItem].[UnitPackageID] = [UnitPackage].[PackageTypeID])
INNER JOIN[Warehouse].[PackageTypes] FOR SYSTEM_TIME AS OF @AsAt as OuterPackage ON ([StockItem].[OuterPackageID] = [OuterPackage].[PackageTypeID])
INNER JOIN[Purchasing].[Suppliers] FOR SYSTEM_TIME AS OF @AsAt as Supplier ON ([StockItem].SupplierID = Supplier.SupplierID)
WHERE[StockItem].[StockItemID] = @StockItemId
END
I found it was easy to miss the “FOR SYSTEM_TIME AS OF @AsAt” on the INNER JOINs.
......
| ADD
{
<column_definition>
| <computed_column_definition>
| <table_constraint>
| <column_set_definition>
} [ ,...n ]
| [ system_start_time_column_name datetime2 GENERATED ALWAYS AS ROW START
[ HIDDEN ] [ NOT NULL ] [ CONSTRAINT constraint_name ]
DEFAULT constant_expression [WITH VALUES] ,
system_end_time_column_name datetime2 GENERATED ALWAYS AS ROW END
[ HIDDEN ] [ NOT NULL ][ CONSTRAINT constraint_name ]
DEFAULT constant_expression [WITH VALUES] ,
start_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID START
]
PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
|
It is also possible to hide the start and end time columns which might be useful for when retrofitting this approach to a legacy application which uses SELECT * FROM … and might not handle the extra columns correctly.