.NET nanoFramework RAK3172 Sleep

The RAKwireless RAK3172 module has “AT+SLEEP -Sleep mode command. To see how it worked I modified the BreakOutSerial application to send an AT-SLEEP command.

public static void Main()
{
#if SERIAL_THREADED_READ
	Thread readThread = new Thread(SerialPortProcessor);
#endif

	Debug.WriteLine("devMobile.IoT.LoRaWAN.nanoFramework.RAK3172 BreakoutSerial starting");

	try
	{
		// set GPIO functions for COM2 (this is UART1 on ESP32)
#if ESP32_WROOM
		Configuration.SetPinFunction(Gpio.IO17, DeviceFunction.COM2_TX);
		Configuration.SetPinFunction(Gpio.IO16, DeviceFunction.COM2_RX);
#endif

		Debug.Write("Ports:");
		foreach (string port in SerialPort.GetPortNames())
		{
			Debug.Write($" {port}");
		}
		Debug.WriteLine("");

		using (_SerialPort = new SerialPort(SerialPortId))
		{
			// set parameters
			_SerialPort.BaudRate = 115200;
			_SerialPort.Parity = Parity.None;
			_SerialPort.DataBits = 8;
			_SerialPort.StopBits = StopBits.One;
			_SerialPort.Handshake = Handshake.None;
			_SerialPort.NewLine = "\r\n";
			_SerialPort.ReadTimeout = 1000;

			//_SerialPort.WatchChar = '\n'; // May 2022 WatchChar event didn't fire github issue https://github.com/nanoframework/Home/issues/1035

#if SERIAL_ASYNC_READ
			_SerialPort.DataReceived += SerialDevice_DataReceived;
#endif

			_SerialPort.Open();

			_SerialPort.WatchChar = '\n';

			_SerialPort.ReadExisting(); // Running at 115K2 this was necessary

#if SERIAL_THREADED_READ
			readThread.Start();
#endif

			for (int i = 0; i < 5; i++)
			{
				string atCommand;
				atCommand = "AT+VER=?";
				//atCommand = "AT+SN=?"; // Empty response?
				//atCommand = "AT+HWMODEL=?";
				//atCommand = "AT+HWID=?";
				//atCommand = "AT+DEVEUI=?";
				//atCommand = "AT+APPEUI=?";
				//atCommand = "AT+APPKEY=?";
				//atCommand = "ATR";
				//atCommand = "AT+SLEEP=4000";
				Debug.WriteLine("");
				Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} {i} TX:{atCommand} bytes:{atCommand.Length}--------------------------------");
				_SerialPort.WriteLine(atCommand);

				Thread.Sleep(5000);
			}
		}
#if SERIAL_THREADED_READ
		_Continue = false;
#endif
		Debug.WriteLine("Done");
	}
	catch (Exception ex)
	{
		Debug.WriteLine(ex.Message);
	}
}

#if SERIAL_ASYNC_READ
private static void SerialDevice_DataReceived(object sender, SerialDataReceivedEventArgs e)
{
	SerialPort serialPort = (SerialPort)sender;

	switch (e.EventType)
	{
		case SerialData.Chars:
			break;

		case SerialData.WatchChar:
			string response = serialPort.ReadExisting();
			//Debug.Write($"{DateTime.UtcNow:hh:mm:ss} RX:{response} bytes:{response.Length}");
			Debug.Write(response);
			break;
		default:
			Debug.Assert(false, $"e.EventType {e.EventType} unknown");
			break;
	}
}
#endif

I then ran the device in the debugger to see how the AT+SLEEP was handled.

BreakoutSerial application executing AT+SLEEP command

There was limited AT+SLEEP -Sleep mode documentation but it looks like the RAK3172 module sleeps, “wakes up” and then replies with “OK”.

Sparkfun Thing Plus ESP32 WROOM + RAK3172 Idle power consumption

Initially the Sleep method didn’t appear to work, the power consumption didn’t change….

private static void SendMessageTimerCallback(object state)
{
	Rak3172LoRaWanDevice device = (Rak3172LoRaWanDevice)state;

#if PAYLOAD_HEX
	Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} port:{MessagePort} payload HEX:{PayloadHex}");
	Result result = device.Send(MessagePort, PayloadHex, SendTimeout);
#endif
#if PAYLOAD_BYTES
	Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} port:{MessagePort} payload bytes:{Rak3172LoRaWanDevice.BytesToHex(PayloadBytes)}");
	Result result = device.Send(MessagePort, PayloadBytes, SendTimeout);
#endif
	if (result != Result.Success)
	{
		Debug.WriteLine($"Send failed {result}");
	}

#if SLEEP
	Thread.Sleep(7500); //10000 Works 5000 to short

	Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} Sleep period:{SleepPeriod:hh:mm:ss}");
	result = device.Sleep(SleepPeriod);
	if (result != Result.Success)
	{
		Debug.WriteLine($"Sleep failed {result}");
		return;
	}
