.NET Core web API + Dapper Error handling

Failure is an Option

For some historical reason I can’t remember my controllers often had an outer try/catch and associated logging. I think may have been ensure no “sensitive” information was returned to the caller even if the application was incorrectly deployed. So I could revisit my approach I added a controller with two methods one which returns an HTTP 500 error and another which has un-caught exception.

[Route("api/[controller]")]
[ApiController]
public class StockItemsNok500Controller : ControllerBase
{
	private readonly string connectionString;
	private readonly ILogger<StockItemsNok500Controller> logger;

	public StockItemsNok500Controller(IConfiguration configuration, ILogger<StockItemsNok500Controller> logger)
	{
		this.connectionString = configuration.GetConnectionString("WorldWideImportersDatabase");

		this.logger = logger;
	}

	public async Task<ActionResult<IAsyncEnumerable<Model.StockItemListDtoV1>>> Get500()
	{
		IEnumerable<Model.StockItemListDtoV1> response = null;

		try
		{
			using (SqlConnection db = new SqlConnection(this.connectionString))
			{
				response = await db.QueryWithRetryAsync<Model.StockItemListDtoV1>(sql: @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItem500]", commandType: CommandType.Text);
			}
		}
		catch (SqlException ex)
		{
			logger.LogError(ex, "Retrieving list of StockItems");

			return this.StatusCode(StatusCodes.Status500InternalServerError);
		}

		return this.Ok(response);
	}
}

The information returned to a caller was generic and the only useful information was the “traceId”.

StockItemsNok500Controller error page
[Route("api/[controller]")]
[ApiController]
public class StockItemsNokExceptionController : ControllerBase
{
	private readonly string connectionString;

	public StockItemsNokExceptionController(IConfiguration configuration)
	{
		this.connectionString = configuration.GetConnectionString("WorldWideImportersDatabase");
	}

	public async Task<ActionResult<IAsyncEnumerable<Model.StockItemListDtoV1>>> GetException()
	{
		IEnumerable<Model.StockItemListDtoV1> response = null;

		using (SqlConnection db = new SqlConnection(this.connectionString))
		{
			response = await db.QueryWithRetryAsync<Model.StockItemListDtoV1>(sql: @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItemsException]", commandType: CommandType.Text);
		}

		return this.Ok(response);
	}
}

In “Development” mode the information returned to the caller contains a detailed stack trace that reveals implementation details which are useful for debugging but would also be useful to an attacker.

Developer StockItemsNok Controller Exception page

When not in “Development” mode no additional information is returned (not even a TraceId).

Production StockItemsNok500Controller Exception

The diagnostic stacktrace information logged by the two different controllers was essentially the same

