public int CheckClosedAlerts(List <AlertData> alerts, List <string> alertIdsToClose, out int moreToClose, int closeMax = 200, bool useTemporaryTables = false)
{
var alertsSaved = 0;
moreToClose = 0;
using (var connection = new SqlConnection(SqlConnectionString))
{
connection.Open();
var alertsTableAdapter = new rt_alertTableAdapter {
Connection = connection
};
var alertsTimeTableAdapter = new rt_alert_timesTableAdapter {
Connection = connection
};
var activePeriodsTableAdapter = new rt_alert_active_periodTableAdapter {
Connection = connection, ClearBeforeFill = false
};
var informedEntitiesTableAdapter = new rt_alert_informed_entityTableAdapter {
Connection = connection, ClearBeforeFill = false
};
if (useTemporaryTables)
{
alertsTableAdapter.SetTempTable();
activePeriodsTableAdapter.SetTempTable();
informedEntitiesTableAdapter.SetTempTable();
alertsTimeTableAdapter.SetTempTable();
//rt_alert.TableName = rt_alert.TableName + "_temp";
//rt_alert_active_period.TableName = rt_alert_active_period.TableName + "_temp";
//rt_alert_informed_entity.TableName = rt_alert_informed_entity.TableName + "_temp";
}
if (alertIdsToClose == null || !alertIdsToClose.Any())
{
alertsTableAdapter.FillByOpenAlerts(rt_alert);
}
else
{
var alertIds = string.Join(",", alertIdsToClose.Select(x => $"'{x}'"));
var query = $" and (alerts.alert_id in ({alertIds}))";
alertsTableAdapter.SelectOpenAlertsCommand.CommandText += query;
alertsTableAdapter.FillByOpenAlerts(rt_alert);
}
if (!rt_alert.Any())
{
return(0);
}
var currentTime = DateTime.UtcNow;
var currentAlertIds = alerts.Select(x => x.AlertId).OrderBy(x => x).ToList();
var fileTime = alerts.FirstOrDefault()?.HeaderTimestamp ?? Utils.GetSecondsFromUtc(currentTime);
var alertsToClose = rt_alert.Where(x => !currentAlertIds.Contains(x.alert_id)).OrderBy(x => x.alert_id).ToList();
var closedAlertsDataTable = new rt_alertDataTable {
TableName = rt_alert.TableName
};
var closedActivePeriodsDataTable = new rt_alert_active_periodDataTable {
TableName = rt_alert_active_period.TableName
};
var closedInformedEntitiesDataTable = new rt_alert_informed_entityDataTable {
TableName = rt_alert_informed_entity.TableName
};
moreToClose = Math.Max(alertsToClose.Count - closeMax, 0);
if (alertsToClose.Count == 0)
{
return(0);
}
foreach (var alertRow in alertsToClose.Take(closeMax))
{
activePeriodsTableAdapter.ClearBeforeFill = true;
informedEntitiesTableAdapter.ClearBeforeFill = true;
alertsTimeTableAdapter.ClearBeforeFill = true;
activePeriodsTableAdapter.FillByAlertIdVersionId(rt_alert_active_period, alertRow.alert_id, alertRow.version_id);
informedEntitiesTableAdapter.FillByAlertIdVersionId(rt_alert_informed_entity, alertRow.alert_id, alertRow.version_id);
alertsTimeTableAdapter.FillByNextFileTimeAfterAlert(rt_alert_times, alertRow.record_id);
if (alertIdsToClose == null || !alertIdsToClose.Any())
{
var nextAlertTime = rt_alert_times.FirstOrDefault();
if (nextAlertTime != null)
{
fileTime = (ulong)nextAlertTime.file_time;
}
}
var alert = alertRow.Alert;
alert.Closed = true;
alert.HeaderTimestamp = fileTime;
InsertAlert(alert, alertRow, closedAlertsDataTable, closedActivePeriodsDataTable, closedInformedEntitiesDataTable);
alertsSaved++;
}
using (var transaction = connection.BeginTransaction())
{
using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, transaction))
{
Utils.BulkInsert(closedAlertsDataTable, sqlBulkCopy, new List <string> {
"record_id"
});
Utils.BulkInsert(closedActivePeriodsDataTable, sqlBulkCopy);
Utils.BulkInsert(closedInformedEntitiesDataTable, sqlBulkCopy);
transaction.Commit();
}
}
}
return(alertsSaved);
}
public int SaveAlerts(List <AlertData> alerts, bool tempTables)
{
var alertsSaved = 0;
using (var connection = new SqlConnection(SqlConnectionString))
{
connection.Open();
var alertsTableAdapter = new rt_alertTableAdapter {
Connection = connection, ClearBeforeFill = true
};
var activePeriodsTableAdapter = new rt_alert_active_periodTableAdapter {
Connection = connection, ClearBeforeFill = true
};
var informedEntitiesTableAdapter = new rt_alert_informed_entityTableAdapter {
Connection = connection, ClearBeforeFill = true
};
if (tempTables)
{
alertsTableAdapter.SetTempTable();
activePeriodsTableAdapter.SetTempTable();
informedEntitiesTableAdapter.SetTempTable();
}
var alertIds = string.Join(",", alerts.Select(x => $"'{x.AlertId}'"));
//var query = $" where alert_id in ({alertIds})";
EnforceConstraints = false;
alertsTableAdapter.SelectCommand.CommandText = alertsTableAdapter.SelectByMaxVersionCommand.CommandText.Replace("'000000'", alertIds);
alertsTableAdapter.Fill(rt_alert);
activePeriodsTableAdapter.SelectCommand.CommandText = activePeriodsTableAdapter.SelectByMaxVersionCommand.CommandText.Replace("'000000'", alertIds);
activePeriodsTableAdapter.Fill(rt_alert_active_period);
informedEntitiesTableAdapter.SelectCommand.CommandText = informedEntitiesTableAdapter.SelectByMaxVersionCommand.CommandText.Replace("'000000'", alertIds);
informedEntitiesTableAdapter.Fill(rt_alert_informed_entity);
foreach (var alert in alerts)
{
if (alert.AlertId == null)
{
continue;
}
var existingAlert = rt_alert.Where(x => x.alert_id == alert.AlertId).OrderByDescending(x => x.version_id).FirstOrDefault();
if (existingAlert != null && !existingAlert.Islast_file_timeNull() && existingAlert.last_file_time >= (int)alert.HeaderTimestamp)
{
// ignore, we have a newer (or the same) file with this alert that was processed
continue;
}
var equal = existingAlert != null && alert.Equals(existingAlert.Alert);
if (equal)
{
// update only last file time...
existingAlert.last_file_time = (int)alert.HeaderTimestamp;
alertsSaved++;
}
else
{
alert.CheckPeriodEndChange = true;
equal = existingAlert != null && alert.Equals(existingAlert.Alert);
alertsSaved++;
if (existingAlert == null || !equal)
{
InsertAlert(alert, existingAlert);
}
else
{
UpdateAlert(alert, existingAlert);
}
}
}
using (var transaction = connection.BeginTransaction())
{
alertsTableAdapter.Transaction = transaction;
alertsTableAdapter.Update(rt_alert);
informedEntitiesTableAdapter.Transaction = transaction;
informedEntitiesTableAdapter.Update(rt_alert_informed_entity);
activePeriodsTableAdapter.Transaction = transaction;
activePeriodsTableAdapter.Update(rt_alert_active_period);
transaction.Commit();
}
}
return(alertsSaved);
}