#endif
}

After some debugging and reading this helpful RAK Wireless forum post I added a short delay before sleeping the RAK3172 module and power consumption reduced.

Sparkfun Thing Plus ESP32 WROOM + RAK3172 Sleep mode power consumption

Initially the Sleep method timed out every time it was called. After some more debugging I figured out that I needed a slightly longer delay for the AutoResetEvent.Waitone as it was timing out just before the “OK” was processed.

public Result Sleep(TimeSpan period)
{
	return Sleep(period, SleepExtensionDefault);
}

public Result Sleep(TimeSpan period, TimeSpan extension)
{
#if DIAGNOSTICS
	Debug.WriteLine($" {DateTime.UtcNow:hh:mm:ss} AT+SLEEP {period.TotalMilliseconds:f0} mSec");
#endif
	Result result = SendCommand("OK", $"AT+SLEEP={period.TotalMilliseconds:f0}", period.Add(extension));
	if (result != Result.Success)
	{
#if DIAGNOSTICS
		Debug.WriteLine($" {DateTime.UtcNow:hh:mm:ss} AT+SLEEP failed {result}");
#endif
		return result;
	}

	return Result.Success;
}

I then disconnected RAK3172 module to see how much power the Sparkfun Thing Plus ESP32 WROOM was using.

The nanoFramework ESP32 support library has a LightSleep and DeepSleep functionality which significantly reduced the power consumption

Sparkfun Thing Plus ESP32 WROOM LightSleep power consumption
Sparkfun Thing Plus ESP32 WROOM DeepSleep power consumption

The Keweisi KWS-MX19 USB Tester DC 4V-30V 0-5A Current Voltage Detector is not a precision laboratory instrument but did show the power consumption of my setup could be reduced by sleeping the RAK3172 module and the Sparkfun Thing Plus ESP32 WROOM.

.NET nanoFramework RAK3172 Factory Reset

The RAKwireless RAK3172 module has “ATR – Restore to Default Parameters” command. To see what settings were “restored” I modified the BreakOutSerial application to send an ATR command to reset the device, then display the DevEUI, JoinEUI, and AppKey.

public static void Main()
{
	Debug.WriteLine("devMobile.IoT.LoRaWAN.nanoFramework.RAK3172 BreakoutSerial starting");

	try
	{
		// set GPIO functions for COM2 (this is UART1 on ESP32)
#if ESP32_WROOM
		Configuration.SetPinFunction(Gpio.IO17, DeviceFunction.COM2_TX);
		Configuration.SetPinFunction(Gpio.IO16, DeviceFunction.COM2_RX);
#endif

		Debug.Write("Ports:");
		foreach (string port in SerialPort.GetPortNames())
		{
			Debug.Write($" {port}");
		}
		Debug.WriteLine("");

		using (_SerialPort = new SerialPort(SerialPortId))
		{
			// set parameters
			_SerialPort.BaudRate = 115200;
			_SerialPort.Parity = Parity.None;
			_SerialPort.DataBits = 8;
			_SerialPort.StopBits = StopBits.One;
			_SerialPort.Handshake = Handshake.None;
			_SerialPort.NewLine = "\r\n";
			_SerialPort.ReadTimeout = 1000;

			//_SerialPort.WatchChar = '\n'; // May 2022 WatchChar event didn't fire github issue https://github.com/nanoframework/Home/issues/1035

#if SERIAL_ASYNC_READ
			_SerialPort.DataReceived += SerialDevice_DataReceived;
#endif

			_SerialPort.Open();

			_SerialPort.WatchChar = '\n';

			_SerialPort.ReadExisting(); // Running at 115K2 this was necessary

...

			for (int i = 0; i < 5; i++)
			{
				string atCommand;
				atCommand = "AT+VER=?";
				//atCommand = "AT+SN=?"; // Empty response?
				//atCommand = "AT+HWMODEL=?";
				//atCommand = "AT+HWID=?";
				//atCommand = "AT+DEVEUI=?";
				//atCommand = "AT+APPEUI=?";
				//atCommand = "AT+APPKEY=?";
				//atCommand = "ATR";
				//atCommand = "AT+SLEEP=4000";
				Debug.WriteLine("");
				Debug.WriteLine($"{i} TX:{atCommand} bytes:{atCommand.Length}--------------------------------");
				_SerialPort.WriteLine(atCommand);

				Thread.Sleep(5000);
			}
		}
...
		Debug.WriteLine("Done");
	}
	catch (Exception ex)
	{
		Debug.WriteLine(ex.Message);
	}
}

After resetting the device I modified the code to display some of the configuration.

