Visual Studio Debug output for RAK3172LoRaWANDeviceClient minimal configuration
public static void Main()
{
Result result;
Debug.WriteLine("devMobile.IoT.RAK3172LoRaWANDeviceClient 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 (Rak3172LoRaWanDevice device = new Rak3172LoRaWanDevice())
{
result = device.Initialise(SerialPortId, 115200, Parity.None, 8, StopBits.One);
if (result != Result.Success)
{
Debug.WriteLine($"Initialise failed {result}");
return;
}
MessageSendTimer = new Timer(SendMessageTimerCallback, device, Timeout.Infinite, Timeout.Infinite);
device.OnJoinCompletion += OnJoinCompletionHandler;
device.OnReceiveMessage += OnReceiveMessageHandler;
#if CONFIRMED
device.OnMessageConfirmation += OnMessageConfirmationHandler;
#endif
#if FACTORY_RESET
Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} FactoryReset");
result = device.FactoryReset();
if (result != Result.Success)
{
Debug.WriteLine($"FactoryReset failed {result}");
return;
}
#endif
#if DEVICE_DEVEUI_SET
Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} Device EUI");
result = device.DeviceEui(Config.devEui);
if (result != Result.Success)
{
Debug.WriteLine($"DeviceEUI set failed {result}");
return;
}
#endif
#if REGION_SET
Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} Region{Band}");
result = device.Band(Band);
if (result != Result.Success)
{
Debug.WriteLine($"Band on failed {result}");
return;
}
#endif
#if ADR_SET
Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} ADR On");
result = device.AdrOn();
if (result != Result.Success)
{
Debug.WriteLine($"ADR on failed {result}");
return;
}
#endif
#if CONFIRMED
Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} Confirmed");
result = device.UplinkMessageConfirmationOn();
if (result != Result.Success)
{
Debug.WriteLine($"Confirm on failed {result}");
return;
}
#endif
#if UNCONFIRMED
Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} Unconfirmed");
result = device.UplinkMessageConfirmationOff();
if (result != Result.Success)
{
Debug.WriteLine($"Confirm off failed {result}");
return;
}
#endif
#if OTAA
Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} OTAA");
result = device.OtaaInitialise(Config.JoinEui, Config.AppKey);
if (result != Result.Success)
{
Debug.WriteLine($"OTAA Initialise failed {result}");
return;
}
#endif
#if ABP
Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} ABP");
result = device.AbpInitialise(Config.DevAddress, Config.NwksKey, Config.AppsKey);
if (result != Result.Success)
{
Debug.WriteLine($"ABP Initialise failed {result}");
return;
}
#endif
Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} Join start Timeout:{JoinTimeOut:hh:mm:ss}");
result = device.Join(JoinTimeOut);
if (result != Result.Success)
{
Debug.WriteLine($"Join failed {result}");
return;
}
Debug.WriteLine($"{DateTime.UtcNow:hh:mm:ss} Join started");
Thread.Sleep(Timeout.Infinite);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
}
One of the major differences between the RAK4200 and RAK3127 libraries is the way a LoRaWAN network join is handled. The RAK4200 library Join method blocks until it succeeds of fails, the RAK3172 library Join method returns immediately then an EventHandler is called with the result.
[HttpGet("IEnumerableSmall")]
public async Task<ActionResult<IEnumerable<Model.StockItemListDtoV1>>> GetIEnumerableSmall([FromQuery] bool buffered = false)
{
IEnumerable<Model.StockItemListDtoV1> response = null;
using (SqlConnection db = new SqlConnection(this.connectionString))
{
logger.LogInformation("IEnumerableSmall start Buffered:{buffered}", buffered);
response = await db.QueryWithRetryAsync<Model.StockItemListDtoV1>(
sql: @"SELECT [SI1].[StockItemID] as ""ID"", [SI1].[StockItemName] as ""Name"", [SI1].[RecommendedRetailPrice], [SI1].[TaxRate]" +
"FROM [Warehouse].[StockItems] as SI1",
buffered,
commandType: CommandType.Text);
logger.LogInformation("IEnumerableSmall done");
}
return this.Ok(response);
}
The easiest way to increase the size of the returned record was with CROSS JOIN(s). This is the first (and most probably the last time) I have used a cross join in a “real” application.
[HttpGet("IEnumerableMedium")]
public async Task<ActionResult<IEnumerable<Model.StockItemListDtoV1>>> GetIEnumerableMedium([FromQuery] bool buffered = false)
{
IEnumerable<Model.StockItemListDtoV1> response = null;
using (SqlConnection db = new SqlConnection(this.connectionString))
{
logger.LogInformation("IEnumerableMedium start Buffered:{buffered}", buffered);
response = await db.QueryWithRetryAsync<Model.StockItemListDtoV1>(
sql: @" SELECT [SI2].[StockItemID] as ""ID"", [SI2].[StockItemName] as ""Name"", [SI2].[RecommendedRetailPrice], [SI2].[TaxRate]" +
"FROM [Warehouse].[StockItems] as SI1" +
"CROSS JOIN[Warehouse].[StockItems] as SI2",
buffered,
commandType: CommandType.Text);
logger.LogInformation("IEnumerableMedium done");
}
return this.Ok(response);
}
The medium controller returns 51,529 (227 x 227) rows and the large controller upto 11,697,083 (227 x 227 x 227) rows.
[HttpGet("IEnumerableLarge")]
public async Task<ActionResult<IEnumerable<Model.StockItemListDtoV1>>> GetIEnumerableLarge()
{
IEnumerable<Model.StockItemListDtoV1> response = null;
using (SqlConnection db = new SqlConnection(this.connectionString))
{
logger.LogInformation("IEnumerableLarge start");
response = await db.QueryWithRetryAsync<Model.StockItemListDtoV1>(
sql: $@"SELECT [SI3].[StockItemID] as ""ID"", [SI3].[StockItemName] as ""Name"", [SI3].[RecommendedRetailPrice], [SI3].[TaxRate]" +
"FROM [Warehouse].[StockItems] as SI1" +
" CROSS JOIN[Warehouse].[StockItems] as SI2" +
" CROSS JOIN[Warehouse].[StockItems] as SI3",
commandType: CommandType.Text);
logger.LogInformation("IEnumerableLarge done");
}
return this.Ok(response);
}
IEnumberableLarge method (buffered=false) response sizes and timings
IEnumberableLarge method (buffered=true) response sizes and timings
The unbuffered buffered version was slower Time To Last Byte(TTLB) and failed earlier which I was expecting.
[HttpGet("IAsyncEnumerableLarge")]
public async Task<ActionResult<IAsyncEnumerable<Model.StockItemListDtoV1>>> GetAsyncEnumerableLarge([FromQuery] bool buffered = false, [FromQuery]int recordCount = 10)
{
IEnumerable<Model.StockItemListDtoV1> response = null;
using (SqlConnection db = new SqlConnection(this.connectionString))
{
logger.LogInformation("IAsyncEnumerableLarge start RecordCount:{recordCount} Buffered:{buffered}", recordCount, buffered);
response = await db.QueryWithRetryAsync<Model.StockItemListDtoV1>(
sql: $@"SELECT TOP({recordCount}) [SI3].[StockItemID] as ""ID"", [SI3].[StockItemName] as ""Name"", [SI3].[RecommendedRetailPrice], [SI3].[TaxRate]" +
"FROM [Warehouse].[StockItems] as SI1" +
" CROSS JOIN[Warehouse].[StockItems] as SI2" +
" CROSS JOIN[Warehouse].[StockItems] as SI3",
buffered,
commandType: CommandType.Text);
logger.LogInformation("IAsyncEnumerableLarge done");
}
return this.Ok(response);
}
IAsyncEnumberableLarge method response sizes and timings
[HttpGet("IAsyncEnumerableLargeYield")]
public async IAsyncEnumerable<Model.StockItemListDtoV1> GetAsyncEnumerableLargeYield([FromQuery] int recordCount = 10)
{
int rowCount = 0;
using (SqlConnection db = new SqlConnection(this.connectionString))
{
logger.LogInformation("IAsyncEnumerableLargeYield start RecordCount:{recordCount}", recordCount);
CommandDefinition commandDefinition = new CommandDefinition(
$@"SELECT TOP({recordCount}) [SI3].[StockItemID] as ""ID"", [SI3].[StockItemName] as ""Name"", [SI3].[RecommendedRetailPrice], [SI3].[TaxRate]" +
"FROM [Warehouse].[StockItems] as SI1" +
" CROSS JOIN[Warehouse].[StockItems] as SI2" +
" CROSS JOIN[Warehouse].[StockItems] as SI3",
//commandTimeout:
CommandType.Text,
//flags: CommandFlags.Pipelined
);
using var reader = await db.ExecuteReaderWithRetryAsync(commandDefinition);
var rowParser = reader.GetRowParser<Model.StockItemListDtoV1>();
while (await reader.ReadAsync())
{
rowCount++;
if ((rowCount % 10000) == 0)
{
logger.LogInformation("Row count:{0}", rowCount);
}
yield return rowParser(reader);
}
logger.LogInformation("IAsyncEnumerableLargeYield done");
}
}
When this post was written (August 2022) Dapper IAsyncEnumerable understanding was limited so I trialed the approach suggested in the StackOverflow post.
IAsyncEnumberableLargeYield method response sizes and timings
The IAsyncEnumerableLargeYield was faster to start responding, the overall duration was less and returned significantly more records 7000000 vs. 13000000. I assume this was because the response was streamed so there wasn’t a timeout.
Azure Application Insights displaying the IAsyncEnumerable with yield method executing
The results of my tests should be treated as “indicative” rather than “definitive”. In a future post I compare the scalability of different approaches. The number of records returned by the IAsyncEnumerableLargeYield not realistic and in a “real-world” scenario paging or an alternate approach should be used.
The RAK11200 documentation described how to upload software developed with the Arduino tools by putting the ESP32 into “bootloader mode” by connecting the BOOT0 and GND pins, then pressing the reset button.
RAK11200 BOOT0 & GND pins connected to
After some “trial and error” the download process worked pretty reliably…
The RAK11200 has two LEDs, a blue attached to IO02 and a green one attached to IO12.
//
// Copyright (c) .NET Foundation and Contributors
// See LICENSE file in the project root for full license information.
//
//
using System.Device.Gpio;
using System;
using System.Threading;
using nanoFramework.Hardware.Esp32;
namespace Blinky
{
public class Program
{
private static GpioController s_GpioController;
public static void Main()
{
s_GpioController = new GpioController();
// pick a board, uncomment one line for GpioPin; default is STM32F769I_DISCO
// DISCOVERY4: PD15 is LED6
//GpioPin led = s_GpioController.OpenPin(PinNumber('D', 15), PinMode.Output);
// ESP32 DevKit: 4 is a valid GPIO pin in, some boards like Xiuxin ESP32 may require GPIO Pin 2 instead.
//GpioPin led = s_GpioController.OpenPin(4, PinMode.Output);
// FEATHER S2:
//GpioPin led = s_GpioController.OpenPin(13, PinMode.Output);
// F429I_DISCO: PG14 is LEDLD4
//GpioPin led = s_GpioController.OpenPin(PinNumber('G', 14), PinMode.Output);
// NETDUINO 3 Wifi: A10 is LED onboard blue
//GpioPin led = s_GpioController.OpenPin(PinNumber('A', 10), PinMode.Output);
// QUAIL: PE15 is LED1
//GpioPin led = s_GpioController.OpenPin(PinNumber('E', 15), PinMode.Output);
// STM32F091RC: PA5 is LED_GREEN
//GpioPin led = s_GpioController.OpenPin(PinNumber('A', 5), PinMode.Output);
// STM32F746_NUCLEO: PB75 is LED2
//GpioPin led = s_GpioController.OpenPin(PinNumber('B', 7), PinMode.Output);
//STM32F769I_DISCO: PJ5 is LD2
//GpioPin led = s_GpioController.OpenPin(PinNumber('J', 5), PinMode.Output);
// ST_B_L475E_IOT01A: PB14 is LD2
//GpioPin led = s_GpioController.OpenPin(PinNumber('B', 14), PinMode.Output);
// STM32L072Z_LRWAN1: PA5 is LD2
//GpioPin led = s_GpioController.OpenPin(PinNumber('A', 5), PinMode.Output);
// TI CC13x2 Launchpad: DIO_07 it's the green LED
//GpioPin led = s_GpioController.OpenPin(7, PinMode.Output);
// TI CC13x2 Launchpad: DIO_06 it's the red LED
//GpioPin led = s_GpioController.OpenPin(6, PinMode.Output);
// ULX3S FPGA board: for the red D22 LED from the ESP32-WROOM32, GPIO5
//GpioPin led = s_GpioController.OpenPin(5, PinMode.Output);
// Silabs SLSTK3701A: LED1 PH14 is LLED1
//GpioPin led = s_GpioController.OpenPin(PinNumber('H', 14), PinMode.Output);
// RAK11200
//GpioPin led = s_GpioController.OpenPin(Gpio.IO12, PinMode.Output); // LED1 Green
GpioPin led = s_GpioController.OpenPin(Gpio.IO02, PinMode.Output); // LED2 Blue
led.Write(PinValue.Low);
while (true)
{
led.Toggle();
Thread.Sleep(125);
led.Toggle();
Thread.Sleep(125);
led.Toggle();
Thread.Sleep(125);
led.Toggle();
Thread.Sleep(525);
}
}
static int PinNumber(char port, byte pin)
{
if (port < 'A' || port > 'J')
throw new ArgumentException();
return ((port - 'A') * 16) + pin;
}
}
}
I added the RAK11200 configuration to nanoFramework Blinky sample and could reliably flash either of the LEDs
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.
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;
}
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;
}
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.
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.
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);
}
...
}
}
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 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