private async Task RefreshAsync()
{
// Prune the queues
while (OutBufferCount > m_MaxMessages)
{
if (m_DataUploadQueue.TryDequeue(out var _))
{
continue;
}
}
//OnDebug?.Invoke($"{m_DataUploadQueue.Count} in queue.");
// Upload the data
if (!m_DataUploadQueue.TryPeek(out var entry))
{
return;
}
var table = MapTable(entry);
if (table == null)
{
OnDebug?.Invoke($"Skipping {entry.GetType().Name} for archive database...");
// Remove the message from the queue
m_DataUploadQueue.TryDequeue(out entry);
return;
}
// The base SQL statement should be as similar as possible so that it will be cached in the database server
// This is why ValuesListForInsertStatement uses parameters instead of actual values
var sql = $"INSERT INTO {table} {entry.InsertStatement}";
OnSQL?.Invoke("Database SQL = " + sql);
// Store to database
try {
using (var conn = connectionFactory()) {
await conn.OpenAsync();
using (var cmd = conn.CreateCommand()) {
// Use a transaction
using (var transaction = conn.BeginTransaction()) {
// Must assign both transaction object and connection to Command object
// for a pending local transaction (as per MSDN)
cmd.Connection = conn;
cmd.Transaction = transaction;
try {
// Prepare the SQL statement
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Parameters.Clear();
entry.AddSqlParameters(cmd.Parameters, createSqlParameter);
await cmd.ExecuteNonQueryAsync();
// Special treatment for cycle data
if (entry is CycleData cycledata)
{
if (cycledata.Data.Count > 0)
{
sql = $"SELECT MAX(ID) FROM {Storage.CycleDataTable}";
OnSQL?.Invoke(sql);
cmd.CommandText = sql;
var id = (int)await cmd.ExecuteScalarAsync();
OnSQL?.Invoke("New Cycle Data ID = " + id);
sql = $"INSERT INTO {Storage.CycleDataValuesTable} (ID, VariableName, Value)\nVALUES";
sql += string.Join(",\n", cycledata.Data.Select(kv => $" ({id}, '{kv.Key}', {(float) kv.Value})"));
OnSQL?.Invoke(sql);
cmd.CommandText = sql;
await cmd.ExecuteNonQueryAsync();
}
}
transaction.Commit();
} catch {
transaction.Rollback();
throw;
}
}
OnUploadSuccess?.Invoke(entry, $"Successfully uploaded {entry.GetType().Name} to archive database.");
}
}
} catch (DbException ex) {
// Do not remove the message from the queue
OnUploadError?.Invoke(entry, ex, $"Cannot upload {entry.GetType().Name} to archive database!\n{sql}");
m_NextTryTime = DateTime.Now.AddMilliseconds(ErrorRefreshInterval);
return;
} catch (Exception ex) {
// Do not remove the message from the queue
OnError?.Invoke(ex, $"Error when uploading {entry.GetType().Name} to archive database!");
m_NextTryTime = DateTime.Now.AddMilliseconds(ErrorRefreshInterval);
return;
}
// Remove the message from the queue
m_DataUploadQueue.TryDequeue(out entry);
}
private async Task UploadBufferAsync()
{
if (m_Buffer.Count > 1 && m_Buffer[0].UseBatches)
{
// Batch upload
var table = MapTable(m_Buffer[0]);
var uploads = new TableBatchOperation();
var links = new TableBatchOperation();
var id = m_Buffer[0].Controller;
foreach (var data in m_Buffer)
{
var entity = data.ToEntity(data.ID ?? m_RowKeyBase + "-" + m_Seq++);
uploads.Insert(entity);
if (data.ID != null)
{
links.Insert(new Link(table.Name, data.ID, entity.PartitionKey, entity.RowKey));
}
}
OnDebug?.Invoke($"Batch uploading {uploads.Count} records to Azure table storage {table.Name} for controller [{id}]...");
try {
if (links.Count > 0)
{
await m_LinksTable.ExecuteBatchAsync(links);
}
var r = await table.ExecuteBatchAsync(uploads);
// Check for errors
var errors = m_Buffer
.Where((entry, x) => r.Count <= x || (r[x].HttpStatusCode != 201 && r[x].HttpStatusCode != 204))
.ToList();
var successes = m_Buffer
.Where((entry, x) => r.Count > x && (r[x].HttpStatusCode == 201 || r[x].HttpStatusCode == 204))
.ToList();
OnUploadSuccess?.Invoke(201, successes, $"{m_Buffer.Count - errors.Count} record(s) out of {m_Buffer.Count} for controller [{id}] successfully uploaded to Azure table storage {table.Name}.");
m_Buffer.Clear();
if (errors.Count > 0)
{
m_Buffer.AddRange(errors);
OnUploadError?.Invoke(0, errors, $"{errors.Count} record(s) for controller [{id}] failed to upload to Azure table storage {table.Name}.");
}
} catch (StorageException ex) {
var status = ex.RequestInformation.HttpStatusCode;
var errmsg = ex.RequestInformation.ExtendedErrorInformation?.ErrorMessage ?? ex.RequestInformation.HttpStatusMessage ?? ex.Message;
switch (status)
{
case 0: {
OnError?.Invoke(ex, $"Azure table storage batch upload to {table.Name} for controller [{id}] failed.");
break;
}
case 401:
case 403: {
OnUploadError?.Invoke(status, m_Buffer, $"Azure table storage batch upload to {table.Name} for controller [{id}] forbidden: {errmsg}");
break;
}
default: {
OnUploadError?.Invoke(status, m_Buffer, $"Azure table storage batch upload to {table.Name} for controller [{id}] failed: {errmsg}");
break;
}
}
} catch (Exception ex) {
OnError?.Invoke(ex, $"Azure table storage batch upload to {table.Name} for controller [{id}] failed.");
}
}
else if (m_Buffer.Count > 0)
{
// Single upload
var data = m_Buffer[0];
var id = data.Controller;
var table = MapTable(data);
var entity = data.ToEntity(data.ID ?? m_RowKeyBase + "-" + m_Seq++);
var insert = TableOperation.Insert(entity);
var link = (data.ID != null) ? TableOperation.Insert(new Link(table.Name, data.ID, entity.PartitionKey, entity.RowKey)) : null;
OnDebug?.Invoke($"Uploading record to Azure table storage {table.Name} for controller [{id}]...");
try {
TableResult r;
if (link != null)
{
r = await m_LinksTable.ExecuteAsync(link);
}
r = await table.ExecuteAsync(insert);
OnUploadSuccess?.Invoke(r.HttpStatusCode, new[] { data }, $"Azure table storage upload to {table.Name} for controller [{id}] succeeded, result = {r.HttpStatusCode}.");
if (m_Buffer.Count <= 1)
{
m_Buffer.Clear();
}
else
{
m_Buffer.RemoveAt(0);
}
} catch (StorageException ex) {
var status = ex.RequestInformation.HttpStatusCode;
var errmsg = ex.RequestInformation.ExtendedErrorInformation?.ErrorMessage ?? ex.RequestInformation.HttpStatusMessage ?? ex.Message;
switch (status)
{
case 0: {
OnError?.Invoke(ex, $"Azure table storage upload to {table.Name} for controller [{id}] failed.");
break;
}
case 401:
case 403: {
OnUploadError?.Invoke(status, new[] { data }, $"Azure table storage upload to {table.Name} for controller [{id}] forbidden: {errmsg}");
break;
}
default: {
OnUploadError?.Invoke(status, new[] { data }, $"Azure table storage upload to {table.Name} for controller [{id}] failed: {errmsg}");
break;
}
}
} catch (Exception ex) {
OnError?.Invoke(ex, $"Azure table storage upload to {table.Name} for controller [{id}] failed.");
}
}
}