DevEUI after ATR command
JoinEUI after ATR command
AppKey after ATR command

To reconfigure the device I ran the RAK3172LoRaWANDeviceClient application with DEVICE_DEVEUI_SET, OTAA, UNCONFIRMED, REGION_SET and ADR_SET defined. The testrig could then successfully connect to The Things Network and when the device was power cycled the configuration was retained.

public Result FactoryReset()
{
#if DIAGNOSTICS
	Debug.WriteLine($" {DateTime.UtcNow:hh:mm:ss} ATR");
#endif
	Result result = SendCommand("OK", "ATR", CommandTimeoutDefault);
	if (result != Result.Success)
	{
#if DIAGNOSTICS
		Debug.WriteLine($" {DateTime.UtcNow:hh:mm:ss} ATR failed {result}");
#endif
		return result;
	}

	return Result.Success;
}

I have added a “FactoryReset” method to the RAK3172LoRaWANDevice library.

.NET Core web API + Dapper – Readonly query workloads with Data Sync

Azure SQL Data Synchronisation Process

Read-only replicas of an Azure SQL Database database with Active geo-replication are easy to setup but there are some disadvantages. e.g. bi-directional synchronisation is not supported, not all tables or selected columns of some tables might not be needed\should not be accessible for reporting, the overhead of replicating tables used for transaction processing might impact on the performance of the solution etc. Azure SQL Data Sync is a service built on Azure SQL Database that can synchronise selected data bi-directionally across multiple databases, both on-premises and in the cloud.

The first step was to remove all the Microsoft SQL Server features used in the the World Wide Importers database (e.g. Sequence Numbers, Column Store indexes etc.) which are not supported(see general limitiations) by Azure SQL Data Sync. I then used the “Deploy Database Wizard” to copy my modified World Wide Importers database to an Azure SQL Database.

Deploy Database to Microsoft Azure SQL Database running
Microsoft Azure SQL Database Servers with WorldWideImporters and ReadonlyReplicaHub database
Data Sync Group creation onReadOnlyReplicaHub database

For my “read-only replicas” scenario if there are any update conflicts the the source database “wins”.

Data Sync Group created
Data Sync Group configuration database management
Data Sync Group configuration adding “source” database synchronisation to Hub
Data Sync Group configuration adding “destination” database(s) synchronisation from Hub
Data Sync Group selecting source database StockItems table and columns to synchronise
Data Sync Group “destination” databases configured
Initial synchronisation of only StockItems table and seed data
Regular synchronisation of a subset of StockItems columns to destination databases
Source Azure SQL Database regular synchronisation compute utilisation
StockItemsReadOnlyReplicas Controller JSON after first replication completed
Azure application Insights Dependencies showing usage of different synchronised databases
StockItems table in source database with updated RRP
StockItems table in destination database with updated RRP after next scheduled snychronisation
StockItems table in destination database after next scheduled synchronisation

The Azure SQL Database Data Sync was pretty easy to setup (configuration in the hub database tripped me up initially). For a production scenario where only a portion of the database (e.g. shaped by Customer, Geography, security considerations, or a bi-directional requirement) it would be an effective solution, though for some applications the delay between synchronisations might be an issue.

.NET Core web API + Dapper – ADO.Net Retries

Recovering from transient failures with ADO.Net RetryLogicProvider

This post is all about learning from failure, hopefully it will help someone else…

A while ago I wrote DapperTransient which uses Polly to retry SQLConnection and SQLCommand operations if the failure might be “transient”. My DapperTransient code wraps nearly all of the Dapper methods with a Polly RetryPolicy.ExecuteAsync.

public static Task<int> ExecuteWithRetryAsync(
			  this IDbConnection connection,
			  string sql,
			  object param = null,
			  IDbTransaction transaction = null,
			  int? commandTimeout = null,
			  CommandType? commandType = null) => RetryPolicy.ExecuteAsync(() => connection.ExecuteAsync(sql, param, transaction, commandTimeout, commandType));


One company I work for has a 10+year old VB.Net codebase that makes extensive use of ADO.Net calls which we moved to Azure Infrastructure as a Service(IaaS) a few years ago. Every so often they would get a cluster of ADO.Net exceptions when executing stored procedures in their Azure SQL database. While I was investigating how to retry transient failures without a major refactoring of the codebase I stumbled across SqlRetryLogicOption + TransientErrors, SqlRetryLogicBaseProvider and RetryLogicProvider which looked like a viable solution. At the time I also wondered if it would be possible to use the same approach with Dapper.

