/// <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> /// Creates and saves new <see cref="OutputStreamMeasurement"/> into the database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamID">ID of the output stream.</param> /// <param name="measurements">Collection of measurements to be added.</param> /// <returns>String, for display use, indicating success.</returns> public static string AddMeasurements(AdoDataConnection database, int outputStreamID, ObservableCollection <Measurement> measurements) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); foreach (Measurement measurement in measurements) { OutputStreamMeasurement outputStreamMeasurement = new OutputStreamMeasurement { NodeID = (Guid)database.CurrentNodeID(), AdapterID = outputStreamID, HistorianID = measurement.HistorianID, PointID = measurement.PointID, SignalReference = measurement.SignalReference }; Save(database, outputStreamMeasurement); } return("Output stream measurements added successfully."); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Method to add <see cref="Measurement"/>s to <see cref="MeasurementGroup"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="measurementGroupID">ID of the <see cref="MeasurementGroup"/> to add <see cref="Measurement"/> to.</param> /// <param name="measurementsToBeAdded">List of <see cref="Measurement"/> signal ids to be added.</param> /// <returns>string, indicating success for UI display.</returns> public static string AddMeasurements(AdoDataConnection database, int measurementGroupID, List <Guid> measurementsToBeAdded) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); foreach (Guid id in measurementsToBeAdded) { query = database.ParameterizedQueryString("INSERT INTO MeasurementGroupMeasurement (NodeID, MeasurementGroupID, SignalID) VALUES ({0}, {1}, {2})", "nodeID", "measurementGroupID", "signalID"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), measurementGroupID, database.Guid(id)); } return("Measurements added to group successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// LoadKeys <see cref="PowerCalculation"/> 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="int"/>.</returns> public static IList <int> LoadKeys(AdoDataConnection database) { var createdConnection = false; try { createdConnection = CreateConnection(ref database); var calculationList = new List <int>(); var queryFormat = "SELECT ID FROM PowerCalculation WHERE NodeID = {0}"; var calculationTable = database.RetrieveData(queryFormat, database.CurrentNodeID()); foreach (DataRow row in calculationTable.Rows) { calculationList.Add(row.ConvertField <int>("ID")); } return(calculationList); } 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(); } } }
/// <summary> /// Saves <see cref="MeasurementGroup"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="measurementGroup">Information about <see cref="MeasurementGroup"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, MeasurementGroup measurementGroup) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (measurementGroup.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO MeasurementGroup (NodeID, Name, Description, FilterExpression, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " + "VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})", "nodeID", "name", "description", "filterExpression", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), measurementGroup.Name, measurementGroup.Description.ToNotNull(), measurementGroup.FilterExpression.ToNotNull(), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE MeasurementGroup SET NodeID = {0}, Name = {1}, Description = {2}, FilterExpression = {3}, " + "UpdatedBy = {4}, UpdatedOn = {5} WHERE ID = {6}", "nodeID", "name", "description", "filterExpression", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), measurementGroup.Name, measurementGroup.Description.ToNotNull(), measurementGroup.FilterExpression.ToNotNull(), CommonFunctions.CurrentUser, database.UtcNow, measurementGroup.ID); } return("Measurement group information saved successfully"); } catch (Exception ex) { return(ex.Message); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Saves <see cref="OutputStreamDeviceAnalog"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamDeviceAnalog">Information about <see cref="OutputStreamDeviceAnalog"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, OutputStreamDeviceAnalog outputStreamDeviceAnalog) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (outputStreamDeviceAnalog.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO OutputStreamDeviceAnalog (NodeID, OutputStreamDeviceID, Label, Type, ScalingValue, LoadOrder, " + "UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9})", "nodeID", "outputStreamDeviceID", "label", "type", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "createdBy", "createdOn"); // TypeName, "typeName", database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDeviceAnalog.OutputStreamDeviceID, outputStreamDeviceAnalog.Label, outputStreamDeviceAnalog.Type, outputStreamDeviceAnalog.ScalingValue, outputStreamDeviceAnalog.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); //outputStreamDeviceAnalog.TypeName, } else { query = database.ParameterizedQueryString("UPDATE OutputStreamDeviceAnalog SET NodeID = {0}, OutputStreamDeviceID = {1}, Label = {2}, Type = {3}, " + "ScalingValue = {4}, LoadOrder = {5}, UpdatedBy = {6}, UpdatedOn = {7} WHERE ID = {8}", "nodeID", "outputStreamDeviceID", "label", "type", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "id"); // TypeName= {6}, "typeName", database.Connection.ExecuteNonQuery(query, DefaultTimeout, outputStreamDeviceAnalog.NodeID, outputStreamDeviceAnalog.OutputStreamDeviceID, outputStreamDeviceAnalog.Label, outputStreamDeviceAnalog.Type, outputStreamDeviceAnalog.ScalingValue, outputStreamDeviceAnalog.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, outputStreamDeviceAnalog.ID); } // OutputStreamDeviceAnalog.TypeName, return("OutputStreamDeviceAnalog information saved successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Saves <see cref="OutputStreamDevicePhasor"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamDevicePhasor">Information about <see cref="OutputStreamDevicePhasor"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, OutputStreamDevicePhasor outputStreamDevicePhasor) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (outputStreamDevicePhasor.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO OutputStreamDevicePhasor (NodeID, OutputStreamDeviceID, Label, Type, Phase, ScalingValue, " + "LoadOrder, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10})", "nodeID", "outputStreamDeviceID", "label", "type", "phase", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDevicePhasor.OutputStreamDeviceID, outputStreamDevicePhasor.Label, outputStreamDevicePhasor.Type, outputStreamDevicePhasor.Phase, outputStreamDevicePhasor.ScalingValue, outputStreamDevicePhasor.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); // PhasorType, PhaseType @phasorName, @phaseType, OutputStreamDevicePhasor.PhasorType, OutputStreamDevicePhasor.PhaseType } else { query = "UPDATE OutputStreamDevicePhasor SET NodeID = {0}, OutputStreamDeviceID = {1}, Label = {2}, Type = {3}, " + "Phase = {4}, ScalingValue = {5}, LoadOrder = {6}, UpdatedBy = {7}, UpdatedOn = {8} WHERE ID = {9}"; database.ExecuteNonQuery(DefaultTimeout, query, outputStreamDevicePhasor.NodeID, outputStreamDevicePhasor.OutputStreamDeviceID, outputStreamDevicePhasor.Label, outputStreamDevicePhasor.Type, outputStreamDevicePhasor.Phase, outputStreamDevicePhasor.ScalingValue, outputStreamDevicePhasor.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, outputStreamDevicePhasor.ID); //PhasorType = @typeName, PhaseType = @PhaseType" OutputStreamDevicePhasor.PhasorType, OutputStreamDevicePhasor.PhaseType, } return("OutputStreamDevicePhasor information saved successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Saves <see cref="Company"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="powerCalculation">Information about <see cref="PowerCalculation"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, PowerCalculation powerCalculation) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (powerCalculation.ID == 0) { query = "INSERT INTO PowerCalculation (CircuitDescription, VoltageAngleSignalID, VoltageMagSignalID, CurrentAngleSignalID, CurrentMagSignalID, ActivePowerOutputSignalID, ReactivePowerOutputSignalID, ApparentPowerOutputSignalID, Enabled, NodeID) " + "VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9})"; database.ExecuteNonQuery(DefaultTimeout, query, powerCalculation.CircuitDescription, powerCalculation.VoltagePhasor.AngleMeasurement.SignalID, powerCalculation.VoltagePhasor.MagnitudeMeasurement.SignalID, powerCalculation.CurrentPhasor.AngleMeasurement.SignalID, powerCalculation.CurrentPhasor.MagnitudeMeasurement.SignalID, (object)powerCalculation.ActivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ReactivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ApparentPowerOutputMeasurement?.SignalID ?? DBNull.Value, powerCalculation.Enabled, database.CurrentNodeID()); } else { query = "UPDATE PowerCalculation SET CircuitDescription = {0}, VoltageAngleSignalID = {1}, VoltageMagSignalID = {2}, CurrentAngleSignalID = {3}, CurrentMagSignalID = {4}, " + "activePowerOutputSignalID = {5}, ReactivePowerOutputSignalID = {6}, ApparentPowerOutputSignalID = {7}, Enabled = {8} WHERE ID = {9}"; database.ExecuteNonQuery(DefaultTimeout, query, powerCalculation.CircuitDescription, database.Guid(powerCalculation.VoltagePhasor.AngleMeasurement.SignalID), powerCalculation.VoltagePhasor.MagnitudeMeasurement.SignalID, powerCalculation.CurrentPhasor.AngleMeasurement.SignalID, powerCalculation.CurrentPhasor.MagnitudeMeasurement.SignalID, (object)powerCalculation.ActivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ReactivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ApparentPowerOutputMeasurement?.SignalID ?? DBNull.Value, powerCalculation.Enabled, powerCalculation.ID); } return("Power Calculation information saved successfully"); } 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(); } } }
// Creates a measurement associated with the given alarm and returns the new measurements signal ID. private static Guid?CreateAlarmMeasurement(AdoDataConnection database, Alarm alarm) { object nodeID; Historian historian; Measurement alarmMeasurement; int signalTypeId; try { nodeID = (alarm.NodeID != Guid.Empty) ? database.Guid(alarm.NodeID) : database.CurrentNodeID(); historian = Historian.GetHistorian(database, string.Format("WHERE Acronym = 'STAT' AND NodeID = '{0}'", nodeID)); signalTypeId = Convert.ToInt32(database.Connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym = 'ALRM'", DefaultTimeout)); alarmMeasurement = new Measurement() { HistorianID = historian.ID, PointTag = alarm.TagName, SignalTypeID = signalTypeId, SignalReference = "ALARM!SERVICES-AL" + alarm.ID, Description = "Measurement associated with alarm " + alarm.ID, Internal = true, Enabled = true }; Measurement.Save(database, alarmMeasurement); alarmMeasurement = Measurement.GetMeasurement(database, string.Format("WHERE PointTag = '{0}'", alarm.TagName)); return(alarmMeasurement.SignalID); } catch { // Return null to indicate measurement // was not saved to database return(null); } }
/// <summary> /// Loads <see cref="Alarm"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="keys">Keys of the adapters to be loaded from the database.</param> /// <returns>Collection of <see cref="Alarm"/>.</returns> public static ObservableCollection <Alarm> Load(AdoDataConnection database, IList <int> keys) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string query; string commaSeparatedKeys; Alarm[] alarmList = null; DataTable alarmTable; object associatedMeasurementId; int id; if ((object)keys != null && keys.Count > 0) { commaSeparatedKeys = keys.Select(key => "" + key.ToString() + "").Aggregate((str1, str2) => str1 + "," + str2); query = database.ParameterizedQueryString(string.Format("SELECT NodeID, TagName, ID, SignalID, AssociatedMeasurementID, Description, Severity, Operation, " + "SetPoint, Tolerance, Delay, Hysteresis, LoadOrder, Enabled FROM Alarm WHERE NodeID = {{0}} AND ID IN ({0})", commaSeparatedKeys), "nodeID"); alarmTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); alarmList = new Alarm[alarmTable.Rows.Count]; foreach (DataRow row in alarmTable.Rows) { id = row.ConvertField <int>("ID"); associatedMeasurementId = row.Field <object>("AssociatedMeasurementID"); alarmList[keys.IndexOf(id)] = new Alarm() { NodeID = database.Guid(row, "NodeID"), ID = id, TagName = row.Field <string>("TagName"), SignalID = database.Guid(row, "SignalID"), AssociatedMeasurementID = (associatedMeasurementId != null) ? Guid.Parse(associatedMeasurementId.ToString()) : (Guid?)null, Description = row.Field <string>("Description"), Severity = row.ConvertField <int>("Severity"), Operation = row.ConvertField <int>("Operation"), SetPoint = row.ConvertNullableField <double>("SetPoint"), Tolerance = row.ConvertNullableField <double>("Tolerance"), Delay = row.ConvertNullableField <double>("Delay"), Hysteresis = row.ConvertNullableField <double>("Hysteresis"), LoadOrder = row.ConvertField <int>("LoadOrder"), Enabled = row.ConvertField <bool>("Enabled"), CreateAssociatedMeasurement = (associatedMeasurementId != null) }; } } return(new ObservableCollection <Alarm>(alarmList ?? new Alarm[0])); } 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> /// Loads <see cref="Node"/> 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="Subscriber"/>.</returns> public static ObservableCollection <Subscriber> Load(AdoDataConnection database) { ObservableCollection <Subscriber> subscriberList; DataTable subscriberTable; bool createdConnection = false; string query; SslPolicyErrors validPolicyErrors; X509ChainStatusFlags validChainFlags; try { createdConnection = CreateConnection(ref database); subscriberList = new ObservableCollection <Subscriber>(); query = database.ParameterizedQueryString("SELECT ID, NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile," + " ValidPolicyErrors, ValidChainFlags, AccessControlFilter, Enabled FROM Subscriber WHERE NodeID = {0} ORDER BY Name", "nodeID"); subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in subscriberTable.Rows) { subscriberList.Add(new Subscriber() { ID = database.Guid(row, "ID"), NodeID = database.Guid(row, "NodeID"), Acronym = row.Field <string>("Acronym"), Name = row.Field <string>("Name"), SharedSecret = row.Field <string>("SharedSecret"), AuthKey = row.Field <string>("AuthKey"), ValidIPAddresses = row.Field <string>("ValidIPAddresses"), RemoteCertificateFile = row.Field <string>("RemoteCertificateFile"), ValidPolicyErrors = Enum.TryParse(row.Field <string>("ValidPolicyErrors"), out validPolicyErrors) ? validPolicyErrors : (SslPolicyErrors?)null, ValidChainFlags = Enum.TryParse(row.Field <string>("ValidChainFlags"), out validChainFlags) ? validChainFlags : (X509ChainStatusFlags?)null, AccessControlFilter = row.Field <string>("AccessControlFilter"), Enabled = Convert.ToBoolean(row.Field <object>("Enabled")), AllowedMeasurementGroups = GetAllowedMeasurementGroups(database, database.Guid(row, "ID")), DeniedMeasurementGroups = GetDeniedMeasurementGroups(database, database.Guid(row, "ID")), AvailableMeasurementGroups = GetAvailableMeasurementGroups(database, database.Guid(row, "ID")), StatusColor = "gray", Version = "" }); } return(subscriberList); } 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(); } }
/// <summary> /// Saves <see cref="OutputStreamDeviceDigital"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamDeviceDigital">Information about <see cref="OutputStreamDeviceDigital"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, OutputStreamDeviceDigital outputStreamDeviceDigital) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (outputStreamDeviceDigital.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO OutputStreamDeviceDigital (NodeID, OutputStreamDeviceID, Label, MaskValue, LoadOrder, " + "UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8})", "nodeID", "outputStreamDeviceID", "label", "maskValue", "loadOrder", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDeviceDigital.OutputStreamDeviceID, outputStreamDeviceDigital.Label, outputStreamDeviceDigital.MaskValue, outputStreamDeviceDigital.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE OutputStreamDeviceDigital SET NodeID = {0}, OutputStreamDeviceID = {1}, Label = {2}, MaskValue = {3}, " + "LoadOrder = {4}, UpdatedBy = {5}, UpdatedOn = {6} WHERE ID = {7}", "nodeID", "outputStreamDeviceID", "label", "maskValue", "loadOrder", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, outputStreamDeviceDigital.NodeID, outputStreamDeviceDigital.OutputStreamDeviceID, outputStreamDeviceDigital.Label, outputStreamDeviceDigital.MaskValue, outputStreamDeviceDigital.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, outputStreamDeviceDigital.ID); } return "OutputStreamDeviceDigital information saved successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
// Static Methods /// <summary> /// Loads <see cref="Adapter"/> IDs as an <see cref="IList{T}"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="adapterType"><see cref="AdapterType"/> collection to be returned.</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="Int32"/>.</returns> public static IList <int> LoadIDs(AdoDataConnection database, AdapterType adapterType, string sortMember, string sortDirection) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string viewName; if (adapterType == AdapterType.Action) { viewName = "CustomActionAdapterDetail"; } else if (adapterType == AdapterType.Filter) { viewName = "CustomFilterAdapterDetail"; } else if (adapterType == AdapterType.Input) { viewName = "CustomInputAdapterDetail"; } else { viewName = "CustomOutputAdapterDetail"; } IList <int> adapterList = new List <int>(); string sortClause = string.Empty; DataTable adapterTable; string query; if (!string.IsNullOrEmpty(sortMember)) { sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection); } query = database.ParameterizedQueryString(string.Format("SELECT ID FROM {0} WHERE NodeID = {{0}} {1}", viewName, sortClause), "nodeID"); adapterTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in adapterTable.Rows) { adapterList.Add(row.ConvertField <int>("ID")); } return(adapterList); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Saves <see cref="MeasurementGroup"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="measurementGroup">Information about <see cref="MeasurementGroup"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, MeasurementGroup measurementGroup) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (measurementGroup.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO MeasurementGroup (NodeID, Name, Description, FilterExpression, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " + "VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})", "nodeID", "name", "description", "filterExpression", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), measurementGroup.Name, measurementGroup.Description.ToNotNull(), measurementGroup.FilterExpression.ToNotNull(), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE MeasurementGroup SET NodeID = {0}, Name = {1}, Description = {2}, FilterExpression = {3}, " + "UpdatedBy = {4}, UpdatedOn = {5} WHERE ID = {6}", "nodeID", "name", "description", "filterExpression", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), measurementGroup.Name, measurementGroup.Description.ToNotNull(), measurementGroup.FilterExpression.ToNotNull(), CommonFunctions.CurrentUser, database.UtcNow, measurementGroup.ID); } return "Measurement group information saved successfully"; } catch (Exception ex) { return ex.Message; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Retrieves a <see cref="Dictionary{T1,T2}"/> style list of <see cref="MeasurementGroup"/> 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 <see cref="MeasurementGroup"/>s defined in the database.</returns> public static Dictionary<int, string> GetLookupList(AdoDataConnection database, bool isOptional = true) { Dictionary<int, string> measurementGroupList; DataTable measurementGroupTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); measurementGroupList = new Dictionary<int, string>(); if (isOptional) measurementGroupList.Add(0, "Select MeasurementGroup"); query = database.ParameterizedQueryString("SELECT ID, Name FROM MeasurementGroup WHERE NodeID = {0} ORDER BY SourceIndex", "nodeID"); measurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in measurementGroupTable.Rows) measurementGroupList[row.ConvertField<int>("ID")] = row.Field<string>("Name"); return measurementGroupList; } finally { if (createdConnection && database != null) database.Dispose(); } }
// Static Methods /// <summary> /// Loads <see cref="MeasurementGroup"/> 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="MeasurementGroup"/>.</returns> public static ObservableCollection<MeasurementGroup> Load(AdoDataConnection database) { ObservableCollection<MeasurementGroup> measurementGroupList; DataTable measurementGroupTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); measurementGroupList = new ObservableCollection<MeasurementGroup>(); query = database.ParameterizedQueryString("SELECT NodeID, ID, Name, Description, FilterExpression FROM MeasurementGroup WHERE NodeID = {0} ORDER BY Name", "nodeID"); measurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in measurementGroupTable.Rows) { measurementGroupList.Add(new MeasurementGroup() { NodeID = database.Guid(row, "NodeID"), ID = row.ConvertField<int>("ID"), Name = row.Field<string>("Name"), Description = row.Field<object>("Description").ToNonNullString(), FilterExpression = row.Field<object>("FilterExpression").ToNonNullString(), }); } measurementGroupList.Insert(0, new MeasurementGroup()); return measurementGroupList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Creates and saves new <see cref="OutputStreamMeasurement"/> into the database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamID">ID of the output stream.</param> /// <param name="measurements">Collection of measurements to be added.</param> /// <returns>String, for display use, indicating success.</returns> public static string AddMeasurements(AdoDataConnection database, int outputStreamID, ObservableCollection<Measurement> measurements) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); foreach (Measurement measurement in measurements) { OutputStreamMeasurement outputStreamMeasurement = new OutputStreamMeasurement { NodeID = (Guid)database.CurrentNodeID(), AdapterID = outputStreamID, HistorianID = measurement.HistorianID, PointID = measurement.PointID, SignalReference = measurement.SignalReference }; Save(database, outputStreamMeasurement); } return "Output stream measurements added successfully."; } finally { if (createdConnection && database != null) database.Dispose(); } }
// Creates a measurement associated with the given alarm and returns the new measurements signal ID. private static Guid? CreateAlarmMeasurement(AdoDataConnection database, Alarm alarm) { object nodeID; Historian historian; Measurement alarmMeasurement; int signalTypeId; try { nodeID = (alarm.NodeID != Guid.Empty) ? database.Guid(alarm.NodeID) : database.CurrentNodeID(); historian = Historian.GetHistorian(database, string.Format("WHERE Acronym = 'STAT' AND NodeID = '{0}'", nodeID)); signalTypeId = Convert.ToInt32(database.Connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym = 'ALRM'", DefaultTimeout)); alarmMeasurement = new Measurement() { HistorianID = historian.ID, PointTag = alarm.TagName, SignalTypeID = signalTypeId, SignalReference = "ALARM!SERVICES-AL" + alarm.ID, Description = "Measurement associated with alarm " + alarm.ID, Internal = true, Enabled = true }; Measurement.Save(database, alarmMeasurement); alarmMeasurement = Measurement.GetMeasurement(database, string.Format("WHERE PointTag = '{0}'", alarm.TagName)); return alarmMeasurement.SignalID; } catch { // Return null to indicate measurement // was not saved to database return null; } }
/// <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> /// 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> /// Loads <see cref="Alarm"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="keys">Keys of the adapters to be loaded from the database.</param> /// <returns>Collection of <see cref="Alarm"/>.</returns> public static ObservableCollection<Alarm> Load(AdoDataConnection database, IList<int> keys) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string query; string commaSeparatedKeys; Alarm[] alarmList = null; DataTable alarmTable; object associatedMeasurementId; int id; if ((object)keys != null && keys.Count > 0) { commaSeparatedKeys = keys.Select(key => "" + key.ToString() + "").Aggregate((str1, str2) => str1 + "," + str2); query = database.ParameterizedQueryString(string.Format("SELECT NodeID, TagName, ID, SignalID, AssociatedMeasurementID, Description, Severity, Operation, " + "SetPoint, Tolerance, Delay, Hysteresis, LoadOrder, Enabled FROM Alarm WHERE NodeID = {{0}} AND ID IN ({0})", commaSeparatedKeys), "nodeID"); alarmTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); alarmList = new Alarm[alarmTable.Rows.Count]; foreach (DataRow row in alarmTable.Rows) { id = row.ConvertField<int>("ID"); associatedMeasurementId = row.Field<object>("AssociatedMeasurementID"); alarmList[keys.IndexOf(id)] = new Alarm() { NodeID = database.Guid(row, "NodeID"), ID = id, TagName = row.Field<string>("TagName"), SignalID = database.Guid(row, "SignalID"), AssociatedMeasurementID = (associatedMeasurementId != null) ? Guid.Parse(associatedMeasurementId.ToString()) : (Guid?)null, Description = row.Field<string>("Description"), Severity = row.ConvertField<int>("Severity"), Operation = row.ConvertField<int>("Operation"), SetPoint = row.ConvertNullableField<double>("SetPoint"), Tolerance = row.ConvertNullableField<double>("Tolerance"), Delay = row.ConvertNullableField<double>("Delay"), Hysteresis = row.ConvertNullableField<double>("Hysteresis"), LoadOrder = row.ConvertField<int>("LoadOrder"), Enabled = row.ConvertField<bool>("Enabled"), CreateAssociatedMeasurement = (associatedMeasurementId != null) }; } } return new ObservableCollection<Alarm>(alarmList ?? new Alarm[0]); } finally { if (createdConnection && database != null) database.Dispose(); } }
// Loads existing alarms defined in the database private IEnumerable<Alarm> LoadDefinedAlarms() { // Create alarms using definitions from the database AdoDataConnection database = null; try { // Create database connection to get currently defined alarms database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory); string query = database.ParameterizedQueryString("SELECT * FROM Alarm WHERE NodeID = {0} AND Enabled <> 0", "nodeID"); DataTable alarms = database.Connection.RetrieveData(database.AdapterType, query, database.CurrentNodeID()); return alarms.Rows.Cast<DataRow>() .Select(CreateAlarm) .ToList(); } finally { if ((object)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.Filter) { tableName = "CustomFilterAdapter"; } 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(); } } }
// Static Methods /// <summary> /// Loads <see cref="MeasurementGroup"/> 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="MeasurementGroup"/>.</returns> public static ObservableCollection <MeasurementGroup> Load(AdoDataConnection database) { ObservableCollection <MeasurementGroup> measurementGroupList; DataTable measurementGroupTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); measurementGroupList = new ObservableCollection <MeasurementGroup>(); query = database.ParameterizedQueryString("SELECT NodeID, ID, Name, Description, FilterExpression FROM MeasurementGroup WHERE NodeID = {0} ORDER BY Name", "nodeID"); measurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in measurementGroupTable.Rows) { measurementGroupList.Add(new MeasurementGroup() { NodeID = database.Guid(row, "NodeID"), ID = row.ConvertField <int>("ID"), Name = row.Field <string>("Name"), Description = row.Field <object>("Description").ToNonNullString(), FilterExpression = row.Field <object>("FilterExpression").ToNonNullString(), CurrentMeasurements = GetCurrentMeasurements(database, row.ConvertField <int>("ID")), PossibleMeasurements = GetPossibleMeasurements(database, row.ConvertField <int>("ID")) }); } measurementGroupList.Insert(0, new MeasurementGroup()); return(measurementGroupList); } 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> /// Retrieves a <see cref="Dictionary{T1,T2}"/> style list of <see cref="MeasurementGroup"/> 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 <see cref="MeasurementGroup"/>s defined in the database.</returns> public static Dictionary <int, string> GetLookupList(AdoDataConnection database, bool isOptional = true) { Dictionary <int, string> measurementGroupList; DataTable measurementGroupTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); measurementGroupList = new Dictionary <int, string>(); if (isOptional) { measurementGroupList.Add(0, "Select MeasurementGroup"); } query = database.ParameterizedQueryString("SELECT ID, Name FROM MeasurementGroup WHERE NodeID = {0} ORDER BY SourceIndex", "nodeID"); measurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in measurementGroupTable.Rows) { measurementGroupList[row.ConvertField <int>("ID")] = row.Field <string>("Name"); } return(measurementGroupList); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Saves <see cref="ApplicationRole"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="applicationRole">Information about <see cref="ApplicationRole"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, ApplicationRole applicationRole) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (applicationRole.ID == null || applicationRole.ID == Guid.Empty) { query = database.ParameterizedQueryString("INSERT INTO ApplicationRole (Name, Description, NodeID, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6})", "name", "description", "nodeID", "updatedBy", "updatedBy", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, applicationRole.Name, applicationRole.Description.ToNotNull(), database.CurrentNodeID(), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE ApplicationRole SET Name = {0}, Description = {1}, NodeID = {2}, UpdatedBy = {3}, UpdatedOn = {4} WHERE ID = {5}", "name", "description", "nodeID", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, applicationRole.Name, applicationRole.Description.ToNotNull(), applicationRole.NodeID, CommonFunctions.CurrentUser, database.UtcNow, database.Guid(applicationRole.ID)); } return("Application role information saved successfully"); } 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="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="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 Methods /// <summary> /// Loads <see cref="Alarm"/> IDs as an <see cref="IList{T}"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</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="Int32"/>.</returns> public static IList <int> LoadKeys(AdoDataConnection database, string sortMember = "", string sortDirection = "") { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); IList <int> alarmList = new List <int>(); string sortClause = string.Empty; DataTable adapterTable; string query; if (!string.IsNullOrEmpty(sortMember)) { sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection); } query = database.ParameterizedQueryString(string.Format("SELECT ID FROM Alarm WHERE NodeID = {{0}} {0}", sortClause), "nodeID"); adapterTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in adapterTable.Rows) { alarmList.Add(row.ConvertField <int>("ID")); } return(alarmList); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Retrieves a collection of devices where original source is not null for mirroring output stream. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="isOptional">Boolean flag indicating if selection is optional on the UI.</param> /// <returns><see cref="Dictionary{T1,T2}"/> type collection of Devices.</returns> public static Dictionary<string, string> GetDevicesForMirroringOutputStream(AdoDataConnection database, bool isOptional = true) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); Dictionary<string, string> deviceList = new Dictionary<string, string>(); if (isOptional) deviceList.Add("", "Select Mirroring Source"); DataTable deviceTable; string query; query = database.ParameterizedQueryString("SELECT DISTINCT OriginalSource FROM Device WHERE NodeID = {0} AND OriginalSource IS NOT NULL ORDER BY OriginalSource", "nodeID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in deviceTable.Rows) deviceList[row.ConvertField<string>("OriginalSource")] = row.Field<string>("OriginalSource"); return deviceList; } 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.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> /// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="OutputStream"/> 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 <see cref="OutputStream"/>s defined in the database.</returns> public static Dictionary<int, string> GetLookupList(AdoDataConnection database, bool isOptional = true) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); Dictionary<int, string> osList = new Dictionary<int, string>(); DataTable results = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Name FROM OutputStream WHERE NodeID = {0} ORDER BY Name", "nodeID"), DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in results.Rows) osList[row.ConvertField<int>("ID")] = row.Field<string>("Name"); return osList; } 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(); } }
/// <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="Device"/> information. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="protocolType">Type of protocol to filter data.</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 devices defined in the database.</returns> public static Dictionary<int, string> GetLookupList(AdoDataConnection database, string protocolType, bool isOptional) { 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, "All Device"); query = database.ParameterizedQueryString("SELECT ID, Acronym, IsConcentrator FROM DeviceDetail WHERE NodeID = {0} AND ProtocolType = {1} ORDER BY LoadOrder", "nodeID", "protocolType"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), protocolType); foreach (DataRow row in deviceTable.Rows) { if (row.ConvertField<bool>("IsConcentrator")) { ObservableCollection<Device> devices = GetDevices(database, " WHERE ParentID = " + row.ConvertField<int>("ID")); if (devices != null) { foreach (Device device in devices) deviceList[device.ID] = device.Acronym; } } else { deviceList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym"); } } return deviceList; } finally { if (createdConnection && database != null) database.Dispose(); } }
// Static Methods /// <summary> /// Loads <see cref="Alarm"/> IDs as an <see cref="IList{T}"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</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="Int32"/>.</returns> public static IList<int> LoadKeys(AdoDataConnection database, string sortMember = "", string sortDirection = "") { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); IList<int> alarmList = new List<int>(); string sortClause = string.Empty; DataTable adapterTable; string query; if (!string.IsNullOrEmpty(sortMember)) sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection); query = database.ParameterizedQueryString(string.Format("SELECT ID FROM Alarm WHERE NodeID = {{0}} {0}", sortClause), "nodeID"); adapterTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in adapterTable.Rows) { alarmList.Add(row.ConvertField<int>("ID")); } return alarmList; } finally { if (createdConnection && database != null) 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> /// Saves <see cref="OutputStreamMeasurement"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamMeasurement">Information about <see cref="OutputStreamMeasurement"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, OutputStreamMeasurement outputStreamMeasurement) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (outputStreamMeasurement.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO OutputStreamMeasurement (NodeID, AdapterID, HistorianID, PointID, SignalReference, " + " UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8})", "nodeID", "adapterID", "historianID", "pointID", "signalReference", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, outputStreamMeasurement.NodeID == Guid.Empty ? database.CurrentNodeID() : database.Guid(outputStreamMeasurement.NodeID), outputStreamMeasurement.AdapterID, outputStreamMeasurement.HistorianID.ToNotNull(), outputStreamMeasurement.PointID, outputStreamMeasurement.SignalReference, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE OutputStreamMeasurement SET NodeID = {0}, AdapterID = {1}, HistorianID = {2}, PointID = {3}, " + "SignalReference = {4}, UpdatedBy = {5}, UpdatedOn = {6} WHERE ID = {7}", "nodeID", "adapterID", "historianID", "pointID", "signalReference", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(outputStreamMeasurement.NodeID), outputStreamMeasurement.AdapterID, outputStreamMeasurement.HistorianID.ToNotNull(), outputStreamMeasurement.PointID, outputStreamMeasurement.SignalReference, CommonFunctions.CurrentUser, database.UtcNow, outputStreamMeasurement.ID); } return "OutputStreamMeasurement information saved successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
// Static Methods /// <summary> /// LoadKeys <see cref="Phasor"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="parentID">ID of the parent device to filter data.</param> /// <param name="searchText">The text to search by.</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="Phasor"/>.</returns> public static IList<int> LoadKeys(AdoDataConnection database, int parentID = 0, string searchText = "", string sortMember = "", string sortDirection = "") { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); IList<int> deviceList = new List<int>(); DataTable deviceTable; string query; string searchParam = null; string searchQuery = null; string sortClause = string.Empty; if (!string.IsNullOrEmpty(searchText)) { searchParam = string.Format("%{0}%", searchText); if (database.IsJetEngine) { // Access queries do not support UPPER but are case-insensitive anyway searchQuery = string.Format("ID LIKE '{0}' OR UniqueID LIKE '{0}' OR Acronym LIKE '{0}' " + "OR Name LIKE '{0}' OR OriginalSource LIKE '{0}' OR Longitude LIKE '{0}' OR Latitude LIKE '{0}' " + "OR ConnectionString LIKE '{0}' OR TimeZone LIKE '{0}' OR FramesPerSecond LIKE '{0}' " + "OR ContactList LIKE '{0}' OR CompanyName LIKE '{0}' OR CompanyAcronym LIKE '{0}' " + "OR CompanyMapAcronym LIKE '{0}' OR HistorianAcronym LIKE '{0}' OR VendorAcronym LIKE '{0}' " + "OR VendorDeviceName LIKE '{0}' OR ProtocolName LIKE '{0}' OR InterconnectionName LIKE '{0}'", searchParam.Replace("'", "''")); } else { searchQuery = database.ParameterizedQueryString("ID LIKE {0} OR UPPER(UniqueID) LIKE UPPER({0}) OR UPPER(Acronym) LIKE UPPER({0}) " + "OR UPPER(Name) LIKE UPPER({0}) OR UPPER(OriginalSource) LIKE UPPER({0}) OR Longitude LIKE {0} OR Latitude LIKE {0} " + "OR UPPER(ConnectionString) LIKE UPPER({0}) OR UPPER(TimeZone) LIKE UPPER({0}) OR FramesPerSecond LIKE {0} " + "OR UPPER(ContactList) LIKE UPPER({0}) OR UPPER(CompanyName) LIKE UPPER({0}) OR UPPER(CompanyAcronym) LIKE UPPER({0}) " + "OR UPPER(CompanyMapAcronym) LIKE UPPER({0}) OR UPPER(HistorianAcronym) LIKE UPPER({0}) OR UPPER(VendorAcronym) LIKE UPPER({0}) " + "OR UPPER(VendorDeviceName) LIKE UPPER({0}) OR UPPER(ProtocolName) LIKE UPPER({0}) OR UPPER(InterconnectionName) LIKE UPPER({0})", "searchParam"); } } if (!string.IsNullOrEmpty(sortMember)) sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection); if (parentID > 0) { if (string.IsNullOrEmpty(searchText)) { query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ParentID = {{1}} {0}", sortClause), "nodeID", "parentID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), parentID); } else if (!database.IsJetEngine) { query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ParentID = {{1}} AND ({0}) {1}", searchText, sortClause), "nodeID", "parentID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), parentID, searchParam); } else { query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ParentID = {{1}} AND ({0}) {1}", searchText, sortClause), "nodeID", "parentID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), parentID); } } else { if (string.IsNullOrEmpty(searchText)) { query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} {0}", sortClause), "nodeID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); } else if (!database.IsJetEngine) { query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ({0}) {1}", searchQuery, sortClause), "nodeID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), searchParam); } else { query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ({0}) {1}", searchQuery, sortClause), "nodeID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); } } foreach (DataRow row in deviceTable.Rows) { deviceList.Add(row.ConvertField<int>("ID")); } return deviceList; } 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(); } } }
// 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(); } }
/// <summary> /// Gets statistic measurements from the database for current node. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <returns>Collection of <see cref="StatisticMeasurement"/>.</returns> public static ObservableCollection <StatisticMeasurement> GetStatisticMeasurements(AdoDataConnection database) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); // Get Statistic Definitions ObservableCollection <Statistic> statisticDefinitions = Statistic.Load(database); // Get statistics measurements. DataTable statisticMeasurements = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT SignalID, ID, DeviceID, PointID, PointTag, SignalReference " + "FROM StatisticMeasurement WHERE NodeID = {0}", "nodeID"), DefaultTimeout, database.CurrentNodeID()); // Assign min and max point IDs as we will need them to request data from web service. MinPointID = int.MaxValue; MaxPointID = int.MinValue; foreach (DataRow row in statisticMeasurements.Rows) { int pointID = Convert.ToInt32(row.Field <object>("PointID")); MinPointID = Math.Min(MinPointID, pointID); MaxPointID = Math.Max(MaxPointID, pointID); } // Takes datarow from statisticMeasurements data table, and associates each row to their statistic source and returns KeyValuePair. Func <DataRow, KeyValuePair <DataRow, string> > mapFunction = measurement => { string signalReference = measurement.Field <string>("SignalReference"); string measurementSource; if (StatisticsEngine.RegexMatch(signalReference, "SYSTEM")) { measurementSource = "System"; } else if (StatisticsEngine.RegexMatch(signalReference, "PMU")) { measurementSource = "Device"; } else if (StatisticsEngine.RegexMatch(signalReference, "OS")) { measurementSource = "OutputStream"; } else if (StatisticsEngine.RegexMatch(signalReference, "IS")) { measurementSource = "InputStream"; } else if (StatisticsEngine.RegexMatch(signalReference, "SUB")) { measurementSource = "Subscriber"; } else if (StatisticsEngine.RegexMatch(signalReference, "PUB")) { measurementSource = "Publisher"; } else { measurementSource = "???"; } return(new KeyValuePair <DataRow, string>(measurement, measurementSource)); }; // Takes KeyValuePair and generates StatisticMeasurement record by maping statistic measurements with statistic definitions. Func <KeyValuePair <DataRow, string>, StatisticMeasurement> selectFunction = keyvaluepair => { DataRow measurement = keyvaluepair.Key; string measurementSource = keyvaluepair.Value; Debug.WriteLine(measurementSource); string signalReference = measurement.Field <string>("SignalReference"); int measurementIndex = Convert.ToInt32(signalReference.Substring(signalReference.LastIndexOf("-ST") + 3)); Statistic statisticDefinition = new Statistic(); foreach (Statistic statistic in statisticDefinitions) { if (statistic.Source == measurementSource && statistic.SignalIndex == measurementIndex) { statisticDefinition = statistic; break; } } return(new StatisticMeasurement() { SignalID = database.Guid(measurement, "SignalID"), ID = measurement.Field <string>("ID"), DeviceID = Convert.ToInt32(measurement.Field <object>("DeviceID") ?? -1), PointID = Convert.ToInt32(measurement.Field <object>("PointID")), PointTag = measurement.Field <string>("PointTag"), SignalReference = signalReference, Source = measurementSource, StatisticName = statisticDefinition.Name, StatisticDescription = statisticDefinition.Description, DataType = statisticDefinition.DataType, DisplayFormat = statisticDefinition.DisplayFormat, ConnectedState = statisticDefinition.IsConnectedState, LoadOrder = statisticDefinition.LoadOrder, TimeTag = "n/a", Quality = "n/a", Value = "--" }); }; return(new ObservableCollection <StatisticMeasurement>(statisticMeasurements.Rows.Cast <DataRow>().Select(mapFunction).OrderBy(pair => pair.Value).Select(selectFunction).OrderBy(s => s.LoadOrder))); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Saves <see cref="OutputStreamDevicePhasor"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamDevicePhasor">Information about <see cref="OutputStreamDevicePhasor"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, OutputStreamDevicePhasor outputStreamDevicePhasor) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (outputStreamDevicePhasor.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO OutputStreamDevicePhasor (NodeID, OutputStreamDeviceID, Label, Type, Phase, ScalingValue, " + "LoadOrder, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10})", "nodeID", "outputStreamDeviceID", "label", "type", "phase", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDevicePhasor.OutputStreamDeviceID, outputStreamDevicePhasor.Label, outputStreamDevicePhasor.Type, outputStreamDevicePhasor.Phase, outputStreamDevicePhasor.ScalingValue, outputStreamDevicePhasor.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); // PhasorType, PhaseType @phasorName, @phaseType, OutputStreamDevicePhasor.PhasorType, OutputStreamDevicePhasor.PhaseType } else { query = database.ParameterizedQueryString("UPDATE OutputStreamDevicePhasor SET NodeID = {0}, OutputStreamDeviceID = {1}, Label = {2}, Type = {3}, " + "Phase = {4}, ScalingValue = {5}, LoadOrder = {6}, UpdatedBy = {7}, UpdatedOn = {8} WHERE ID = {9}", "nodeID", "outputStreamDeviceID", "label", "type", "phase", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, outputStreamDevicePhasor.NodeID, outputStreamDevicePhasor.OutputStreamDeviceID, outputStreamDevicePhasor.Label, outputStreamDevicePhasor.Type, outputStreamDevicePhasor.Phase, outputStreamDevicePhasor.ScalingValue, outputStreamDevicePhasor.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, outputStreamDevicePhasor.ID); //PhasorType = @typeName, PhaseType = @PhaseType" OutputStreamDevicePhasor.PhasorType, OutputStreamDevicePhasor.PhaseType, } return "OutputStreamDevicePhasor information saved successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Loads <see cref="CalculatedMeasurement"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="keys"> Keys of the measurement to be loaded from the database</param> /// <returns>Collection of <see cref="CalculatedMeasurement"/>.</returns> public static ObservableCollection <CalculatedMeasurement> Load(AdoDataConnection database, IList <int> keys) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string query; string commaSeparatedKeys; CalculatedMeasurement[] calculatedMeasurementList = null; DataTable calculatedMeasurementTable; int id; if ((object)keys != null && keys.Count > 0) { commaSeparatedKeys = keys.Select(key => key.ToString()).Aggregate((str1, str2) => str1 + "," + str2); query = string.Format("SELECT NodeID, ID, Acronym, Name, AssemblyName, " + "TypeName, ConnectionString, ConfigSection, InputMeasurements, OutputMeasurements, MinimumMeasurementsToUse, FramesPerSecond, LagTime, " + "LeadTime, UseLocalClockAsRealTime, AllowSortsByArrival, LoadOrder, Enabled, IgnoreBadTimeStamps, TimeResolution, AllowPreemptivePublishing, " + "DownSamplingMethod, NodeName, PerformTimeReasonabilityCheck From CalculatedMeasurementDetail WHERE ID IN ({0}) AND NodeID = '{1}'", commaSeparatedKeys, database.CurrentNodeID()); calculatedMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query); calculatedMeasurementList = new CalculatedMeasurement[calculatedMeasurementTable.Rows.Count]; foreach (DataRow row in calculatedMeasurementTable.Rows) { id = row.ConvertField <int>("ID"); calculatedMeasurementList[keys.IndexOf(id)] = new CalculatedMeasurement() { NodeID = database.Guid(row, "NodeID"), ID = id, Acronym = row.Field <string>("Acronym"), Name = row.Field <string>("Name"), AssemblyName = row.Field <string>("AssemblyName"), TypeName = row.Field <string>("TypeName"), ConnectionString = row.Field <string>("ConnectionString"), ConfigSection = row.Field <string>("ConfigSection"), InputMeasurements = row.Field <string>("InputMeasurements"), OutputMeasurements = row.Field <string>("OutputMeasurements"), MinimumMeasurementsToUse = row.ConvertField <int>("MinimumMeasurementsToUse"), FramesPerSecond = Convert.ToInt32(row.Field <object>("FramesPerSecond") ?? 30), LagTime = row.ConvertField <double>("LagTime"), LeadTime = row.ConvertField <double>("LeadTime"), UseLocalClockAsRealTime = Convert.ToBoolean(row.Field <object>("UseLocalClockAsRealTime")), AllowSortsByArrival = Convert.ToBoolean(row.Field <object>("AllowSortsByArrival")), LoadOrder = row.ConvertField <int>("LoadOrder"), Enabled = Convert.ToBoolean(row.Field <object>("Enabled")), IgnoreBadTimeStamps = Convert.ToBoolean(row.Field <object>("IgnoreBadTimeStamps")), TimeResolution = Convert.ToInt32(row.Field <object>("TimeResolution")), AllowPreemptivePublishing = Convert.ToBoolean(row.Field <object>("AllowPreemptivePublishing")), DownsamplingMethod = row.Field <string>("DownSamplingMethod"), m_nodeName = row.Field <string>("NodeName"), PerformTimestampReasonabilityCheck = Convert.ToBoolean(row.Field <object>("PerformTimeReasonabilityCheck")) }; } } return(new ObservableCollection <CalculatedMeasurement>(calculatedMeasurementList ?? new CalculatedMeasurement[0])); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Loads <see cref="Adapter"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="adapterType"><see cref="AdapterType"/> collection to be returned.</param> /// <param name="keys">Keys of the adapters to be loaded from the database.</param> /// <returns>Collection of <see cref="Adapter"/>.</returns> public static ObservableCollection <Adapter> Load(AdoDataConnection database, AdapterType adapterType, IList <int> keys) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string viewName; if (adapterType == AdapterType.Action) { viewName = "CustomActionAdapterDetail"; } else if (adapterType == AdapterType.Filter) { viewName = "CustomFilterAdapterDetail"; } else if (adapterType == AdapterType.Input) { viewName = "CustomInputAdapterDetail"; } else { viewName = "CustomOutputAdapterDetail"; } string query; string commaSeparatedKeys; Adapter[] adapterList = null; DataTable adapterTable; int id; if ((object)keys != null && keys.Count > 0) { commaSeparatedKeys = keys.Select(key => "" + key.ToString() + "").Aggregate((str1, str2) => str1 + "," + str2); query = database.ParameterizedQueryString(string.Format("SELECT NodeID, ID, AdapterName, AssemblyName, TypeName, ConnectionString, " + "LoadOrder, Enabled, NodeName FROM {0} WHERE NodeID = {{0}} AND ID IN ({1})", viewName, commaSeparatedKeys), "nodeID"); adapterTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); adapterList = new Adapter[adapterTable.Rows.Count]; foreach (DataRow row in adapterTable.Rows) { id = row.ConvertField <int>("ID"); adapterList[keys.IndexOf(id)] = new Adapter() { NodeID = database.Guid(row, "NodeID"), ID = id, AdapterName = row.Field <string>("AdapterName"), AssemblyName = row.Field <string>("AssemblyName"), TypeName = row.Field <string>("TypeName"), ConnectionString = row.Field <string>("ConnectionString"), LoadOrder = row.ConvertField <int>("LoadOrder"), Enabled = Convert.ToBoolean(row.Field <object>("Enabled")), NodeName = row.Field <string>("NodeName"), Type = adapterType }; } } return(new ObservableCollection <Adapter>(adapterList ?? new Adapter[0])); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Retrieves a <see cref="Node"/> defined in the database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <returns><see cref="Node"/> information.</returns> public static Node GetCurrentNode(AdoDataConnection database) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); DataTable nodeTable; string query; query = database.ParameterizedQueryString("Select ID, Name, CompanyID, Longitude, Latitude, Description, " + "ImagePath, Settings, MenuData, MenuType, Master, LoadOrder, Enabled, CompanyName " + "From NodeDetail WHERE ID = {0} ORDER BY LoadOrder", "id"); nodeTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); if (nodeTable.Rows.Count == 0) { return(null); } DataRow row = nodeTable.Rows[0]; Node node = new Node { ID = database.Guid(row, "ID"), Name = row.Field <string>("Name"), CompanyID = row.ConvertNullableField <int>("CompanyID"), Longitude = row.ConvertNullableField <decimal>("Longitude"), Latitude = row.ConvertNullableField <decimal>("Latitude"), Description = row.Field <string>("Description"), ImagePath = row.Field <string>("ImagePath"), Settings = row.Field <string>("Settings"), MenuType = row.Field <string>("MenuType"), MenuData = row.Field <string>("MenuData"), Master = Convert.ToBoolean(row.Field <object>("Master")), LoadOrder = row.ConvertField <int>("LoadOrder"), Enabled = Convert.ToBoolean(row.Field <object>("Enabled")), m_companyName = row.Field <string>("CompanyName") }; return(node); } 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(); } } }
// Loads the data set used to determine effective permissions private void LoadPermissionsDataSet() { Dictionary <string, string> dataTableDefinitions = new Dictionary <string, string>() { { "ActiveMeasurement", "ActiveMeasurements" }, { "Subscriber", "Subscribers" }, { "SubscriberMeasurement", "SubscriberMeasurements" }, { "SubscriberMeasurementGroup", "SubscriberMeasurementGroups" }, { "MeasurementGroup", "MeasurementGroups" }, { "MeasurementGroupMeasurement", "MeasurementGroupMeasurements" } }; DataTable dataTable; string queryFormat; string parameterizedQuery; m_subscriberPermissionsDataSet = new DataSet(); using (AdoDataConnection database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory)) { foreach (KeyValuePair <string, string> definition in dataTableDefinitions) { queryFormat = string.Format("SELECT * FROM {0} WHERE NodeID = {{0}}", definition.Key); parameterizedQuery = database.ParameterizedQueryString(queryFormat, "nodeID"); dataTable = database.Connection.RetrieveData(database.AdapterType, parameterizedQuery, DataExtensions.DefaultTimeoutDuration, database.CurrentNodeID()); dataTable.TableName = definition.Value; dataTable.DataSet.Tables.Remove(dataTable); m_subscriberPermissionsDataSet.Tables.Add(dataTable); } } UpdateEffectivePermissions(); }
// 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, AccessID, 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") && device.ConvertField <int>("AccessID") != GroupAccessID 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", LongTimeTag = "N/A", 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", LongTimeTag = "N/A", 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", LongTimeTag = "N/A", 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(); } } }
// Static Methods /// <summary> /// Loads <see cref="UserAccount"/> information as an OberservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <returns>Collection of <see cref="UserAccount"/></returns> public static ObservableCollection<UserAccount> Load(AdoDataConnection database) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); ObservableCollection<UserAccount> userAccountList = new ObservableCollection<UserAccount>(); DataTable userAccountTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * From UserAccount WHERE DefaultNodeID = '" + database.CurrentNodeID() + "' ORDER BY Name"); foreach (DataRow row in userAccountTable.Rows) { userAccountList.Add(new UserAccount() { ID = database.Guid(row, "ID"), Name = UserInfo.SIDToAccountName(row.Field<string>("Name")), Password = row.Field<object>("Password") == null ? string.Empty : row.Field<string>("Password"), FirstName = row.Field<object>("FirstName") == null ? string.Empty : row.Field<string>("FirstName"), LastName = row.Field<object>("LastName") == null ? string.Empty : row.Field<string>("LastName"), DefaultNodeID = database.Guid(row, "DefaultNodeID"), Phone = row.Field<object>("Phone") == null ? string.Empty : row.Field<string>("Phone"), Email = row.Field<object>("Email") == null ? string.Empty : row.Field<string>("Email"), LockedOut = Convert.ToBoolean(row.Field<object>("LockedOut")), UseADAuthentication = Convert.ToBoolean(row.Field<object>("UseADAuthentication")), ChangePasswordOn = row.Field<object>("ChangePasswordOn") == null ? DateTime.MinValue : Convert.ToDateTime(row.Field<object>("ChangePasswordOn")), CreatedOn = Convert.ToDateTime(row["CreatedOn"]), CreatedBy = row.Field<string>("CreatedBy"), UpdatedOn = Convert.ToDateTime(row.Field<object>("UpdatedOn")), UpdatedBy = row.Field<string>("UpdatedBy") }); } userAccountList.Insert(0, new UserAccount { ID = Guid.Empty, ChangePasswordOn = DateTime.Now.AddDays(90) }); return userAccountList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Loads <see cref="Node"/> 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="Subscriber"/>.</returns> public static ObservableCollection<Subscriber> Load(AdoDataConnection database) { ObservableCollection<Subscriber> subscriberList; DataTable subscriberTable; bool createdConnection = false; string query; SslPolicyErrors validPolicyErrors; X509ChainStatusFlags validChainFlags; try { createdConnection = CreateConnection(ref database); subscriberList = new ObservableCollection<Subscriber>(); query = database.ParameterizedQueryString("SELECT ID, NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile," + " ValidPolicyErrors, ValidChainFlags, AccessControlFilter, Enabled FROM Subscriber WHERE NodeID = {0} ORDER BY Name", "nodeID"); subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in subscriberTable.Rows) { subscriberList.Add(new Subscriber() { ID = database.Guid(row, "ID"), NodeID = database.Guid(row, "NodeID"), Acronym = row.Field<string>("Acronym"), Name = row.Field<string>("Name"), SharedSecret = row.Field<string>("SharedSecret"), AuthKey = row.Field<string>("AuthKey"), ValidIPAddresses = row.Field<string>("ValidIPAddresses"), RemoteCertificateFile = row.Field<string>("RemoteCertificateFile"), ValidPolicyErrors = Enum.TryParse(row.Field<string>("ValidPolicyErrors"), out validPolicyErrors) ? validPolicyErrors : (SslPolicyErrors?)null, ValidChainFlags = Enum.TryParse(row.Field<string>("ValidChainFlags"), out validChainFlags) ? validChainFlags : (X509ChainStatusFlags?)null, AccessControlFilter = row.Field<string>("AccessControlFilter"), Enabled = Convert.ToBoolean(row.Field<object>("Enabled")), AllowedMeasurementGroups = GetAllowedMeasurementGroups(database, database.Guid(row, "ID")), DeniedMeasurementGroups = GetDeniedMeasurementGroups(database, database.Guid(row, "ID")), AvailableMeasurementGroups = GetAvailableMeasurementGroups(database, database.Guid(row, "ID")), StatusColor = "gray", Version = "" }); } return subscriberList; } 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> /// 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 Methods /// <summary> /// Loads <see cref="ApplicationRole"/> 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="ApplicationRole"/>.</returns> public static ObservableCollection <ApplicationRole> Load(AdoDataConnection database) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); ObservableCollection <ApplicationRole> applicationRoleList = new ObservableCollection <ApplicationRole>(); string query = database.ParameterizedQueryString("SELECT * FROM ApplicationRole WHERE NodeID = {0} ORDER BY Name", "nodeID"); DataTable applicationRoleTable = database.Connection.RetrieveData(database.AdapterType, query, database.CurrentNodeID()); foreach (DataRow row in applicationRoleTable.Rows) { applicationRoleList.Add(new ApplicationRole() { ID = database.Guid(row, "ID"), //Guid.Parse(row.Field<object>("ID").ToString()), Name = row.Field <string>("Name"), Description = row.Field <string>("Description"), NodeID = Guid.Parse(row.Field <object>("NodeID").ToString()), CreatedOn = row.Field <DateTime>("CreatedOn"), CreatedBy = row.Field <string>("CreatedBy"), UpdatedOn = row.Field <DateTime>("UpdatedOn"), UpdatedBy = row.Field <string>("UpdatedBy"), CurrentUsers = GetCurrentUsers(database, Guid.Parse(row.Field <object>("ID").ToString())), PossibleUsers = GetPossibleUsers(database, Guid.Parse(row.Field <object>("ID").ToString())), CurrentGroups = GetCurrentGroups(database, Guid.Parse(row.Field <object>("ID").ToString())), PossibleGroups = GetPossibleGroups(database, Guid.Parse(row.Field <object>("ID").ToString())) }); } return(applicationRoleList); } finally { if (createdConnection && database != null) { database.Dispose(); } } }