System.Data.SqlClient.SqlException:
   at System.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__126_0 (System.Data.SqlClient, Version=4.6.1.3, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Threading.Tasks.Task+<>c.<.cctor>b__272_0 (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Threading.ExecutionContext.RunInternal (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Threading.ExecutionContext.RunInternal (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Dapper.SqlMapper+<QueryAsync>d__33`1.MoveNext (Dapper, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null: /_/Dapper/SqlMapper.Async.cs:418)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Polly.Retry.AsyncRetryEngine+<ImplementationAsync>d__0`1.MoveNext (Polly, Version=7.0.0.0, Culture=neutral, PublicKeyToken=c8a3ffc3f8f825cc)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1+ConfiguredTaskAwaiter.GetResult (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Polly.AsyncPolicy+<ExecuteAsync>d__21`1.MoveNext (Polly, Version=7.0.0.0, Culture=neutral, PublicKeyToken=c8a3ffc3f8f825cc)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult (System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at devMobile.WebAPIDapper.Lists.Controllers.StockItemsNokController+<Get500>d__4.MoveNext (ListsClassic, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null: C:\Users\BrynLewis\source\repos\WebAPIDapper\Lists\Controllers\14.StockItemsNokController.cs:70)

One customer wanted their client application to display a corporate help desk number for staff to call for support. This information was made configurable

namespace devMobile.WebAPIDapper.Lists
{
	public class ErrorHandlerSettings
	{
		public string Detail { get; set; } = "devMobile Lists Classic API failure";

		public string Title { get; set; } = "System Error";
	}
}
public void ConfigureServices(IServiceCollection services)
{
	services.AddControllers();

	var errorHandlerSettings = Configuration.GetSection(nameof(ErrorHandlerSettings));
	services.Configure<ErrorHandlerSettings>(errorHandlerSettings);

	services.AddResponseCaching();

	services.AddDapperForMSSQL();
#if DAPPER_EXTENSIONS_CACHE_MEMORY
	services.AddDapperCachingInMemory(new MemoryConfiguration
	{
		AllMethodsEnableCache = false
	});
#endif
#if DAPPER_EXTENSIONS_CACHE_REDIS
	services.AddDapperCachingInRedis(new RedisConfiguration
	{
		AllMethodsEnableCache = false,
		KeyPrefix = Configuration.GetConnectionString("RedisKeyPrefix"),
		ConnectionString = Configuration.GetConnectionString("RedisConnection")
	}); 
#endif
	services.AddApplicationInsightsTelemetry();
}
{
 ...
  },
  "ErrorHandlerSettings": {
    "Title": "Webpage has died",
    "Detail": "Something has gone wrong call the help desk on 0800-RebootIt"
  },
...
}
namespace devMobile.WebAPIDapper.Lists.Controllers
{
	using Microsoft.AspNetCore.Mvc;
	using Microsoft.Extensions.Hosting;
	using Microsoft.Extensions.Options;


	[ApiController]
	public class ErrorController : Controller
	{
		private readonly ErrorHandlerSettings errorHandlerSettings;

		public ErrorController(IOptions<ErrorHandlerSettings> errorHandlerSettings)
		{
			this.errorHandlerSettings = errorHandlerSettings.Value;
		}

		[Route("/error")]
		public IActionResult HandleError([FromServices] IHostEnvironment hostEnvironment)
		{
			return Problem(detail: errorHandlerSettings.Detail, title: errorHandlerSettings.Title);
		}
	}
}
StockItemsNok Controller Error page with configurable title and details

Another customer wanted their client application to display a corporate help desk number based on the source hostname.

  • ClientA.SaasApplicationProvider.co.nz
  • ClientB.SaasApplicationProvider.co.nz
  • ClientC.SaasApplicationProvider.co.nz
  • SaasApplication.ClientD.co.nz

This information was also made configurable

namespace devMobile.WebAPIDapper.Lists
{
	using System.Collections.Generic;

	public class UrlSpecificSetting
	{
		public string Title { get; set; } = "";
		
		public string Detail { get; set; } = "";

		public UrlSpecificSetting()
		{
		}

		public UrlSpecificSetting(string title, string detail)
		{
			this.Title = title;
			this.Detail = detail;
		}
	}

	public class ErrorHandlerSettings
	{
		public string Title { get; set; } = "System Error";

		public string Detail { get; set; } = "devMobile Lists Classic API failure";

		public Dictionary<string, UrlSpecificSetting> UrlSpecificSettings { get; set; }

		public ErrorHandlerSettings()
		{
		}

		public ErrorHandlerSettings(string title, string detail, Dictionary<string, UrlSpecificSetting> urlSpecificSettings )
		{
			Title = title;

			Detail = detail;

			UrlSpecificSettings = urlSpecificSettings;
		}
	}
}

We considered storing the title and details message in the database but that approach was discounted as we wanted to minimise dependencies.

{
 ...
  "ErrorHandlerSettings": {
    "Detail": "Default detail",
    "Title": "Default title",
    "UrlSpecificSettings": {
      "localhost": {
        "Title": "Title for localhost",
        "Detail": "Detail for localhost"
      },
      "127.0.0.1": {
        "Title": "Title for 127.0.0.1",
        "Detail": "Detail for 127.0.0.1"
      }
    }
  }
}
namespace devMobile.WebAPIDapper.Lists.Controllers
{
	using Microsoft.AspNetCore.Mvc;
	using Microsoft.Extensions.Hosting;
	using Microsoft.Extensions.Options;


	[ApiController]
	public class ErrorController : Controller
	{
		private readonly ErrorHandlerSettings errorHandlerSettings;

		public ErrorController(IOptions<ErrorHandlerSettings> errorHandlerSettings)
		{
			this.errorHandlerSettings = errorHandlerSettings.Value;
		}

		[Route("/error")]
		public IActionResult HandleError([FromServices] IHostEnvironment hostEnvironment)
		{
			if (!this.errorHandlerSettings.UrlSpecificSettings.ContainsKey(this.Request.Host.Host))
			{
				return Problem(detail: errorHandlerSettings.Detail, title: errorHandlerSettings.Title);
			}

			return Problem(errorHandlerSettings.UrlSpecificSettings[this.Request.Host.Host].Title, errorHandlerSettings.UrlSpecificSettings[this.Request.Host.Host].Detail);
		}
	}
}

The sample configuration has custom title and details text for localhost and 127.0.0.1 with a default title and details text for all other hostnames.

StockItemsNok Controller Error page with 127.0.0.1 specific title and details
StockItemsNok Controller Error page with localhost specific title and details

One customer had a staff member who would take a photo of the client application error page with their mobile and email it to us which made it really easy to track down issues. This was especially usefully as they were in an awkward timezone.

Application Insights TraceId search
Application Insights TraceId search result with exception details

With a customisable error page my approach with the outer try/catch has limited benefit and just adds complexity.

TTI V3 Connector Azure IoT Central Device Provisioning Service(DPS) support

The TTI Connector supports the Azure IoT Hub Device Provisioning Service(DPS) which is required (it is possible to provision individual devices but this intended for small deployments or testing) for Azure IoT Central applications. The TTI Connector implementation also supports Azure IoT Central Digital Twin Definition Language (DTDL V2) for “automagic” device provisioning.

The first step was to configure and Azure IoT Central enrollment group (ensure “Automatically connect devices in this group” is on for “zero touch” provisioning) and copy the IDScope and Group Enrollment key to the TTI Connector configuration

RAK3172 Enrollment Group creation
Azure IoT Hub Device Provisioning Service configuration

I then created an Azure IoT Central template for my RAK3172 breakout board based.Net Core powered test device.

{
    "@id": "dtmi:ttnv3connectorclient:RASK3172Breakout1c7;1",
    "@type": "Interface",
    "contents": [
        {
            "@id": "dtmi:ttnv3connectorclient:RASK3172Breakout1c7:temperature_0;1",
            "@type": [
                "Telemetry",
                "Temperature"
            ],
            "displayName": {
                "en": "Temperature"
            },
            "name": "temperature_0",
            "schema": "double",
            "unit": "degreeCelsius"
        },
        {
            "@id": "dtmi:ttnv3connectorclient:RASK3172Breakout1c7:relative_humidity_0;1",
            "@type": [
                "Telemetry",
                "RelativeHumidity"
            ],
            "displayName": {
                "en": "Humidity"
            },
            "name": "relative_humidity_0",
            "schema": "double",
            "unit": "percent"
        },
        {
            "@id": "dtmi:ttnv3connectorclient:RASK3172Breakout1c7:value_0;1",
            "@type": "Command",
            "displayName": {
                "en": "Temperature OOB alert minimum"
            },
            "name": "value_0",
            "request": {
                "@type": "CommandPayload",
                "displayName": {
                    "en": "Minimum"
                },
                "name": "value_0",
                "schema": "double"
            },
            "durable": true
        },
        {
            "@id": "dtmi:ttnv3connectorclient:RASK3172Breakout1c7:value_1;1",
            "@type": "Command",
            "displayName": {
                "en": "Temperature OOB alert maximum"
            },
            "name": "value_1",
            "request": {
                "@type": "CommandPayload",
                "displayName": {
                    "en": "Maximum"
                },
                "name": "value_1",
                "schema": "double"
            },
            "durable": true
        },
        {
            "@id": "dtmi:ttnv3connectorclient:RASK3172Breakout1c7:TemperatureOOBAlertMinimumAndMaximum;1",
            "@type": "Command",
            "displayName": {
                "en": "Temperature OOB alert minimum and maximum"
            },
            "name": "TemperatureOOBAlertMinimumAndMaximum",
            "request": {
                "@type": "CommandPayload",
                "displayName": {
                    "en": "Alert Temperature"
                },
                "name": "AlertTemperature",
                "schema": {
                    "@type": "Object",
                    "displayName": {
                        "en": "Object"
                    },
                    "fields": [
                        {
                            "displayName": {
                                "en": "minimum"
                            },
                            "name": "value_0",
                            "schema": "double"
                        },
                        {
                            "displayName": {
                                "en": "maximum"
                            },
                            "name": "value_1",
                            "schema": "double"
                        }
                    ]
                }
            },
            "durable": true
        },
        {
            "@id": "dtmi:ttnv3connectorclient:RASK3172Breakout1c7:value_2;1",
            "@type": "Command",
            "displayName": {
                "en": "Fan"
            },
            "name": "value_2",
            "request": {
                "@type": "CommandPayload",
                "displayName": {
                    "en": "On"
                },
                "name": "value_3",
                "schema": {
                    "@type": "Enum",
                    "displayName": {
                        "en": "Enum"
                    },
                    "enumValues": [
                        {
                            "displayName": {
                                "en": "On"
                            },
                            "enumValue": 1,
                            "name": "On"
                        },
                        {
                            "displayName": {
                                "en": "Off"
                            },
                            "enumValue": 0,
                            "name": "Off"
                        }
                    ],
                    "valueSchema": "integer"
                }
            },
            "durable": true
        },
        {
            "@id": "dtmi:ttnv3connectorclient:RASK3172Breakout1c7:LightsGoOn;1",
            "@type": "Command",
            "displayName": {
                "en": "LightsGoOn"
            },
            "name": "LightsGoOn",
            "durable": true
        },
        {
            "@id": "dtmi:ttnv3connectorclient:RASK3172Breakout1c7:LightsGoOff;1",
            "@type": "Command",
            "displayName": {
                "en": "LightsGoOff"
            },
            "name": "LightsGoOff",
            "durable": true
        }
    ],
    "displayName": {
        "en": "RASK3172 Breakout"
    },
    "@context": [
        "dtmi:iotcentral:context;2",
        "dtmi:dtdl:context;2"
    ]
}

The Device Template @Id can also be set for a TTI application using an optional dtdlmodelid which is specified the the TTI Connector configuration.

RAK7258 Local server and Message Queuing Telemetry Transport(MQTT)

This post was originally about getting the built in Network Server of my RAKWireless RAK7258 WisGate Edge Lite to connect to an Azure IoT Hub or Azure IoT Central. The RAK7258 had been connected to The Things Industries(TTI) network so I updated the firmware and checked the “mode” in the LoRaWAN Network settings.

RAK 7258 LoRaWAN Network settings

Azure IoT Hub is not a fully featured MQTT broker so I initially looked at running Eclipse Mosquitto or HiveMQ locally but this seemed like a lot of effort for a Proof of Concept(PoC).

RAK 7258 Network Server Global Integration settings

I have used MQTTNet in a few other projects (The Things Network(TTN) V3 Azure IoT Connector, The Things Network V2 MQTT SQL Connector, Windows 10 IoT Core MQTT Field gateway etc.) and there was a sample application which showed ho to build a simple server so that became my preferred approach.

I then started exploring how applications and devices are provisioned in the RAK Network Server.

RAK 7258 Network Server applications list

The network server software has “unified” and “separate” “Device authentication mode”s and will “auto Add LoRa Device”s if enabled.

RAK 7258 Network Server Separate Application basic setup
RAK 7258 Network Server Separate Application device basic setup
RAK 7258 Network Server Unified Application device basic setup

Applications also have configurable payload formats(raw & CayenneLPP) and integrations (uplink messages plus join, ack, and device notifications etc.)

RAK7258 live device data display

In the sample server I could see how ValidatingConnectionAsync was used to check the clientID, username and password when a device connected. I just wanted to display messages and payloads without having to use an MQTT client and it looked like InterceptingPublishAsync was a possible solution.

But the search results were a bit sparse…

InterceptingPublishAsync + MQTTNet search results

After some reading the MQTTNet documentation and some experimentation I could display the message payload (same as in the live device data display) in a “nasty” console application.

namespace devMobile.IoT.RAKWisgate.ServerBasic
{
   using System;
	using System.Threading.Tasks;

   using MQTTnet;
   using MQTTnet.Protocol;
   using MQTTnet.Server;

   public static class Program
   {
      static async Task Main(string[] args)
      {
         var mqttFactory = new MqttFactory();

         var mqttServerOptions = new MqttServerOptionsBuilder()
             .WithDefaultEndpoint()
             .Build();

         using (var mqttServer = mqttFactory.CreateMqttServer(mqttServerOptions))
         {
            mqttServer.InterceptingPublishAsync += e =>
            {
               Console.WriteLine($"Client:{e.ClientId} Topic:{e.ApplicationMessage.Topic} {e.ApplicationMessage.ConvertPayloadToString()}");

               return Task.CompletedTask;
            };

            mqttServer.ValidatingConnectionAsync += e =>
            {
               if (e.ClientId != "RAK Wisgate7258")
               {
                  e.ReasonCode = MqttConnectReasonCode.ClientIdentifierNotValid;
               }

               if (e.Username != "ValidUser")
               {
                  e.ReasonCode = MqttConnectReasonCode.BadUserNameOrPassword;
               }

               if (e.Password != "TopSecretPassword")
               {
                  e.ReasonCode = MqttConnectReasonCode.BadUserNameOrPassword;
               }

               return Task.CompletedTask;
            };

            await mqttServer.StartAsync();

            Console.WriteLine("Press Enter to exit.");
            Console.ReadLine();

            await mqttServer.StopAsync();
         }
      }
   }
}
MQTTNet based console application displaying device payloads

The process of provisioning Applications and Devices is quite different (The use of the AppEUI/JoinEUI is odd) to The Things Network(TTN) and other platforms I have used so I will explore this some more in future post(s).

.NET Core web API + Dapper – MultiMapping

Shaping recordsets with SplitOn

Sometimes there is no easy way to build a “list of lists” using the contents of multiple database tables. I have run into this problem a few times especially when building webby services which query the database of a “legacy” (aka. production) system.

Retrieving a list of StockGroups and their StockItems from the World Wide Importers database was one of the better “real world” examples I could come up with.

SQL Server Management Studio Diagram showing relationships of tables

There is a fair bit of duplication (StockGroupID, StockGroupName) in the results set

SQL Server Management Studio StockItems-StockItemStockGroups-StockGroups query and results

There were 442 rows in the results set and 227 StockItems in the database so I ordered the query results by StockItemID and confirmed that there were many StockItems in several StockGroups.

public class StockItemListDtoV1
{
	public int Id { get; set; }

	public string Name { get; set; }

	public decimal RecommendedRetailPrice { get; set; }

	public decimal TaxRate { get; set; }
}

public class StockGroupStockItemsListDto
{
	StockGroupStockItemsListDto()
	{
		StockItems = new List<StockItemListDto>();
	}

	public int StockGroupID { get; set; }

	public string StockGroupName { get; set; }

	public List<StockItemListDto> StockItems { get; set; }
}

My initial version uses a Generic List for a StockGroup’s StockItems which is most probably not a good idea.

[Route("api/[controller]")]
[ApiController]
public class InvoiceQuerySplitOnController : ControllerBase
{
	private readonly string connectionString;
	private readonly ILogger<InvoiceQuerySplitOnController> logger;

	public InvoiceQuerySplitOnController(IConfiguration configuration, ILogger<InvoiceQuerySplitOnController> logger)
	{
		this.connectionString = configuration.GetConnectionString("WorldWideImportersDatabase");

		this.logger = logger;
	}

	[HttpGet]
	public async Task<ActionResult<IAsyncEnumerable<StockGroupStockItemsListDto>>> Get()
	{
		IEnumerable<StockGroupStockItemsListDto> response = null;

		try
		{
			using (SqlConnection db = new SqlConnection(this.connectionString))
			{
				var stockGroups = await db.QueryAsync<StockGroupStockItemsListDto, StockItemListDto, StockGroupStockItemsListDto>(
					sql: @"SELECT [StockGroups].[StockGroupID] as 'StockGroupID'" +
								",[StockGroups].[StockGroupName]" +
								",[StockItems].StockItemID as 'ID'" +
								",[StockItems].StockItemName as 'Name'" +
								",[StockItems].TaxRate" +
								",[StockItems].RecommendedRetailPrice " +
							"FROM [Warehouse].[StockGroups] " +
							"INNER JOIN[Warehouse].[StockItemStockGroups] ON ([StockGroups].[StockGroupID] = [StockItemStockGroups].[StockGroupID])" +
							"INNER JOIN[Warehouse].[StockItems] ON ([Warehouse].[StockItemStockGroups].[StockItemID] = [StockItems].[StockItemID])",
					(stockGroup, stockItem) =>
					{
						// Not certain I think using a List<> here is a good idea...
						stockGroup.StockItems.Add(stockItem);
						return stockGroup;
					},
				splitOn: "ID",
				commandType: CommandType.Text);

			response = stockGroups.GroupBy(p => p.StockGroupID).Select(g =>
			{
				var groupedStockGroup = g.First();
				groupedStockGroup.StockItems = g.Select(p => p.StockItems.Single()).ToList();
				return groupedStockGroup;
			});
		}
	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "Retrieving S, Invoice Lines or Stock Item Transactions");

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}

The MultiMapper syntax always trips me up and it usually takes a couple of attempts to get it to work.

List of StockGroups with StockItems

I have extended my DapperTransient module adding WithRetry versions of the 14 MultiMapper methods.

.NET Core web API + Dapper – QueryMultiple

Returning multiple recordsets

My current “day job” is building applications for managing portfolios of foreign currency instruments. A portfolio can contain many different types of instrument (Forwards, Options, Swaps etc.). One of the “optimisations” we use is retrieving all the different types of instruments in a portfolio with one stored procedure call.

SQL Server Management Studio Dependency viewer

The closest scenario I could come up with using the World Wide Importers database was retrieving a summary of all the information associated with an Invoice for display on a single screen.

CREATE PROCEDURE [Sales].[InvoiceSummaryGetV1](@InvoiceID as int)
AS
BEGIN

SELECT [InvoiceID]
--        ,[CustomerID]
--        ,[BillToCustomerID]
		,[OrderID]
		,[Invoices].[DeliveryMethodID]
		,[DeliveryMethodName]
--        ,[ContactPersonID]
--        ,[AccountsPersonID]
		,[SalespersonPersonID] as SalesPersonID
		,[SalesPerson].[PreferredName] as SalesPersonName
--        ,[PackedByPersonID]
		,[InvoiceDate]
		,[CustomerPurchaseOrderNumber]
		,[IsCreditNote]
		,[CreditNoteReason]
		,[Comments]
		,[DeliveryInstructions]
--        ,[InternalComments]
--        ,[TotalDryItems]
--        ,[TotalChillerItems]
		,[DeliveryRun]
		,[RunPosition] as DeliveryRunPosition
		,[ReturnedDeliveryData] as DeliveryData
		,[ConfirmedDeliveryTime] as DeliveredAt
		,[ConfirmedReceivedBy] as DeliveredTo
--        ,[LastEditedBy]
--        ,[LastEditedWhen]
	FROM [Sales].[Invoices]
	INNER JOIN [Application].[People] as SalesPerson ON (Invoices.[SalespersonPersonID] = [SalesPerson].[PersonID])
	INNER JOIN [Application].[DeliveryMethods] as DeliveryMethod ON (Invoices.[DeliveryMethodID] = DeliveryMethod.[DeliveryMethodID])
WHERE ([Invoices].[InvoiceID] = @InvoiceID)

SELECT [InvoiceLineID]
      ,[InvoiceID]
      ,[StockItemID]
      ,[Description] as StockItemDescription
      ,[InvoiceLines].[PackageTypeID]
	  ,[PackageType].[PackageTypeName]
      ,[Quantity]
      ,[UnitPrice]
      ,[TaxRate]
      ,[TaxAmount]
--      ,[LineProfit]
      ,[ExtendedPrice]
--      ,[LastEditedBy]
--      ,[LastEditedWhen]
	FROM [Sales].[InvoiceLines]
		INNER JOIN [Warehouse].[PackageTypes] as PackageType ON ([PackageType].[PackageTypeID] = [InvoiceLines].[PackageTypeID])
WHERE ([InvoiceLines].[InvoiceID] = @InvoiceID)

SELECT [StockItemTransactionID]
      ,[StockItemTransactions].[StockItemID]
      ,StockItem.[StockItemName] as StockItemName
      ,[StockItemTransactions].[TransactionTypeID]
      ,[TransactionType].[TransactionTypeName]
--      ,[CustomerID]
--      ,[InvoiceID]
--      ,[SupplierID]
--      ,[PurchaseOrderID]
      ,[TransactionOccurredWhen] as TransactionAt
      ,[Quantity]
--      ,[LastEditedBy]
--      ,[LastEditedWhen]
	FROM [Warehouse].[StockItemTransactions]
	INNER JOIN [Warehouse].[StockItems] as StockItem ON ([StockItemTransactions].StockItemID = [StockItem].StockItemID)
	INNER JOIN [Application].[TransactionTypes] as TransactionType ON ([StockItemTransactions].[TransactionTypeID] = TransactionType.[TransactionTypeID])
	WHERE ([StockItemTransactions].[InvoiceID] = @InvoiceID)

END

The stored procedure returns 3 recordsets, a “summary” of the Order, a summary of the associated OrderLines and a summary of the associated StockItemTransactions.

public async Task<ActionResult<Model.InvoiceSummaryGetDtoV1>>Get([Range(1, int.MaxValue, ErrorMessage = "Invoice id must greater than 0")] int id)
{
	Model.InvoiceSummaryGetDtoV1 response = null;

	try
	{
		using (SqlConnection db = new SqlConnection(this.connectionString))
		{
			var invoiceSummary = await db.QueryMultipleWithRetryAsync("[Sales].[InvoiceSummaryGetV1]", param: new { InvoiceId = id }, commandType: CommandType.StoredProcedure);

			response = await invoiceSummary.ReadSingleOrDefaultWithRetryAsync<Model.InvoiceSummaryGetDtoV1>();
			if (response == default)
			{
				logger.LogInformation("Invoice:{0} not found", id);

				return this.NotFound($"Invoice:{id} not found");
			}

			response.InvoiceLines = (await invoiceSummary.ReadWithRetryAsync<Model.InvoiceLineSummaryListDtoV1>()).ToArray();

			response.StockItemTransactions = (await invoiceSummary.ReadWithRetryAsync<Model.StockItemTransactionSummaryListDtoV1>()).ToArray();
		}
	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "Retrieving Invoice, Invoice Lines or Stock Item Transactions");

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}

I use Google Chrome, Mozilla Firefox, Microsoft Edgeium, and Opera but the screen capture was done with FireFox mainly because it formats the Java Script Object Notation(JSON) response payloads nicely.

FireFox displaying Invoice Summary response

I had to extend the DapperTransient module to add SqlMapper extension (plus all the different overloads) retry methods.

Azure Smartish Edge Camera – Background Service

This is a “note to self” post about deploying a .NET Core Worker Service to a Raspberry PI 4B 8G running Raspberry PI OS (Bullseye). After reading many posts, then a lot of trial and error this approach appeared to work reliably for my system configuration.(Though YMMV with other distros etc.)

The first step was to create a new Worker Service project in Visual Studio 2019

VS 2019 Add new Worker Service project wizard
VS 2019 Add new Worker Service project name
Visual Studio 2019 NuGet management

I intentionally did not update the Microsoft.Extensions.Hosting and Microsoft.Extensions.Hosting.Systemd (for UseSystemd) NuGet packages for my initial development.

using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;

namespace devMobile.IoT.MachineLearning.AzureIoTSmartEdgeCameraService
{
	public class Program
	{
		public static void Main(string[] args)
		{
			CreateHostBuilder(args).Build().Run();
		}

		public static IHostBuilder CreateHostBuilder(string[] args) =>
			 Host.CreateDefaultBuilder(args)
					.UseSystemd()
				  .ConfigureServices((hostContext, services) =>
				  {
					  services.AddHostedService<Worker>();
				  });
	}
}

program.cs

using System;
using System.Threading;
using System.Threading.Tasks;

using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;

namespace devMobile.IoT.MachineLearning.AzureIoTSmartEdgeCameraService
{
	public class Worker : BackgroundService
	{
		private readonly ILogger<Worker> _logger;

		public Worker(ILogger<Worker> logger)
		{
			_logger = logger;
		}

		protected override async Task ExecuteAsync(CancellationToken stoppingToken)
		{
			while (!stoppingToken.IsCancellationRequested)
			{
				_logger.LogInformation("Worker running at: {time}", DateTimeOffset.Now);

				await Task.Delay(1000, stoppingToken);
			}
		}
	}
}

Worker.cs

The first step was to create a new directory (AzureIoTSmartEdgeCameraService) on the device. In Visual Studio 2019 I “published” my application and copied the contents of the “publish” folder to the Raspberry PI with Winscp. (This intermediary folder was to avoid issues with the permissions of the /usr/sbin/ & etc/systemd/system folders)

Using Winscp to copy files to AzureIoTSmartEdgeCameraService folder on my device
Install service

Test in application directory
	/home/pi/.dotnet/dotnet AzureIoTSmartEdgeCameraService.dll

Make service directory
	sudo mkdir /usr/sbin/AzureIoTSmartEdgeCameraService

Copy files to service directory
	sudo cp *.* /usr/sbin/AzureIoTSmartEdgeCameraService

Copy .service file to systemd folderclear
	sudo cp AzureIoTSmartEdgeCameraService.service /etc/systemd/system/AzureIoTSmartEdgeCameraService.service
 
Force reload of systemd configuration
	sudo systemctl daemon-reload

Start the Azure IoT SmartEdge Camera service
	sudo systemctl start AzureIoTSmartEdgeCameraService
Installing and starting the AzureIoTSmartEdgeCameraService
Uninstall service
	sudo systemctl stop AzureIoTSmartEdgeCameraService

	sudo rm /etc/systemd/system/AzureIoTSmartEdgeCameraService.service

	sudo systemctl daemon-reload

	sudo rm /usr/sbin/AzureIoTSmartEdgeCameraService/*.*

	sudo rmdir /usr/sbin/AzureIoTSmartEdgeCameraService

	See what is happening
	journalctl -xe

Stopping and removing the AzureIoTSmartEdgeCameraService

It took a lot of attempts to get a clean install then uninstall for the screen captures.

.NET Core web API + Dapper – Caching

Response Cache

In the beginning this was long long post about In-memory caching, distributed caching, Response caching, Response caching with middleware and Object reuse with ObjectPool. As I was re-reading the post before publishing it I came to the realisation that these different caching approaches didn’t require Dapper.

I started again, but kept the first section as it covers one of the simplest possible approaches to caching using the [ResponseCache] attribute and VaryByQueryKeys.

[HttpGet("Response")]
[ResponseCache(Duration = StockItemsListResponseCacheDuration)]
public async Task<ActionResult<IAsyncEnumerable<Model.StockItemListDtoV1>>> GetResponse()
{
	IEnumerable<Model.StockItemListDtoV1> response = null;

	logger.LogInformation("Response cache load");

	try
	{
		response = await dapper.QueryAsync<Model.StockItemListDtoV1>(sql: @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItems]", commandType: CommandType.Text);
	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "Retrieving list of StockItems");

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}

[HttpGet("ResponseVarying")]
[ResponseCache(Duration = StockItemsListResponseCacheDuration, VaryByQueryKeys = new string[] { "id" })]
public async Task<ActionResult<Model.StockItemGetDtoV1>> Get([FromQuery(Name = "id"), Range(1, int.MaxValue, ErrorMessage = "Stock item id must greater than 0")] int id)
{
	Model.StockItemGetDtoV1 response = null;

	logger.LogInformation("Response cache varying load id:{0}", id);

	try
	{
		response = await dapper.QuerySingleOrDefaultAsync<Model.StockItemGetDtoV1>(sql: "[Warehouse].[StockItemsStockItemLookupV1]", 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, "Looking up StockItem with Id:{0}", id);

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}

I use Google Chrome, Mozilla Firefox, Microsoft Edgeium, and Opera but the screen captures have been done with FireFox mainly because it formats the Java Script Object Notation(JSON) response payloads nicely.

All the browsers appeared to respect the cache control headers but Firefox was the only one which did not initiate a new request when I pressed return in the Uniform Resource Locator(URL) field.

Firefox displaying list of stock items

I used Telerik Fiddler and FiddlerFox to capture the HTTP GET method request and response payloads.

Fiddler Fox extension details
Response payload for a list of StockItems with cache control headers highlighted
Firefox displaying single stock item
Response payload for a single StockItem with cache control headers highlighted

Dapper Cache

The Dapper Extensions Library has built in support for In-memory and Redis caching. The Dapper.Extensions Library extends Dapper’s functionality. It requires minimal configuration but I was tripped up by the default connection string requirement because I was using Dependency Injection

Dapper.Extensions NuGet package configuration

The configuration code in the application startup.cs supports in-memory and Redis caches.

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
	services.AddControllers();

	services.AddResponseCaching();

	services.AddDapperForMSSQL();

#if DAPPER_EXTENSIONS_CACHE_MEMORY
	services.AddDapperCachingInMemory(new MemoryConfiguration
	{
		AllMethodsEnableCache = false
	});
#endif
#if DAPPER_EXTENSIONS_CACHE_REDIS
	services.AddDapperCachingInRedis(new RedisConfiguration
	{
		AllMethodsEnableCache = false,
		KeyPrefix = Configuration.GetValue<string>("RedisKeyPrefix"),
		ConnectionString = Configuration.GetConnectionString("RedisConnection")
	}); 
#endif
	services.AddApplicationInsightsTelemetry();
}

The StockItemsCachingController was rewritten with the Dapper.Extensions QueryAsync and QuerySingleOrDefaultAsync methods.

[HttpGet("DapperMemory")]
public async Task<ActionResult<IAsyncEnumerable<Model.StockItemListDtoV1>>> GetDapper()
{
	List<Model.StockItemListDtoV1> response;

	logger.LogInformation("Dapper cache load");

	try
	{
		response = await dapper.QueryAsync<Model.StockItemListDtoV1>(
							sql: @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItems]",
							commandType: CommandType.Text,
							enableCache: true,
							cacheExpire: TimeSpan.Parse(this.Configuration.GetValue<string>("DapperCachingDuration"))
					);

	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "Retrieving list of StockItems");

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}

[HttpGet("DapperMemoryVarying")]
public async Task<ActionResult<Model.StockItemGetDtoV1>> GetDapperVarying([FromQuery(Name = "id"), Range(1, int.MaxValue, ErrorMessage = "Stock item id must greater than 0")] int id)
{
	Model.StockItemGetDtoV1 response = null;

	logger.LogInformation("Dapper cache varying load id:{0}", id);

	try
	{
		response = await dapper.QuerySingleOrDefaultAsync<Model.StockItemGetDtoV1>(
					sql: "[Warehouse].[StockItemsStockItemLookupV1]",
					param: new { stockItemId = id },
					commandType: CommandType.StoredProcedure,
					cacheKey: $"StockItem:{id}",
					enableCache: true,
					cacheExpire: TimeSpan.Parse(this.Configuration.GetValue<string>("DapperCachingDuration"))
							);
		if (response == default)
		{
			logger.LogInformation("StockItem:{0} not found", id);

			return this.NotFound($"StockItem:{id} not found");
		}
	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "Looking up StockItem with Id:{0}", id);

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}

Both the Dapper.Extensions In-Memory and Redis cache reduced the number of database requests to the bare minimum. In a larger application the formatting of the cacheKey (cacheKey: “StockItems” & cacheKey: $”StockItem:{id}”) would be important to stop database query result collisions.

SQL Server Profiler displaying the list and single record requests.

I used Memurai which is a Microsoft Windows version of Redis for testing on my development machine before deploying to Microsoft Azure and using Azure Cache for Redis. Memurai runs as a Windows Service and supports master, replica, cluster node or sentinel roles.

Memurai running as a Windows Service on my development machine

When the Web API project was restarted the contents in-memory cache were lost. The Redis cache contents survive a restart and can be access from multiple clients.

The Dapper.Extensions Query, QueryAsync, QueryFirstOrDefaultAsync, QuerySingleOrDefault, QuerySingleOrDefaultAsync, QueryMultiple, QueryMultipleAsync, ExecuteReader, ExecuteReaderAsync, QueryPageAsync, QueryPageAsync, QueryPlainPage, QueryPlainPageAsync, Execute, ExecuteAsync, ExecuteScalar, ExecuteScalarAsync, BeginTransaction, CommitTransactionm and RollbackTransaction do not appear to a versions which “Retry” actions when there is a “Transient” failure. If there is no solution available I will build one using the approach in my DapperTransient module.

libcamera-jpeg on Raspberry Pi OS Bullseye Duration

The image capture process was taking about 5 seconds which a bit longer than I was expecting.

libcamera-jpeg -o rotated.jpg --rotation 180

The libcamera-jpeg program has a lot of command line parameters.

pi@raspberrypi4a:~ $ libcamera-jpeg --help
Valid options are:
  -h [ --help ] [=arg(=1)] (=0)         Print this help message
  --version [=arg(=1)] (=0)             Displays the build version number
  -v [ --verbose ] [=arg(=1)] (=0)      Output extra debug and diagnostics
  -c [ --config ] [=arg(=config.txt)]   Read the options from a file. If no filename is specified, default to
                                        config.txt. In case of duplicate options, the ones provided on the command line
                                        will be used. Note that the config file must only contain the long form
                                        options.
  --info-text arg (=#%frame (%fps fps) exp %exp ag %ag dg %dg)
                                        Sets the information string on the titlebar. Available values:
                                        %frame (frame number)
                                        %fps (framerate)
                                        %exp (shutter speed)
                                        %ag (analogue gain)
                                        %dg (digital gain)
                                        %rg (red colour gain)
                                        %bg (blue colour gain)
                                        %focus (focus FoM value)
                                        %aelock (AE locked status)
  --width arg (=0)                      Set the output image width (0 = use default value)
  --height arg (=0)                     Set the output image height (0 = use default value)
  -t [ --timeout ] arg (=5000)          Time (in ms) for which program runs
  -o [ --output ] arg                   Set the output file name
  --post-process-file arg               Set the file name for configuring the post-processing
  --rawfull [=arg(=1)] (=0)             Force use of full resolution raw frames
  -n [ --nopreview ] [=arg(=1)] (=0)    Do not show a preview window
  -p [ --preview ] arg (=0,0,0,0)       Set the preview window dimensions, given as x,y,width,height e.g. 0,0,640,480
  -f [ --fullscreen ] [=arg(=1)] (=0)   Use a fullscreen preview window
  --qt-preview [=arg(=1)] (=0)          Use Qt-based preview window (WARNING: causes heavy CPU load, fullscreen not
                                        supported)
  --hflip [=arg(=1)] (=0)               Request a horizontal flip transform
  --vflip [=arg(=1)] (=0)               Request a vertical flip transform
  --rotation arg (=0)                   Request an image rotation, 0 or 180
  --roi arg (=0,0,0,0)                  Set region of interest (digital zoom) e.g. 0.25,0.25,0.5,0.5
  --shutter arg (=0)                    Set a fixed shutter speed
  --analoggain arg (=0)                 Set a fixed gain value (synonym for 'gain' option)
  --gain arg                            Set a fixed gain value
  --metering arg (=centre)              Set the metering mode (centre, spot, average, custom)
  --exposure arg (=normal)              Set the exposure mode (normal, sport)
  --ev arg (=0)                         Set the EV exposure compensation, where 0 = no change
  --awb arg (=auto)                     Set the AWB mode (auto, incandescent, tungsten, fluorescent, indoor, daylight,
                                        cloudy, custom)
  --awbgains arg (=0,0)                 Set explict red and blue gains (disable the automatic AWB algorithm)
  --flush [=arg(=1)] (=0)               Flush output data as soon as possible
  --wrap arg (=0)                       When writing multiple output files, reset the counter when it reaches this
                                        number
  --brightness arg (=0)                 Adjust the brightness of the output images, in the range -1.0 to 1.0
  --contrast arg (=1)                   Adjust the contrast of the output image, where 1.0 = normal contrast
  --saturation arg (=1)                 Adjust the colour saturation of the output, where 1.0 = normal and 0.0 =
                                        greyscale
  --sharpness arg (=1)                  Adjust the sharpness of the output image, where 1.0 = normal sharpening
  --framerate arg (=30)                 Set the fixed framerate for preview and video modes
  --denoise arg (=auto)                 Sets the Denoise operating mode: auto, off, cdn_off, cdn_fast, cdn_hq
  --viewfinder-width arg (=0)           Width of viewfinder frames from the camera (distinct from the preview window
                                        size
  --viewfinder-height arg (=0)          Height of viewfinder frames from the camera (distinct from the preview window
                                        size)
  --tuning-file arg (=-)                Name of camera tuning file to use, omit this option for libcamera default
                                        behaviour
  --lores-width arg (=0)                Width of low resolution frames (use 0 to omit low resolution stream
  --lores-height arg (=0)               Height of low resolution frames (use 0 to omit low resolution stream
  -q [ --quality ] arg (=93)            Set the JPEG quality parameter
  -x [ --exif ] arg                     Add these extra EXIF tags to the output file
  --timelapse arg (=0)                  Time interval (in ms) between timelapse captures
  --framestart arg (=0)                 Initial frame counter value for timelapse captures
  --datetime [=arg(=1)] (=0)            Use date format for output file names
  --timestamp [=arg(=1)] (=0)           Use system timestamps for output file names
  --restart arg (=0)                    Set JPEG restart interval
  -k [ --keypress ] [=arg(=1)] (=0)     Perform capture when ENTER pressed
  -s [ --signal ] [=arg(=1)] (=0)       Perform capture when signal received
  --thumb arg (=320:240:70)             Set thumbnail parameters as width:height:quality
  -e [ --encoding ] arg (=jpg)          Set the desired output encoding, either jpg, png, rgb, bmp or yuv420
  -r [ --raw ] [=arg(=1)] (=0)          Also save raw file in DNG format
  --latest arg                          Create a symbolic link with this name to most recent saved file
  --immediate [=arg(=1)] (=0)           Perform first capture immediately, with no preview phase
pi@raspberrypi4a:~ $

My libcamera-jpeg application is run “headless” so I tried turning off the image preview functionality.

libcamera-jpeg -o rotatednopreview.jpg --nopreview

When I ran libcamera-jpeg in a console windows or my application this didn’t appear to make any noticeable difference.

libcamera-jpeg run from the command line with –nopreview

libcamera-jpeg run by my application with –nopreview

I then had another look at the libcamera-jpeg command line parameters to see if any looked useful for reducing the time that it took to take a save an image and this one caught my attention.

I had assumed the delay was related to how long the preview window was displayed.

libcamera-jpeg run from the command line with –nopreview –t1

I modified the application (V5) then ran it from the command line and the time reduced to less than a second.

private static void ImageUpdateTimerCallback(object state)
{
	try
	{
		Console.WriteLine($"{DateTime.UtcNow:yy-MM-dd HH:mm:ss} Image update start");

		// Just incase - stop code being called while photo already in progress
		if (_cameraBusy)
		{
			return;
		}

		Console.WriteLine($" {DateTime.UtcNow:yy-MM-dd HH:mm:ss} Image capture start");

		using (Process process = new Process())
		{
			process.StartInfo.FileName = @"libcamera-jpeg";
			// V1 it works
			//process.StartInfo.Arguments = $"-o {_applicationSettings.ImageFilenameLocal}";
			// V3a Image right way up
			//process.StartInfo.Arguments = $"-o {_applicationSettings.ImageFilenameLocal} --vflip --hflip";
			// V3b Image right way up
			//process.StartInfo.Arguments = $"-o {_applicationSettings.ImageFilenameLocal} --rotation 180";
			// V4 Image no preview
			//process.StartInfo.Arguments = $"-o {_applicationSettings.ImageFilenameLocal} --rotation 180 --nopreview";
			// V5 Image no preview, no timeout
			process.StartInfo.Arguments = $"-o {_applicationSettings.ImageFilenameLocal} --nopreview -t1 --rotation 180";
			//process.StartInfo.RedirectStandardOutput = true;
			// V2 No diagnostics
			process.StartInfo.RedirectStandardError = true;
			//process.StartInfo.UseShellExecute = false;
			//process.StartInfo.CreateNoWindow = true; 

			process.Start();

			if (!process.WaitForExit(10000) || (process.ExitCode != 0))
			{
				Console.WriteLine($"{DateTime.UtcNow:yy-MM-dd HH:mm:ss} Image update failure {process.ExitCode}");
			}
		}

		Console.WriteLine($" {DateTime.UtcNow:yy-MM-dd HH:mm:ss} Image capture done");
	}
	catch (Exception ex)
	{
		Console.WriteLine($"{DateTime.UtcNow:yy-MM-dd HH:mm:ss} Image update error {ex.Message}");
	}
	finally
	{
		_cameraBusy = false;
	}
}
libcamera-jpeg run by my application with –nopreview -t1

The image capture process now takes less that a second which is much better (but not a lot less than retrieving an image from one of my security cameras).

libcamera on Raspberry Pi OS Bullseye

This is a “note to self” post about using libcamera(replacement for raspistill) on my Raspberry PI 4 Model B to capture an image from my Raspberry Pi Camera Module 2 with an application built with .NET Core.

I wanted one of my ML.Net demos to use the Raspberry PI Camera rather than a security camera (so it was more portable) but it took a bit more work than I expected.

Version 1 used Process.Start to launch the libcamera-jpeg application with a command line to store an image to the local file system.

libcamera-jpeg -o latest.jpg
libcamera-jpeg with diagnostic information displayed

There was a lot of diagnostic information which I didn’t want displayed so after reading many stackoverflow posts (lots of different approaches none of which worked in my scenario), then some trial and error I found that I only had to enable RedirectStandardError.

libcamera-jpeg without diagnostic information displayed

At this point there was a lot less noise but the image was upside down.

Inverted picture of my 30th anniversary Mini Cooper in the backyard

I then added a vertical flip to the command line parameters

libcamera-jpeg -o latest.jpg --vflip
My 30th anniversary Mini Cooper in the backyard

The image was backwards so I added a horizontal flip to the commandline parameters

libcamera-jpeg -o latest.jpg --vflip --hflip

or

libcamera-jpeg -o latest.jpg --rotation 180
My 30th anniversary Mini Cooper in the backyard with the correct orientation

The libcamera code is in a Timer callback so I added the _cameraBusy boolean flag to stop reentrancy problems.

private static void ImageUpdateTimerCallback(object state)
{
	try
	{
		Console.WriteLine($"{DateTime.UtcNow:yy-MM-dd HH:mm:ss} Image update start");

		// Just incase - stop code being called while photo already in progress
		if (_cameraBusy)
		{
			return;
		}

		Console.WriteLine($" {DateTime.UtcNow:yy-MM-dd HH:mm:ss} Image capture start");

		using (Process process = new Process())
		{
			process.StartInfo.FileName = @"libcamera-jpeg";
			// V1 it works
			//process.StartInfo.Arguments = $"-o {_applicationSettings.ImageFilenameLocal}";
			// V3 Image right way up
			//process.StartInfo.Arguments = $"-o {_applicationSettings.ImageFilenameLocal} --vflip";
			// V3 Image right way round
			process.StartInfo.Arguments = $"-o {_applicationSettings.ImageFilenameLocal} --vflip --hflip";
			//process.StartInfo.RedirectStandardOutput = true;
			// V2 No diagnostics
			process.StartInfo.RedirectStandardError = true;
			//process.StartInfo.UseShellExecute = false;
			//process.StartInfo.CreateNoWindow = true; 

			process.Start();

			if (!process.WaitForExit(10000) || (process.ExitCode != 0))
			{
				Console.WriteLine($"{DateTime.UtcNow:yy-MM-dd HH:mm:ss} Image update failure {process.ExitCode}");
			}
		}

		Console.WriteLine($" {DateTime.UtcNow:yy-MM-dd HH:mm:ss} Image capture done");
	}
	catch (Exception ex)
	{
		Console.WriteLine($"{DateTime.UtcNow:yy-MM-dd HH:mm:ss} Image update error {ex.Message}");
	}
	finally
	{
		_cameraBusy = false;
	}
}

This was the simplest way I could get an image onto the local file system without lots of dependencies on third party libraries. The image capture process takes about 5 seconds which a bit longer than I was expecting.

TTI V3 Connector Device Provisioning Service(DPS) support

The previous versions of my Things Network Industries(TTI) and The Things Network(TTN) connectors supported the Azure IoT Hub Device Provisioning Service(DPS) with Symmetric Key Attestation(SAS) to “automagically” setup the LoRaWAN devices in a TTI Application.(See my V2 Gateway DPS setup post for more detail).

Azure Device Provisioning Service configuring Azure IoT Hubs

I used an “evenly weighted distribution” to spread the devices across five Azure IoT Hubs.

Azure IoT Hub no registered devices

In the Azure Portal I configured the DPS ID Scope (AzureSettings:DeviceProvisioningServiceSettings:IdScope) and the Group Enrollment Key(AzureSettings:DeviceProvisioningServiceSettings:GroupEnrollmentKey) then saved the configuration which restarted the AppService.

Azure Portal AppService configration

The first time a device sent an uplink message the cache query fails and the RegisterAsync method of the ProvisioningDeviceClient is called to get a device connection string.

	logger.LogInformation("Uplink-ApplicationID:{0} DeviceID:{1} Port:{2} Payload Raw:{3}", applicationId, deviceId, port, payload.UplinkMessage.PayloadRaw);

	if (!_DeviceClients.TryGetValue(deviceId, out DeviceClient deviceClient))
	{
		logger.LogInformation("Uplink-Unknown device for ApplicationID:{0} DeviceID:{1}", applicationId, deviceId);

		// Check that only one of Azure Connection string or DPS is configured
		if (string.IsNullOrEmpty(_azureSettings.IoTHubConnectionString) && (_azureSettings.DeviceProvisioningServiceSettings == null))
		{
			logger.LogError("Uplink-Neither Azure IoT Hub connection string or Device Provisioning Service configured");

			return req.CreateResponse(HttpStatusCode.UnprocessableEntity);
		}

		// Check that only one of Azure Connection string or DPS is configured
		if (!string.IsNullOrEmpty(_azureSettings.IoTHubConnectionString) && (_azureSettings.DeviceProvisioningServiceSettings != null))
		{
			logger.LogError("Uplink-Both Azure IoT Hub connection string and Device Provisioning Service configured");

			return req.CreateResponse(HttpStatusCode.UnprocessableEntity);
		}

		// User Azure IoT Connection string if configured and Device Provisioning Service isn't
		if (!string.IsNullOrEmpty(_azureSettings.IoTHubConnectionString))
		{
			deviceClient = DeviceClient.CreateFromConnectionString(_azureSettings.IoTHubConnectionString, deviceId, transportSettings);

			try
			{
				await deviceClient.OpenAsync();
			}
			catch (DeviceNotFoundException)
			{
				logger.LogWarning("Uplink-Unknown DeviceID:{0}", deviceId);

				return req.CreateResponse(HttpStatusCode.NotFound);
			}
		}

		// Azure IoT Hub Device provisioning service if configured
		if (_azureSettings.DeviceProvisioningServiceSettings != null) 
		{
			string deviceKey;

			if ( string.IsNullOrEmpty(_azureSettings.DeviceProvisioningServiceSettings.IdScope) || string.IsNullOrEmpty(_azureSettings.DeviceProvisioningServiceSettings.GroupEnrollmentKey))
			{
				logger.LogError("Uplink-Device Provisioning Service requires ID Scope and Group Enrollment Key configured");

				return req.CreateResponse(HttpStatusCode.UnprocessableEntity);
			}

			using (var hmac = new HMACSHA256(Convert.FromBase64String(_azureSettings.DeviceProvisioningServiceSettings.GroupEnrollmentKey)))
			{
				deviceKey = Convert.ToBase64String(hmac.ComputeHash(Encoding.UTF8.GetBytes(deviceId)));
			}

			using (var securityProvider = new SecurityProviderSymmetricKey(deviceId, deviceKey, null))
			{
				using (var transport = new ProvisioningTransportHandlerAmqp(TransportFallbackType.TcpOnly))
				{
					ProvisioningDeviceClient provClient = ProvisioningDeviceClient.Create(
						Constants.AzureDpsGlobalDeviceEndpoint,
						_azureSettings.DeviceProvisioningServiceSettings.IdScope,
						securityProvider,
						transport);

					DeviceRegistrationResult result = await provClient.RegisterAsync();

					if (result.Status != ProvisioningRegistrationStatusType.Assigned)
					{
						_logger.LogError("Config-DeviceID:{0} Status:{1} RegisterAsync failed ", deviceId, result.Status);

						return req.CreateResponse(HttpStatusCode.FailedDependency);
					}

					IAuthenticationMethod authentication = new DeviceAuthenticationWithRegistrySymmetricKey(result.DeviceId, (securityProvider as SecurityProviderSymmetricKey).GetPrimaryKey());

					deviceClient = DeviceClient.Create(result.AssignedHub, authentication, transportSettings);

					await deviceClient.OpenAsync();
				}
			}
		}

		if (!_DeviceClients.TryAdd(deviceId, deviceClient))
		{
			logger.LogWarning("Uplink-TryAdd failed for ApplicationID:{0} DeviceID:{1}", applicationId, deviceId);

			return req.CreateResponse(HttpStatusCode.Conflict);
		}

		Models.AzureIoTHubReceiveMessageHandlerContext context = new Models.AzureIoTHubReceiveMessageHandlerContext()
		{
			DeviceId = deviceId,
			ApplicationId = applicationId,
			WebhookId = _theThingsIndustriesSettings.WebhookId,
			WebhookBaseURL = _theThingsIndustriesSettings.WebhookBaseURL,
			ApiKey = _theThingsIndustriesSettings.ApiKey
		};

		await deviceClient.SetReceiveMessageHandlerAsync(AzureIoTHubClientReceiveMessageHandler, context);

		await deviceClient.SetMethodDefaultHandlerAsync(AzureIoTHubClientDefaultMethodHandler, context);
	}

	JObject telemetryEvent = new JObject
	{
		{ "ApplicationID", applicationId },
		{ "DeviceID", deviceId },
		{ "Port", port },
		{ "Simulated", payload.Simulated },
		{ "ReceivedAtUtc", payload.UplinkMessage.ReceivedAtUtc.ToString("s", CultureInfo.InvariantCulture) },
		{ "PayloadRaw", payload.UplinkMessage.PayloadRaw }
	};

	// If the payload has been decoded by payload formatter, put it in the message body.
	if (payload.UplinkMessage.PayloadDecoded != null)
	{
		telemetryEvent.Add("PayloadDecoded", payload.UplinkMessage.PayloadDecoded);
	}

	// Send the message to Azure IoT Hub
	using (Message ioTHubmessage = new Message(Encoding.ASCII.GetBytes(JsonConvert.SerializeObject(telemetryEvent))))
	{
		// Ensure the displayed time is the acquired time rather than the uploaded time. 
		ioTHubmessage.Properties.Add("iothub-creation-time-utc", payload.UplinkMessage.ReceivedAtUtc.ToString("s", CultureInfo.InvariantCulture));
		ioTHubmessage.Properties.Add("ApplicationId", applicationId);
		ioTHubmessage.Properties.Add("DeviceEUI", payload.EndDeviceIds.DeviceEui);
		ioTHubmessage.Properties.Add("DeviceId", deviceId);
		ioTHubmessage.Properties.Add("port", port.ToString());
		ioTHubmessage.Properties.Add("Simulated", payload.Simulated.ToString());

		await deviceClient.SendEventAsync(ioTHubmessage);

		logger.LogInformation("Uplink-DeviceID:{0} SendEventAsync success", payload.EndDeviceIds.DeviceId);
	}
}
catch (Exception ex)
{
	logger.LogError(ex, "Uplink-Message processing failed");

	return req.CreateResponse(HttpStatusCode.InternalServerError);
}

I used Telerik Fiddler and some sample payloads copied from my Azure Storage Queue sample to simulate many devices and the registrations were spread across my five Azure IoT Hubs.

DPS Device Registrations tab showing distribution of LoRaWAN Devices

I need to review the HTTP Error codes returned for different errors and ensure failures are handled robustly.