namespace devMobile.WebAPIDapper.Lists.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class StockItemsRetryADONetController : ControllerBase
    {
        private readonly string connectionString;
        private readonly ILogger<StockItemsRetryADONetController> logger;

        // This is a bit nasty but sufficient for PoC
        private readonly int NumberOfRetries = 3;
        private readonly TimeSpan TimeBeforeNextExecution = TimeSpan.Parse("00:00:01");
        private readonly TimeSpan MaximumInterval = TimeSpan.Parse("00:00:30");
        private readonly List<int> TransientErrors = new List<int>()
        {
            49920, // Cannot process rquest. Too many operations in progress for subscription
			49919, // Cannot process create or update request.Too many create or update operations in progress for subscription
			49918, // Cannot process request. Not enough resources to process request.
			41839, // Transaction exceeded the maximum number of commit dependencies.
			41325, // The current transaction failed to commit due to a serializable validation failure.
			41305, // The current transaction failed to commit due to a repeatable read validation failure.
			41302, // The current transaction attempted to update a record that has been updated since the transaction started.
			41301, // Dependency failure: a dependency was taken on another transaction that later failed to commit.
			40613, // Database XXXX on server YYYY is not currently available. Please retry the connection later.
			40501, // The service is currently busy. Retry the request after 10 seconds
			40197, // The service has encountered an error processing your request. Please try again
			20041, // Transaction rolled back. Could not execute trigger. Retry your transaction.
			17197, // Login failed due to timeout; the connection has been closed. This error may indicate heavy server load.
			14355, // The MSSQLServerADHelper service is busy. Retry this operation later.
			11001, // Connection attempt failed
			10936, // The request limit for the elastic pool has been reached. 
			10929, // The server is currently too busy to support requests.
			10928, // The limit for the database is has been reached
			10922, // Operation failed. Rerun the statement.
			10060, // A network-related or instance-specific error occurred while establishing a connection to SQL Server.
			10054, // A transport-level error has occurred when sending the request to the server.
			10053, // A transport-level error has occurred when receiving results from the server.
			9515, // An XML schema has been altered or dropped, and the query plan is no longer valid. Please rerun the query batch.
			8651, // Could not perform the operation because the requested memory grant was not available in resource pool
			8645, // A timeout occurred while waiting for memory resources to execute the query in resource pool, Rerun the query
			8628, // A timeout occurred while waiting to optimize the query. Rerun the query. 
			4221, // Login to read-secondary failed due to long wait on 'HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING'. The replica is not available for login because row versions are missing for transactions that were in-flight when the replica was recycled
			4060, // Cannot open database requested by the login. The login failed.
			3966, // Transaction is rolled back when accessing version store. It was earlier marked as victim when the version store was shrunk due to insufficient space in tempdb. Retry the transaction.
			3960, // Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table directly or indirectly in database
			3935, // A FILESTREAM transaction context could not be initialized. This might be caused by a resource shortage. Retry the operation.
			1807, // Could not obtain exclusive lock on database 'model'. Retry the operation later.
			1221, // The Database Engine is attempting to release a group of locks that are not currently held by the transaction. Retry the transaction.
			1205, // Deadlock
			1204, // The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement.
			1203, // A process attempted to unlock a resource it does not own. Retry the transaction.
			997, // A connection was successfully established with the server, but then an error occurred during the login process.
			921, // Database has not been recovered yet. Wait and try again.
			669, // The row object is inconsistent. Please rerun the query.
			617, // Descriptor for object in database not found in the hash table during attempt to un-hash it. Rerun the query. If a cursor is involved, close and reopen the cursor.
			601, // Could not continue scan with NOLOCK due to data movement.
			233, // The client was unable to establish a connection because of an error during connection initialization process before login.
			121, // The semaphore timeout period has expired.
			64, // A connection was successfully established with the server, but then an error occurred during the login process.
			20, // The instance of SQL Server you attempted to connect to does not support encryption.
		};
...
}

After some experimentation the most reliable way I could reproduce a transient failure (usually SQL Error 11001-“An error has occurred while establishing a connection to the server”) was by modifying the database connection string or unplugging the network cable after a connection had been explicitly opened or command executed.

