/// <summary>
/// Saves measurement back to the configuration database
/// </summary>
/// <param name="database">Database connection for query. Will be created from config if this value is null.</param>
/// <param name="measurement">Measurement to be inserted or updated</param>
public void Save(AdoDataConnection database, PowerMeasurement measurement)
{
var createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
if (measurement.SignalID == Guid.Empty)
{
database.ExecuteNonQuery("INSERT INTO Measurement (DeviceID, PointTag, SignalTypeID, " +
"SignalReference, Adder, Multiplier, Description, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES " +
"({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11})", ToNotNull(measurement.DeviceID), measurement.PointTag,
measurement.SignalTypeID, measurement.SignalReference, measurement.Adder, measurement.Multiplier, ToNotNull(measurement.Description),
database.Bool(measurement.Enabled), Thread.CurrentPrincipal.Identity.Name, database.UtcNow, Thread.CurrentPrincipal.Identity.Name, database.UtcNow);
measurement.SignalID = database.ExecuteScalar<Guid>("SELECT SignalID FROM Measurement WHERE PointTag={0}", measurement.PointTag);
}
else
{
database.ExecuteNonQuery("UPDATE Measurement SET DeviceID = {0}, PointTag = {1}, " +
"SignalTypeID = {2}, SignalReference = {3}, Adder = {4}, Multiplier = {5}, Description = {6}, " +
"Enabled = {7}, UpdatedBy = {8}, UpdatedOn = {9} WHERE SignalId = {10}", ToNotNull(measurement.DeviceID), measurement.PointTag,
measurement.SignalTypeID, measurement.SignalReference, measurement.Adder, measurement.Multiplier, ToNotNull(measurement.Description),
database.Bool(measurement.Enabled), Thread.CurrentPrincipal.Identity.Name, database.UtcNow, measurement.SignalID);
}
}
finally
{
if (createdConnection)
database?.Dispose();
}
}
/// <summary>
/// Retrieves devices for output stream.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamID">ID of the output stream to filter out devices that already exist.</param>
/// <returns>Collection of <see cref="Device"/>.</returns>
public static ObservableCollection<Device> GetNewDevicesForOutputStream(AdoDataConnection database, int outputStreamID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<Device> deviceList = new ObservableCollection<Device>();
DataTable deviceTable;
string query;
// Note that OleDB does not support parameterized sub-query.
if (database.DatabaseType == DatabaseType.Access)
{
query = database.ParameterizedQueryString("SELECT * FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Acronym NOT IN "
+ "(SELECT Acronym FROM OutputStreamDevice WHERE AdapterID = " + outputStreamID + ") ORDER BY Acronym", "nodeID", "isConcentrator");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), database.Bool(false));
}
else
{
query = database.ParameterizedQueryString("SELECT * FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Acronym NOT IN "
+ "(SELECT Acronym FROM OutputStreamDevice WHERE AdapterID = {2}) ORDER BY Acronym", "nodeID", "isConcentrator", "adapterID");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), database.Bool(false), outputStreamID);
}
foreach (DataRow row in deviceTable.Rows)
{
deviceList.Add(new Device
{
NodeID = database.Guid(row, "NodeID"),
ID = row.ConvertField<int>("ID"),
ParentID = row.ConvertNullableField<int>("ParentID"),
UniqueID = database.Guid(row, "UniqueID"),
Acronym = row.Field<string>("Acronym"),
Name = row.Field<string>("Name"),
IsConcentrator = Convert.ToBoolean(row.Field<object>("IsConcentrator")),
CompanyID = row.ConvertNullableField<int>("CompanyID"),
HistorianID = row.ConvertNullableField<int>("HistorianID"),
AccessID = row.ConvertField<int>("AccessID"),
VendorDeviceID = row.ConvertNullableField<int>("VendorDeviceID"),
ProtocolID = row.ConvertNullableField<int>("ProtocolID"),
Longitude = row.ConvertNullableField<decimal>("Longitude"),
Latitude = row.ConvertNullableField<decimal>("Latitude"),
InterconnectionID = row.ConvertNullableField<int>("InterconnectionID"),
ConnectionString = ParseConnectionString(row.Field<string>("ConnectionString").ToNonNullString()),
AlternateCommandChannel = ParseAlternateCommand(row.Field<string>("ConnectionString").ToNonNullString()),
TimeZone = row.Field<string>("TimeZone"),
FramesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30),
TimeAdjustmentTicks = Convert.ToInt64(row.Field<object>("TimeAdjustmentTicks")),
DataLossInterval = row.ConvertField<double>("DataLossInterval"),
ContactList = row.Field<string>("ContactList"),
MeasuredLines = row.ConvertNullableField<int>("MeasuredLines"),
LoadOrder = row.ConvertField<int>("LoadOrder"),
Enabled = false, // We will use enable flag for check boxes on output stream device wizard so that we do not need to add selected flag.
CreatedOn = row.Field<DateTime>("CreatedOn"),
AllowedParsingExceptions = Convert.ToInt32(row.Field<object>("AllowedParsingExceptions")),
ParsingExceptionWindow = row.ConvertField<double>("ParsingExceptionWindow"),
DelayedConnectionInterval = row.ConvertField<double>("DelayedConnectionInterval"),
AllowUseOfCachedConfiguration = Convert.ToBoolean(row.Field<object>("AllowUseOfCachedConfiguration")),
AutoStartDataParsingSequence = Convert.ToBoolean(row.Field<object>("AutoStartDataParsingSequence")),
SkipDisableRealTimeData = Convert.ToBoolean(row.Field<object>("SkipDisableRealTimeData")),
MeasurementReportingInterval = Convert.ToInt32(row.Field<object>("MeasurementReportingInterval")),
ConnectOnDemand = Convert.ToBoolean(row.Field<object>("ConnectOnDemand")),
m_companyName = row.Field<string>("CompanyName"),
m_companyAcronym = row.Field<string>("CompanyAcronym"),
m_historianAcronym = row.Field<string>("HistorianAcronym"),
m_vendorDeviceName = row.Field<string>("VendorDeviceName"),
m_vendorAcronym = row.Field<string>("VendorAcronym"),
m_protocolName = row.Field<string>("ProtocolName"),
m_protocolCategory = row.Field<string>("Category"),
m_interconnectionName = row.Field<string>("InterconnectionName"),
m_nodeName = row.Field<string>("NodeName"),
m_parentAcronym = row.Field<string>("ParentAcronym"),
m_originalSource = row.Field<string>("OriginalSource")
});
}
return deviceList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Device"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="deviceType"><see cref="DeviceType"/> to filter data.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <param name="showAll">boolean flag indicates if all the devices should be returned irrespective of node.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of devices defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, DeviceType deviceType = DeviceType.DirectConnected, bool isOptional = false, bool showAll = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> deviceList = new Dictionary<int, string>();
DataTable deviceTable;
string query;
if (isOptional)
deviceList.Add(0, "Select Device");
if (deviceType == DeviceType.Concentrator)
{
query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Device WHERE IsConcentrator = {0} AND NodeID = {1} AND Enabled= {2} ORDER BY LoadOrder",
"isConcentrator", "nodeID", "Enabled");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID(), database.Bool(true));
}
else if (deviceType == DeviceType.DirectConnected)
{
query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Device WHERE IsConcentrator = {0} AND NodeID = {1} AND Enabled= {2} ORDER BY LoadOrder",
"isConcentrator", "nodeID", "Enabled");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(false), database.CurrentNodeID(), database.Bool(true));
}
else
{
if (showAll)
{
query = "SELECT ID, Acronym FROM Device ORDER BY LoadOrder";
deviceTable = database.Connection.RetrieveData(database.AdapterType, query);
}
else
{
query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Device WHERE NodeID = {0} ORDER BY LoadOrder", "nodeID");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
}
}
foreach (DataRow row in deviceTable.Rows)
deviceList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym");
return deviceList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Adds measurement groups to <see cref="Subscriber"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberID">ID of the <see cref="Subscriber"/> to which measurements to be added.</param>
/// <param name="measurementGroupsToBeAdded">List of <see cref="MeasurementGroup"/> IDs to be added.</param>
/// <param name="allowed">boolean flag to indicate if measurement groups are allowed or denied.</param>
/// <returns>string, indicating success for UI display.</returns>
public static string AddMeasurementGroups(AdoDataConnection database, Guid subscriberID, List<int> measurementGroupsToBeAdded, bool allowed)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
foreach (int id in measurementGroupsToBeAdded)
{
query = database.ParameterizedQueryString("INSERT INTO SubscriberMeasurementGroup (NodeID, SubscriberID, MeasurementGroupID, Allowed, UpdatedOn, " +
"UpdatedBy, CreatedOn, CreatedBy) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})", "nodeID", "subscriberID", "measurementGroupID",
"allowed", "updatedOn", "updatedBy", "createdOn", "createdBy");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), database.Guid(subscriberID), id, database.Bool(allowed),
database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser);
}
if (allowed)
return "Measurement groups added to allowed measurement groups list for subscriber successfully";
else
return "Measurement groups added to denied measurement groups list for subscriber successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
static int Main(string[] args)
{
// System settings
ConfigurationFile configFile = ConfigurationFile.Current;
CategorizedSettingsElementCollection systemSettings = configFile.Settings["systemSettings"];
systemSettings.Add("NodeID", Guid.NewGuid().ToString(), "Unique Node ID");
Guid nodeID = systemSettings["NodeID"].ValueAs<Guid>();
bool useMemoryCache = systemSettings["UseMemoryCache"].ValueAsBoolean(false);
string connectionString = systemSettings["ConnectionString"].Value;
string nodeIDQueryString = null;
string parameterizedQuery;
int protocolID, signalTypeID;
// Define guid with query string delimiters according to database needs
Dictionary<string, string> settings = connectionString.ParseKeyValuePairs();
string setting;
if (settings.TryGetValue("Provider", out setting))
{
// Check if provider is for Access since it uses braces as Guid delimiters
if (setting.StartsWith("Microsoft.Jet.OLEDB", StringComparison.OrdinalIgnoreCase))
nodeIDQueryString = "{" + nodeID + "}";
}
if (string.IsNullOrWhiteSpace(nodeIDQueryString))
nodeIDQueryString = "'" + nodeID + "'";
using (AdoDataConnection database = new AdoDataConnection("systemSettings"))
{
IDbConnection connection = database.Connection;
if (Convert.ToInt32(connection.ExecuteScalar("SELECT COUNT(*) FROM Protocol WHERE Acronym='WAV'")) == 0)
{
if (database.IsSQLServer || database.IsJetEngine)
connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, [Type], Category, AssemblyName, TypeName) VALUES('WAV', 'Wave Form Input Adapter', 'Frame', 'Audio', 'WavInputAdapter.dll', 'WavInputAdapter.WavInputAdapter')");
else
connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName) VALUES('WAV', 'Wave Form Input Adapter', 'Frame', 'Audio', 'WavInputAdapter.dll', 'WavInputAdapter.WavInputAdapter')");
}
protocolID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM Protocol WHERE Acronym='WAV'"));
// Typically these values should be defined as analogs, however, we use a voltage magnitude signal type
// since these types of values can be better graphed with auto-scaling in the visualization tools
signalTypeID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym='VPHM'"));
//signalTypeID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym='ALOG'"));
string pathRoot = FilePath.GetDirectoryName((args.Length > 0) ? args[0] : systemSettings["MusicDirectory"].Value);
string sourcePath = Path.Combine(pathRoot, "*" + Path.DirectorySeparatorChar + "*.wav");
foreach (string sourceFileName in FilePath.GetFileList(sourcePath))
{
WaveFile sourceWave;
string fileName = FilePath.GetFileName(sourceFileName);
char[] invalidChars = { '\'', '[', ']', '(', ')', ',', '-', '.' };
Console.WriteLine("Loading metadata for \"{0}\"...\r\n", fileName);
sourceWave = WaveFile.Load(sourceFileName, false);
fileName = FilePath.GetFileNameWithoutExtension(fileName).RemoveDuplicateWhiteSpace().RemoveCharacters(invalidChars.Contains).Trim();
string acronym = fileName.Replace(' ', '_').ToUpper() + "_" + (int)(sourceWave.SampleRate / SI.Kilo) + "KHZ";
string name = GenerateSongName(sourceWave, fileName);
Console.WriteLine(" Acronym = {0}", acronym);
Console.WriteLine(" Name = {0}", name);
Console.WriteLine("");
// Check to see if device exists
if (Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE Acronym = {0}", "acronym"), acronym)) == 0)
{
parameterizedQuery = database.ParameterizedQueryString("INSERT INTO Device(NodeID, Acronym, Name, ProtocolID, FramesPerSecond, " +
"MeasurementReportingInterval, ConnectionString, Enabled) VALUES(" + nodeIDQueryString + ", {0}, {1}, {2}, {3}, {4}, {5}, {6})",
"acronym", "name", "protocolID", "framesPerSecond", "measurementReportingInterval",
"connectionString", "enabled");
// Insert new device record
connection.ExecuteNonQuery(parameterizedQuery, acronym, name, protocolID, sourceWave.SampleRate, 1000000, string.Format("wavFileName={0}; connectOnDemand=true; outputSourceIDs={1}; memoryCache={2}", FilePath.GetAbsolutePath(sourceFileName), acronym, useMemoryCache), database.Bool(true));
int deviceID = Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT ID FROM Device WHERE Acronym = {0}", "acronym"), acronym));
string pointTag;
// Add a measurement for each defined wave channel
for (int i = 0; i < sourceWave.Channels; i++)
{
int index = i + 1;
pointTag = acronym + ":WAVA" + index;
parameterizedQuery = database.ParameterizedQueryString("INSERT INTO Measurement(DeviceID, PointTag, SignalTypeID, SignalReference, Description, " +
"Enabled) VALUES({0}, {1}, {2}, {3}, {4}, {5})", "deviceID", "pointTag", "signalTypeID", "signalReference", "description", "enabled");
// Insert new measurement record
connection.ExecuteNonQuery(parameterizedQuery, (object)deviceID, pointTag, signalTypeID, acronym + "-AV" + index, name + " - channel " + index, database.Bool(true));
//index = Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT PointID FROM Measurement WHERE PointTag = {0}", "pointTag"), pointTag));
}
// Disable all non analog measurements that may be associated with this device
connection.ExecuteNonQuery(database.ParameterizedQueryString("UPDATE Measurement SET Enabled = {0} WHERE DeviceID = {1} AND SignalTypeID <> {2}", "enabled", "deviceID", "signalTypeID"), database.Bool(false), deviceID, signalTypeID);
}
}
}
return 0;
}
/// <summary>
/// Saves <see cref="Alarm"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="alarm">Information about <see cref="Alarm"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Alarm alarm)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string updateQuery;
Alarm createdAlarm = alarm;
string successMessage = "Alarm information saved successfully";
object associatedMeasurementId = (alarm.AssociatedMeasurementID != null) ? database.Guid(alarm.AssociatedMeasurementID.Value) : DBNull.Value;
AlarmMonitor monitor = AlarmMonitor.Default;
if (alarm.SetPointEnabled && (object)alarm.SetPoint == null)
throw new InvalidOperationException($"Set Point is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Set Point.");
if (alarm.ToleranceEnabled && (object)alarm.Tolerance == null)
throw new InvalidOperationException($"Tolerance is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Tolerance.");
if (alarm.DelayEnabled && (object)alarm.Delay == null)
throw new InvalidOperationException($"Delay is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Delay.");
if (alarm.HysteresisEnabled && (object)alarm.Hysteresis == null)
throw new InvalidOperationException($"Hysteresis is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Hysteresis.");
if (alarm.ID == 0)
{
string query = database.ParameterizedQueryString("INSERT INTO Alarm (NodeID, TagName, SignalID, AssociatedMeasurementID, Description, Severity, Operation, SetPoint, Tolerance, Delay, " +
"Hysteresis, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16})",
"nodeID", "tagName", "signalId", "associatedMeasurementId", "description", "severity", "operation", "setPoint", "tolerance", "delay",
"hysteresis", "loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (alarm.NodeID != Guid.Empty) ? database.Guid(alarm.NodeID) : database.CurrentNodeID(),
alarm.TagName.ToNotNull(), database.Guid(alarm.SignalID), associatedMeasurementId, alarm.Description.ToNotNull(), alarm.Severity, alarm.Operation, alarm.SetPoint.ToNotNull(),
alarm.Tolerance.ToNotNull(), alarm.Delay.ToNotNull(), alarm.Hysteresis.ToNotNull(), alarm.LoadOrder, database.Bool(alarm.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
CommonFunctions.CurrentUser, database.UtcNow);
createdAlarm = GetAlarm(database, string.Format("WHERE TagName = '{0}'", alarm.TagName));
}
else
{
string query = database.ParameterizedQueryString("UPDATE Alarm SET NodeID = {0}, TagName = {1}, SignalID = {2}, AssociatedMeasurementID = {3}, Description = {4}, Severity = {5}, " +
"Operation = {6}, SetPoint = {7}, Tolerance = {8}, Delay = {9}, Hysteresis = {10}, LoadOrder = {11}, Enabled = {12}, UpdatedBy = {13}, UpdatedOn = {14} WHERE ID = {15}",
"nodeID", "tagName", "signalId", "associatedMeasurementId", "description", "severity", "operation", "setPoint", "tolerance", "delay", "hysteresis",
"loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (alarm.NodeID != Guid.Empty) ? database.Guid(alarm.NodeID) : database.CurrentNodeID(),
alarm.TagName, database.Guid(alarm.SignalID), associatedMeasurementId, alarm.Description.ToNotNull(), alarm.Severity, alarm.Operation,
alarm.SetPoint.ToNotNull(), alarm.Tolerance.ToNotNull(), alarm.Delay.ToNotNull(), alarm.Hysteresis.ToNotNull(), alarm.LoadOrder, database.Bool(alarm.Enabled),
CommonFunctions.CurrentUser, database.UtcNow, alarm.ID);
}
updateQuery = database.ParameterizedQueryString("UPDATE Alarm SET AssociatedMeasurementID = {0} WHERE ID = {1}", "associatedMeasurementId", "id");
if (alarm.CreateAssociatedMeasurement && (object)alarm.AssociatedMeasurementID == null)
{
alarm.AssociatedMeasurementID = CreateAlarmMeasurement(database, createdAlarm);
if ((object)alarm.AssociatedMeasurementID != null)
database.Connection.ExecuteNonQuery(updateQuery, DefaultTimeout, database.Guid(alarm.AssociatedMeasurementID.Value), createdAlarm.ID);
else
successMessage += " but failed to create associated measurement";
}
else if (!alarm.CreateAssociatedMeasurement && (object)alarm.AssociatedMeasurementID != null)
{
database.Connection.ExecuteNonQuery(updateQuery, DefaultTimeout, DBNull.Value, createdAlarm.ID);
DeleteAlarmMeasurement(database, createdAlarm.AssociatedMeasurementID.Value);
alarm.AssociatedMeasurementID = null;
}
if ((object)monitor != null)
monitor.UpdateDefinedAlarms();
try
{
CommonFunctions.SendCommandToService("ReloadConfig");
}
catch (Exception ex)
{
CommonFunctions.LogException(database, "Alarm Save", ex);
}
return successMessage;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="OutputStreamDevice"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamDevice">Information about <see cref="OutputStreamDevice"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, OutputStreamDevice outputStreamDevice)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (outputStreamDevice.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO OutputStreamDevice (NodeID, AdapterID, IDCode, Acronym, BpaAcronym, Name, " +
"PhasorDataFormat, FrequencyDataFormat, AnalogDataFormat, CoordinateFormat, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn)" +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15})", "nodeID", "adapterID", "idCode", "acronym",
"bpaAcronym", "name", "phasorDataFormat", "frequencyDataFormat", "analogDataFormat", "coordinateFormat", "loadOrder", "enabled",
"updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDevice.AdapterID, outputStreamDevice.IDCode,
outputStreamDevice.Acronym, outputStreamDevice.BpaAcronym.ToNotNull(), outputStreamDevice.Name, outputStreamDevice.PhasorDataFormat.ToNotNull(),
outputStreamDevice.FrequencyDataFormat.ToNotNull(), outputStreamDevice.AnalogDataFormat.ToNotNull(), outputStreamDevice.CoordinateFormat.ToNotNull(),
outputStreamDevice.LoadOrder, database.Bool(outputStreamDevice.Enabled), CommonFunctions.CurrentUser,
database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
OutputStreamDevice originalDevice = GetOutputStreamDevice(database, "WHERE ID = " + outputStreamDevice.ID);
query = database.ParameterizedQueryString("UPDATE OutputStreamDevice SET NodeID = {0}, AdapterID = {1}, IDCode = {2}, Acronym = {3}, BpaAcronym = {4}, " +
"Name = {5}, PhasorDataFormat = {6}, FrequencyDataFormat = {7}, AnalogDataFormat = {8}, CoordinateFormat = {9}, LoadOrder = {10}, Enabled = {11}, " +
" UpdatedBy = {12}, UpdatedOn = {13} WHERE ID = {14}", "nodeID", "adapterID", "idCode", "acronym", "bpaAcronym", "name",
"phasorDataFormat", "frequencyDataFormat", "analogDataFormat", "coordinateFormat", "loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(outputStreamDevice.NodeID), outputStreamDevice.AdapterID, outputStreamDevice.IDCode,
outputStreamDevice.Acronym, outputStreamDevice.BpaAcronym.ToNotNull(), outputStreamDevice.Name, outputStreamDevice.PhasorDataFormat.ToNotNull(),
outputStreamDevice.FrequencyDataFormat.ToNotNull(), outputStreamDevice.AnalogDataFormat.ToNotNull(), outputStreamDevice.CoordinateFormat.ToNotNull(),
outputStreamDevice.LoadOrder, database.Bool(outputStreamDevice.Enabled), CommonFunctions.CurrentUser,
database.UtcNow, outputStreamDevice.ID);
if (originalDevice != null && originalDevice.Acronym != outputStreamDevice.Acronym)
{
IList<int> keys = OutputStreamMeasurement.LoadKeys(database, originalDevice.AdapterID);
foreach (OutputStreamMeasurement measurement in OutputStreamMeasurement.Load(database, keys))
{
measurement.SignalReference = measurement.SignalReference.Replace(originalDevice.Acronym + "-", outputStreamDevice.Acronym + "-");
OutputStreamMeasurement.Save(database, measurement);
}
}
}
return "OutputStreamDevice information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Alarm"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and tag name of alarms defined in the database.</returns>
public static Dictionary <int, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <int, string> alarmList = new Dictionary <int, string>();
if (isOptional)
{
alarmList.Add(0, "Select Alarm");
}
string query = database.ParameterizedQueryString("SELECT ID, TagName FROM Alarm WHERE Enabled = {0} ORDER BY LoadOrder", "enabled");
DataTable nodeTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true));
foreach (DataRow row in nodeTable.Rows)
{
alarmList[row.ConvertField <int>("ID")] = row.Field <string>("TagName");
}
return(alarmList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Node"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of nodes defined in the database.</returns>
public static Dictionary<Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<Guid, string> nodeList = new Dictionary<Guid, string>();
if (isOptional)
nodeList.Add(Guid.Empty, "Select Node");
string query = database.ParameterizedQueryString("SELECT ID, Name FROM Node WHERE Enabled = {0} ORDER BY LoadOrder", "enabled");
DataTable nodeTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true));
foreach (DataRow row in nodeTable.Rows)
{
nodeList[database.Guid(row, "ID")] = row.Field<string>("Name");
}
return nodeList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="CalculatedMeasurement"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="calculatedMeasurement">Information about <see cref="CalculatedMeasurement"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, CalculatedMeasurement calculatedMeasurement)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (calculatedMeasurement.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO CalculatedMeasurement (NodeID, Acronym, Name, AssemblyName, TypeName, ConnectionString, " +
"ConfigSection, InputMeasurements, OutputMeasurements, MinimumMeasurementsToUse, FramesPerSecond, LagTime, LeadTime, UseLocalClockAsRealTime, " +
"AllowSortsByArrival, LoadOrder, Enabled, IgnoreBadTimeStamps, TimeResolution, AllowPreemptivePublishing, DownsamplingMethod, " +
"PerformTimeReasonabilityCheck, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, " +
"{9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25})", "nodeID", "acronym", "name",
"assemblyName", "typeName", "connectionString", "configSection", "inputMeasurements", "outputMeasurements", "minimumMeasurementsToUse",
"framesPerSecond", "lagTime", "leadTime", "useLocalClockAsRealTime", "allowSortsByArrival", "loadOrder", "enabled", "ignoreBadTimeStamps",
"timeResolution", "allowPreemptivePublishing", "downsamplingMethod", "performTimeReasonabilityCheck", "updatedBy", "updatedOn",
"createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query,
DefaultTimeout, database.CurrentNodeID(), calculatedMeasurement.Acronym.Replace(" ", "").ToUpper(), calculatedMeasurement.Name.ToNotNull(),
calculatedMeasurement.AssemblyName, calculatedMeasurement.TypeName, calculatedMeasurement.ConnectionString.ToNotNull(), calculatedMeasurement.ConfigSection.ToNotNull(),
calculatedMeasurement.InputMeasurements.ToNotNull(), calculatedMeasurement.OutputMeasurements.ToNotNull(), calculatedMeasurement.MinimumMeasurementsToUse,
calculatedMeasurement.FramesPerSecond, calculatedMeasurement.LagTime, calculatedMeasurement.LeadTime, database.Bool(calculatedMeasurement.UseLocalClockAsRealTime),
database.Bool(calculatedMeasurement.AllowSortsByArrival), calculatedMeasurement.LoadOrder, database.Bool(calculatedMeasurement.Enabled),
database.Bool(calculatedMeasurement.IgnoreBadTimeStamps), calculatedMeasurement.TimeResolution, database.Bool(calculatedMeasurement.AllowPreemptivePublishing),
calculatedMeasurement.DownsamplingMethod, database.Bool(calculatedMeasurement.PerformTimestampReasonabilityCheck), CommonFunctions.CurrentUser,
database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE CalculatedMeasurement SET NodeID = {0}, Acronym = {1}, Name = {2}, AssemblyName = {3}, " +
"TypeName = {4}, ConnectionString = {5}, ConfigSection = {6}, InputMeasurements = {7}, OutputMeasurements = {8}, MinimumMeasurementsToUse = {9}, " +
"FramesPerSecond = {10}, LagTime = {11}, LeadTime = {12}, UseLocalClockAsRealTime = {13}, AllowSortsByArrival = {14}, LoadOrder = {15}, " +
"Enabled = {16}, IgnoreBadTimeStamps = {17}, TimeResolution = {18}, AllowPreemptivePublishing = {19}, DownsamplingMethod = {20}, " +
"PerformTimeReasonabilityCheck = {21}, UpdatedBy = {22}, UpdatedOn = {23} WHERE ID = {24}", "nodeID", "acronym", "name", "assemblyName",
"typeName", "connectionString", "configSection", "inputMeasurements", "outputMeasurements", "minimumMeasurementsToUse", "framesPerSecond",
"lagTime", "leadTime", "useLocalClockAsRealTime", "allowSortsByArrival", "loadOrder", "enabled", "ignoreBadTimeStamps", "timeResolution",
"allowPreemptivePublishing", "downsamplingMethod", "performTimeReasonabilityCheck", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(calculatedMeasurement.NodeID),
calculatedMeasurement.Acronym.Replace(" ", "").ToUpper(), calculatedMeasurement.Name.ToNotNull(), calculatedMeasurement.AssemblyName,
calculatedMeasurement.TypeName, calculatedMeasurement.ConnectionString.ToNotNull(), calculatedMeasurement.ConfigSection.ToNotNull(), calculatedMeasurement.InputMeasurements.ToNotNull(),
calculatedMeasurement.OutputMeasurements.ToNotNull(), calculatedMeasurement.MinimumMeasurementsToUse, calculatedMeasurement.FramesPerSecond,
calculatedMeasurement.LagTime, calculatedMeasurement.LeadTime, database.Bool(calculatedMeasurement.UseLocalClockAsRealTime), database.Bool(calculatedMeasurement.AllowSortsByArrival),
calculatedMeasurement.LoadOrder, database.Bool(calculatedMeasurement.Enabled), database.Bool(calculatedMeasurement.IgnoreBadTimeStamps), calculatedMeasurement.TimeResolution,
database.Bool(calculatedMeasurement.AllowPreemptivePublishing), calculatedMeasurement.DownsamplingMethod, database.Bool(calculatedMeasurement.PerformTimestampReasonabilityCheck),
CommonFunctions.CurrentUser, database.UtcNow, calculatedMeasurement.ID);
}
return("Calculated measurement information saved successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Historian"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <param name="includeStatHistorian">Indicates if statistical historian included in the collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of historians defined in the database.</returns>
public static Dictionary <int, string> GetLookupList(AdoDataConnection database, bool isOptional = false, bool includeStatHistorian = true)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <int, string> historianList = new Dictionary <int, string>();
if (isOptional)
{
historianList.Add(0, "Select Historian");
}
string query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Historian WHERE Enabled = {0} AND NodeID = {1} ORDER BY LoadOrder", "enabled", "nodeID");
DataTable historianTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID());
foreach (DataRow row in historianTable.Rows)
{
if (!includeStatHistorian)
{
if (row.Field <string>("Acronym").ToUpper() != "STAT")
{
historianList[row.ConvertField <int>("ID")] = row.Field <string>("Acronym");
}
}
else
{
historianList[row.ConvertField <int>("ID")] = row.Field <string>("Acronym");
}
}
return(historianList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="UserAccount"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="userAccount">Information about <see cref="UserAccount"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, UserAccount userAccount)
{
const string ErrorMessage = "User name already exists.";
bool createdConnection = false;
string query;
string userAccountSID;
int existing;
try
{
createdConnection = CreateConnection(ref database);
string pColumn = "Password";
if (database.IsJetEngine)
{
pColumn = "[Password]";
}
object changePasswordOn = userAccount.ChangePasswordOn;
if (userAccount.ChangePasswordOn == DateTime.MinValue)
{
changePasswordOn = (object)DBNull.Value;
}
else if (database.IsJetEngine)
{
changePasswordOn = userAccount.ChangePasswordOn.ToOADate();
}
userAccountSID = UserInfo.UserNameToSID(userAccount.Name);
if (!userAccount.UseADAuthentication || !UserInfo.IsUserSID(userAccountSID))
{
userAccountSID = userAccount.Name;
}
if (userAccount.ID == Guid.Empty)
{
existing = Convert.ToInt32(database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM UserAccount WHERE Name = {0}", "name"), DefaultTimeout, userAccountSID));
if (existing > 0)
{
throw new InvalidOperationException(ErrorMessage);
}
query = database.ParameterizedQueryString("INSERT INTO UserAccount (Name, " + pColumn + ", FirstName, LastName, DefaultNodeID, Phone, Email, " +
"LockedOut, UseADAuthentication, ChangePasswordOn, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, " +
"{9}, {10}, {11}, {12}, {13})", "name", "password", "firstName", "lastName", "defaultNodeID", "phone", "email", "lockedOut", "useADAuthentication",
"changePasswordOn", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, userAccountSID,
userAccount.Password.ToNotNull(), userAccount.FirstName.ToNotNull(), userAccount.LastName.ToNotNull(), database.CurrentNodeID(),
userAccount.Phone.ToNotNull(), userAccount.Email.ToNotNull(), database.Bool(userAccount.LockedOut), database.Bool(userAccount.UseADAuthentication),
changePasswordOn, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
CommonFunctions.LogEvent(string.Format("New user \"{0}\" created successfully by user \"{1}\".", userAccount.Name, CommonFunctions.CurrentUser), 2);
}
else
{
existing = Convert.ToInt32(database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM UserAccount WHERE Name = {0} AND ID <> {1}", "name", "id"), DefaultTimeout, userAccountSID, userAccount.ID));
if (existing > 0)
{
throw new InvalidOperationException(ErrorMessage);
}
query = database.ParameterizedQueryString("UPDATE UserAccount SET Name = {0}, " + pColumn + " = {1}, FirstName = {2}, LastName = {3}, " +
"DefaultNodeID = {4}, Phone = {5}, Email = {6}, LockedOut = {7}, UseADAuthentication = {8}, ChangePasswordOn = {9}, UpdatedBy = {10}, " +
"UpdatedOn = {11} WHERE ID = {12}", "name", "password", "firstName", "lastName", "defaultNodeID", "phone", "email", "lockedOut",
"useADAuthentication", "changePasswordOn", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, userAccountSID,
userAccount.Password.ToNotNull(), userAccount.FirstName.ToNotNull(), userAccount.LastName.ToNotNull(), database.Guid(userAccount.DefaultNodeID),
userAccount.Phone.ToNotNull(), userAccount.Email.ToNotNull(), database.Bool(userAccount.LockedOut), database.Bool(userAccount.UseADAuthentication),
changePasswordOn, CommonFunctions.CurrentUser, database.UtcNow, database.Guid(userAccount.ID));
CommonFunctions.LogEvent(string.Format("Information about user \"{0}\" updated successfully by user \"{1}\".", userAccount.Name, CommonFunctions.CurrentUser), 3);
}
return("User account information saved successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="Adapter"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="adapter">Information about <see cref="Adapter"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Adapter adapter)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string tableName;
if (adapter.Type == AdapterType.Action)
{
tableName = "CustomActionAdapter";
}
else if (adapter.Type == AdapterType.Input)
{
tableName = "CustomInputAdapter";
}
else
{
tableName = "CustomOutputAdapter";
}
if (adapter.ID == 0)
{
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("INSERT INTO " + tableName + " (NodeID, AdapterName, AssemblyName, TypeName, ConnectionString, LoadOrder, " +
"Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10})",
"nodeID", "adapterName", "assemblyName", "typeName", "connectionString", "loadOrder",
"enabled", "updatedBy", "updatedOn", "createdBy", "createdOn"), DefaultTimeout,
(adapter.NodeID != Guid.Empty) ? database.Guid(adapter.NodeID) : database.CurrentNodeID(), adapter.AdapterName, adapter.AssemblyName,
adapter.TypeName, adapter.ConnectionString.ToNotNull(), adapter.LoadOrder, database.Bool(adapter.Enabled), CommonFunctions.CurrentUser,
database.UtcNow, CommonFunctions.CurrentUser,
database.UtcNow);
}
else
{
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("UPDATE " + tableName + " SET NodeID = {0}, AdapterName = {1}, AssemblyName = {2}, " +
"TypeName = {3}, ConnectionString = {4}, LoadOrder = {5}, Enabled = {6}, UpdatedBy = {7}, " +
"UpdatedOn = {8} WHERE ID = {9}", "nodeID", "adapterName", "assemblyName", "typeName", "connectionString",
"loadOrder", "enabled", "updatedBy", "updatedOn", "id"), DefaultTimeout, (adapter.NodeID != Guid.Empty) ? database.Guid(adapter.NodeID) : database.CurrentNodeID(),
adapter.AdapterName, adapter.AssemblyName, adapter.TypeName, adapter.ConnectionString.ToNotNull(), adapter.LoadOrder, database.Bool(adapter.Enabled),
CommonFunctions.CurrentUser, database.UtcNow, adapter.ID);
}
return("Adapter information saved successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Adapter"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="adapterType">Type of the <see cref="Adapter"/>.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of adapters defined in the database.</returns>
public static Dictionary <int, string> GetLookupList(AdoDataConnection database, AdapterType adapterType, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <int, string> adapterList = new Dictionary <int, string>();
if (isOptional)
{
adapterList.Add(0, "Select Adapter");
}
string tableName;
if (adapterType == AdapterType.Action)
{
tableName = "CustomActionAdapter";
}
else if (adapterType == AdapterType.Input)
{
tableName = "CustomInputAdapter";
}
else
{
tableName = "CustomOutputAdapter";
}
string query = database.ParameterizedQueryString("SELECT ID, Name FROM " + tableName + " WHERE Enabled = {0} ORDER BY LoadOrder", "enabled");
DataTable adapterTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true));
foreach (DataRow row in adapterTable.Rows)
{
adapterList[row.ConvertField <int>("ID")] = row.Field <string>("Name");
}
return(adapterList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="Alarm"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="alarm">Information about <see cref="Alarm"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Alarm alarm)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string updateQuery;
Alarm createdAlarm = alarm;
string successMessage = "Alarm information saved successfully";
object associatedMeasurementId = (alarm.AssociatedMeasurementID != null) ? database.Guid(alarm.AssociatedMeasurementID.Value) : DBNull.Value;
AlarmMonitor monitor = AlarmMonitor.Default;
if (alarm.SetPointEnabled && (object)alarm.SetPoint == null)
{
throw new InvalidOperationException($"Set Point is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Set Point.");
}
if (alarm.ToleranceEnabled && (object)alarm.Tolerance == null)
{
throw new InvalidOperationException($"Tolerance is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Tolerance.");
}
if (alarm.DelayEnabled && (object)alarm.Delay == null)
{
throw new InvalidOperationException($"Delay is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Delay.");
}
if (alarm.HysteresisEnabled && (object)alarm.Hysteresis == null)
{
throw new InvalidOperationException($"Hysteresis is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Hysteresis.");
}
if (alarm.ID == 0)
{
string query = database.ParameterizedQueryString("INSERT INTO Alarm (NodeID, TagName, SignalID, AssociatedMeasurementID, Description, Severity, Operation, SetPoint, Tolerance, Delay, " +
"Hysteresis, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16})",
"nodeID", "tagName", "signalId", "associatedMeasurementId", "description", "severity", "operation", "setPoint", "tolerance", "delay",
"hysteresis", "loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (alarm.NodeID != Guid.Empty) ? database.Guid(alarm.NodeID) : database.CurrentNodeID(),
alarm.TagName.ToNotNull(), database.Guid(alarm.SignalID), associatedMeasurementId, alarm.Description.ToNotNull(), alarm.Severity, alarm.Operation, alarm.SetPoint.ToNotNull(),
alarm.Tolerance.ToNotNull(), alarm.Delay.ToNotNull(), alarm.Hysteresis.ToNotNull(), alarm.LoadOrder, database.Bool(alarm.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
CommonFunctions.CurrentUser, database.UtcNow);
createdAlarm = GetAlarm(database, string.Format("WHERE TagName = '{0}'", alarm.TagName));
}
else
{
string query = database.ParameterizedQueryString("UPDATE Alarm SET NodeID = {0}, TagName = {1}, SignalID = {2}, AssociatedMeasurementID = {3}, Description = {4}, Severity = {5}, " +
"Operation = {6}, SetPoint = {7}, Tolerance = {8}, Delay = {9}, Hysteresis = {10}, LoadOrder = {11}, Enabled = {12}, UpdatedBy = {13}, UpdatedOn = {14} WHERE ID = {15}",
"nodeID", "tagName", "signalId", "associatedMeasurementId", "description", "severity", "operation", "setPoint", "tolerance", "delay", "hysteresis",
"loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (alarm.NodeID != Guid.Empty) ? database.Guid(alarm.NodeID) : database.CurrentNodeID(),
alarm.TagName, database.Guid(alarm.SignalID), associatedMeasurementId, alarm.Description.ToNotNull(), alarm.Severity, alarm.Operation,
alarm.SetPoint.ToNotNull(), alarm.Tolerance.ToNotNull(), alarm.Delay.ToNotNull(), alarm.Hysteresis.ToNotNull(), alarm.LoadOrder, database.Bool(alarm.Enabled),
CommonFunctions.CurrentUser, database.UtcNow, alarm.ID);
}
updateQuery = database.ParameterizedQueryString("UPDATE Alarm SET AssociatedMeasurementID = {0} WHERE ID = {1}", "associatedMeasurementId", "id");
if (alarm.CreateAssociatedMeasurement && (object)alarm.AssociatedMeasurementID == null)
{
alarm.AssociatedMeasurementID = CreateAlarmMeasurement(database, createdAlarm);
if ((object)alarm.AssociatedMeasurementID != null)
{
database.Connection.ExecuteNonQuery(updateQuery, DefaultTimeout, database.Guid(alarm.AssociatedMeasurementID.Value), createdAlarm.ID);
}
else
{
successMessage += " but failed to create associated measurement";
}
}
else if (!alarm.CreateAssociatedMeasurement && (object)alarm.AssociatedMeasurementID != null)
{
database.Connection.ExecuteNonQuery(updateQuery, DefaultTimeout, DBNull.Value, createdAlarm.ID);
DeleteAlarmMeasurement(database, createdAlarm.AssociatedMeasurementID.Value);
alarm.AssociatedMeasurementID = null;
}
if ((object)monitor != null)
{
monitor.UpdateDefinedAlarms();
}
try
{
CommonFunctions.SendCommandToService("ReloadConfig");
}
catch (Exception ex)
{
CommonFunctions.LogException(database, "Alarm Save", ex);
}
return(successMessage);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="OutputStream"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStream">Information about <see cref="OutputStream"/>.</param>
/// <param name="mirrorMode">Boolean value to use mirror mode when saving output stream.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, OutputStream outputStream, bool mirrorMode)
{
bool createdConnection = false;
string query;
try
{
OutputStream oldOutputStream;
createdConnection = CreateConnection(ref database);
if (outputStream.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO OutputStream (NodeID, Acronym, Name, Type, ConnectionString, IDCode, CommandChannel, DataChannel, " +
"AutoPublishConfigFrame, AutoStartDataChannel, NominalFrequency, FramesPerSecond, LagTime, LeadTime, UseLocalClockAsRealTime, AllowSortsByArrival, " +
"LoadOrder, Enabled, IgnoreBadTimeStamps, TimeResolution, AllowPreemptivePublishing, DownSamplingMethod, DataFormat, CoordinateFormat, " +
"CurrentScalingValue, VoltageScalingValue, AnalogScalingValue, DigitalMaskValue, PerformTimeReasonabilityCheck, UpdatedBy, UpdatedOn, " +
"CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, " +
"{21}, {22}, {23}, {24}, {25}, {26}, {27}, {28}, {29}, {30}, {31}, {32})", "nodeID", "acronym", "name", "type", "connectionString", "idCode",
"commandChannel", "dataChannel", "autoPublishConfigFrame", "autoStartDataChannel", "nominalFrequency", "framesPerSecond", "lagTime", "leadTime",
"useLocalClockAsRealTime", "allowSortsByArrival", "loadOrder", "enabled", "ignoreBadTimeStamps", "timeResolution", "allowPreemptivePublishing",
"downSamplingMethod", "dataFormat", "coordinateFormat", "currentScalingValue", "voltageScalingValue", "analogScalingValue", "digitalMaskValue",
"performTimeReasonabilityCheck", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query,
database.CurrentNodeID(), outputStream.Acronym.Replace(" ", "").ToUpper(), outputStream.Name.ToNotNull(), outputStream.Type - 1, outputStream.ConnectionString.ToNotNull(),
outputStream.IDCode, outputStream.CommandChannel.ToNotNull(), outputStream.DataChannel.ToNotNull(), database.Bool(outputStream.AutoPublishConfigFrame), database.Bool(outputStream.AutoStartDataChannel),
outputStream.NominalFrequency, outputStream.FramesPerSecond, outputStream.LagTime, outputStream.LeadTime, database.Bool(outputStream.UseLocalClockAsRealTime), database.Bool(outputStream.AllowSortsByArrival),
outputStream.LoadOrder, database.Bool(outputStream.Enabled), database.Bool(outputStream.IgnoreBadTimeStamps), outputStream.TimeResolution, database.Bool(outputStream.AllowPreemptivePublishing),
outputStream.DownSamplingMethod.ToNotNull(), outputStream.DataFormat.ToNotNull(), outputStream.CoordinateFormat.ToNotNull(), outputStream.CurrentScalingValue,
outputStream.VoltageScalingValue, outputStream.AnalogScalingValue, outputStream.DigitalMaskValue, database.Bool(outputStream.PerformTimestampReasonabilityCheck),
CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
oldOutputStream = GetOutputStream(database, " WHERE ID = " + outputStream.ID);
query = database.ParameterizedQueryString("UPDATE OutputStream SET NodeID = {0}, Acronym = {1}, Name = {2}, Type = {3}, ConnectionString = {4}, " +
"IDCode = {5}, CommandChannel = {6}, DataChannel = {7}, AutoPublishConfigFrame = {8}, AutoStartDataChannel = {9}, NominalFrequency = {10}, " +
"FramesPerSecond = {11}, LagTime = {12}, LeadTime = {13}, UseLocalClockAsRealTime = {14}, AllowSortsByArrival = {15}, LoadOrder = {16}, " +
"Enabled = {17}, IgnoreBadTimeStamps = {18}, TimeResolution = {19}, AllowPreemptivePublishing = {20}, DownSamplingMethod = {21}, " +
"DataFormat = {22}, CoordinateFormat = {23}, CurrentScalingValue = {24}, VoltageScalingValue = {25}, AnalogScalingValue = {26}, " +
"DigitalMaskValue = {27}, PerformTimeReasonabilityCheck = {28}, UpdatedBy = {29}, UpdatedOn = {30} WHERE ID = {31}", "nodeID", "acronym", "name",
"type", "connectionString", "idCode", "commandChannel", "dataChannel", "autoPublishConfigFrame", "autoStartDataChannel", "nominalFrequency",
"framesPerSecond", "lagTime", "leadTime", "useLocalClockAsRealTime", "allowSortsByArrival", "loadOrder", "enabled", "ignoreBadTimeStamps",
"timeResolution", "allowPreemptivePublishing", "downsamplingMethod", "dataFormat", "coordinateFormat", "currentScalingValue", "voltageScalingValue",
"analogScalingValue", "digitalMaskValue", "performTimeReasonabilityCheck", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout,
database.Guid(outputStream.NodeID), outputStream.Acronym.Replace(" ", "").ToUpper(), outputStream.Name.ToNotNull(), outputStream.Type - 1, outputStream.ConnectionString.ToNotNull(),
outputStream.IDCode, outputStream.CommandChannel.ToNotNull(), outputStream.DataChannel.ToNotNull(), database.Bool(outputStream.AutoPublishConfigFrame), database.Bool(outputStream.AutoStartDataChannel),
outputStream.NominalFrequency, outputStream.FramesPerSecond, outputStream.LagTime, outputStream.LeadTime, database.Bool(outputStream.UseLocalClockAsRealTime),
database.Bool(outputStream.AllowSortsByArrival), outputStream.LoadOrder, database.Bool(outputStream.Enabled), database.Bool(outputStream.IgnoreBadTimeStamps), outputStream.TimeResolution,
database.Bool(outputStream.AllowPreemptivePublishing), outputStream.DownSamplingMethod.ToNotNull(), outputStream.DataFormat.ToNotNull(), outputStream.CoordinateFormat.ToNotNull(),
outputStream.CurrentScalingValue, outputStream.VoltageScalingValue, outputStream.AnalogScalingValue, outputStream.DigitalMaskValue, database.Bool(outputStream.PerformTimestampReasonabilityCheck),
CommonFunctions.CurrentUser, database.UtcNow, outputStream.ID);
if (oldOutputStream != null && oldOutputStream.Acronym != outputStream.Acronym.Replace(" ", "").ToUpper())
{
ObservableCollection<Measurement> measurementList = Measurement.GetOutputStatisticMeasurements(database, oldOutputStream.Acronym);
foreach (Measurement measurement in measurementList)
{
measurement.SignalReference = measurement.SignalReference.Replace(oldOutputStream.Acronym, outputStream.Acronym.Replace(" ", "").ToUpper());
measurement.PointTag = measurement.PointTag.Replace(oldOutputStream.Acronym, outputStream.Acronym.Replace(" ", "").ToUpper());
measurement.Description = Regex.Replace(measurement.Description, oldOutputStream.Name, outputStream.Name ?? outputStream.Acronym.Replace(" ", "").ToUpper(), RegexOptions.IgnoreCase);
Measurement.Save(database, measurement);
}
SignalType qualityType = SignalType.Load(database).FirstOrDefault(type => type.Acronym == "QUAL");
if ((object)qualityType != null)
{
IList<int> keys = database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT ID FROM OutputStreamMeasurement WHERE AdapterID = {0}", outputStream.ID))
.Select().Select(row => row.ConvertField<int>("ID")).ToList();
foreach (OutputStreamMeasurement measurement in OutputStreamMeasurement.Load(database, keys))
{
if (Regex.IsMatch(measurement.SignalReference, string.Format("{0}-{1}", oldOutputStream.Acronym, qualityType.Suffix)))
{
measurement.SignalReference = measurement.SignalReference.Replace(oldOutputStream.Acronym, outputStream.Acronym.Replace(" ", "").ToUpper());
OutputStreamMeasurement.Save(database, measurement);
}
}
}
}
}
if (mirrorMode)
{
// Get ID of the output stream if a new one was inserted above.
if (outputStream.ID == 0)
outputStream.ID = GetOutputStream(database, " WHERE Acronym = '" + outputStream.Acronym.Replace(" ", "").ToUpper() + "'").ID;
IList<int> keys = OutputStreamDevice.LoadKeys(database, outputStream.ID);
// Get all existing devices associated with output stream and delete them.
ObservableCollection<OutputStreamDevice> outputStreamDevices = OutputStreamDevice.Load(database, keys);
foreach (OutputStreamDevice outputStreamDevice in outputStreamDevices)
OutputStreamDevice.Delete(database, outputStream.ID, outputStreamDevice.Acronym);
if (!string.IsNullOrEmpty(outputStream.MirroringSourceDevice))
{
// Get list of input devices, filter by original source = outputstream.MirrorSourceDevice.
ObservableCollection<Device> devices = Device.GetDevices(database, "WHERE OriginalSource = '" + outputStream.MirroringSourceDevice + "'");
// Add these above input devices as output stream devices.
OutputStreamDevice.AddDevices(database, outputStream.ID, devices, true, true);
}
}
return "Output Stream Information Saved Successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Subscriber"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of subscribers defined in the database.</returns>
public static Dictionary<Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
Dictionary<Guid, string> subscriberList;
DataTable subscriberTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
subscriberList = new Dictionary<Guid, string>();
if (isOptional)
subscriberList.Add(Guid.Empty, "Select Subscriber");
query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Subscriber WHERE Enabled = {0} AND NodeID = {1} ORDER BY Name", "enabled", "nodeID");
subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID());
foreach (DataRow row in subscriberTable.Rows)
{
subscriberList[database.Guid(row, "ID")] = row.Field<string>("Acronym");
}
return subscriberList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Node"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="node">Information about <see cref="Node"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Node node)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (node.ID == null || node.ID == Guid.Empty)
{
query = database.ParameterizedQueryString("INSERT INTO Node (Name, CompanyID, Longitude, Latitude, Description, ImagePath, Settings, MenuType, MenuData, " +
"Master, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, " +
"{13}, {14}, {15})", "name", "companyID", "longitude", "latitude", "description", "imagePath", "settings", "menuType", "menuData", "master",
"loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, node.Name, node.CompanyID.ToNotNull(), node.Longitude.ToNotNull(), node.Latitude.ToNotNull(),
node.Description.ToNotNull(), node.ImagePath.ToNotNull(), node.Settings.ToNotNull(), node.MenuType, node.MenuData, database.Bool(node.Master), node.LoadOrder,
database.Bool(node.Enabled), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = string.Format("SELECT Name FROM NodeDetail WHERE ID IN ('{0}')", node.ID);
DataTable nodeTable = database.Connection.RetrieveData(database.AdapterType, query);
query = string.Format("SELECT SignalIndex FROM Statistic WHERE Source = 'System'");
DataTable systemTable = database.Connection.RetrieveData(database.AdapterType, query);
query = database.ParameterizedQueryString("UPDATE Node SET Name = {0}, CompanyID = {1}, Longitude = {2}, Latitude = {3}, " +
"Description = {4}, ImagePath = {5}, Settings = {6}, MenuType = {7}, MenuData = {8}, Master = {9}, LoadOrder = {10}, Enabled = {11}, " +
"UpdatedBy = {12}, UpdatedOn = {13} WHERE ID = {14}", "name", "companyID", "longitude", "latitude", "description", "imagePath",
"Settings", "MenuType", "MenuData", "master", "loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, node.Name, node.CompanyID.ToNotNull(), node.Longitude.ToNotNull(), node.Latitude.ToNotNull(),
node.Description.ToNotNull(), node.ImagePath.ToNotNull(), node.Settings.ToNotNull(), node.MenuType, node.MenuData, database.Bool(node.Master), node.LoadOrder,
database.Bool(node.Enabled), CommonFunctions.CurrentUser, database.UtcNow, database.Guid(node.ID));
if (nodeTable.Rows.Count > 0)
{
string newNodeName = node.Name
.RemoveCharacters(c => !char.IsLetterOrDigit(c))
.Replace(' ', '_')
.ToUpper();
string oldNodeName = nodeTable.Rows[0]["Name"].ToString()
.RemoveCharacters(c => !char.IsLetterOrDigit(c))
.Replace(' ', '_')
.ToUpper();
//SystemTable is read from the database.
for (int i = 0; i < systemTable.Rows.Count; i++)
{
string signalIndex = systemTable.Rows[i]["SignalIndex"].ToString();
string pointTag = string.Format("{0}!SYSTEM:ST{1}", newNodeName, signalIndex);
string newSignalReference = string.Format("{0}!SYSTEM-ST{1}", newNodeName, signalIndex);
string oldSignalReference = string.Format("{0}!SYSTEM-ST{1}", oldNodeName, signalIndex);
query = database.ParameterizedQueryString("UPDATE Measurement SET PointTag = {0}, SignalReference = {1} WHERE SignalReference = {2}", "name", "newSignalReference", "oldSignalReference");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, pointTag, newSignalReference, oldSignalReference);
}
}
}
return "Node information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Historian"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="historian">Infomration about <see cref="Historian"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Historian historian)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (historian.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO Historian (NodeID, Acronym, Name, AssemblyName, TypeName, ConnectionString, IsLocal, " +
"MeasurementReportingInterval, Description, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, " +
"{5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14})", "nodeID", "acronym", "name", "assemblyName", "typeName", "connectionString", "isLocal",
"measurementReportingInterval", "description", "loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (historian.NodeID != Guid.Empty) ? database.Guid(historian.NodeID) : database.CurrentNodeID(),
historian.Acronym.Replace(" ", "").ToUpper(), historian.Name.ToNotNull(), historian.AssemblyName.ToNotNull(),
historian.TypeName.ToNotNull(), historian.ConnectionString.ToNotNull(), database.Bool(historian.IsLocal), historian.MeasurementReportingInterval,
historian.Description.ToNotNull(), historian.LoadOrder, database.Bool(historian.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE Historian SET NodeID = {0}, Acronym = {1}, Name = {2}, AssemblyName = {3}, TypeName = {4}, " +
"ConnectionString = {5}, IsLocal = {6}, MeasurementReportingInterval = {7}, Description = {8}, LoadOrder = {9}, Enabled = {10}, " +
"UpdatedBy = {11}, UpdatedOn = {12} WHERE ID = {13}", "nodeID", "acronym", "name", "assemblyName", "typeName", "connectionString",
"isLocal", "measurementReportingInterval", "description", "loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (historian.NodeID != Guid.Empty) ? database.Guid(historian.NodeID) : database.CurrentNodeID(),
historian.Acronym.Replace(" ", "").ToUpper(), historian.Name.ToNotNull(), historian.AssemblyName.ToNotNull(), historian.TypeName.ToNotNull(),
historian.ConnectionString.ToNotNull(), database.Bool(historian.IsLocal), historian.MeasurementReportingInterval, historian.Description.ToNotNull(),
historian.LoadOrder, database.Bool(historian.Enabled), CommonFunctions.CurrentUser, database.UtcNow, historian.ID);
}
return "Historian information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static Methods
/// <summary>
/// Loads <see cref="RealTimeStream"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="RealTimeStream"/>.</returns>
public static ObservableCollection <RealTimeStream> Load(AdoDataConnection database)
{
bool createdConnection = false;
try
{
ObservableCollection <RealTimeStream> realTimeStreamList = null;
createdConnection = CreateConnection(ref database);
DataSet resultSet = new DataSet();
resultSet.EnforceConstraints = false;
// Get PDCs list.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name, ConnectionString, CompanyName, Enabled FROM DeviceDetail " +
"WHERE NodeID = {0} AND IsConcentrator = {1} AND Enabled = {2} ORDER BY Acronym", "nodeID", "isConcentrator", "enabled"), DefaultTimeout, database.CurrentNodeID(), database.Bool(true), database.Bool(true)).Copy());
resultSet.Tables[0].TableName = "PdcTable";
// Add a dummy device row in PDC table to associate PMUs which are not PDC and connected directly.
DataRow row = resultSet.Tables["PdcTable"].NewRow();
row["ID"] = 0;
row["Acronym"] = string.Empty;
row["Name"] = "Devices Connected Directly";
row["CompanyName"] = string.Empty;
row["Enabled"] = false;
resultSet.Tables["PdcTable"].Rows.Add(row);
// Get Non-PDC device list.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name, CompanyName, ProtocolName, VendorDeviceName, " +
"ParentAcronym, Enabled FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Enabled = {2} ORDER BY Acronym", "nodeID", "isConcentrator", "enabled"),
DefaultTimeout, database.CurrentNodeID(), database.Bool(false), database.Bool(true)).Copy());
resultSet.Tables[1].TableName = "DeviceTable";
// Get non-statistical measurement list
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, DeviceID, SignalID, PointID, PointTag, SignalReference, " +
"SignalAcronym, Description, SignalName, EngineeringUnits, HistorianAcronym, Subscribed, Internal FROM MeasurementDetail WHERE NodeID = {0} AND " +
"SignalAcronym <> {1} ORDER BY SignalReference", "nodeID", "signalAcronym"), DefaultTimeout, database.CurrentNodeID(), "STAT").Copy());
resultSet.Tables[2].TableName = "MeasurementTable";
// Query for any non-statistical measurements that are subscribed via GEP, but are a part of another node in the same database
// IMPORTANT: Make sure columns in this external node query exactly match those of the previous primary measurement query
DataTable otherMeasurements = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, 0 AS DeviceID, SignalID, PointID, PointTag, SignalReference, " +
"SignalAcronym, Description, SignalName, EngineeringUnits, HistorianAcronym, Subscribed, Internal FROM MeasurementDetail WHERE NodeID <> {0} AND " +
"SignalAcronym <> {1} AND Subscribed <> 0 ORDER BY SignalReference", "nodeID", "signalAcronym"), DefaultTimeout, database.CurrentNodeID(), "STAT");
realTimeStreamList = new ObservableCollection <RealTimeStream>(
from pdc in resultSet.Tables["PdcTable"].AsEnumerable()
let settings = pdc.Field <string>("ConnectionString").ToNonNullString().ParseKeyValuePairs()
select new RealTimeStream
{
ID = pdc.ConvertField <int>("ID"),
Acronym = string.IsNullOrEmpty(pdc.Field <string>("Acronym")) ? "DIRECT CONNECTED" : pdc.Field <string>("Acronym"),
Name = pdc.Field <string>("Name"),
CompanyName = pdc.Field <string>("CompanyName"),
StatusColor = string.IsNullOrEmpty(pdc.Field <string>("Acronym")) ? "Transparent" : "Gray",
Enabled = Convert.ToBoolean(pdc.Field <object>("Enabled")),
Expanded = false,
DeviceList = new ObservableCollection <RealTimeDevice>(
from device in resultSet.Tables["DeviceTable"].AsEnumerable()
where device.Field <string>("ParentAcronym").ToNonNullString() == pdc.Field <string>("Acronym")
select new RealTimeDevice
{
ID = device.ConvertNullableField <int>("ID"),
Acronym = device.Field <string>("Acronym"),
Name = device.Field <string>("Name"),
ProtocolName = device.Field <string>("ProtocolName"),
VendorDeviceName = device.Field <string>("VendorDeviceName"),
ParentAcronym = string.IsNullOrEmpty(device.Field <string>("ParentAcronym")) ? "DIRECT CONNECTED" : device.Field <string>("ParentAcronym"),
Expanded = false,
StatusColor = device.ConvertNullableField <int>("ID") == null ? "Transparent" : "Gray",
Enabled = Convert.ToBoolean(device.Field <object>("Enabled")),
MeasurementList = new ObservableCollection <RealTimeMeasurement>(
from measurement in resultSet.Tables["MeasurementTable"].AsEnumerable()
where measurement.ConvertNullableField <int>("DeviceID") == device.ConvertNullableField <int>("ID") && (measurement.ConvertField <bool>("Subscribed") || measurement.ConvertField <bool>("Internal") || (settings.ContainsKey("securityMode") && settings["securityMode"].Equals("None", StringComparison.OrdinalIgnoreCase))) //We will only display measurements which are internal or subscribed to avoid confusion.
select new RealTimeMeasurement
{
ID = measurement.Field <string>("ID"),
DeviceID = measurement.ConvertNullableField <int>("DeviceID"),
SignalID = Guid.Parse(measurement.Field <object>("SignalID").ToString()),
PointID = measurement.ConvertField <int>("PointID"),
PointTag = measurement.Field <string>("PointTag"),
SignalReference = measurement.Field <string>("SignalReference"),
Description = measurement.Field <string>("description"),
SignalName = measurement.Field <string>("SignalName"),
SignalAcronym = measurement.Field <string>("SignalAcronym"),
EngineeringUnit = measurement.Field <string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field <string>("EngineeringUnits"),
Expanded = false,
Selected = false,
Selectable = measurement.Field <string>("SignalAcronym") == "IPHM" || measurement.Field <string>("SignalAcronym") == "IPHA" || measurement.Field <string>("SignalAcronym") == "VPHM" || measurement.Field <string>("SignalAcronym") == "VPHA" || measurement.Field <string>("SignalAcronym") == "FREQ",
TimeTag = "N/A",
Value = "--",
Quality = "N/A",
Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0))
}
)
}
)
}
);
if (otherMeasurements.Rows.Count > 0)
{
// Add subscribed measurements from other nodes
realTimeStreamList.Add(new RealTimeStream
{
ID = 0,
Acronym = "SUBSCRIBED",
Name = "Subscribed Measurements",
CompanyName = string.Empty,
StatusColor = "Transparent",
Enabled = false,
Expanded = false,
DeviceList = new ObservableCollection <RealTimeDevice>(
otherMeasurements.Rows
.Cast <DataRow>()
.Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null)
.Select(measurement => measurement.Field <string>("SignalReference"))
.Select(GetSourceName)
.Distinct()
.Select(source => new RealTimeDevice
{
ID = 0,
Acronym = source,
Name = source,
ProtocolName = string.Empty,
VendorDeviceName = string.Empty,
ParentAcronym = "SUBSCRIBED",
Expanded = false,
StatusColor = "Gray",
Enabled = false,
MeasurementList = new ObservableCollection <RealTimeMeasurement>(
otherMeasurements.Rows
.Cast <DataRow>()
.Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null && measurement.Field <string>("SignalReference").StartsWith(source))
.Select(measurement => new RealTimeMeasurement
{
ID = measurement.Field <string>("ID"),
DeviceID = measurement.ConvertNullableField <int>("DeviceID"),
SignalID = Guid.Parse(measurement.Field <object>("SignalID").ToString()),
PointID = measurement.ConvertField <int>("PointID"),
PointTag = measurement.Field <string>("PointTag"),
SignalReference = measurement.Field <string>("SignalReference"),
Description = measurement.Field <string>("description"),
SignalName = measurement.Field <string>("SignalName"),
SignalAcronym = measurement.Field <string>("SignalAcronym"),
EngineeringUnit = measurement.Field <string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field <string>("EngineeringUnits"),
Expanded = false,
Selected = false,
Selectable = measurement.Field <string>("SignalAcronym") == "IPHM" || measurement.Field <string>("SignalAcronym") == "IPHA" || measurement.Field <string>("SignalAcronym") == "VPHM" || measurement.Field <string>("SignalAcronym") == "VPHA" || measurement.Field <string>("SignalAcronym") == "FREQ",
TimeTag = "N/A",
Value = "--",
Quality = "N/A",
Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0))
})
)
})
)
});
}
if (resultSet.Tables["MeasurementTable"].Select("DeviceID IS NULL").Length > 0)
{
// Add direct connected measurements with no associated device (DeviceID IS NULL)
realTimeStreamList.Add(new RealTimeStream
{
ID = 0,
Acronym = "CALCULATED",
Name = "Calculated Measurements",
CompanyName = string.Empty,
StatusColor = "Transparent",
Enabled = false,
Expanded = false,
DeviceList = new ObservableCollection <RealTimeDevice>(
resultSet.Tables["MeasurementTable"].Rows
.Cast <DataRow>()
.Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null)
.Select(measurement => measurement.Field <string>("SignalReference"))
.Select(GetSourceName)
.Distinct()
.Select(source => new RealTimeDevice
{
ID = 0,
Acronym = source,
Name = source,
ProtocolName = string.Empty,
VendorDeviceName = string.Empty,
ParentAcronym = "CALCULATED",
Expanded = false,
StatusColor = "Gray",
Enabled = false,
MeasurementList = new ObservableCollection <RealTimeMeasurement>(
resultSet.Tables["MeasurementTable"].Rows
.Cast <DataRow>()
.Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null && measurement.Field <string>("SignalReference").StartsWith(source))
.Select(measurement => new RealTimeMeasurement
{
ID = measurement.Field <string>("ID"),
DeviceID = measurement.ConvertNullableField <int>("DeviceID"),
SignalID = Guid.Parse(measurement.Field <object>("SignalID").ToString()),
PointID = measurement.ConvertField <int>("PointID"),
PointTag = measurement.Field <string>("PointTag"),
SignalReference = measurement.Field <string>("SignalReference"),
Description = measurement.Field <string>("description"),
SignalName = measurement.Field <string>("SignalName"),
SignalAcronym = measurement.Field <string>("SignalAcronym"),
EngineeringUnit = measurement.Field <string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field <string>("EngineeringUnits"),
Expanded = false,
Selected = false,
Selectable = measurement.Field <string>("SignalAcronym") == "IPHM" || measurement.Field <string>("SignalAcronym") == "IPHA" || measurement.Field <string>("SignalAcronym") == "VPHM" || measurement.Field <string>("SignalAcronym") == "VPHA" || measurement.Field <string>("SignalAcronym") == "FREQ",
TimeTag = "N/A",
Value = "--",
Quality = "N/A",
Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0))
})
)
})
)
});
}
// Assign parent references for real-time measurements
foreach (RealTimeStream stream in realTimeStreamList)
{
foreach (RealTimeDevice device in stream.DeviceList)
{
device.Parent = stream;
foreach (RealTimeMeasurement measurement in device.MeasurementList)
{
measurement.Parent = device;
}
}
}
return(realTimeStreamList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Alarm"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and tag name of alarms defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> alarmList = new Dictionary<int, string>();
if (isOptional)
alarmList.Add(0, "Select Alarm");
string query = database.ParameterizedQueryString("SELECT ID, TagName FROM Alarm WHERE Enabled = {0} ORDER BY LoadOrder", "enabled");
DataTable nodeTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true));
foreach (DataRow row in nodeTable.Rows)
{
alarmList[row.ConvertField<int>("ID")] = row.Field<string>("TagName");
}
return alarmList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Measurement"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="measurement">Information about <see cref="Measurement"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Measurement measurement)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (measurement.PointID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO Measurement (HistorianID, DeviceID, PointTag, AlternateTag, SignalTypeID, PhasorSourceIndex, " +
"SignalReference, Adder, Multiplier, Subscribed, Internal, Description, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, " +
"{3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16})", "historianID", "deviceID", "pointTag", "alternateTag", "signalTypeID",
"phasorSourceIndex", "signalReference", "adder", "multiplier", "subscribed", "internal", "description", "enabled", "updatedBy", "updatedOn",
"createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, measurement.HistorianID.ToNotNull(), measurement.DeviceID.ToNotNull(), measurement.PointTag,
measurement.AlternateTag.ToNotNull(), measurement.SignalTypeID, measurement.PhasorSourceIndex ?? measurement.PhasorSourceIndex.ToNotNull(), measurement.SignalReference,
measurement.Adder, measurement.Multiplier, database.Bool(measurement.Subscribed), database.Bool(measurement.Internal), measurement.Description.ToNotNull(),
database.Bool(measurement.Enabled), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE Measurement SET HistorianID = {0}, DeviceID = {1}, PointTag = {2}, AlternateTag = {3}, " +
"SignalTypeID = {4}, PhasorSourceIndex = {5}, SignalReference = {6}, Adder = {7}, Multiplier = {8}, Description = {9}, Subscribed = {10}, " +
"Internal = {11}, Enabled = {12}, UpdatedBy = {13}, UpdatedOn = {14} WHERE PointID = {15}", "historianID", "deviceID", "pointTag",
"alternateTag", "signalTypeID", "phasorSourceINdex", "signalReference", "adder", "multiplier", "description", "subscribed", "internal",
"enabled", "updatedBy", "updatedOn", "pointID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, measurement.HistorianID.ToNotNull(), measurement.DeviceID.ToNotNull(), measurement.PointTag,
measurement.AlternateTag.ToNotNull(), measurement.SignalTypeID, measurement.PhasorSourceIndex ?? measurement.PhasorSourceIndex.ToNotNull(), measurement.SignalReference,
measurement.Adder, measurement.Multiplier, measurement.Description.ToNotNull(), database.Bool(measurement.Subscribed), database.Bool(measurement.Internal),
database.Bool(measurement.Enabled), CommonFunctions.CurrentUser, database.UtcNow, measurement.PointID);
}
return "Measurement information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="UserAccount"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="userAccount">Information about <see cref="UserAccount"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, UserAccount userAccount)
{
const string ErrorMessage = "User name already exists.";
bool createdConnection = false;
string query;
string userAccountSID;
int existing;
try
{
createdConnection = CreateConnection(ref database);
string pColumn = "Password";
if (database.IsJetEngine)
pColumn = "[Password]";
object changePasswordOn = userAccount.ChangePasswordOn;
if (userAccount.ChangePasswordOn == DateTime.MinValue)
changePasswordOn = (object)DBNull.Value;
else if (database.IsJetEngine)
changePasswordOn = userAccount.ChangePasswordOn.ToOADate();
userAccountSID = UserInfo.UserNameToSID(userAccount.Name);
if (!userAccount.UseADAuthentication || !UserInfo.IsUserSID(userAccountSID))
userAccountSID = userAccount.Name;
if (userAccount.ID == Guid.Empty)
{
existing = Convert.ToInt32(database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM UserAccount WHERE Name = {0}", "name"), DefaultTimeout, userAccountSID));
if (existing > 0)
throw new InvalidOperationException(ErrorMessage);
query = database.ParameterizedQueryString("INSERT INTO UserAccount (Name, " + pColumn + ", FirstName, LastName, DefaultNodeID, Phone, Email, " +
"LockedOut, UseADAuthentication, ChangePasswordOn, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, " +
"{9}, {10}, {11}, {12}, {13})", "name", "password", "firstName", "lastName", "defaultNodeID", "phone", "email", "lockedOut", "useADAuthentication",
"changePasswordOn", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, userAccountSID,
userAccount.Password.ToNotNull(), userAccount.FirstName.ToNotNull(), userAccount.LastName.ToNotNull(), database.CurrentNodeID(),
userAccount.Phone.ToNotNull(), userAccount.Email.ToNotNull(), database.Bool(userAccount.LockedOut), database.Bool(userAccount.UseADAuthentication),
changePasswordOn, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
CommonFunctions.LogEvent(string.Format("New user \"{0}\" created successfully by user \"{1}\".", userAccount.Name, CommonFunctions.CurrentUser), 2);
}
else
{
existing = database.ExecuteScalar<int>("SELECT COUNT(*) FROM UserAccount WHERE Name = {0} AND ID <> {1}", userAccountSID, userAccount.ID);
if (existing > 0)
throw new InvalidOperationException(ErrorMessage);
query = database.ParameterizedQueryString("UPDATE UserAccount SET Name = {0}, " + pColumn + " = {1}, FirstName = {2}, LastName = {3}, " +
"DefaultNodeID = {4}, Phone = {5}, Email = {6}, LockedOut = {7}, UseADAuthentication = {8}, ChangePasswordOn = {9}, UpdatedBy = {10}, " +
"UpdatedOn = {11} WHERE ID = {12}", "name", "password", "firstName", "lastName", "defaultNodeID", "phone", "email", "lockedOut",
"useADAuthentication", "changePasswordOn", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, userAccountSID,
userAccount.Password.ToNotNull(), userAccount.FirstName.ToNotNull(), userAccount.LastName.ToNotNull(), database.Guid(userAccount.DefaultNodeID),
userAccount.Phone.ToNotNull(), userAccount.Email.ToNotNull(), database.Bool(userAccount.LockedOut), database.Bool(userAccount.UseADAuthentication),
changePasswordOn, CommonFunctions.CurrentUser, database.UtcNow, database.Guid(userAccount.ID));
CommonFunctions.LogEvent(string.Format("Information about user \"{0}\" updated successfully by user \"{1}\".", userAccount.Name, CommonFunctions.CurrentUser), 3);
}
return "User account information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Measurement"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <param name="subscribedOnly">boolean flag to indicate if only subscribed measurements to be returned.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing PointID and SignalID of measurements defined in the database.</returns>
public static Dictionary<Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false, bool subscribedOnly = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<Guid, string> measurementList = new Dictionary<Guid, string>();
if (isOptional)
measurementList.Add(Guid.Empty, "Select Measurement");
DataTable measurementTable;
string query;
if (subscribedOnly)
{
// If subscribedOnly is set then return only those measurements which are not internal and subscribed flag is set to true.
query = database.ParameterizedQueryString("SELECT SignalID, PointTag FROM MeasurementDetail WHERE " +
"NodeID = {0} AND Internal = {1} AND Subscribed = {2} ORDER BY PointID", "nodeID", "internal", "subscribed");
measurementTable = database.Connection.RetrieveData(database.AdapterType, query,
DefaultTimeout, database.CurrentNodeID(), database.Bool(false), database.Bool(true));
}
else
{
query = database.ParameterizedQueryString("SELECT SignalID, PointTag FROM MeasurementDetail WHERE NodeID = {0} ORDER BY PointID", "nodeID");
measurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
}
foreach (DataRow row in measurementTable.Rows)
measurementList[database.Guid(row, "SignalID")] = row.Field<string>("PointTag");
return measurementList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="Measurement"/> allowed for <see cref="Subscriber"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of SignalID and PointTag of <see cref="Measurement"/>.</returns>
public static Dictionary<Guid, string> GetAllowedMeasurements(AdoDataConnection database, Guid subscriberID)
{
Dictionary<Guid, string> allowedMeasurements;
DataTable allowedMeasurementTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
allowedMeasurements = new Dictionary<Guid, string>();
query = database.ParameterizedQueryString("SELECT SignalID, PointTag FROM SubscriberMeasurementDetail WHERE SubscriberID = {0} AND Allowed = {1} ORDER BY PointTag", "subscriberID", "allowed");
allowedMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID), database.Bool(true));
foreach (DataRow row in allowedMeasurementTable.Rows)
allowedMeasurements[database.Guid(row, "SignalID")] = row.Field<string>("PointTag");
return allowedMeasurements;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves only subscribed <see cref="Measurement"/> collection.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns><see cref="ObservableCollection{T1}"/> type list of <see cref="Measurement"/>.</returns>
public static ObservableCollection<Measurement> GetSubscribedMeasurements(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<Measurement> measurementList = new ObservableCollection<Measurement>();
DataTable measurementTable;
string query;
query = database.ParameterizedQueryString("SELECT * FROM MeasurementDetail WHERE " +
"Subscribed = {0} ORDER BY PointTag", "subscribed");
measurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true));
foreach (DataRow row in measurementTable.Rows)
{
measurementList.Add(new Measurement
{
SignalID = database.Guid(row, "SignalID"),
HistorianID = row.ConvertNullableField<int>("HistorianID"),
PointID = row.ConvertField<int>("PointID"),
DeviceID = row.ConvertNullableField<int>("DeviceID"),
PointTag = row.Field<string>("PointTag"),
AlternateTag = row.Field<string>("AlternateTag"),
SignalTypeID = row.ConvertField<int>("SignalTypeID"),
PhasorSourceIndex = row.ConvertNullableField<int>("PhasorSourceIndex"),
SignalReference = row.Field<string>("SignalReference"),
Adder = row.ConvertField<double>("Adder"),
Multiplier = row.ConvertField<double>("Multiplier"),
Internal = Convert.ToBoolean(row.Field<object>("Internal")),
Subscribed = Convert.ToBoolean(row.Field<object>("Subscribed")),
Description = row.Field<string>("Description"),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
m_historianAcronym = row.Field<string>("HistorianAcronym"),
m_deviceAcronym = row.Field<object>("DeviceAcronym") == null ? string.Empty : row.Field<string>("DeviceAcronym"),
m_signalName = row.Field<string>("SignalName"),
m_signalAcronym = row.Field<string>("SignalAcronym"),
m_signalSuffix = row.Field<string>("SignalTypeSuffix"),
m_phasorLabel = row.Field<string>("PhasorLabel"),
m_framesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30),
m_id = row.Field<string>("ID"),
m_companyAcronym = row.Field<object>("CompanyAcronym") == null ? string.Empty : row.Field<string>("CompanyAcronym"),
m_companyName = row.Field<object>("CompanyName") == null ? string.Empty : row.Field<string>("CompanyName"),
Selected = false
});
}
return measurementList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Handles meta-data synchronization to local system.
/// </summary>
/// <remarks>
/// This function should only be initiated from call to <see cref="SynchronizeMetadata(DataSet)"/> to make
/// sure only one meta-data synchronization happens at once. Users can override this method to customize
/// process of meta-data synchronization.
/// </remarks>
protected virtual void SynchronizeMetadata()
{
bool dataMonitoringEnabled = false;
// TODO: This function is complex and very closely tied to the current time-series data schema - perhaps it should be moved outside this class and referenced
// TODO: as a delegate that can be assigned and called to allow other schemas as well. DataPublisher is already very flexible in what data it can deliver.
try
{
DataSet metadata = m_receivedMetadata;
// Only perform database synchronization if meta-data has changed since last update
if (!SynchronizedMetadataChanged(metadata))
return;
if ((object)metadata == null)
{
OnStatusMessage("WARNING: Meta-data synchronization was not performed, deserialized dataset was empty.");
return;
}
// Reset data stream monitor while meta-data synchronization is in progress
if ((object)m_dataStreamMonitor != null && m_dataStreamMonitor.Enabled)
{
m_dataStreamMonitor.Enabled = false;
dataMonitoringEnabled = true;
}
// Track total meta-data synchronization process time
Ticks startTime = DateTime.UtcNow.Ticks;
DateTime updateTime;
DateTime latestUpdateTime = DateTime.MinValue;
// Open the configuration database using settings found in the config file
using (AdoDataConnection database = new AdoDataConnection("systemSettings"))
using (IDbCommand command = database.Connection.CreateCommand())
{
IDbTransaction transaction = null;
if (m_useTransactionForMetadata)
transaction = database.Connection.BeginTransaction(database.DefaultIsloationLevel);
try
{
if ((object)transaction != null)
command.Transaction = transaction;
// Query the actual record ID based on the known run-time ID for this subscriber device
int parentID = Convert.ToInt32(command.ExecuteScalar($"SELECT SourceID FROM Runtime WHERE ID = {ID} AND SourceTable='Device'", m_metadataSynchronizationTimeout));
// Validate that the subscriber device is marked as a concentrator (we are about to associate children devices with it)
if (!command.ExecuteScalar($"SELECT IsConcentrator FROM Device WHERE ID = {parentID}", m_metadataSynchronizationTimeout).ToString().ParseBoolean())
command.ExecuteNonQuery($"UPDATE Device SET IsConcentrator = 1 WHERE ID = {parentID}", m_metadataSynchronizationTimeout);
// Get any historian associated with the subscriber device
object historianID = command.ExecuteScalar($"SELECT HistorianID FROM Device WHERE ID = {parentID}", m_metadataSynchronizationTimeout);
// Determine the active node ID - we cache this since this value won't change for the lifetime of this class
if (m_nodeID == Guid.Empty)
m_nodeID = Guid.Parse(command.ExecuteScalar($"SELECT NodeID FROM IaonInputAdapter WHERE ID = {(int)ID}", m_metadataSynchronizationTimeout).ToString());
// Determine the protocol record auto-inc ID value for the gateway transport protocol (GEP) - this value is also cached since it shouldn't change for the lifetime of this class
if (m_gatewayProtocolID == 0)
m_gatewayProtocolID = int.Parse(command.ExecuteScalar("SELECT ID FROM Protocol WHERE Acronym='GatewayTransport'", m_metadataSynchronizationTimeout).ToString());
// Ascertain total number of actions required for all meta-data synchronization so some level feed back can be provided on progress
InitSyncProgress(metadata.Tables.Cast<DataTable>().Select(dataTable => (long)dataTable.Rows.Count).Sum() + 3);
// Prefix all children devices with the name of the parent since the same device names could appear in different connections (helps keep device names unique)
string sourcePrefix = Name + "!";
Dictionary<string, int> deviceIDs = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
string deviceAcronym, signalTypeAcronym;
decimal longitude, latitude;
decimal? location;
object originalSource;
int deviceID;
// Check to see if data for the "DeviceDetail" table was included in the meta-data
if (metadata.Tables.Contains("DeviceDetail"))
{
DataTable deviceDetail = metadata.Tables["DeviceDetail"];
List<Guid> uniqueIDs = new List<Guid>();
DataRow[] deviceRows;
// Define SQL statement to query if this device is already defined (this should always be based on the unique guid-based device ID)
string deviceExistsSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE UniqueID = {0}", "uniqueID");
// Define SQL statement to insert new device record
string insertDeviceSql = database.ParameterizedQueryString("INSERT INTO Device(NodeID, ParentID, HistorianID, Acronym, Name, ProtocolID, FramesPerSecond, OriginalSource, AccessID, Longitude, Latitude, ContactList, IsConcentrator, Enabled) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, 0, 1)", "nodeID", "parentID", "historianID", "acronym", "name", "protocolID", "framesPerSecond", "originalSource", "accessID", "longitude", "latitude", "contactList");
// Define SQL statement to update device's guid-based unique ID after insert
string updateDeviceUniqueIDSql = database.ParameterizedQueryString("UPDATE Device SET UniqueID = {0} WHERE Acronym = {1}", "uniqueID", "acronym");
// Define SQL statement to query if a device can be safely updated
string deviceIsUpdatableSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE UniqueID = {0} AND (ParentID <> {1} OR ParentID IS NULL)", "uniqueID", "parentID");
// Define SQL statement to update existing device record
string updateDeviceSql = database.ParameterizedQueryString("UPDATE Device SET Acronym = {0}, Name = {1}, OriginalSource = {2}, ProtocolID = {3}, FramesPerSecond = {4}, HistorianID = {5}, AccessID = {6}, Longitude = {7}, Latitude = {8}, ContactList = {9} WHERE UniqueID = {10}",
"acronym", "name", "originalSource", "protocolID", "framesPerSecond", "historianID", "accessID", "longitude", "latitude", "contactList", "uniqueID");
// Define SQL statement to retrieve device's auto-inc ID based on its unique guid-based ID
string queryDeviceIDSql = database.ParameterizedQueryString("SELECT ID FROM Device WHERE UniqueID = {0}", "uniqueID");
// Define SQL statement to retrieve all unique device ID's for the current parent to check for mismatches
string queryUniqueDeviceIDsSql = database.ParameterizedQueryString("SELECT UniqueID FROM Device WHERE ParentID = {0}", "parentID");
// Define SQL statement to remove device records that no longer exist in the meta-data
string deleteDeviceSql = database.ParameterizedQueryString("DELETE FROM Device WHERE UniqueID = {0}", "uniqueID");
// Determine which device rows should be synchronized based on operational mode flags
if (ReceiveInternalMetadata && ReceiveExternalMetadata)
deviceRows = deviceDetail.Select();
else if (ReceiveInternalMetadata)
deviceRows = deviceDetail.Select("OriginalSource IS NULL");
else if (ReceiveExternalMetadata)
deviceRows = deviceDetail.Select("OriginalSource IS NOT NULL");
else
deviceRows = new DataRow[0];
// Check existence of optional meta-data fields
DataColumnCollection deviceDetailColumns = deviceDetail.Columns;
bool accessIDFieldExists = deviceDetailColumns.Contains("AccessID");
bool longitudeFieldExists = deviceDetailColumns.Contains("Longitude");
bool latitudeFieldExists = deviceDetailColumns.Contains("Latitude");
bool companyAcronymFieldExists = deviceDetailColumns.Contains("CompanyAcronym");
bool protocolNameFieldExists = deviceDetailColumns.Contains("ProtocolName");
bool vendorAcronymFieldExists = deviceDetailColumns.Contains("VendorAcronym");
bool vendorDeviceNameFieldExists = deviceDetailColumns.Contains("VendorDeviceName");
bool interconnectionNameFieldExists = deviceDetailColumns.Contains("InterconnectionName");
bool updatedOnFieldExists = deviceDetailColumns.Contains("UpdatedOn");
// Older versions of GEP did not include the AccessID field, so this is treated as optional
int accessID = 0;
foreach (DataRow row in deviceRows)
{
Guid uniqueID = Guid.Parse(row.Field<object>("UniqueID").ToString());
bool recordNeedsUpdating;
// Track unique device Guids in this meta-data session, we'll need to remove any old associated devices that no longer exist
uniqueIDs.Add(uniqueID);
// Determine if record has changed since last synchronization
if (updatedOnFieldExists)
{
try
{
updateTime = Convert.ToDateTime(row["UpdatedOn"]);
recordNeedsUpdating = updateTime > m_lastMetaDataRefreshTime;
if (updateTime > latestUpdateTime)
latestUpdateTime = updateTime;
}
catch
{
recordNeedsUpdating = true;
}
}
else
{
recordNeedsUpdating = true;
}
// We will synchronize meta-data only if the source owns this device and it's not defined as a concentrator (these should normally be filtered by publisher - but we check just in case).
if (!row["IsConcentrator"].ToNonNullString("0").ParseBoolean())
{
if (accessIDFieldExists)
accessID = row.ConvertField<int>("AccessID");
// Get longitude and latitude values if they are defined
longitude = 0M;
latitude = 0M;
if (longitudeFieldExists)
{
location = row.ConvertNullableField<decimal>("Longitude");
if (location.HasValue)
longitude = location.Value;
}
if (latitudeFieldExists)
{
location = row.ConvertNullableField<decimal>("Latitude");
if (location.HasValue)
latitude = location.Value;
}
// Save any reported extraneous values from device meta-data in connection string formatted contact list - all fields are considered optional
Dictionary<string, string> contactList = new Dictionary<string, string>();
if (companyAcronymFieldExists)
contactList["companyAcronym"] = row.Field<string>("CompanyAcronym") ?? string.Empty;
if (protocolNameFieldExists)
contactList["protocolName"] = row.Field<string>("ProtocolName") ?? string.Empty;
if (vendorAcronymFieldExists)
contactList["vendorAcronym"] = row.Field<string>("VendorAcronym") ?? string.Empty;
if (vendorDeviceNameFieldExists)
contactList["vendorDeviceName"] = row.Field<string>("VendorDeviceName") ?? string.Empty;
if (interconnectionNameFieldExists)
contactList["interconnectionName"] = row.Field<string>("InterconnectionName") ?? string.Empty;
// Determine if device record already exists
if (Convert.ToInt32(command.ExecuteScalar(deviceExistsSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID))) == 0)
{
// Insert new device record
command.ExecuteNonQuery(insertDeviceSql, m_metadataSynchronizationTimeout, database.Guid(m_nodeID), parentID, historianID, sourcePrefix + row.Field<string>("Acronym"), row.Field<string>("Name"), m_gatewayProtocolID, row.ConvertField<int>("FramesPerSecond"),
m_internal ? (object)DBNull.Value : string.IsNullOrEmpty(row.Field<string>("ParentAcronym")) ? sourcePrefix + row.Field<string>("Acronym") : sourcePrefix + row.Field<string>("ParentAcronym"), accessID, longitude, latitude, contactList.JoinKeyValuePairs());
// Guids are normally auto-generated during insert - after insertion update the Guid so that it matches the source data. Most of the database
// scripts have triggers that support properly assigning the Guid during an insert, but this code ensures the Guid will always get assigned.
command.ExecuteNonQuery(updateDeviceUniqueIDSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID), sourcePrefix + row.Field<string>("Acronym"));
}
else if (recordNeedsUpdating)
{
// Perform safety check to preserve device records which are not safe to overwrite
if (Convert.ToInt32(command.ExecuteScalar(deviceIsUpdatableSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID), parentID)) > 0)
continue;
// Gateway is assuming ownership of the device records when the "internal" flag is true - this means the device's measurements can be forwarded to another party. From a device record perspective,
// ownership is inferred by setting 'OriginalSource' to null. When gateway doesn't own device records (i.e., the "internal" flag is false), this means the device's measurements can only be consumed
// locally - from a device record perspective this means the 'OriginalSource' field is set to the acronym of the PDC or PMU that generated the source measurements. This field allows a mirrored source
// restriction to be implemented later to ensure all devices in an output protocol came from the same original source connection, if desired.
originalSource = m_internal ? (object)DBNull.Value : string.IsNullOrEmpty(row.Field<string>("ParentAcronym")) ? sourcePrefix + row.Field<string>("Acronym") : sourcePrefix + row.Field<string>("ParentAcronym");
// Update existing device record
command.ExecuteNonQuery(updateDeviceSql, m_metadataSynchronizationTimeout, sourcePrefix + row.Field<string>("Acronym"), row.Field<string>("Name"), originalSource, m_gatewayProtocolID, row.ConvertField<int>("FramesPerSecond"), historianID, accessID, longitude, latitude, contactList.JoinKeyValuePairs(), database.Guid(uniqueID));
}
}
// Capture local device ID auto-inc value for measurement association
deviceIDs[row.Field<string>("Acronym")] = Convert.ToInt32(command.ExecuteScalar(queryDeviceIDSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID)));
// Periodically notify user about synchronization progress
UpdateSyncProgress();
}
// Remove any device records associated with this subscriber that no longer exist in the meta-data
if (uniqueIDs.Count > 0)
{
// Sort unique ID list so that binary search can be used for quick lookups
uniqueIDs.Sort();
DataTable deviceUniqueIDs = command.RetrieveData(database.AdapterType, queryUniqueDeviceIDsSql, m_metadataSynchronizationTimeout, parentID);
Guid uniqueID;
foreach (DataRow deviceRow in deviceUniqueIDs.Rows)
{
uniqueID = database.Guid(deviceRow, "UniqueID");
// Remove any devices in the database that are associated with the parent device and do not exist in the meta-data
if (uniqueIDs.BinarySearch(uniqueID) < 0)
command.ExecuteNonQuery(deleteDeviceSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID));
}
UpdateSyncProgress();
}
}
// Check to see if data for the "MeasurementDetail" table was included in the meta-data
if (metadata.Tables.Contains("MeasurementDetail"))
{
DataTable measurementDetail = metadata.Tables["MeasurementDetail"];
List<Guid> signalIDs = new List<Guid>();
DataRow[] measurementRows;
// Define SQL statement to query if this measurement is already defined (this should always be based on the unique signal ID Guid)
string measurementExistsSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Measurement WHERE SignalID = {0}", "signalID");
// Define SQL statement to insert new measurement record
string insertMeasurementSql = database.ParameterizedQueryString("INSERT INTO Measurement(DeviceID, HistorianID, PointTag, AlternateTag, SignalTypeID, PhasorSourceIndex, SignalReference, Description, Internal, Subscribed, Enabled) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, 0, 1)", "deviceID", "historianID", "pointTag", "alternateTag", "signalTypeID", "phasorSourceIndex", "signalReference", "description", "internal");
// Define SQL statement to update measurement's signal ID after insert
string updateMeasurementSignalIDSql = database.ParameterizedQueryString("UPDATE Measurement SET SignalID = {0}, AlternateTag = NULL WHERE AlternateTag = {1}", "signalID", "alternateTag");
// Define SQL statement to update existing measurement record
string updateMeasurementSql = database.ParameterizedQueryString("UPDATE Measurement SET HistorianID = {0}, PointTag = {1}, SignalTypeID = {2}, PhasorSourceIndex = {3}, SignalReference = {4}, Description = {5}, Internal = {6} WHERE SignalID = {7}",
"historianID", "pointTag", "signalTypeID", "phasorSourceIndex", "signalReference", "description", "internal", "signalID");
// Define SQL statement to retrieve all measurement signal ID's for the current parent to check for mismatches - note that we use the ActiveMeasurements view
// since it associates measurements with their top-most parent runtime device ID, this allows us to easily query all measurements for the parent device
string queryMeasurementSignalIDsSql = database.ParameterizedQueryString("SELECT SignalID FROM ActiveMeasurement WHERE DeviceID = {0}", "deviceID");
// Define SQL statement to retrieve measurement's associated device ID, i.e., actual record ID, based on measurement's signal ID
string queryMeasurementDeviceIDSql = database.ParameterizedQueryString("SELECT DeviceID FROM Measurement WHERE SignalID = {0}", "signalID");
// Define SQL statement to remove device records that no longer exist in the meta-data
string deleteMeasurementSql = database.ParameterizedQueryString("DELETE FROM Measurement WHERE SignalID = {0}", "signalID");
// Load signal type ID's from local database associated with their acronym for proper signal type translation
Dictionary<string, int> signalTypeIDs = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
foreach (DataRow row in command.RetrieveData(database.AdapterType, "SELECT ID, Acronym FROM SignalType").Rows)
{
signalTypeAcronym = row.Field<string>("Acronym");
if (!string.IsNullOrWhiteSpace(signalTypeAcronym))
signalTypeIDs[signalTypeAcronym] = row.ConvertField<int>("ID");
}
// Determine which measurement rows should be synchronized based on operational mode flags
if (ReceiveInternalMetadata && ReceiveExternalMetadata)
measurementRows = measurementDetail.Select();
else if (ReceiveInternalMetadata)
measurementRows = measurementDetail.Select("Internal <> 0");
else if (ReceiveExternalMetadata)
measurementRows = measurementDetail.Select("Internal = 0");
else
measurementRows = new DataRow[0];
// Check existence of optional meta-data fields
DataColumnCollection measurementDetailColumns = measurementDetail.Columns;
bool phasorSourceIndexFieldExists = measurementDetailColumns.Contains("PhasorSourceIndex");
bool updatedOnFieldExists = measurementDetailColumns.Contains("UpdatedOn");
object phasorSourceIndex = DBNull.Value;
foreach (DataRow row in measurementRows)
{
bool recordNeedsUpdating;
// Determine if record has changed since last synchronization
if (updatedOnFieldExists)
{
try
{
updateTime = Convert.ToDateTime(row["UpdatedOn"]);
recordNeedsUpdating = updateTime > m_lastMetaDataRefreshTime;
if (updateTime > latestUpdateTime)
latestUpdateTime = updateTime;
}
catch
{
recordNeedsUpdating = true;
}
}
else
{
recordNeedsUpdating = true;
}
// Get device and signal type acronyms
deviceAcronym = row.Field<string>("DeviceAcronym") ?? string.Empty;
signalTypeAcronym = row.Field<string>("SignalAcronym") ?? string.Empty;
// Get phasor source index if field is defined
if (phasorSourceIndexFieldExists)
{
// Using ConvertNullableField extension since publisher could use SQLite database in which case
// all integers would arrive in data set as longs and need to be converted back to integers
int? index = row.ConvertNullableField<int>("PhasorSourceIndex");
phasorSourceIndex = index.HasValue ? (object)index.Value : (object)DBNull.Value;
}
// Make sure we have an associated device and signal type already defined for the measurement
if (!string.IsNullOrWhiteSpace(deviceAcronym) && deviceIDs.ContainsKey(deviceAcronym) && !string.IsNullOrWhiteSpace(signalTypeAcronym) && signalTypeIDs.ContainsKey(signalTypeAcronym))
{
Guid signalID = Guid.Parse(row.Field<object>("SignalID").ToString());
// Track unique measurement signal Guids in this meta-data session, we'll need to remove any old associated measurements that no longer exist
signalIDs.Add(signalID);
// Prefix the tag name with the "updated" device name
string pointTag = sourcePrefix + row.Field<string>("PointTag");
// Look up associated device ID (local DB auto-inc)
deviceID = deviceIDs[deviceAcronym];
// Determine if measurement record already exists
if (Convert.ToInt32(command.ExecuteScalar(measurementExistsSql, m_metadataSynchronizationTimeout, database.Guid(signalID))) == 0)
{
string alternateTag = Guid.NewGuid().ToString();
// Insert new measurement record
command.ExecuteNonQuery(insertMeasurementSql, m_metadataSynchronizationTimeout, deviceID, historianID, pointTag, alternateTag, signalTypeIDs[signalTypeAcronym], phasorSourceIndex, sourcePrefix + row.Field<string>("SignalReference"), row.Field<string>("Description") ?? string.Empty, database.Bool(m_internal));
// Guids are normally auto-generated during insert - after insertion update the Guid so that it matches the source data. Most of the database
// scripts have triggers that support properly assigning the Guid during an insert, but this code ensures the Guid will always get assigned.
command.ExecuteNonQuery(updateMeasurementSignalIDSql, m_metadataSynchronizationTimeout, database.Guid(signalID), alternateTag);
}
else if (recordNeedsUpdating)
{
// Update existing measurement record. Note that this update assumes that measurements will remain associated with a static source device.
command.ExecuteNonQuery(updateMeasurementSql, m_metadataSynchronizationTimeout, historianID, pointTag, signalTypeIDs[signalTypeAcronym], phasorSourceIndex, sourcePrefix + row.Field<string>("SignalReference"), row.Field<string>("Description") ?? string.Empty, database.Bool(m_internal), database.Guid(signalID));
}
}
// Periodically notify user about synchronization progress
UpdateSyncProgress();
}
// Remove any measurement records associated with existing devices in this session but no longer exist in the meta-data
if (signalIDs.Count > 0)
{
// Sort signal ID list so that binary search can be used for quick lookups
signalIDs.Sort();
// Query all the guid-based signal ID's for all measurement records associated with the parent device using run-time ID
DataTable measurementSignalIDs = command.RetrieveData(database.AdapterType, queryMeasurementSignalIDsSql, m_metadataSynchronizationTimeout, (int)ID);
Guid signalID;
// Walk through each database record and see if the measurement exists in the provided meta-data
foreach (DataRow measurementRow in measurementSignalIDs.Rows)
{
signalID = database.Guid(measurementRow, "SignalID");
// Remove any measurements in the database that are associated with received devices and do not exist in the meta-data
if (signalIDs.BinarySearch(signalID) < 0)
{
// Measurement was not in the meta-data, get the measurement's actual record based ID for its associated device
object measurementDeviceID = command.ExecuteScalar(queryMeasurementDeviceIDSql, m_metadataSynchronizationTimeout, database.Guid(signalID));
// If the unknown measurement is directly associated with a device that exists in the meta-data it is assumed that this measurement
// was removed from the publishing system and no longer exists therefore we remove it from the local measurement cache. If the user
// needs custom local measurements associated with a remote device, they should be associated with the parent device only.
if (measurementDeviceID != null && !(measurementDeviceID is DBNull) && deviceIDs.ContainsValue(Convert.ToInt32(measurementDeviceID)))
command.ExecuteNonQuery(deleteMeasurementSql, m_metadataSynchronizationTimeout, database.Guid(signalID));
}
}
UpdateSyncProgress();
}
}
// Check to see if data for the "PhasorDetail" table was included in the meta-data
if (metadata.Tables.Contains("PhasorDetail"))
{
Dictionary<int, int> maxSourceIndicies = new Dictionary<int, int>();
int sourceIndex;
// Phasor data is normally only needed so that the user can properly generate a mirrored IEEE C37.118 output stream from the source data.
// This is necessary since, in this protocol, the phasors are described (i.e., labeled) as a unit (i.e., as a complex number) instead of
// as two distinct angle and magnitude measurements.
// Define SQL statement to query if phasor record is already defined (no Guid is defined for these simple label records)
string phasorExistsSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Phasor WHERE DeviceID = {0} AND SourceIndex = {1}", "deviceID", "sourceIndex");
// Define SQL statement to insert new phasor record
string insertPhasorSql = database.ParameterizedQueryString("INSERT INTO Phasor(DeviceID, Label, Type, Phase, SourceIndex) VALUES ({0}, {1}, {2}, {3}, {4})", "deviceID", "label", "type", "phase", "sourceIndex");
// Define SQL statement to update existing phasor record
string updatePhasorSql = database.ParameterizedQueryString("UPDATE Phasor SET Label = {0}, Type = {1}, Phase = {2} WHERE DeviceID = {3} AND SourceIndex = {4}", "label", "type", "phase", "deviceID", "sourceIndex");
// Define SQL statement to delete a phasor record
string deletePhasorSql = database.ParameterizedQueryString("DELETE FROM Phasor WHERE DeviceID = {0} AND SourceIndex > {1}", "deviceID", "sourceIndex");
foreach (DataRow row in metadata.Tables["PhasorDetail"].Rows)
{
// Get device acronym
deviceAcronym = row.Field<string>("DeviceAcronym") ?? string.Empty;
// Make sure we have an associated device already defined for the phasor record
if (!string.IsNullOrWhiteSpace(deviceAcronym) && deviceIDs.ContainsKey(deviceAcronym))
{
bool recordNeedsUpdating;
// Determine if record has changed since last synchronization
try
{
updateTime = Convert.ToDateTime(row["UpdatedOn"]);
recordNeedsUpdating = updateTime > m_lastMetaDataRefreshTime;
if (updateTime > latestUpdateTime)
latestUpdateTime = updateTime;
}
catch
{
recordNeedsUpdating = true;
}
deviceID = deviceIDs[deviceAcronym];
// Determine if phasor record already exists
if (Convert.ToInt32(command.ExecuteScalar(phasorExistsSql, m_metadataSynchronizationTimeout, deviceID, row.ConvertField<int>("SourceIndex"))) == 0)
{
// Insert new phasor record
command.ExecuteNonQuery(insertPhasorSql, m_metadataSynchronizationTimeout, deviceID, row.Field<string>("Label") ?? "undefined", (row.Field<string>("Type") ?? "V").TruncateLeft(1), (row.Field<string>("Phase") ?? "+").TruncateLeft(1), row.ConvertField<int>("SourceIndex"));
}
else if (recordNeedsUpdating)
{
// Update existing phasor record
command.ExecuteNonQuery(updatePhasorSql, m_metadataSynchronizationTimeout, row.Field<string>("Label") ?? "undefined", (row.Field<string>("Type") ?? "V").TruncateLeft(1), (row.Field<string>("Phase") ?? "+").TruncateLeft(1), deviceID, row.ConvertField<int>("SourceIndex"));
}
// Track largest source index for each device
maxSourceIndicies.TryGetValue(deviceID, out sourceIndex);
if (row.ConvertField<int>("SourceIndex") > sourceIndex)
maxSourceIndicies[deviceID] = row.ConvertField<int>("SourceIndex");
}
// Periodically notify user about synchronization progress
UpdateSyncProgress();
}
// Remove any phasor records associated with existing devices in this session but no longer exist in the meta-data
if (maxSourceIndicies.Count > 0)
{
foreach (KeyValuePair<int, int> deviceIndexPair in maxSourceIndicies)
{
command.ExecuteNonQuery(deletePhasorSql, m_metadataSynchronizationTimeout, deviceIndexPair.Key, deviceIndexPair.Value);
}
}
}
if ((object)transaction != null)
transaction.Commit();
// Update local in-memory synchronized meta-data cache
m_synchronizedMetadata = metadata;
}
catch (Exception ex)
{
OnProcessException(new InvalidOperationException("Failed to synchronize meta-data to local cache: " + ex.Message, ex));
if ((object)transaction != null)
{
try
{
transaction.Rollback();
}
catch (Exception rollbackException)
{
OnProcessException(new InvalidOperationException("Failed to roll back database transaction due to exception: " + rollbackException.Message, rollbackException));
}
}
return;
}
finally
{
if ((object)transaction != null)
transaction.Dispose();
}
}
// New signals may have been defined, take original remote signal index cache and apply changes
if (m_remoteSignalIndexCache != null)
m_signalIndexCache = new SignalIndexCache(DataSource, m_remoteSignalIndexCache);
m_lastMetaDataRefreshTime = latestUpdateTime > DateTime.MinValue ? latestUpdateTime : DateTime.UtcNow;
OnStatusMessage("Meta-data synchronization completed successfully in {0}", (DateTime.UtcNow.Ticks - startTime).ToElapsedTimeString(2));
// Send notification that system configuration has changed
OnConfigurationChanged();
}
catch (Exception ex)
{
OnProcessException(new InvalidOperationException("Failed to synchronize meta-data to local cache: " + ex.Message, ex));
}
finally
{
// Restart data stream monitor after meta-data synchronization if it was originally enabled
if (dataMonitoringEnabled && (object)m_dataStreamMonitor != null)
m_dataStreamMonitor.Enabled = true;
}
}
// Static Methods
/// <summary>
/// Loads <see cref="RealTimeStream"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="RealTimeStream"/>.</returns>
public static ObservableCollection<RealTimeStream> Load(AdoDataConnection database)
{
bool createdConnection = false;
try
{
ObservableCollection<RealTimeStream> realTimeStreamList = null;
createdConnection = CreateConnection(ref database);
DataSet resultSet = new DataSet();
resultSet.EnforceConstraints = false;
// Get PDCs list.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name, ConnectionString, CompanyName, Enabled FROM DeviceDetail " +
"WHERE NodeID = {0} AND IsConcentrator = {1} AND Enabled = {2} ORDER BY Acronym", "nodeID", "isConcentrator", "enabled"), DefaultTimeout, database.CurrentNodeID(), database.Bool(true), database.Bool(true)).Copy());
resultSet.Tables[0].TableName = "PdcTable";
// Add a dummy device row in PDC table to associate PMUs which are not PDC and connected directly.
DataRow row = resultSet.Tables["PdcTable"].NewRow();
row["ID"] = 0;
row["Acronym"] = string.Empty;
row["Name"] = "Devices Connected Directly";
row["CompanyName"] = string.Empty;
row["Enabled"] = false;
resultSet.Tables["PdcTable"].Rows.Add(row);
// Get Non-PDC device list.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name, CompanyName, ProtocolName, VendorDeviceName, " +
"ParentAcronym, Enabled FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Enabled = {2} ORDER BY Acronym", "nodeID", "isConcentrator", "enabled"),
DefaultTimeout, database.CurrentNodeID(), database.Bool(false), database.Bool(true)).Copy());
resultSet.Tables[1].TableName = "DeviceTable";
// Get non-statistical measurement list
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, DeviceID, SignalID, PointID, PointTag, SignalReference, " +
"SignalAcronym, Description, SignalName, EngineeringUnits, HistorianAcronym, Subscribed, Internal FROM MeasurementDetail WHERE NodeID = {0} AND " +
"SignalAcronym <> {1} ORDER BY SignalReference", "nodeID", "signalAcronym"), DefaultTimeout, database.CurrentNodeID(), "STAT").Copy());
resultSet.Tables[2].TableName = "MeasurementTable";
// Query for any non-statistical measurements that are subscribed via GEP, but are a part of another node in the same database
// IMPORTANT: Make sure columns in this external node query exactly match those of the previous primary measurement query
DataTable otherMeasurements = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, 0 AS DeviceID, SignalID, PointID, PointTag, SignalReference, " +
"SignalAcronym, Description, SignalName, EngineeringUnits, HistorianAcronym, Subscribed, Internal FROM MeasurementDetail WHERE NodeID <> {0} AND " +
"SignalAcronym <> {1} AND Subscribed <> 0 ORDER BY SignalReference", "nodeID", "signalAcronym"), DefaultTimeout, database.CurrentNodeID(), "STAT");
realTimeStreamList = new ObservableCollection<RealTimeStream>(
from pdc in resultSet.Tables["PdcTable"].AsEnumerable()
let settings = pdc.Field<string>("ConnectionString").ToNonNullString().ParseKeyValuePairs()
select new RealTimeStream
{
ID = pdc.ConvertField<int>("ID"),
Acronym = string.IsNullOrEmpty(pdc.Field<string>("Acronym")) ? "DIRECT CONNECTED" : pdc.Field<string>("Acronym"),
Name = pdc.Field<string>("Name"),
CompanyName = pdc.Field<string>("CompanyName"),
StatusColor = string.IsNullOrEmpty(pdc.Field<string>("Acronym")) ? "Transparent" : "Gray",
Enabled = Convert.ToBoolean(pdc.Field<object>("Enabled")),
Expanded = false,
DeviceList = new ObservableCollection<RealTimeDevice>(
from device in resultSet.Tables["DeviceTable"].AsEnumerable()
where device.Field<string>("ParentAcronym").ToNonNullString() == pdc.Field<string>("Acronym")
select new RealTimeDevice
{
ID = device.ConvertNullableField<int>("ID"),
Acronym = device.Field<string>("Acronym"),
Name = device.Field<string>("Name"),
ProtocolName = device.Field<string>("ProtocolName"),
VendorDeviceName = device.Field<string>("VendorDeviceName"),
ParentAcronym = string.IsNullOrEmpty(device.Field<string>("ParentAcronym")) ? "DIRECT CONNECTED" : device.Field<string>("ParentAcronym"),
Expanded = false,
StatusColor = device.ConvertNullableField<int>("ID") == null ? "Transparent" : "Gray",
Enabled = Convert.ToBoolean(device.Field<object>("Enabled")),
MeasurementList = new ObservableCollection<RealTimeMeasurement>(
from measurement in resultSet.Tables["MeasurementTable"].AsEnumerable()
where measurement.ConvertNullableField<int>("DeviceID") == device.ConvertNullableField<int>("ID") && (measurement.ConvertField<bool>("Subscribed") || measurement.ConvertField<bool>("Internal") || (settings.ContainsKey("securityMode") && settings["securityMode"].Equals("None", StringComparison.OrdinalIgnoreCase))) //We will only display measurements which are internal or subscribed to avoid confusion.
select new RealTimeMeasurement
{
ID = measurement.Field<string>("ID"),
DeviceID = measurement.ConvertNullableField<int>("DeviceID"),
SignalID = Guid.Parse(measurement.Field<object>("SignalID").ToString()),
PointID = measurement.ConvertField<int>("PointID"),
PointTag = measurement.Field<string>("PointTag"),
SignalReference = measurement.Field<string>("SignalReference"),
Description = measurement.Field<string>("description"),
SignalName = measurement.Field<string>("SignalName"),
SignalAcronym = measurement.Field<string>("SignalAcronym"),
EngineeringUnit = measurement.Field<string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field<string>("EngineeringUnits"),
Expanded = false,
Selected = false,
Selectable = measurement.Field<string>("SignalAcronym") == "IPHM" || measurement.Field<string>("SignalAcronym") == "IPHA" || measurement.Field<string>("SignalAcronym") == "VPHM" || measurement.Field<string>("SignalAcronym") == "VPHA" || measurement.Field<string>("SignalAcronym") == "FREQ",
TimeTag = "N/A",
Value = "--",
Quality = "N/A",
Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0))
}
)
}
)
}
);
if (otherMeasurements.Rows.Count > 0)
{
// Add subscribed measurements from other nodes
realTimeStreamList.Add(new RealTimeStream
{
ID = 0,
Acronym = "SUBSCRIBED",
Name = "Subscribed Measurements",
CompanyName = string.Empty,
StatusColor = "Transparent",
Enabled = false,
Expanded = false,
DeviceList = new ObservableCollection<RealTimeDevice>(
otherMeasurements.Rows
.Cast<DataRow>()
.Where(measurement => measurement.ConvertNullableField<int>("DeviceID") == null)
.Select(measurement => measurement.Field<string>("SignalReference"))
.Select(GetSourceName)
.Distinct()
.Select(source => new RealTimeDevice
{
ID = 0,
Acronym = source,
Name = source,
ProtocolName = string.Empty,
VendorDeviceName = string.Empty,
ParentAcronym = "SUBSCRIBED",
Expanded = false,
StatusColor = "Gray",
Enabled = false,
MeasurementList = new ObservableCollection<RealTimeMeasurement>(
otherMeasurements.Rows
.Cast<DataRow>()
.Where(measurement => measurement.ConvertNullableField<int>("DeviceID") == null && measurement.Field<string>("SignalReference").StartsWith(source))
.Select(measurement => new RealTimeMeasurement
{
ID = measurement.Field<string>("ID"),
DeviceID = measurement.ConvertNullableField<int>("DeviceID"),
SignalID = Guid.Parse(measurement.Field<object>("SignalID").ToString()),
PointID = measurement.ConvertField<int>("PointID"),
PointTag = measurement.Field<string>("PointTag"),
SignalReference = measurement.Field<string>("SignalReference"),
Description = measurement.Field<string>("description"),
SignalName = measurement.Field<string>("SignalName"),
SignalAcronym = measurement.Field<string>("SignalAcronym"),
EngineeringUnit = measurement.Field<string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field<string>("EngineeringUnits"),
Expanded = false,
Selected = false,
Selectable = measurement.Field<string>("SignalAcronym") == "IPHM" || measurement.Field<string>("SignalAcronym") == "IPHA" || measurement.Field<string>("SignalAcronym") == "VPHM" || measurement.Field<string>("SignalAcronym") == "VPHA" || measurement.Field<string>("SignalAcronym") == "FREQ",
TimeTag = "N/A",
Value = "--",
Quality = "N/A",
Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0))
})
)
})
)
});
}
if (resultSet.Tables["MeasurementTable"].Select("DeviceID IS NULL").Length > 0)
{
// Add direct connected measurements with no associated device (DeviceID IS NULL)
realTimeStreamList.Add(new RealTimeStream
{
ID = 0,
Acronym = "CALCULATED",
Name = "Calculated Measurements",
CompanyName = string.Empty,
StatusColor = "Transparent",
Enabled = false,
Expanded = false,
DeviceList = new ObservableCollection<RealTimeDevice>(
resultSet.Tables["MeasurementTable"].Rows
.Cast<DataRow>()
.Where(measurement => measurement.ConvertNullableField<int>("DeviceID") == null)
.Select(measurement => measurement.Field<string>("SignalReference"))
.Select(GetSourceName)
.Distinct()
.Select(source => new RealTimeDevice
{
ID = 0,
Acronym = source,
Name = source,
ProtocolName = string.Empty,
VendorDeviceName = string.Empty,
ParentAcronym = "CALCULATED",
Expanded = false,
StatusColor = "Gray",
Enabled = false,
MeasurementList = new ObservableCollection<RealTimeMeasurement>(
resultSet.Tables["MeasurementTable"].Rows
.Cast<DataRow>()
.Where(measurement => measurement.ConvertNullableField<int>("DeviceID") == null && measurement.Field<string>("SignalReference").StartsWith(source))
.Select(measurement => new RealTimeMeasurement
{
ID = measurement.Field<string>("ID"),
DeviceID = measurement.ConvertNullableField<int>("DeviceID"),
SignalID = Guid.Parse(measurement.Field<object>("SignalID").ToString()),
PointID = measurement.ConvertField<int>("PointID"),
PointTag = measurement.Field<string>("PointTag"),
SignalReference = measurement.Field<string>("SignalReference"),
Description = measurement.Field<string>("description"),
SignalName = measurement.Field<string>("SignalName"),
SignalAcronym = measurement.Field<string>("SignalAcronym"),
EngineeringUnit = measurement.Field<string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field<string>("EngineeringUnits"),
Expanded = false,
Selected = false,
Selectable = measurement.Field<string>("SignalAcronym") == "IPHM" || measurement.Field<string>("SignalAcronym") == "IPHA" || measurement.Field<string>("SignalAcronym") == "VPHM" || measurement.Field<string>("SignalAcronym") == "VPHA" || measurement.Field<string>("SignalAcronym") == "FREQ",
TimeTag = "N/A",
Value = "--",
Quality = "N/A",
Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0))
})
)
})
)
});
}
// Assign parent references for real-time measurements
foreach (RealTimeStream stream in realTimeStreamList)
{
foreach (RealTimeDevice device in stream.DeviceList)
{
device.Parent = stream;
foreach (RealTimeMeasurement measurement in device.MeasurementList)
{
measurement.Parent = device;
}
}
}
return realTimeStreamList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Device"/> information to database along with analogs and digital measurements if requested..
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="device">Information about <see cref="Device"/>.</param>
/// <param name="notifyService">Boolean value to notify service if needed.</param>
/// <param name="digitalCount">Number of digital measurements to add.</param>
/// <param name="analogCount">Number of analog measurements to add.</param>
/// <param name="digitalLabels">Collection of digital labels associated with a device in configuration frame.</param>
/// <param name="analogLabels">Collection of analog labels associated with a device in configuration frame.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string SaveWithAnalogsDigitals(AdoDataConnection database, Device device, bool notifyService, int digitalCount, int analogCount, List<string> digitalLabels = null, List<string> analogLabels = null)
{
bool createdConnection = false;
string query;
try
{
Device oldDevice = null;
createdConnection = CreateConnection(ref database);
object nodeID;
if (device.NodeID == Guid.Empty)
nodeID = database.CurrentNodeID();
else
nodeID = database.Guid(device.NodeID);
if (device.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO Device (NodeID, ParentID, UniqueID, Acronym, Name, IsConcentrator, CompanyID, HistorianID, AccessID, VendorDeviceID, " +
"ProtocolID, Longitude, Latitude, InterconnectionID, ConnectionString, TimeZone, FramesPerSecond, TimeAdjustmentTicks, DataLossInterval, ContactList, " +
"MeasuredLines, LoadOrder, Enabled, AllowedParsingExceptions, ParsingExceptionWindow, DelayedConnectionInterval, AllowUseOfCachedConfiguration, " +
"AutoStartDataParsingSequence, SkipDisableRealTimeData, MeasurementReportingInterval, ConnectOndemand, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values " +
"({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25}, " +
"{26}, {27}, {28}, {29}, {30}, {31}, {32}, {33}, {34})", "nodeID", "parentID", "uniqueID", "acronym", "name", "isConcentrator", "companyID",
"historianID", "accessID", "vendorDeviceID", "protocolID", "longitude", "latitude", "interconnectionID", "connectionString", "timezone",
"framesPerSecond", "timeAdjustmentTicks", "dataLossInterval", "contactList", "measuredLines", "loadOrder", "enabled", "allowedParsingExceptions",
"parsingExceptionWindow", "delayedConnectionInterval", "allowUseOfCachedConfiguration", "autoStartDataParsingSequence", "skipDisableRealTimeData",
"measurementReportingInterval", "connectOndemand", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, nodeID,
device.ParentID.ToNotNull(), database.Guid(Guid.NewGuid()), device.Acronym.Replace(" ", "").ToUpper(), device.Name.ToNotNull(), database.Bool(device.IsConcentrator), device.CompanyID.ToNotNull(),
device.HistorianID.ToNotNull(), device.AccessID, device.VendorDeviceID.ToNotNull(),
device.ProtocolID.ToNotNull(), device.Longitude.ToNotNull(), device.Latitude.ToNotNull(), device.InterconnectionID.ToNotNull(),
BuildConnectionString(device), device.TimeZone.ToNotNull(), device.FramesPerSecond ?? 30, device.TimeAdjustmentTicks, device.DataLossInterval, device.ContactList.ToNotNull(), device.MeasuredLines.ToNotNull(),
device.LoadOrder, database.Bool(device.Enabled), device.AllowedParsingExceptions, device.ParsingExceptionWindow, device.DelayedConnectionInterval, database.Bool(device.AllowUseOfCachedConfiguration),
database.Bool(device.AutoStartDataParsingSequence), database.Bool(device.SkipDisableRealTimeData), device.MeasurementReportingInterval, database.Bool(device.ConnectOnDemand), CommonFunctions.CurrentUser,
database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
oldDevice = GetDevice(database, " WHERE ID = " + device.ID);
query = database.ParameterizedQueryString("UPDATE Device SET NodeID = {0}, ParentID = {1}, UniqueID = {2}, Acronym = {3}, Name = {4}, " +
"IsConcentrator = {5}, CompanyID = {6}, HistorianID = {7}, AccessID = {8}, VendorDeviceID = {9}, ProtocolID = {10}, Longitude = {11}, " +
"Latitude = {12}, InterconnectionID = {13}, ConnectionString = {14}, TimeZone = {15}, FramesPerSecond = {16}, TimeAdjustmentTicks = {17}, " +
"DataLossInterval = {18}, ContactList = {19}, MeasuredLines = {20}, LoadOrder = {21}, Enabled = {22}, AllowedParsingExceptions = {23}, " +
"ParsingExceptionWindow = {24}, DelayedConnectionInterval = {25}, AllowUseOfCachedConfiguration = {26}, AutoStartDataParsingSequence = {27}, " +
"SkipDisableRealTimeData = {28}, MeasurementReportingInterval = {29}, ConnectOnDemand = {30}, UpdatedBy = {31}, UpdatedOn = {32} WHERE ID = {33}",
"nodeID", "parentID", "uniqueID", "acronym", "name", "isConcentrator", "companyID", "historianID", "accessID", "vendorDeviceID", "protocolID",
"longitude", "latitude", "interconnectionID", "connectionString", "timezone", "framesPerSecond", "timeAdjustmentTicks", "dataLossInterval",
"contactList", "measuredLines", "loadOrder", "enabled", "allowedParsingExceptions", "parsingExceptionWindow", "delayedConnectionInterval",
"allowUseOfCachedConfiguration", "autoStartDataParsingSequence", "skipDisableRealTimeData", "measurementReportingInterval", "connectOnDemand",
"updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, nodeID,
device.ParentID.ToNotNull(), database.Guid(device.UniqueID), device.Acronym.Replace(" ", "").ToUpper(), device.Name.ToNotNull(), database.Bool(device.IsConcentrator), device.CompanyID.ToNotNull(),
device.HistorianID.ToNotNull(), device.AccessID, device.VendorDeviceID.ToNotNull(),
device.ProtocolID.ToNotNull(), device.Longitude.ToNotNull(), device.Latitude.ToNotNull(), device.InterconnectionID.ToNotNull(),
BuildConnectionString(device), device.TimeZone.ToNotNull(), device.FramesPerSecond ?? 30, device.TimeAdjustmentTicks, device.DataLossInterval, device.ContactList.ToNotNull(), device.MeasuredLines.ToNotNull(),
device.LoadOrder, database.Bool(device.Enabled), device.AllowedParsingExceptions, device.ParsingExceptionWindow, device.DelayedConnectionInterval, database.Bool(device.AllowUseOfCachedConfiguration),
database.Bool(device.AutoStartDataParsingSequence), database.Bool(device.SkipDisableRealTimeData), device.MeasurementReportingInterval, database.Bool(device.ConnectOnDemand), CommonFunctions.CurrentUser,
database.UtcNow, device.ID);
}
Device savedDevice = GetDevice(database, "WHERE Acronym = '" + device.Acronym.Replace(" ", "").ToUpper() + "'");
if ((object)savedDevice == null)
return "Device information saved successfully but failed to create measurements";
// Determine if device is using a phasor protocol
bool deviceIsUsingPhasorProtocol = (string.Compare(savedDevice.ProtocolCategory, "Phasor", StringComparison.OrdinalIgnoreCase) == 0);
// Add default measurements for non-concentrator devices when device protocol category is Phasor
if (!savedDevice.IsConcentrator && deviceIsUsingPhasorProtocol)
{
// Setup and/or validate default signals associated with non-concentrator devices (e.g., directly connected PMUs or PMUs in a concentrator)
foreach (TimeSeries.UI.DataModels.SignalType signal in TimeSeries.UI.DataModels.SignalType.GetPmuSignalTypes())
{
Measurement measurement;
if (signal.Suffix == "AV" && analogCount > 0)
{
for (int i = 1; i <= analogCount; i++)
{
measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + savedDevice.ID + " AND SignalReference = '" + savedDevice.Acronym + "-AV" + i + "'");
if ((object)measurement == null)
{
measurement = new Measurement();
measurement.DeviceID = savedDevice.ID;
measurement.HistorianID = savedDevice.HistorianID;
measurement.PointTag = CommonPhasorServices.CreatePointTag(savedDevice.CompanyAcronym, savedDevice.Acronym, savedDevice.VendorAcronym, "ALOG", i);
measurement.SignalReference = savedDevice.Acronym + "-AV" + i;
measurement.Description = savedDevice.Name + (string.IsNullOrWhiteSpace(savedDevice.VendorDeviceName) ? "" : " " + savedDevice.VendorDeviceName) + " Analog Value " + i;
measurement.SignalTypeID = signal.ID;
measurement.PhasorSourceIndex = (int?)null;
measurement.Enabled = true;
if ((object)analogLabels != null && (object)analogLabels[i - 1] != null)
measurement.AlternateTag = analogLabels[i - 1];
Measurement.Save(database, measurement);
}
else if (measurement.SignalTypeID != signal.ID)
{
// Correct signal type if it has been changed
measurement.SignalTypeID = signal.ID;
Measurement.Save(database, measurement);
}
}
}
else if (signal.Suffix == "DV" && digitalCount > 0)
{
for (int i = 1; i <= digitalCount; i++)
{
measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + savedDevice.ID + " AND SignalReference = '" + savedDevice.Acronym + "-DV" + i + "'");
if ((object)measurement == null)
{
measurement = new Measurement();
measurement.DeviceID = savedDevice.ID;
measurement.HistorianID = savedDevice.HistorianID;
measurement.PointTag = CommonPhasorServices.CreatePointTag(savedDevice.CompanyAcronym, savedDevice.Acronym, savedDevice.VendorAcronym, "DIGI", i);
measurement.SignalReference = savedDevice.Acronym + "-DV" + i;
measurement.SignalTypeID = signal.ID;
measurement.Description = savedDevice.Name + (string.IsNullOrWhiteSpace(savedDevice.VendorDeviceName) ? "" : " " + savedDevice.VendorDeviceName) + " Digital Value " + i;
measurement.PhasorSourceIndex = (int?)null;
measurement.Enabled = true;
if ((object)digitalLabels != null && (object)digitalLabels[i - 1] != null)
measurement.AlternateTag = digitalLabels[i - 1];
Measurement.Save(database, measurement);
}
else if (measurement.SignalTypeID != signal.ID)
{
// Correct signal type if it has been changed
measurement.SignalTypeID = signal.ID;
Measurement.Save(database, measurement);
}
}
}
else if (signal.Suffix == "FQ" || signal.Suffix == "DF" || signal.Suffix == "SF")
{
measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + savedDevice.ID + " AND SignalTypeSuffix = '" + signal.Suffix + "'");
if ((object)measurement == null)
{
measurement = new Measurement();
measurement.DeviceID = savedDevice.ID;
measurement.HistorianID = savedDevice.HistorianID;
measurement.PointTag = CommonPhasorServices.CreatePointTag(savedDevice.CompanyAcronym, savedDevice.Acronym, savedDevice.VendorAcronym, signal.Acronym);
measurement.SignalReference = savedDevice.Acronym + "-" + signal.Suffix;
measurement.SignalTypeID = signal.ID;
measurement.Description = savedDevice.Name + (string.IsNullOrWhiteSpace(savedDevice.VendorDeviceName) ? "" : " " + savedDevice.VendorDeviceName) + " " + signal.Name;
measurement.PhasorSourceIndex = (int?)null;
measurement.Enabled = true;
Measurement.Save(database, measurement);
}
// Based on query filter of SignalTypeSuffix, the following will never be true
//else if (measurement.SignalTypeID != signal.ID)
//{
// // Correct signal type if it has been changed
// measurement.SignalTypeID = signal.ID;
// Measurement.Save(database, measurement);
//}
}
}
}
if (device.ID > 0)
{
if (!device.IsConcentrator)
{
// For existing non-concentrator devices, call Save on each phasor so that measurements related to those phasors will reflect possible device changes.
IList<int> keys = Phasor.LoadKeys(database, device.ID);
foreach (Phasor phasor in Phasor.Load(database, keys))
{
Phasor.SaveWithoutMeasurementUpdate(database, phasor);
}
}
// Update existing device measurements to reflect possible device changes
if ((object)oldDevice != null)
{
bool companyUpdated = (deviceIsUsingPhasorProtocol && savedDevice.CompanyID != oldDevice.CompanyID);
bool deviceRenamed = (deviceIsUsingPhasorProtocol && (string.CompareOrdinal(savedDevice.Acronym, oldDevice.Acronym) != 0 || string.CompareOrdinal(savedDevice.Name, oldDevice.Name) != 0));
bool historianUpdated = (savedDevice.HistorianID != oldDevice.HistorianID);
if (companyUpdated || deviceRenamed || historianUpdated)
{
string companyAcronym = "";
int underScoreIndex;
if (companyUpdated)
{
if (savedDevice.CompanyID.HasValue && !string.IsNullOrWhiteSpace(savedDevice.CompanyAcronym))
companyAcronym = savedDevice.CompanyAcronym;
}
foreach (Measurement measurement in Measurement.GetMeasurements(database, "WHERE DeviceID = " + oldDevice.ID))
{
if (companyUpdated)
{
// WARNING: This assumes company name is followed by an underscore - this may not be a valid assumption for custom point tag naming conventions
underScoreIndex = measurement.PointTag.ToNonNullString().IndexOf('_');
if (underScoreIndex > -1)
measurement.PointTag = companyAcronym + measurement.PointTag.Substring(underScoreIndex);
}
if (deviceRenamed)
{
measurement.PointTag = measurement.PointTag.Replace(oldDevice.Acronym, savedDevice.Acronym);
measurement.SignalReference = measurement.SignalReference.Replace(oldDevice.Acronym, savedDevice.Acronym);
measurement.Description = Regex.Replace(measurement.Description.ToNonNullString(), oldDevice.Name, savedDevice.Name, RegexOptions.IgnoreCase);
}
if (historianUpdated && string.Compare(measurement.HistorianAcronym, "STAT", StringComparison.OrdinalIgnoreCase) != 0)
measurement.HistorianID = savedDevice.HistorianID;
Measurement.Save(database, measurement);
}
}
// If changing the historian for a concentrator style device - must assume desire to change historian for all children devices
if (historianUpdated && device.IsConcentrator)
{
foreach (Device childDevice in GetDevices(database, "WHERE ParentID = " + device.ID))
{
// Recursively call this function for each child device with updated historian which will also fix measurement's historian
childDevice.HistorianID = savedDevice.HistorianID;
SaveWithAnalogsDigitals(database, childDevice, false, 0, 0);
}
}
}
}
try
{
// Notify service about configuration changes made here.
if (notifyService)
NotifyService(savedDevice);
}
catch (Exception ex)
{
return "Device information saved successfully. Failed to send Initialize command to backend service." + Environment.NewLine + ex.Message;
}
return "Device information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
static int Main(string[] args)
{
// System settings
ConfigurationFile configFile = ConfigurationFile.Current;
CategorizedSettingsElementCollection systemSettings = configFile.Settings["systemSettings"];
systemSettings.Add("NodeID", Guid.NewGuid().ToString(), "Unique Node ID");
Guid nodeID = systemSettings["NodeID"].ValueAs <Guid>();
bool useMemoryCache = systemSettings["UseMemoryCache"].ValueAsBoolean(false);
string connectionString = systemSettings["ConnectionString"].Value;
string nodeIDQueryString = null;
string parameterizedQuery;
int protocolID, signalTypeID;
// Define guid with query string delimiters according to database needs
Dictionary <string, string> settings = connectionString.ParseKeyValuePairs();
string setting;
if (settings.TryGetValue("Provider", out setting))
{
// Check if provider is for Access since it uses braces as Guid delimiters
if (setting.StartsWith("Microsoft.Jet.OLEDB", StringComparison.OrdinalIgnoreCase))
{
nodeIDQueryString = "{" + nodeID + "}";
}
}
if (string.IsNullOrWhiteSpace(nodeIDQueryString))
{
nodeIDQueryString = "'" + nodeID + "'";
}
using (AdoDataConnection database = new AdoDataConnection("systemSettings"))
{
IDbConnection connection = database.Connection;
if (Convert.ToInt32(connection.ExecuteScalar("SELECT COUNT(*) FROM Protocol WHERE Acronym='WAV'")) == 0)
{
if (database.IsSQLServer || database.IsJetEngine)
{
connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, [Type], Category, AssemblyName, TypeName) VALUES('WAV', 'Wave Form Input Adapter', 'Frame', 'Audio', 'WavInputAdapter.dll', 'WavInputAdapter.WavInputAdapter')");
}
else
{
connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName) VALUES('WAV', 'Wave Form Input Adapter', 'Frame', 'Audio', 'WavInputAdapter.dll', 'WavInputAdapter.WavInputAdapter')");
}
}
protocolID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM Protocol WHERE Acronym='WAV'"));
// Typically these values should be defined as analogs, however, we use a voltage magnitude signal type
// since these types of values can be better graphed with auto-scaling in the visualization tools
signalTypeID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym='VPHM'"));
//signalTypeID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym='ALOG'"));
string pathRoot = FilePath.GetDirectoryName((args.Length > 0) ? args[0] : systemSettings["MusicDirectory"].Value);
string sourcePath = Path.Combine(pathRoot, "*" + Path.DirectorySeparatorChar + "*.wav");
foreach (string sourceFileName in FilePath.GetFileList(sourcePath))
{
WaveFile sourceWave;
string fileName = FilePath.GetFileName(sourceFileName);
char[] invalidChars = { '\'', '[', ']', '(', ')', ',', '-', '.' };
Console.WriteLine("Loading metadata for \"{0}\"...\r\n", fileName);
sourceWave = WaveFile.Load(sourceFileName, false);
fileName = FilePath.GetFileNameWithoutExtension(fileName).RemoveDuplicateWhiteSpace().RemoveCharacters(invalidChars.Contains).Trim();
string acronym = fileName.Replace(' ', '_').ToUpper() + "_" + (int)(sourceWave.SampleRate / SI.Kilo) + "KHZ";
string name = GenerateSongName(sourceWave, fileName);
Console.WriteLine(" Acronym = {0}", acronym);
Console.WriteLine(" Name = {0}", name);
Console.WriteLine("");
// Check to see if device exists
if (Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE Acronym = {0}", "acronym"), acronym)) == 0)
{
parameterizedQuery = database.ParameterizedQueryString("INSERT INTO Device(NodeID, Acronym, Name, ProtocolID, FramesPerSecond, " +
"MeasurementReportingInterval, ConnectionString, Enabled) VALUES(" + nodeIDQueryString + ", {0}, {1}, {2}, {3}, {4}, {5}, {6})",
"acronym", "name", "protocolID", "framesPerSecond", "measurementReportingInterval",
"connectionString", "enabled");
// Insert new device record
connection.ExecuteNonQuery(parameterizedQuery, acronym, name, protocolID, sourceWave.SampleRate, 1000000, string.Format("wavFileName={0}; connectOnDemand=true; outputSourceIDs={1}; memoryCache={2}", FilePath.GetAbsolutePath(sourceFileName), acronym, useMemoryCache), database.Bool(true));
int deviceID = Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT ID FROM Device WHERE Acronym = {0}", "acronym"), acronym));
string pointTag;
// Add a measurement for each defined wave channel
for (int i = 0; i < sourceWave.Channels; i++)
{
int index = i + 1;
pointTag = acronym + ":WAVA" + index;
parameterizedQuery = database.ParameterizedQueryString("INSERT INTO Measurement(DeviceID, PointTag, SignalTypeID, SignalReference, Description, " +
"Enabled) VALUES({0}, {1}, {2}, {3}, {4}, {5})", "deviceID", "pointTag", "signalTypeID", "signalReference", "description", "enabled");
// Insert new measurement record
connection.ExecuteNonQuery(parameterizedQuery, (object)deviceID, pointTag, signalTypeID, acronym + "-AV" + index, name + " - channel " + index, database.Bool(true));
//index = Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT PointID FROM Measurement WHERE PointTag = {0}", "pointTag"), pointTag));
}
// Disable all non analog measurements that may be associated with this device
connection.ExecuteNonQuery(database.ParameterizedQueryString("UPDATE Measurement SET Enabled = {0} WHERE DeviceID = {1} AND SignalTypeID <> {2}", "enabled", "deviceID", "signalTypeID"), database.Bool(false), deviceID, signalTypeID);
}
}
}
return(0);
}
// Static Methods
/// <summary>
/// Loads <see cref="OutputStream"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="enabledOnly">Boolean flag indicating if only enabled <see cref="OutputStream"/>s needed.</param>
/// <param name="sortMember">The field to sort by.</param>
/// <param name="sortDirection"><c>ASC</c> or <c>DESC</c> for ascending or descending respectively.</param>
/// <returns>Collection of <see cref="OutputStream"/>.</returns>
public static IList<int> LoadKeys(AdoDataConnection database, bool enabledOnly, string sortMember, string sortDirection)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList<int> outputStreamList = new List<int>();
DataTable outputStreamTable;
string query;
string sortClause = string.Empty;
if (!string.IsNullOrEmpty(sortMember))
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
if (enabledOnly)
{
query = database.ParameterizedQueryString(string.Format("SELECT ID FROM OutputStreamDetail WHERE NodeID = {{0}} AND Enabled = {{1}} {0}", sortClause), "nodeID", "enabled");
outputStreamTable = database.Connection.RetrieveData(database.AdapterType, query, database.CurrentNodeID(), database.Bool(true));
}
else
{
query = database.ParameterizedQueryString(string.Format("SELECT * FROM OutputStreamDetail WHERE NodeID = {{0}} {0}", sortClause), "nodeID");
outputStreamTable = database.Connection.RetrieveData(database.AdapterType, query, database.CurrentNodeID());
}
foreach (DataRow row in outputStreamTable.Rows)
{
outputStreamList.Add(row.ConvertField<int>("ID"));
}
return outputStreamList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static
/// <summary>
/// Creates <see cref="ObservableCollection{T}"/> type collection of <see cref="RealTimeStatistic"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="RealTimeStatistic"/>.</returns>
public static ObservableCollection <RealTimeStatistic> Load(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection <RealTimeStatistic> realTimeStatisticList = new ObservableCollection <RealTimeStatistic>();
DataSet resultSet = new DataSet();
resultSet.EnforceConstraints = false;
// Get PDCs and directly connected devices.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name FROM DeviceDetail " +
"WHERE NodeID = {0} AND (IsConcentrator = {1} OR ParentAcronym = {2} OR ParentAcronym IS NULL) AND Enabled = {3} ORDER BY Acronym", "nodeID", "isConcentrator", "parentAcronym", "enabled"),
DefaultTimeout, database.CurrentNodeID(), database.Bool(true), string.Empty, database.Bool(true)).Copy());
resultSet.Tables[0].TableName = "DirectDevices";
// Get all the devices connected via PDC.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name, ParentID, " +
"ParentAcronym FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Enabled = {2} AND ParentID > 0 ORDER BY Acronym", "nodeID", "isConcentrator", "enabled"),
DefaultTimeout, database.CurrentNodeID(), database.Bool(false), database.Bool(true)).Copy());
resultSet.Tables[1].TableName = "PdcDevices";
// Get output stream information.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name FROM OutputStream WHERE " +
"NodeID = {0} AND Enabled = {1} ORDER BY Acronym", "nodeID", "enabled"), DefaultTimeout, database.CurrentNodeID(), database.Bool(true)).Copy());
resultSet.Tables[2].TableName = "OutputStreams";
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, AdapterName FROM CustomActionAdapter WHERE " +
"NodeID = {0} AND TypeName = {1} AND Enabled = {2} ORDER BY AdapterName", "nodeID", "typeName", "enabled"), DefaultTimeout, database.CurrentNodeID(), "GSF.TimeSeries.Transport.DataPublisher", database.Bool(true)).Copy());
resultSet.Tables[3].TableName = "DataPublishers";
// Get list of statistic measurements detail.
ObservableCollection <StatisticMeasurement> statisticMeasurements = GetStatisticMeasurements(database);
bool expanded = (statisticMeasurements.Count < 100);
// We do this for later use in refreshing data.
StatisticMeasurements = new Dictionary <Guid, StatisticMeasurement>();
foreach (StatisticMeasurement statisticMeasurement in statisticMeasurements)
{
StatisticMeasurements.Add(statisticMeasurement.SignalID, statisticMeasurement);
}
// Create a system statistics list.
ObservableCollection <StreamStatistic> systemStatistics = new ObservableCollection <StreamStatistic>();
systemStatistics.Add(new StreamStatistic()
{
ID = 0,
Acronym = "SYSTEM",
Name = "System",
StatusColor = "Green",
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection <StatisticMeasurement>(statisticMeasurements.Where(sm => sm.SignalReference.Contains("!SYSTEM"))),
DeviceStatisticList = new ObservableCollection <PdcDeviceStatistic>()
});
SystemStatistics = new Dictionary <int, StreamStatistic>();
foreach (StreamStatistic streamStatistic in systemStatistics)
{
// We do this to associate statistic measurement to parent output stream easily.
foreach (StatisticMeasurement measurement in streamStatistic.StatisticMeasurementList)
{
measurement.DeviceID = streamStatistic.ID;
}
streamStatistic.DeviceStatisticList.Insert(0, new PdcDeviceStatistic()
{
DeviceID = 0,
DeviceAcronym = "Run-time Statistics",
DeviceName = "",
Expanded = expanded,
StatisticMeasurementList = streamStatistic.StatisticMeasurementList
});
streamStatistic.StatisticMeasurementList = null;
// We do this for later use in refreshing data.
SystemStatistics.Add(streamStatistic.ID, streamStatistic);
}
// Create an input stream statistics list.
ObservableCollection <StreamStatistic> inputStreamStatistics = new ObservableCollection <StreamStatistic>
(
from stream in resultSet.Tables["DirectDevices"].AsEnumerable()
select new StreamStatistic
{
ID = Convert.ToInt32(stream.Field <object>("ID")),
Acronym = stream.Field <string>("Acronym"),
Name = stream.Field <string>("Name"),
StatusColor = "Gray",
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection <StatisticMeasurement>
(
(
from statisticMeasurement in statisticMeasurements
where statisticMeasurement.DeviceID == Convert.ToInt32(stream.Field <object>("ID"))
select statisticMeasurement
).OrderBy(sm => sm.Source).ThenBy(sm => sm.LoadOrder)
),
DeviceStatisticList = new ObservableCollection <PdcDeviceStatistic>
(
from pdcdevice in resultSet.Tables["PdcDevices"].AsEnumerable()
where Convert.ToInt32(pdcdevice.Field <object>("ParentID")) == Convert.ToInt32(stream.Field <object>("ID"))
select new PdcDeviceStatistic()
{
DeviceID = Convert.ToInt32(pdcdevice.Field <object>("ID")),
ParentID = Convert.ToInt32(pdcdevice.Field <object>("ParentID")),
DeviceAcronym = pdcdevice.Field <string>("Acronym"),
DeviceName = pdcdevice.Field <string>("Name"),
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection <StatisticMeasurement>
(
(
from statisticMeasurement in statisticMeasurements
where statisticMeasurement.DeviceID == Convert.ToInt32(pdcdevice.Field <object>("ID"))
select statisticMeasurement
).OrderBy(sm => sm.LoadOrder)
)
}
)
}
);
InputStreamStatistics = new Dictionary <int, StreamStatistic>();
DevicesWithStatisticMeasurements = new Dictionary <int, ObservableCollection <StatisticMeasurement> >();
foreach (StreamStatistic streamStatistic in inputStreamStatistics)
{
streamStatistic.DeviceStatisticList.Insert(0, new PdcDeviceStatistic()
{
DeviceID = 0,
DeviceAcronym = "Run-time Statistics",
DeviceName = "",
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection <StatisticMeasurement>(streamStatistic.StatisticMeasurementList)
});
// We do this for later use in refreshing data.
InputStreamStatistics.Add(streamStatistic.ID, streamStatistic);
// We do this for use in Input Status & Monitoring screen to set proper status color.
if (streamStatistic.ID > 0)
{
DevicesWithStatisticMeasurements.Add(streamStatistic.ID, streamStatistic.StatisticMeasurementList);
foreach (PdcDeviceStatistic device in streamStatistic.DeviceStatisticList)
{
if (device.DeviceID > 0)
{
DevicesWithStatisticMeasurements.Add(device.DeviceID, device.StatisticMeasurementList);
}
}
}
streamStatistic.StatisticMeasurementList = null;
}
// Create an output stream statistics list.
ObservableCollection <StreamStatistic> outputStreamStatistics = new ObservableCollection <StreamStatistic>
(
from outputStream in resultSet.Tables["OutputStreams"].AsEnumerable()
select new StreamStatistic()
{
ID = Convert.ToInt32(outputStream.Field <object>("ID")),
Acronym = outputStream.Field <string>("Acronym"),
Name = outputStream.Field <string>("Name"),
StatusColor = "Gray",
DeviceStatisticList = new ObservableCollection <PdcDeviceStatistic>(),
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection <StatisticMeasurement>
(
(
from statisticMeasurement in statisticMeasurements
where statisticMeasurement.SignalReference.StartsWith(outputStream.Field <string>("Acronym") + "!OS-")
select statisticMeasurement
).OrderBy(sm => sm.Source).ThenBy(sm => sm.LoadOrder)
)
}
);
OutputStreamStatistics = new Dictionary <int, StreamStatistic>();
foreach (StreamStatistic streamStatistic in outputStreamStatistics)
{
// We do this to associate statistic measurement to parent output stream easily.
foreach (StatisticMeasurement measurement in streamStatistic.StatisticMeasurementList)
{
measurement.DeviceID = streamStatistic.ID;
}
streamStatistic.DeviceStatisticList.Insert(0, new PdcDeviceStatistic
{
DeviceID = 0,
DeviceAcronym = "Run-time Statistics",
DeviceName = "",
Expanded = expanded,
StatisticMeasurementList = streamStatistic.StatisticMeasurementList
});
streamStatistic.StatisticMeasurementList = null;
// We do this for later use in refreshing data.
OutputStreamStatistics.Add(streamStatistic.ID, streamStatistic);
}
// Create a data publisher statistics list
ObservableCollection <StreamStatistic> dataPublisherStatistics = new ObservableCollection <StreamStatistic>(
from publisher in resultSet.Tables["DataPublishers"].AsEnumerable()
select new StreamStatistic()
{
ID = Convert.ToInt32(publisher.Field <object>("ID")),
Acronym = publisher.Field <string>("AdapterName"),
Name = "",
StatusColor = "Gray",
DeviceStatisticList = new ObservableCollection <PdcDeviceStatistic>(),
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection <StatisticMeasurement>
(
(
from statisticMeasurement in statisticMeasurements
where statisticMeasurement.SignalReference.StartsWith(publisher.Field <string>("AdapterName") + "!PUB-")
select statisticMeasurement
).OrderBy(sm => sm.Source).ThenBy(sm => sm.LoadOrder)
)
}
);
DataPublisherStatistics = new Dictionary <int, StreamStatistic>();
foreach (StreamStatistic streamStatistic in dataPublisherStatistics)
{
// We do this to associate statistic measurement to parent output stream easily.
foreach (StatisticMeasurement measurement in streamStatistic.StatisticMeasurementList)
{
measurement.DeviceID = streamStatistic.ID;
}
streamStatistic.DeviceStatisticList.Insert(0, new PdcDeviceStatistic()
{
DeviceID = 0,
DeviceAcronym = "Run-time Statistics",
DeviceName = "",
Expanded = expanded,
StatisticMeasurementList = streamStatistic.StatisticMeasurementList
});
streamStatistic.StatisticMeasurementList = null;
// We do this for later use in refreshing data.
DataPublisherStatistics.Add(streamStatistic.ID, streamStatistic);
}
// Merge system, input and output stream statistics to create a realtime statistics list.
realTimeStatisticList.Add(new RealTimeStatistic()
{
SourceType = "System",
Expanded = false,
StreamStatisticList = systemStatistics
});
realTimeStatisticList.Add(new RealTimeStatistic()
{
SourceType = "Input Streams",
Expanded = false,
StreamStatisticList = inputStreamStatistics
});
realTimeStatisticList.Add(new RealTimeStatistic()
{
SourceType = "Output Streams",
Expanded = false,
StreamStatisticList = outputStreamStatistics
});
realTimeStatisticList.Add(new RealTimeStatistic()
{
SourceType = "Data Publisher",
Expanded = false,
StreamStatisticList = dataPublisherStatistics
});
return(realTimeStatisticList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> denied for <see cref="Subscriber"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of ID and Name of <see cref="MeasurementGroup"/>.</returns>
public static Dictionary<int, string> GetDeniedMeasurementGroups(AdoDataConnection database, Guid subscriberID)
{
Dictionary<int, string> deniedMeasurementGroups;
DataTable deniedMeasurementGroupTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
deniedMeasurementGroups = new Dictionary<int, string>();
query = database.ParameterizedQueryString("SELECT MeasurementGroupID, MeasurementGroupName FROM SubscriberMeasGroupDetail WHERE SubscriberID = {0} AND Allowed = {1} ORDER BY MeasurementGroupName", "subscriberID", "allowed");
deniedMeasurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID), database.Bool(false));
foreach (DataRow row in deniedMeasurementGroupTable.Rows)
deniedMeasurementGroups[row.ConvertField<int>("MeasurementGroupID")] = row.Field<string>("MeasurementGroupName");
return deniedMeasurementGroups;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Historian"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <param name="includeStatHistorian">Indicates if statistical historian included in the collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of historians defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, bool isOptional = false, bool includeStatHistorian = true)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> historianList = new Dictionary<int, string>();
if (isOptional)
historianList.Add(0, "Select Historian");
string query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Historian WHERE Enabled = {0} AND NodeID = {1} ORDER BY LoadOrder", "enabled", "nodeID");
DataTable historianTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID());
foreach (DataRow row in historianTable.Rows)
{
if (!includeStatHistorian)
{
if (row.Field<string>("Acronym").ToUpper() != "STAT")
historianList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym");
}
else
historianList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym");
}
return historianList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Subscriber"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriber">Information about <see cref="Subscriber"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Subscriber subscriber)
{
bool createdConnection = false;
SslPolicyErrors validPolicyErrors;
X509ChainStatusFlags validChainFlags;
string query;
try
{
createdConnection = CreateConnection(ref database);
validPolicyErrors = (subscriber.ValidPolicyErrors ?? SslPolicyErrors.None) | (subscriber.RemoteCertificateIsSelfSigned ? SslPolicyErrors.RemoteCertificateChainErrors : SslPolicyErrors.None);
validChainFlags = (subscriber.ValidChainFlags ?? X509ChainStatusFlags.NoError) | (subscriber.RemoteCertificateIsSelfSigned ? X509ChainStatusFlags.UntrustedRoot : X509ChainStatusFlags.NoError);
if (subscriber.ID == Guid.Empty)
{
query = database.ParameterizedQueryString("INSERT INTO Subscriber (NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile, ValidPolicyErrors, ValidChainFlags, " +
"AccessControlFilter, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, " +
"{13}, {14})", "nodeID", "acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags",
"accessControlFilter", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(),
subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(),
validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE Subscriber SET NodeID = {0}, Acronym = {1}, Name = {2}, SharedSecret = {3}, AuthKey = {4}, ValidIPAddresses = {5}, RemoteCertificateFile = {6}, " +
"ValidPolicyErrors = {7}, ValidChainFlags = {8}, AccessControlFilter = {9}, Enabled = {10}, UpdatedBy = {11}, UpdatedOn = {12} WHERE ID = {13}", "nodeID",
"acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags", "accessControlFilter",
"enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriber.NodeID), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(),
subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(),
validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
database.Guid(subscriber.ID));
}
try
{
CommonFunctions.SendCommandToService("ReloadConfig");
}
catch (Exception ex)
{
return "Subscriber information saved successfully. Failed to send ReloadConfig command to backend service." + Environment.NewLine + ex.Message;
}
return "Subscriber information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static
/// <summary>
/// Creates <see cref="ObservableCollection{T}"/> type collection of <see cref="RealTimeStatistic"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="RealTimeStatistic"/>.</returns>
public static ObservableCollection<RealTimeStatistic> Load(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<RealTimeStatistic> realTimeStatisticList = new ObservableCollection<RealTimeStatistic>();
DataSet resultSet = new DataSet();
resultSet.EnforceConstraints = false;
// Get PDCs and directly connected devices.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name FROM DeviceDetail " +
"WHERE NodeID = {0} AND (IsConcentrator = {1} OR ParentAcronym = {2} OR ParentAcronym IS NULL) AND Enabled = {3} ORDER BY Acronym", "nodeID", "isConcentrator", "parentAcronym", "enabled"),
DefaultTimeout, database.CurrentNodeID(), database.Bool(true), string.Empty, database.Bool(true)).Copy());
resultSet.Tables[0].TableName = "DirectDevices";
// Get all the devices connected via PDC.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name, ParentID, " +
"ParentAcronym FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Enabled = {2} AND ParentID > 0 ORDER BY Acronym", "nodeID", "isConcentrator", "enabled"),
DefaultTimeout, database.CurrentNodeID(), database.Bool(false), database.Bool(true)).Copy());
resultSet.Tables[1].TableName = "PdcDevices";
// Get output stream information.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name FROM OutputStream WHERE " +
"NodeID = {0} AND Enabled = {1} ORDER BY Acronym", "nodeID", "enabled"), DefaultTimeout, database.CurrentNodeID(), database.Bool(true)).Copy());
resultSet.Tables[2].TableName = "OutputStreams";
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, AdapterName FROM CustomActionAdapter WHERE " +
"NodeID = {0} AND TypeName = {1} AND Enabled = {2} ORDER BY AdapterName", "nodeID", "typeName", "enabled"), DefaultTimeout, database.CurrentNodeID(), "GSF.TimeSeries.Transport.DataPublisher", database.Bool(true)).Copy());
resultSet.Tables[3].TableName = "DataPublishers";
// Get list of statistic measurements detail.
ObservableCollection<StatisticMeasurement> statisticMeasurements = GetStatisticMeasurements(database);
bool expanded = (statisticMeasurements.Count < 100);
// We do this for later use in refreshing data.
StatisticMeasurements = new Dictionary<Guid, StatisticMeasurement>();
foreach (StatisticMeasurement statisticMeasurement in statisticMeasurements)
StatisticMeasurements.Add(statisticMeasurement.SignalID, statisticMeasurement);
// Create a system statistics list.
ObservableCollection<StreamStatistic> systemStatistics = new ObservableCollection<StreamStatistic>();
systemStatistics.Add(new StreamStatistic()
{
ID = 0,
Acronym = "SYSTEM",
Name = "System",
StatusColor = "Green",
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection<StatisticMeasurement>(statisticMeasurements.Where(sm => sm.SignalReference.Contains("!SYSTEM"))),
DeviceStatisticList = new ObservableCollection<PdcDeviceStatistic>()
});
SystemStatistics = new Dictionary<int, StreamStatistic>();
foreach (StreamStatistic streamStatistic in systemStatistics)
{
// We do this to associate statistic measurement to parent output stream easily.
foreach (StatisticMeasurement measurement in streamStatistic.StatisticMeasurementList)
measurement.DeviceID = streamStatistic.ID;
streamStatistic.DeviceStatisticList.Insert(0, new PdcDeviceStatistic()
{
DeviceID = 0,
DeviceAcronym = "Run-time Statistics",
DeviceName = "",
Expanded = expanded,
StatisticMeasurementList = streamStatistic.StatisticMeasurementList
});
streamStatistic.StatisticMeasurementList = null;
// We do this for later use in refreshing data.
SystemStatistics.Add(streamStatistic.ID, streamStatistic);
}
// Create an input stream statistics list.
ObservableCollection<StreamStatistic> inputStreamStatistics = new ObservableCollection<StreamStatistic>
(
from stream in resultSet.Tables["DirectDevices"].AsEnumerable()
select new StreamStatistic
{
ID = Convert.ToInt32(stream.Field<object>("ID")),
Acronym = stream.Field<string>("Acronym"),
Name = stream.Field<string>("Name"),
StatusColor = "Gray",
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection<StatisticMeasurement>
(
(
from statisticMeasurement in statisticMeasurements
where statisticMeasurement.DeviceID == Convert.ToInt32(stream.Field<object>("ID"))
select statisticMeasurement
).OrderBy(sm => sm.Source).ThenBy(sm => sm.LoadOrder)
),
DeviceStatisticList = new ObservableCollection<PdcDeviceStatistic>
(
from pdcdevice in resultSet.Tables["PdcDevices"].AsEnumerable()
where Convert.ToInt32(pdcdevice.Field<object>("ParentID")) == Convert.ToInt32(stream.Field<object>("ID"))
select new PdcDeviceStatistic()
{
DeviceID = Convert.ToInt32(pdcdevice.Field<object>("ID")),
ParentID = Convert.ToInt32(pdcdevice.Field<object>("ParentID")),
DeviceAcronym = pdcdevice.Field<string>("Acronym"),
DeviceName = pdcdevice.Field<string>("Name"),
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection<StatisticMeasurement>
(
(
from statisticMeasurement in statisticMeasurements
where statisticMeasurement.DeviceID == Convert.ToInt32(pdcdevice.Field<object>("ID"))
select statisticMeasurement
).OrderBy(sm => sm.LoadOrder)
)
}
)
}
);
InputStreamStatistics = new Dictionary<int, StreamStatistic>();
DevicesWithStatisticMeasurements = new Dictionary<int, ObservableCollection<StatisticMeasurement>>();
foreach (StreamStatistic streamStatistic in inputStreamStatistics)
{
streamStatistic.DeviceStatisticList.Insert(0, new PdcDeviceStatistic()
{
DeviceID = 0,
DeviceAcronym = "Run-time Statistics",
DeviceName = "",
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection<StatisticMeasurement>(streamStatistic.StatisticMeasurementList)
});
// We do this for later use in refreshing data.
InputStreamStatistics.Add(streamStatistic.ID, streamStatistic);
// We do this for use in Input Status & Monitoring screen to set proper status color.
if (streamStatistic.ID > 0)
{
DevicesWithStatisticMeasurements.Add(streamStatistic.ID, streamStatistic.StatisticMeasurementList);
foreach (PdcDeviceStatistic device in streamStatistic.DeviceStatisticList)
{
if (device.DeviceID > 0)
DevicesWithStatisticMeasurements.Add(device.DeviceID, device.StatisticMeasurementList);
}
}
streamStatistic.StatisticMeasurementList = null;
}
// Create an output stream statistics list.
ObservableCollection<StreamStatistic> outputStreamStatistics = new ObservableCollection<StreamStatistic>
(
from outputStream in resultSet.Tables["OutputStreams"].AsEnumerable()
select new StreamStatistic()
{
ID = Convert.ToInt32(outputStream.Field<object>("ID")),
Acronym = outputStream.Field<string>("Acronym"),
Name = outputStream.Field<string>("Name"),
StatusColor = "Gray",
DeviceStatisticList = new ObservableCollection<PdcDeviceStatistic>(),
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection<StatisticMeasurement>
(
(
from statisticMeasurement in statisticMeasurements
where statisticMeasurement.SignalReference.StartsWith(outputStream.Field<string>("Acronym") + "!OS-")
select statisticMeasurement
).OrderBy(sm => sm.Source).ThenBy(sm => sm.LoadOrder)
)
}
);
OutputStreamStatistics = new Dictionary<int, StreamStatistic>();
foreach (StreamStatistic streamStatistic in outputStreamStatistics)
{
// We do this to associate statistic measurement to parent output stream easily.
foreach (StatisticMeasurement measurement in streamStatistic.StatisticMeasurementList)
measurement.DeviceID = streamStatistic.ID;
streamStatistic.DeviceStatisticList.Insert(0, new PdcDeviceStatistic
{
DeviceID = 0,
DeviceAcronym = "Run-time Statistics",
DeviceName = "",
Expanded = expanded,
StatisticMeasurementList = streamStatistic.StatisticMeasurementList
});
streamStatistic.StatisticMeasurementList = null;
// We do this for later use in refreshing data.
OutputStreamStatistics.Add(streamStatistic.ID, streamStatistic);
}
// Create a data publisher statistics list
ObservableCollection<StreamStatistic> dataPublisherStatistics = new ObservableCollection<StreamStatistic>(
from publisher in resultSet.Tables["DataPublishers"].AsEnumerable()
select new StreamStatistic()
{
ID = Convert.ToInt32(publisher.Field<object>("ID")),
Acronym = publisher.Field<string>("AdapterName"),
Name = "",
StatusColor = "Gray",
DeviceStatisticList = new ObservableCollection<PdcDeviceStatistic>(),
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection<StatisticMeasurement>
(
(
from statisticMeasurement in statisticMeasurements
where statisticMeasurement.SignalReference.StartsWith(publisher.Field<string>("AdapterName") + "!PUB-")
select statisticMeasurement
).OrderBy(sm => sm.Source).ThenBy(sm => sm.LoadOrder)
)
}
);
DataPublisherStatistics = new Dictionary<int, StreamStatistic>();
foreach (StreamStatistic streamStatistic in dataPublisherStatistics)
{
// We do this to associate statistic measurement to parent output stream easily.
foreach (StatisticMeasurement measurement in streamStatistic.StatisticMeasurementList)
measurement.DeviceID = streamStatistic.ID;
streamStatistic.DeviceStatisticList.Insert(0, new PdcDeviceStatistic()
{
DeviceID = 0,
DeviceAcronym = "Run-time Statistics",
DeviceName = "",
Expanded = expanded,
StatisticMeasurementList = streamStatistic.StatisticMeasurementList
});
streamStatistic.StatisticMeasurementList = null;
// We do this for later use in refreshing data.
DataPublisherStatistics.Add(streamStatistic.ID, streamStatistic);
}
// Merge system, input and output stream statistics to create a realtime statistics list.
realTimeStatisticList.Add(new RealTimeStatistic()
{
SourceType = "System",
Expanded = false,
StreamStatisticList = systemStatistics
});
realTimeStatisticList.Add(new RealTimeStatistic()
{
SourceType = "Input Streams",
Expanded = false,
StreamStatisticList = inputStreamStatistics
});
realTimeStatisticList.Add(new RealTimeStatistic()
{
SourceType = "Output Streams",
Expanded = false,
StreamStatisticList = outputStreamStatistics
});
realTimeStatisticList.Add(new RealTimeStatistic()
{
SourceType = "Data Publisher",
Expanded = false,
StreamStatisticList = dataPublisherStatistics
});
return realTimeStatisticList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}