namespace devMobile.WebAPIDapper.Lists.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class StockItemsRetryADONetController : ControllerBase
    {
...
		[HttpGet("Dapper")]
		public async Task<ActionResult<IAsyncEnumerable<Model.StockItemListDtoV1>>> GetDapper()
        {
            IEnumerable<Model.StockItemListDtoV1> response = null;

            SqlRetryLogicOption sqlRetryLogicOption = new SqlRetryLogicOption()
            {
                NumberOfTries = NumberOfRetries,
                DeltaTime = TimeBeforeNextExecution,
                MaxTimeInterval = MaximumInterval,
                TransientErrors = TransientErrors,
                //AuthorizedSqlCondition = x => string.IsNullOrEmpty(x) || Regex.IsMatch(x, @"^SELECT", RegexOptions.IgnoreCase),
            };

            SqlRetryLogicBaseProvider sqlRetryLogicProvider = SqlConfigurableRetryFactory.CreateFixedRetryProvider(sqlRetryLogicOption);

            using (SqlConnection db = new SqlConnection(this.connectionString))
            {
                db.RetryLogicProvider = sqlRetryLogicProvider;

                db.RetryLogicProvider.Retrying += new EventHandler<SqlRetryingEventArgs>(OnDapperRetrying);

                await db.OpenAsync(); // Did explicitly so I could yank out the LAN cable.

                response = await db.QueryAsync<Model.StockItemListDtoV1>(sql: @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItems]", commandType: CommandType.Text);
            }

            return this.Ok(response);
        }

        protected void OnDapperRetrying(object sender, SqlRetryingEventArgs args)
        {
            logger.LogInformation("Dapper retrying for {RetryCount} times for {args.Delay.TotalMilliseconds:0.} mSec - Error code: {Number}", args.RetryCount, args.Delay.TotalMilliseconds, (args.Exceptions[0] as SqlException).Number);
        }
...
    }
}

For my initial testing I used an invalid Azure SQL Database connection string and in the Visual Studio 2022 Debug output I could see retries.

ADO.Net RetryLogicProvider retrying request 3 times

I then added an OpenAsync just before the Dapper query so I could open the database connection, pause the program with a breakpoint, unplug the LAN cable and then continue execution. The QueryAsync failed without any retries and modifying the AuthorizedSqlCondition didn’t seem change the way different SQL statement failures were handled.

There was limited documentation about how to use ADO.Net retry functionality so I hacked up another method to try and figure out what I had done wrong. The method uses the same SqlRetryLogicOption configuration for retrying connection and command failures.

namespace devMobile.WebAPIDapper.Lists.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class StockItemsRetryADONetController : ControllerBase
    {
...
        [HttpGet("AdoNet")]
        public async Task<ActionResult<IAsyncEnumerable<Model.StockItemListDtoV1>>> GetAdoNet()
        {
            List<Model.StockItemListDtoV1> response = new List<Model.StockItemListDtoV1>();

            // Both connection and command share same logic not really an issue for nasty demo
            SqlRetryLogicOption sqlRetryLogicOption = new SqlRetryLogicOption()
            {
                NumberOfTries = NumberOfRetries,
                DeltaTime = TimeBeforeNextExecution,
                MaxTimeInterval = MaximumInterval,
                TransientErrors = TransientErrors,
                //AuthorizedSqlCondition = x => string.IsNullOrEmpty(x) || Regex.IsMatch(x, @"^SELECT", RegexOptions.IgnoreCase),
            };

            SqlRetryLogicBaseProvider sqlRetryLogicProvider = SqlConfigurableRetryFactory.CreateFixedRetryProvider(sqlRetryLogicOption);


            // This ADO.Net is a bit overkill but just wanted to highlight ADO.Net vs. Dapper
            using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
            {
                sqlConnection.RetryLogicProvider = sqlRetryLogicProvider;
                sqlConnection.RetryLogicProvider.Retrying += new EventHandler<SqlRetryingEventArgs>(OnConnectionRetrying);

                await sqlConnection.OpenAsync(); // Did explicitly so I could yank out the LAN cable.

                using (SqlCommand sqlCommand = new SqlCommand())
                {
                    sqlCommand.Connection = sqlConnection;
                    sqlCommand.CommandText = @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItems]";
                    sqlCommand.CommandType = CommandType.Text;

                    sqlCommand.RetryLogicProvider = sqlRetryLogicProvider;
                    sqlCommand.RetryLogicProvider.Retrying += new EventHandler<SqlRetryingEventArgs>(OnCommandRetrying);

                    // Over kill but makes really obvious
                    using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync(CommandBehavior.CloseConnection))
                    {
                        while (await sqlDataReader.ReadAsync())
                        {
                            response.Add(new Model.StockItemListDtoV1()
                            {
                                Id = sqlDataReader.GetInt32("Id"),
                                Name = sqlDataReader.GetString("Name"),
                                RecommendedRetailPrice = sqlDataReader.GetDecimal("RecommendedRetailPrice"),
                                TaxRate = sqlDataReader.GetDecimal("TaxRate"),
                            });
                        }
                    }
                };
            }

            return this.Ok(response);
        }

        protected void OnConnectionRetrying(object sender, SqlRetryingEventArgs args)
        {
            logger.LogInformation("Connection retrying for {RetryCount} times for {args.Delay.TotalMilliseconds:0.} mSec - Error code: {Number}", args.RetryCount, args.Delay.TotalMilliseconds, (args.Exceptions[0] as SqlException).Number);
        }

        protected void OnCommandRetrying(object sender, SqlRetryingEventArgs args)
        {
            logger.LogInformation("Command retrying for {RetryCount} times for {args.Delay.TotalMilliseconds:0.} mSec - Error code: {Number}", args.RetryCount, args.Delay.TotalMilliseconds, (args.Exceptions[0] as SqlException).Number);
        }
    }
}

I also added two RetryLogicProvider.Retrying handlers one for SQLConnection and the other for SQLCommand so I could see what was being retried.

sqlConnection.RetryLogicProvider with a broken connection string
sqlCommand.RetryLogicProvider with the LAN cable unplugged just before executing query

The number of retries when I unplugged the LAN cable wasn’t quite what I was expecting….

I didn’t fully understand the differences between System.Data.Sqlclient and Microsoft.Data.Sqlclient so I downloaded the source code for Dapper and starting hacking. My approach was to modify the Dapper CommandDefinition struct so a caller could pass in a SqlRetryLogicBaseProvider instance.

namespace Dapper
{
    /// <summary>
    /// Represents the key aspects of a sql operation
    /// </summary>
    public struct CommandDefinition
    {
        internal static CommandDefinition ForCallback(object parameters)
        {
            if (parameters is DynamicParameters)
            {
                return new CommandDefinition(parameters);
            }
            else
            {
                return default;
            }
        }

        internal void OnCompleted()
        {
            (Parameters as SqlMapper.IParameterCallbacks)?.OnCompleted();
        }

        /// <summary>
        /// The command (sql or a stored-procedure name) to execute
        /// </summary>
        public string CommandText { get; }

        /// <summary>
        /// The parameters associated with the command
        /// </summary>
        public object Parameters { get; }

        /// <summary>
        /// The active transaction for the command
        /// </summary>
        public IDbTransaction Transaction { get; }

        /// <summary>
        /// The effective timeout for the command
        /// </summary>
        public int? CommandTimeout { get; }

        /// <summary>
        /// The type of command that the command-text represents
        /// </summary>
        public CommandType? CommandType { get; }

        /// <summary>
        /// Should data be buffered before returning?
        /// </summary>
        public bool Buffered => (Flags & CommandFlags.Buffered) != 0;

        /// <summary>
        /// 
        /// </summary>
        public SqlRetryLogicBaseProvider SqlRetryLogicProvider { get; }

        /// <summary>
        /// Should the plan for this query be cached?
        /// </summary>
        internal bool AddToCache => (Flags & CommandFlags.NoCache) == 0;

        /// <summary>
        /// Additional state flags against this command
        /// </summary>
        public CommandFlags Flags { get; }

        /// <summary>
        /// Can async queries be pipelined?
        /// </summary>
        public bool Pipelined => (Flags & CommandFlags.Pipelined) != 0;

        /// <summary>
        /// Initialize the command definition
        /// </summary>
        /// <param name="commandText">The text for this command.</param>
        /// <param name="parameters">The parameters for this command.</param>
        /// <param name="transaction">The transaction for this command to participate in.</param>
        /// <param name="commandTimeout">The timeout (in seconds) for this command.</param>
        /// <param name="commandType">The <see cref="CommandType"/> for this command.</param>
        /// <param name="flags">The behavior flags for this command.</param>
        /// <param name="sqlRetryLogicProvider">Retry strategy for this command.</param>
        /// <param name="cancellationToken">The cancellation token for this command.</param>
        public CommandDefinition(string commandText, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null,
                                 CommandType? commandType = null, CommandFlags flags = CommandFlags.Buffered
                                 , SqlRetryLogicBaseProvider sqlRetryLogicProvider = null
                                 , CancellationToken cancellationToken = default
            )
        {
            CommandText = commandText;
            Parameters = parameters;
            Transaction = transaction;
            CommandTimeout = commandTimeout;
            CommandType = commandType;
            Flags = flags;
            SqlRetryLogicProvider = sqlRetryLogicProvider;
            CancellationToken = cancellationToken;
        }
...
}

This didn’t end well, as the Dapper library extends System.Data.IDbConnection which doesn’t “natively” support retry logic. Several hours lost from my life I now understand a bit more about the differences between System.Data.Sqlclient and Microsoft.Data.Sqlclient.

.NET nanoFramework RAK3172 LoRaWAN library ABP Join

After getting my RAKwireless RAK3172 module to reliably connect to The Things Network(TTN) using Over The Air Activation(OTAA) the next step was to built an Activation By Personalisation(ABP) sample application.

SparkFun Thing Plus – ESP32 WROOM, RAK3172 Breakout board

I modified the NetworkJoinOTAA sample(based on the asynchronous version of BreakOutSerial) to send the required sequence of AT commands and displays the responses.

namespace devMobile.IoT.LoRaWAN.nanoFramework.RAK3172
{
	using System;
	using System.Diagnostics;
	using System.IO.Ports;
	using System.Threading;
#if ESP32_WROOM
	using global::nanoFramework.Hardware.Esp32; //need NuGet nanoFramework.Hardware.Esp32
#endif

	public class Program
	{
#if ESP32_WROOM
		private const string SerialPortId = "COM2";
#endif
#if ST_STM32F769I_DISCOVERY
		private const string SerialPortId = "COM6";
#endif
		private const string DevEui = "...";
		private const string DevAddress = "...";
		private const string NwksKey = "...";
		private const string AppsKey = "..."; 
		private const byte MessagePort = 1;
		private const string Payload = "A0EEE456D02AFF4AB8BAFD58101D2A2A"; // Hello LoRaWAN

		public static void Main()
		{
			Debug.WriteLine("devMobile.IoT.LoRaWAN.nanoFramework.RAK3172.NetworkJoinABP starting");

			try
			{
				// set GPIO functions for COM2 (this is UART1 on ESP32)
#if ESP32_WROOM
				Configuration.SetPinFunction(Gpio.IO17, DeviceFunction.COM2_TX);
				Configuration.SetPinFunction(Gpio.IO16, DeviceFunction.COM2_RX);
#endif

				Debug.Write("Ports:");
				foreach (string port in SerialPort.GetPortNames())
				{
					Debug.Write($" {port}");
				}
				Debug.WriteLine("");

				using (SerialPort serialPort = new SerialPort(SerialPortId))
				{
					// set parameters
					serialPort.BaudRate = 115200;
					serialPort.Parity = Parity.None;
					serialPort.DataBits = 8;
					serialPort.StopBits = StopBits.One;
					serialPort.Handshake = Handshake.None;
					serialPort.NewLine = "\r\n";
					serialPort.ReadTimeout = 1000;

					serialPort.DataReceived += SerialDevice_DataReceived;

					serialPort.Open();

					serialPort.WatchChar = '\n';

					serialPort.ReadExisting(); // Running at 115K2 this was necessary

					// Set the Device EUI
					Console.WriteLine("Set Device EUI");
					serialPort.WriteLine($"AT+DEVEUI={DevEui}");

					// Set the Working mode to LoRaWAN
					Console.WriteLine("Set Work mode");
					serialPort.WriteLine("AT+NWM=1");

					// Set the Region to AS923
					Console.WriteLine("Set Region");
					serialPort.WriteLine("AT+BAND=8");

					// Set the JoinMode
					Console.WriteLine("Set Join mode");
					serialPort.WriteLine("AT+NJM=0");

					// Set the DevAddress
					Console.WriteLine("Set Device Address");
					serialPort.WriteLine($"AT+DEVADDR={DevAddress}");

					// Set the Network Session Key
					Console.WriteLine("Set NwksKey");
					serialPort.WriteLine($"AT+NWKSKEY={NwksKey}");

					// Set the Application Session Key
					Console.WriteLine("Set AppsKey");
					serialPort.WriteLine($"AT+APPSKEY={AppsKey}");

					// Set the Confirm flag
					Console.WriteLine("Set Confirm off");
					serialPort.WriteLine("AT+CFM=0");

					// Join the network
					Console.WriteLine("Start Join");
					serialPort.WriteLine("AT+JOIN=1:0:10:2");

					// Wait for the +EVT:JOINED

					while (true)
					{
						Console.WriteLine("Sending");
						serialPort.WriteLine($"AT+SEND={MessagePort}:{Payload}");

						Thread.Sleep(300000);
					}
				}
			}
			catch (Exception ex)
			{
				Debug.WriteLine(ex.Message);
			}
		}

		private static void SerialDevice_DataReceived(object sender, SerialDataReceivedEventArgs e)
		{
			SerialPort serialPort = (SerialPort)sender;

			switch (e.EventType)
			{
				case SerialData.Chars:
					break;

				case SerialData.WatchChar:
					string response = serialPort.ReadExisting();
					Debug.Write(response);
					break;
				default:
					Debug.Assert(false, $"e.EventType {e.EventType} unknown");
					break;
			}
		}
	}
}

The NetworkJoinABP application assumes that all of the AT commands succeed.

TTN Console live data tab connection process
Visual Studio Output windows displaying connection process and a D2C message
TTN Console live data tab connection process with a couple of D2C messages
Visual Studio Output windows displaying connection process and a couple of C2D messages

The Activation By Personalisation(ABP) sample was a bit more fragile than I expected. The The Things Network(TTN) Live Data Tab updates were often delayed or required a page refresh to be displayed (July 2022).

.NET nanoFramework RAK3172 LoRaWAN library OTAA Join

When I first tried Over The Air Activation(OTAA) to connect to The Things Network(TTN) with my RAKwireless RAK3172 module it didn’t work. I had flashed my Sparkfun Thing plus ESP32 WROOM with the ESP32 PSRAM binaries (Thanks AlbertK on the nanoFramework Discord).

nanoff --target ESP32_REV0 --serialport COM17 --update
RAK3172 Sparkfun ESP32 WROOM Testrig

I modified the NetworkJoinOTAA sample (based on the asynchronous version of BreakOutSerial) to send the sequence of AT commands to join The Things Network(TTN) and display the responses.

#define ST_STM32F769I_DISCOVERY      // nanoff --target ST_STM32F769I_DISCOVERY --update 
//#define ESP32_WROOM   //nanoff --target ESP32_REV0 --serialport COM17 --update

namespace devMobile.IoT.LoRaWAN.nanoFramework.RAK3172
{
	using System;
	using System.Diagnostics;
	using System.IO.Ports;
	using System.Threading;
#if ESP32_WROOM
	using global::nanoFramework.Hardware.Esp32; //need NuGet nanoFramework.Hardware.Esp32
#endif

	public class Program
	{
#if ESP32_WROOM
		private const string SerialPortId = "COM2";
#endif
#if ST_STM32F769I_DISCOVERY
		private const string SerialPortId = "COM6";
#endif
		private const string DevEui = "...";
		private const string AppEui = "...";
		private const string AppKey = "...";
		private const byte MessagePort = 1;
		private const string Payload = "A0EEE456D02AFF4AB8BAFD58101D2A2A"; // Hello LoRaWAN

		public static void Main()
		{
			Debug.WriteLine("devMobile.IoT.LoRaWAN.nanoFramework.RAK3172 NetworkJoinOTAA starting");

			try
			{
				// set GPIO functions for COM2 (this is UART1 on ESP32)
#if ESP32_WROOM
				Configuration.SetPinFunction(Gpio.IO17, DeviceFunction.COM2_TX);
				Configuration.SetPinFunction(Gpio.IO16, DeviceFunction.COM2_RX);
#endif

				Debug.Write("Ports:");
				foreach (string port in SerialPort.GetPortNames())
				{
					Debug.Write($" {port}");
				}
				Debug.WriteLine("");

				using (SerialPort serialPort = new SerialPort(SerialPortId))
				{
					// set parameters
					serialPort.BaudRate = 115200;
					serialPort.Parity = Parity.None;
					serialPort.DataBits = 8;
					serialPort.StopBits = StopBits.One;
					serialPort.Handshake = Handshake.None;
					serialPort.NewLine = "\r\n";
					serialPort.ReadTimeout = 1000;

					serialPort.DataReceived += SerialDevice_DataReceived;

					serialPort.Open();

					serialPort.WatchChar = '\n';

					serialPort.ReadExisting(); // Running at 115K2 this was necessary

					// Set the devEUI
					Console.WriteLine("Set Dev Eui");
					serialPort.WriteLine($"AT+DEVEUI={DevEui}");

					// Set the Working mode to LoRaWAN
					Console.WriteLine("Set Work mode");
					serialPort.WriteLine("AT+NWM=1");

					// Set the Region to AS923
					Console.WriteLine("Set Region");
					serialPort.WriteLine("AT+BAND=8");

					// Set the JoinMode
					Console.WriteLine("Set Join mode");
					serialPort.WriteLine("AT+NJM=1");

					// Set the appEUI
					Console.WriteLine("Set App Eui");
					serialPort.WriteLine($"AT+APPEUI={AppEui}");

					// Set the appKey
					Console.WriteLine("Set App Key");
					serialPort.WriteLine($"AT+APPKEY={AppKey}");

					// Set the Confirm flag
					Console.WriteLine("Set Confirm off");
					serialPort.WriteLine("AT+CFM=0");

					// Join the network
					Console.WriteLine("Start Join");
					serialPort.WriteLine("AT+JOIN=1:0:10:2");

					// Wait for the +EVT:JOINED

					while (true)
					{
						Console.WriteLine("Sending");
						serialPort.WriteLine($"AT+SEND={MessagePort}:{Payload}");

						Thread.Sleep(300000);
					}
				}
			}
			catch (Exception ex)
			{
				Debug.WriteLine(ex.Message);
			}
		}

		private static void SerialDevice_DataReceived(object sender, SerialDataReceivedEventArgs e)
		{
			SerialPort serialPort = (SerialPort)sender;

			switch (e.EventType)
			{
				case SerialData.Chars:
					break;
 
				case SerialData.WatchChar:
					string response = serialPort.ReadExisting();
					Debug.Write(response);
					break;
				default:
					Debug.Assert(false, $"e.EventType {e.EventType} unknown");
					break;
			}
		}
	}
}
Visual Studio Debug Output displaying Configuration-Join-Send
Things Network(TTN) Live Data tab display successful join then D2C message

The NetworkJoinOTAA application assumes that all of the AT commands succeed. I usually step though the application in the debugger so I can monitor progress.