/// <summary>
/// Deletes specified <see cref="Node"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="nodeID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, Guid nodeID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
// Setup current user context for any delete triggers
CommonFunctions.SetCurrentUserContext(database);
// Before we delete node, make sure to delete all the devices associated with this node.
// All other node related items get deleted by cascade delete.
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Device WHERE NodeID = {0}", "nodeID"), DefaultTimeout, database.Guid(nodeID));
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Node WHERE ID = {0}", "nodeID"), DefaultTimeout, database.Guid(nodeID));
return("Node deleted successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Deletes <see cref="SecurityGroup"/> from <see cref="ApplicationRole"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="roleID">ID of <see cref="ApplicationRole"/> from which <see cref="SecurityGroup"/>s are being deleted.</param>
/// <param name="groupsToBeDeleted">List of <see cref="SecurityGroup"/> IDs to be deleted.</param>
/// <returns>string, for display use, indicating success.</returns>
public static string RemoveGroups(AdoDataConnection database, Guid roleID, List <Guid> groupsToBeDeleted)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in groupsToBeDeleted)
{
string groupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), DefaultTimeout, database.Guid(id)).ToNonNullString();
string roleName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM ApplicationRole WHERE ID = {0}", "applicationRoleID"), DefaultTimeout, database.Guid(roleID)).ToNonNullString();
string query = database.ParameterizedQueryString("DELETE FROM ApplicationRoleSecurityGroup WHERE ApplicationRoleID = {0} AND SecurityGroupID = {1}", "roleID", "groupID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(roleID), database.Guid(id));
CommonFunctions.LogEvent(string.Format("Group \"{0}\" removed from role \"{1}\" by user \"{2}\".", UserInfo.SIDToAccountName(groupName), roleName, CommonFunctions.CurrentUser), 11);
}
return("Security groups deleted from role successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Deletes <see cref="UserAccount"/> from <see cref="ApplicationRole"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="roleID">ID of <see cref="ApplicationRole"/> from which <see cref="UserAccount"/>s are being deleted.</param>
/// <param name="usersToBeDeleted">List of <see cref="UserAccount"/> IDs to be deleted.</param>
/// <returns>string, for display use, indicating success.</returns>
public static string RemoveUsers(AdoDataConnection database, Guid roleID, List <Guid> usersToBeDeleted)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in usersToBeDeleted)
{
string userName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM UserAccount WHERE ID = {0}", "userAccountID"), DefaultTimeout, database.Guid(id)).ToNonNullString();
string roleName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM ApplicationRole WHERE ID = {0}", "applicationRoleID"), DefaultTimeout, database.Guid(roleID)).ToNonNullString();
string query = database.ParameterizedQueryString("DELETE FROM ApplicationRoleUserAccount WHERE ApplicationRoleID = {0} AND UserAccountID = {1}", "roleID", "userID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(roleID), database.Guid(id));
CommonFunctions.LogEvent($"User \"{UserInfo.SIDToAccountName(userName)}\" removed from role \"{roleName}\" by user \"{CommonFunctions.CurrentUser}\".", 5);
}
return("User accounts deleted from role successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Deletes <see cref="UserAccount"/> from <see cref="SecurityGroup"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="groupID">ID of <see cref="SecurityGroup"/> from which <see cref="UserAccount"/>s are being deleted.</param>
/// <param name="usersToBeDeleted">List of <see cref="UserAccount"/> IDs to be deleted.</param>
/// <returns>string, for display use, indicating success.</returns>
public static string RemoveUsers(AdoDataConnection database, Guid groupID, List <Guid> usersToBeDeleted)
{
bool createdConnection = false;
string query;
string userName;
string securityGroupName;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in usersToBeDeleted)
{
userName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM UserAccount WHERE ID = {0}", "userID"), database.Guid(id)).ToNonNullString();
securityGroupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), database.Guid(groupID)).ToNonNullString();
query = database.ParameterizedQueryString("DELETE FROM SecurityGroupUserAccount WHERE SecurityGroupID = {0} AND UserAccountID = {1}", "groupID", "userID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(groupID), database.Guid(id));
CommonFunctions.LogEvent(string.Format("User \"{0}\" successfully removed from security group \"{1}\" by user \"{2}\".", UserInfo.SIDToAccountName(userName), UserInfo.SIDToAccountName(securityGroupName), CommonFunctions.CurrentUser), 9);
}
return("User accounts deleted from group successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Adds <see cref="SecurityGroup"/> to <see cref="ApplicationRole"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="roleID">ID of <see cref="ApplicationRole"/> to which <see cref="SecurityGroup"/>s are being added.</param>
/// <param name="groupsToBeAdded">List of <see cref="SecurityGroup"/> IDs to be added.</param>
/// <returns>string, for display use, indicating success.</returns>
public static string AddGroups(AdoDataConnection database, Guid roleID, List <Guid> groupsToBeAdded)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in groupsToBeAdded)
{
string groupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), DefaultTimeout, database.Guid(id)).ToNonNullString();
string roleName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM ApplicationRole WHERE ID = {0}", "applicationRoleID"), DefaultTimeout, database.Guid(roleID)).ToNonNullString();
string query = database.ParameterizedQueryString("INSERT INTO ApplicationRoleSecurityGroup (ApplicationRoleID, SecurityGroupID) Values ({0}, {1})", "roleID", "groupID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(roleID), database.Guid(id));
CommonFunctions.LogEvent($"Group \"{UserInfo.SIDToAccountName(groupName)}\" added to role \"{roleName}\" by user \"{CommonFunctions.CurrentUser}\".", 10);
}
return("Security groups added to role successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Deletes specified <see cref="SecurityGroup"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="securityGroupID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, Guid securityGroupID)
{
bool createdConnection = false;
string securityGroupName;
try
{
createdConnection = CreateConnection(ref database);
// Get the name of the security group to be deleted
securityGroupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "userAccountID"), DefaultTimeout, database.Guid(securityGroupID)).ToNonNullString();
// Setup current user context for any delete triggers
CommonFunctions.SetCurrentUserContext(database);
// Delete the security group from the database
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), DefaultTimeout, database.Guid(securityGroupID));
// Write to the event log
CommonFunctions.LogEvent(string.Format("Security group \"{0}\" deleted successfully by user \"{1}\".", UserInfo.SIDToAccountName(securityGroupName), CommonFunctions.CurrentUser), 13);
return("Security group deleted successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Deletes specified <see cref="OutputStreamDevice"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamID">ID of the output stream to filter data.</param>
/// <param name="outputStreamDeviceAcronym">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, int outputStreamID, string outputStreamDeviceAcronym)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
// Setup current user context for any delete triggers
CommonFunctions.SetCurrentUserContext(database);
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamDevice WHERE AdapterID = {0} AND Acronym = {1}", "outputStreamID", "outputStreamDeviceAcronym"), DefaultTimeout, outputStreamID, outputStreamDeviceAcronym);
try
{
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE From OutputStreamMeasurement WHERE AdapterID = {0} AND SignalReference LIKE {1}", "outputStreamID", "outputStreamDeviceAcronym"),
DefaultTimeout, outputStreamID, "%" + outputStreamDeviceAcronym + "%");
}
catch (Exception ex)
{
throw new InvalidOperationException("Failed to delete measurements associated with output stream device.", ex);
}
return("OutputStreamDevice deleted 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>
/// Adds <see cref="UserAccount"/> to <see cref="ApplicationRole"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="roleID">ID of <see cref="ApplicationRole"/> to which <see cref="UserAccount"/>s are being added.</param>
/// <param name="usersToBeAdded">List of <see cref="UserAccount"/> IDs to be added.</param>
/// <returns>string, for display use, indicating success.</returns>
public static string AddUsers(AdoDataConnection database, Guid roleID, List <Guid> usersToBeAdded)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in usersToBeAdded)
{
string userName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM UserAccount WHERE ID = {0}", "userAccountID"), DefaultTimeout, database.Guid(id)).ToNonNullString();
string roleName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM ApplicationRole WHERE ID = {0}", "applicationRoleID"), DefaultTimeout, database.Guid(roleID)).ToNonNullString();
string query = database.ParameterizedQueryString("INSERT INTO ApplicationRoleUserAccount (ApplicationRoleID, UserAccountID) VALUES ({0}, {1})", "roleID", "userID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(roleID), database.Guid(id));
CommonFunctions.LogEvent(string.Format("User \"{0}\" added to role \"{1}\" by user \"{2}\".", UserInfo.SIDToAccountName(userName), roleName, CommonFunctions.CurrentUser), 4);
}
return("User accounts added to role successfully");
}
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="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();
}
}
}
/// <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="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>
/// Removed measurement groups from <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 measurement groups to be removed.</param>
/// <param name="measurementGroupsToBeRemoved">List of <see cref="MeasurementGroup"/> IDs to be removed.</param>
/// <returns>string, indicating success for UI display.</returns>
public static string RemoveMeasurementGroups(AdoDataConnection database, Guid subscriberID, List <int> measurementGroupsToBeRemoved)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
foreach (int id in measurementGroupsToBeRemoved)
{
query = database.ParameterizedQueryString("DELETE FROM SubscriberMeasurementGroup WHERE SubscriberID = {0} AND MeasurementGroupID = {1}", "subscriberID", "measurementGroupID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriberID), id);
}
return("Measurement groups removed from allowed measurement groups list for subscriber successfully");
}
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>
/// Retrieves a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Measurement"/> assigned 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 filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing SignalID and PointTag of <see cref="Measurement"/>s assigned to <see cref="MeasurementGroup"/>.</returns>
public static Dictionary <Guid, string> GetCurrentMeasurements(AdoDataConnection database, int measurementGroupId)
{
Dictionary <Guid, string> currentMeasurements;
DataTable currentMeasurementTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
currentMeasurements = new Dictionary <Guid, string>();
query = database.ParameterizedQueryString("SELECT * FROM MeasurementGroupMeasDetail WHERE MeasurementGroupID = {0} ORDER BY PointID", "measurementGroupID");
currentMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, measurementGroupId);
foreach (DataRow row in currentMeasurementTable.Rows)
{
currentMeasurements[database.Guid(row, "SignalID")] = row.Field <string>("PointTag");
}
return(currentMeasurements);
}
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>
/// Retrieves collection of <see cref="UserAccount"/>s currently NOT assinged to security group.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="groupID">ID of <see cref="SecurityGroup"/> to filter users.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of <see cref="UserAccount"/>s currently NOT assigned to <see cref="SecurityGroup"/>.</returns>
public static Dictionary <Guid, string> GetPossibleUsers(AdoDataConnection database, Guid groupID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <Guid, string> possibleGroupUsers = new Dictionary <Guid, string>();
string query = database.ParameterizedQueryString("SELECT ID, Name FROM UserAccount WHERE ID NOT IN (SELECT UserAccountID FROM SecurityGroupUserAccount WHERE SecurityGroupID = {0}) ORDER BY Name", "groupID");
DataTable possibleUsersTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(groupID));
foreach (DataRow row in possibleUsersTable.Rows)
{
possibleGroupUsers[database.Guid(row, "ID")] = UserInfo.SIDToAccountName(row.Field <string>("Name"));
}
return(possibleGroupUsers);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Deletes specified <see cref="Subscriber"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="id">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, Guid id)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
// Setup current user context for any delete triggers
CommonFunctions.SetCurrentUserContext(database);
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Subscriber WHERE ID = {0}", "id"), DefaultTimeout, database.Guid(id));
try
{
CommonFunctions.SendCommandToService("ReloadConfig");
}
catch (Exception ex)
{
return("Subscriber deleted successfully. Failed to send ReloadConfig command to backend service." + Environment.NewLine + ex.Message);
}
return("Subscriber deleted successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> neither allowed nor denied for <see cref="Subscriber"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of ID and Name of <see cref="MeasurementGroup"/>.</returns>
public static Dictionary <int, string> GetAvailableMeasurementGroups(AdoDataConnection database, Guid subscriberID)
{
Dictionary <int, string> availableMeasurementGroups;
DataTable availableMeasurementGroupTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
availableMeasurementGroups = new Dictionary <int, string>();
query = database.ParameterizedQueryString("SELECT ID, Name FROM MeasurementGroup WHERE ID NOT IN (SELECT MeasurementGroupID FROM SubscriberMeasurementGroup WHERE SubscriberID = {0}) ORDER BY Name", "subscriberID");
availableMeasurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID));
foreach (DataRow row in availableMeasurementGroupTable.Rows)
{
availableMeasurementGroups[row.ConvertField <int>("ID")] = row.Field <string>("Name");
}
return(availableMeasurementGroups);
}
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 <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> denied for <see cref="Subscriber"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of ID and Name of <see cref="MeasurementGroup"/>.</returns>
public static Dictionary <int, string> GetDeniedMeasurementGroups(AdoDataConnection database, Guid subscriberID)
{
Dictionary <int, string> deniedMeasurementGroups;
DataTable deniedMeasurementGroupTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
deniedMeasurementGroups = new Dictionary <int, string>();
query = database.ParameterizedQueryString("SELECT MeasurementGroupID, MeasurementGroupName FROM SubscriberMeasGroupDetail WHERE SubscriberID = {0} AND Allowed = {1} ORDER BY MeasurementGroupName", "subscriberID", "allowed");
deniedMeasurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID), database.Bool(false));
foreach (DataRow row in deniedMeasurementGroupTable.Rows)
{
deniedMeasurementGroups[row.ConvertField <int>("MeasurementGroupID")] = row.Field <string>("MeasurementGroupName");
}
return(deniedMeasurementGroups);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="Measurement"/> allowed for <see cref="Subscriber"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of SignalID and PointTag of <see cref="Measurement"/>.</returns>
public static Dictionary <Guid, string> GetAllowedMeasurements(AdoDataConnection database, Guid subscriberID)
{
Dictionary <Guid, string> allowedMeasurements;
DataTable allowedMeasurementTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
allowedMeasurements = new Dictionary <Guid, string>();
query = database.ParameterizedQueryString("SELECT SignalID, PointTag FROM SubscriberMeasurementDetail WHERE SubscriberID = {0} AND Allowed = {1} ORDER BY PointTag", "subscriberID", "allowed");
allowedMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID), database.Bool(true));
foreach (DataRow row in allowedMeasurementTable.Rows)
{
allowedMeasurements[database.Guid(row, "SignalID")] = row.Field <string>("PointTag");
}
return(allowedMeasurements);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Method to remove <see cref="Measurement"/>s from <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 remove <see cref="Measurement"/> from.</param>
/// <param name="measurementsToBeRemoved">List of <see cref="Measurement"/> signal ids to be removed.</param>
/// <returns>string, indicating success for UI display.</returns>
public static string RemoveMeasurements(AdoDataConnection database, int measurementGroupID, List <Guid> measurementsToBeRemoved)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in measurementsToBeRemoved)
{
query = database.ParameterizedQueryString("DELETE FROM MeasurementGroupMeasurement WHERE MeasurementGroupID = {0} AND SignalID = {1}", "measurementGroupID", "signalID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, measurementGroupID, database.Guid(id));
}
return("Measurements deleted from group successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Deletes specified <see cref="CalculatedMeasurement"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="calculatedMeasurementID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, int calculatedMeasurementID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
// Setup current user context for any delete triggers
CommonFunctions.SetCurrentUserContext(database);
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM CalculatedMeasurement WHERE ID = {0}", "calculatedMeasurementID"), DefaultTimeout, calculatedMeasurementID);
CommonFunctions.SendCommandToService("ReloadConfig");
return("Calculated measurement deleted successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Retrieves collection of <see cref="SecurityGroup"/>s assigned to <see cref="ApplicationRole"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="roleID">ID of the <see cref="ApplicationRole"/> to search for.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of <see cref="SecurityGroup"/>.</returns>
public static Dictionary <Guid, string> GetCurrentGroups(AdoDataConnection database, Guid roleID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <Guid, string> currentGroups = new Dictionary <Guid, string>();
string query = database.ParameterizedQueryString("SELECT * FROM AppRoleSecurityGroupDetail WHERE ApplicationRoleID = {0} ORDER BY SecurityGroupName", "applicationRoleID");
DataTable currentGroupsTable = database.Connection.RetrieveData(database.AdapterType, query, database.Guid(roleID));
foreach (DataRow row in currentGroupsTable.Rows)
{
currentGroups[database.Guid(row, "SecurityGroupID")] = UserInfo.SIDToAccountName(row.Field <string>("SecurityGroupName"));
}
return(currentGroups);
}
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();
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Node"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of nodes defined in the database.</returns>
public static Dictionary <Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <Guid, string> nodeList = new Dictionary <Guid, string>();
if (isOptional)
{
nodeList.Add(Guid.Empty, "Select Node");
}
string query = database.ParameterizedQueryString("SELECT ID, Name FROM Node WHERE Enabled = {0} ORDER BY LoadOrder", "enabled");
DataTable nodeTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true));
foreach (DataRow row in nodeTable.Rows)
{
nodeList[database.Guid(row, "ID")] = row.Field <string>("Name");
}
return(nodeList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="OutputStreamDevice"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamID">ID of the output stream 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 OutputStreamDevice defined in the database.</returns>
public static Dictionary <int, string> GetLookupList(AdoDataConnection database, int outputStreamID, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <int, string> OutputStreamDeviceList = new Dictionary <int, string>();
if (isOptional)
{
OutputStreamDeviceList.Add(0, "Select OutputStreamDevice");
}
string query = database.ParameterizedQueryString("SELECT ID, Name FROM OutputStreamDevice WHERE AdapterID = {0} ORDER BY LoadOrder", "adapterID");
DataTable OutputStreamDeviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, outputStreamID);
foreach (DataRow row in OutputStreamDeviceTable.Rows)
{
OutputStreamDeviceList[row.ConvertField <int>("ID")] = row.Field <string>("Name");
}
return(OutputStreamDeviceList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Loads <see cref="Historian"/> 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 to load, if any.</param>
/// <returns>Collection of <see cref="Historian"/>.</returns>
public static ObservableCollection <Historian> Load(AdoDataConnection database, IList <int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
Historian[] historianList = null;
DataTable historianTable;
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, Acronym, Name, AssemblyName, TypeName, " +
"ConnectionString, IsLocal, Description, LoadOrder, Enabled, MeasurementReportingInterval, NodeName" +
" FROM HistorianDetail WHERE NodeID = {{0}} AND ID IN ({0})", commaSeparatedKeys), "nodeID");
historianTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
historianList = new Historian[historianTable.Rows.Count];
foreach (DataRow row in historianTable.Rows)
{
id = row.ConvertField <int>("ID");
historianList[keys.IndexOf(id)] = new Historian()
{
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"),
IsLocal = Convert.ToBoolean(row.Field <object>("IsLocal")),
Description = row.Field <string>("Description"),
LoadOrder = row.ConvertField <int>("LoadOrder"),
Enabled = Convert.ToBoolean(row.Field <object>("Enabled")),
MeasurementReportingInterval = row.ConvertField <int>("MeasurementReportingInterval"),
m_nodeName = row.Field <string>("NodeName")
};
}
}
return(new ObservableCollection <Historian>(historianList ?? new Historian[0]));
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Deletes specified <see cref="OutputStreamDevicePhasor"/> record and its associated measurements from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamDevicePhasorID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, int outputStreamDevicePhasorID)
{
bool createdConnection = false;
int adapterID;
int outputStreamDeviceID;
int deletedSignalReferenceIndex;
int presentDevicePhasorCount;
string angleSignalReference;
string angleSignalReferenceBase;
string magnitudeSignalReference;
string magnitudeSignalReferenceBase;
string previousAngleSignalReference;
string previousMagnitudeSignalReference;
string nextAngleSignalReference = string.Empty;
string nextMagnitudeSignalReference = string.Empty;
string lastAffectedMeasurementsMessage = string.Empty;
try
{
createdConnection = CreateConnection(ref database);
// Setup current user context for any delete triggers
CommonFunctions.SetCurrentUserContext(database);
GetDeleteMeasurementDetails(database, outputStreamDevicePhasorID, out angleSignalReference, out magnitudeSignalReference, out adapterID, out outputStreamDeviceID);
// Delete angle/magnitude of measurements if they exist
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamMeasurement WHERE SignalReference = {0} AND AdapterID = {1}", "signalReference", "adapterID"), DefaultTimeout, angleSignalReference, adapterID);
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamMeasurement WHERE SignalReference = {0} AND AdapterID = {1}", "signalReference", "adapterID"), DefaultTimeout, magnitudeSignalReference, adapterID);
presentDevicePhasorCount = Convert.ToInt32(database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM OutputStreamDevicePhasor WHERE OutputStreamDeviceID = {0}", "outputStreamDeviceID"), DefaultTimeout, outputStreamDeviceID));
// Using signal reference angle/mag deleted build the next signal reference (increment by 1)
int.TryParse(Regex.Match(magnitudeSignalReference, @"\d+$").Value, out deletedSignalReferenceIndex);
angleSignalReferenceBase = Regex.Replace(angleSignalReference, @"\d+$", "");
magnitudeSignalReferenceBase = Regex.Replace(magnitudeSignalReference, @"\d+$", "");
for (int i = deletedSignalReferenceIndex; i < presentDevicePhasorCount; i++)
{
// We will be modifying the measurements with signal reference index i+1 to have signal refrence index i.
previousAngleSignalReference = string.Format("{0}{1}", angleSignalReferenceBase, i);
nextAngleSignalReference = string.Format("{0}{1}", angleSignalReferenceBase, i + 1);
previousMagnitudeSignalReference = string.Format("{0}{1}", magnitudeSignalReferenceBase, i);
nextMagnitudeSignalReference = string.Format("{0}{1}", magnitudeSignalReferenceBase, i + 1);
// For angle...
// Obtain details of measurements after the deleted measurements, then modify the signal reference (decrement by 1) and put it back
OutputStreamMeasurement outputStreamMeasurement = GetOutputMeasurementDetails(database, nextAngleSignalReference, adapterID);
outputStreamMeasurement.SignalReference = previousAngleSignalReference;
OutputStreamMeasurement.Save(database, outputStreamMeasurement);
// For magnitude...
outputStreamMeasurement = GetOutputMeasurementDetails(database, nextMagnitudeSignalReference, adapterID);
outputStreamMeasurement.SignalReference = previousMagnitudeSignalReference;
OutputStreamMeasurement.Save(database, outputStreamMeasurement);
}
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamDevicePhasor WHERE ID = {0}", "outputStreamDevicePhasorID"), DefaultTimeout, outputStreamDevicePhasorID);
return "OutputStreamDevicePhasor deleted successfully";
}
catch (Exception ex)
{
if (!string.IsNullOrEmpty(nextMagnitudeSignalReference))
lastAffectedMeasurementsMessage = string.Format("{0}(Last affected measurements: {1} {2})", Environment.NewLine, nextMagnitudeSignalReference, nextAngleSignalReference);
CommonFunctions.LogException(database, "OutputStreamDevicePhasor.Delete", ex);
MessageBoxResult dialogResult = MessageBox.Show(string.Format("Could not delete or modify measurements.{0}Do you still wish to delete this Phasor?{1}", Environment.NewLine, lastAffectedMeasurementsMessage), "", MessageBoxButton.YesNo);
if (dialogResult == MessageBoxResult.Yes)
{
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamDevicePhasor WHERE ID = {0}", "outputStreamDevicePhasorID"), DefaultTimeout, outputStreamDevicePhasorID);
return "OutputStreamDevicePhasor deleted successfully but failed to modify all measurements ";
}
else
{
Exception exception = ex.InnerException ?? ex;
return string.Format("Delete OutputStreamDevicePhasor was unsuccessful: {0}", exception.Message);
}
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> neither allowed nor denied for <see cref="Subscriber"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of ID and Name of <see cref="MeasurementGroup"/>.</returns>
public static Dictionary<int, string> GetAvailableMeasurementGroups(AdoDataConnection database, Guid subscriberID)
{
Dictionary<int, string> availableMeasurementGroups;
DataTable availableMeasurementGroupTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
availableMeasurementGroups = new Dictionary<int, string>();
query = database.ParameterizedQueryString("SELECT ID, Name FROM MeasurementGroup WHERE ID NOT IN (SELECT MeasurementGroupID FROM SubscriberMeasurementGroup WHERE SubscriberID = {0}) ORDER BY Name", "subscriberID");
availableMeasurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID));
foreach (DataRow row in availableMeasurementGroupTable.Rows)
availableMeasurementGroups[row.ConvertField<int>("ID")] = row.Field<string>("Name");
return availableMeasurementGroups;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Deletes specified <see cref="Phasor"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="phasorID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, int phasorID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
// Setup current user context for any delete triggers
CommonFunctions.SetCurrentUserContext(database);
Phasor phasor = GetPhasor(database, "WHERE ID = " + phasorID);
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Phasor WHERE ID = {0}", "phasorID"), DefaultTimeout, phasorID);
if (phasor != null)
{
try
{
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Measurement WHERE DeviceID = {0} AND PhasorSourceIndex = {1}", "deviceID", "phasorSourceIndex"), DefaultTimeout, phasor.DeviceID, phasor.SourceIndex);
}
catch (Exception ex)
{
CommonFunctions.LogException(database, "Phasor.Delete", ex);
throw new Exception("Phasor deleted successfully but failed to delete measurements. " + Environment.NewLine + ex.Message);
}
}
return "Phasor deleted successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Phasor"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="deviceID">ID of the <see cref="Device"/> 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 Label of phasors defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, int deviceID, bool isOptional = true)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> phasorList = new Dictionary<int, string>();
if (isOptional)
phasorList.Add(0, "Select Phasor");
if (deviceID == 0)
return phasorList;
string query = database.ParameterizedQueryString("SELECT ID, Label FROM Phasor WHERE DeviceID = {0} ORDER BY SourceIndex", "deviceID");
DataTable phasorTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, deviceID);
foreach (DataRow row in phasorTable.Rows)
phasorList[row.ConvertField<int>("ID")] = row.Field<string>("Label");
return phasorList;
}
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>
/// 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();
}
}
private static void PhasorDataSourceValidation(AdoDataConnection database, string nodeIDQueryString, ulong trackingVersion, string arguments, Action<string> statusMessage, Action<Exception> processException)
{
// Make sure setting exists to allow user to by-pass phasor data source validation at startup
ConfigurationFile configFile = ConfigurationFile.Current;
CategorizedSettingsElementCollection settings = configFile.Settings["systemSettings"];
settings.Add("ProcessPhasorDataSourceValidation", true, "Determines if the phasor data source validation should be processed at startup");
// See if this node should process phasor source validation
if (!settings["ProcessPhasorDataSourceValidation"].ValueAsBoolean())
return;
Dictionary<string, string> args = new Dictionary<string, string>();
bool skipOptimization = false, renameAllPointTags = false;
string arg, acronym;
if (!string.IsNullOrEmpty(arguments))
args = arguments.ParseKeyValuePairs();
if (args.TryGetValue("skipOptimization", out arg))
skipOptimization = arg.ParseBoolean();
if (args.TryGetValue("renameAllPointTags", out arg))
renameAllPointTags = arg.ParseBoolean();
CreateDefaultNode(database, nodeIDQueryString, statusMessage, processException);
LoadDefaultConfigurationEntity(database, statusMessage, processException);
LoadDefaultInterconnection(database, statusMessage, processException);
LoadDefaultProtocol(database, statusMessage, processException);
LoadDefaultSignalType(database, statusMessage, processException);
LoadDefaultStatistic(database, statusMessage, processException);
EstablishDefaultMeasurementKeyCache(database, statusMessage, processException);
statusMessage("Validating signal types...");
// Validate that the acronym for status flags is FLAG (it was STAT in prior versions)
if (database.Connection.ExecuteScalar("SELECT Acronym FROM SignalType WHERE Suffix='SF'").ToNonNullString().ToUpper() == "STAT")
database.Connection.ExecuteNonQuery("UPDATE SignalType SET Acronym='FLAG' WHERE Suffix='SF'");
// Validate that the calculation and statistic signal types are defined (they did not in initial release)
if (Convert.ToInt32(database.Connection.ExecuteScalar("SELECT COUNT(*) FROM SignalType WHERE Acronym='CALC'")) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO SignalType(Name, Acronym, Suffix, Abbreviation, LongAcronym, Source, EngineeringUnits) VALUES('Calculated Value', 'CALC', 'CV', 'CV', 'Calculated', 'PMU', '')");
if (Convert.ToInt32(database.Connection.ExecuteScalar("SELECT COUNT(*) FROM SignalType WHERE Acronym='STAT'")) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO SignalType(Name, Acronym, Suffix, Abbreviation, LongAcronym, Source, EngineeringUnits) VALUES('Statistic', 'STAT', 'ST', 'ST', 'Statistic', 'Any', '')");
if (Convert.ToInt32(database.Connection.ExecuteScalar("SELECT COUNT(*) FROM SignalType WHERE Acronym='QUAL'")) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO SignalType(Name, Acronym, Suffix, Abbreviation, LongAcronym, Source, EngineeringUnits) VALUES('Quality Flags', 'QUAL', 'QF', 'QF', 'QualityFlags', 'Frame', '')");
// Make sure values are defined for long acronyms (did not exist in prior versions)
if (Convert.ToInt32(database.Connection.ExecuteScalar("SELECT COUNT(*) FROM SignalType WHERE LongAcronym='Undefined'")) > 0)
{
// Update abbreviations to better values for consistent custom point tag naming convention
if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='ALOG'").ToNonNullString().ToUpper() == "A")
database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='AV' WHERE Acronym='ALOG'");
if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='DIGI'").ToNonNullString().ToUpper() == "D")
database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='DV' WHERE Acronym='DIGI'");
if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='CALC'").ToNonNullString().ToUpper() == "C")
database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='CV' WHERE Acronym='CALC'");
if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='STAT'").ToNonNullString().ToUpper() == "P")
database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='ST' WHERE Acronym='STAT'");
if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='QUAL'").ToNonNullString().ToUpper() == "Q")
database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='QF' WHERE Acronym='QUAL'");
IEnumerable<DataRow> signalTypes = database.Connection.RetrieveData(database.AdapterType, "SELECT Name, Acronym FROM SignalType WHERE LongAcronym='Undefined'").AsEnumerable();
string longAcronym;
foreach (DataRow row in signalTypes)
{
acronym = row.Field<string>("Acronym").ToUpperInvariant().Trim();
switch (acronym)
{
case "IPHM":
longAcronym = "CurrentMagnitude";
break;
case "IPHA":
longAcronym = "CurrentAngle";
break;
case "VPHM":
longAcronym = "VoltageMagnitude";
break;
case "VPHA":
longAcronym = "VoltageAngle";
break;
case "FREQ":
longAcronym = "Frequency";
break;
case "DFDT":
longAcronym = "DfDt";
break;
case "ALOG":
longAcronym = "Analog";
break;
case "FLAG":
longAcronym = "StatusFlags";
break;
case "DIGI":
longAcronym = "Digital";
break;
case "CALC":
longAcronym = "Calculated";
break;
case "STAT":
longAcronym = "Statistic";
break;
case "ALRM":
longAcronym = "Alarm";
break;
case "QUAL":
longAcronym = "QualityFlags";
break;
default:
longAcronym = row.Field<string>("Name").Trim().RemoveWhiteSpace();
if (string.IsNullOrEmpty(longAcronym))
longAcronym = acronym.ToNonNullString("?");
break;
}
database.Connection.ExecuteNonQuery(string.Format("UPDATE SignalType SET LongAcronym='{0}' WHERE Acronym='{1}'", longAcronym, acronym));
}
}
statusMessage("Validating output stream device ID codes...");
// Validate all ID codes for output stream devices are not set their default value
database.Connection.ExecuteNonQuery("UPDATE OutputStreamDevice SET IDCode = ID WHERE IDCode = 0");
statusMessage("Verifying statistics archive exists...");
// Validate that the statistics historian exists
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Historian WHERE Acronym='STAT' AND NodeID={0}", nodeIDQueryString))) == 0)
database.Connection.ExecuteNonQuery(string.Format("INSERT INTO Historian(NodeID, Acronym, Name, AssemblyName, TypeName, ConnectionString, IsLocal, Description, LoadOrder, Enabled) VALUES({0}, 'STAT', 'Statistics Archive', 'HistorianAdapters.dll', 'HistorianAdapters.LocalOutputAdapter', '', 1, 'Local historian used to archive system statistics', 9999, 1)", nodeIDQueryString));
// Make sure statistics path exists to hold historian files
string statisticsPath = FilePath.GetAbsolutePath(FilePath.AddPathSuffix("Statistics"));
if (!Directory.Exists(statisticsPath))
Directory.CreateDirectory(statisticsPath);
// Make sure needed statistic historian configuration settings are properly defined
settings = configFile.Settings["statMetadataFile"];
settings.Add("FileName", string.Format("Statistics{0}stat_dbase.dat", Path.DirectorySeparatorChar), "Name of the statistics meta-data file including its path.");
settings.Add("LoadOnOpen", true, "True if file records are to be loaded in memory when opened; otherwise False - this defaults to True for the statistics meta-data file.");
settings.Add("ReloadOnModify", false, "True if file records loaded in memory are to be re-loaded when file is modified on disk; otherwise False - this defaults to False for the statistics meta-data file.");
settings["LoadOnOpen"].Update(true);
settings["ReloadOnModify"].Update(false);
settings = configFile.Settings["statStateFile"];
settings.Add("FileName", string.Format("Statistics{0}stat_startup.dat", Path.DirectorySeparatorChar), "Name of the statistics state file including its path.");
settings.Add("AutoSaveInterval", 10000, "Interval in milliseconds at which the file records loaded in memory are to be saved automatically to disk. Use -1 to disable automatic saving - this defaults to 10,000 for the statistics state file.");
settings.Add("LoadOnOpen", true, "True if file records are to be loaded in memory when opened; otherwise False - this defaults to True for the statistics state file.");
settings.Add("SaveOnClose", true, "True if file records loaded in memory are to be saved to disk when file is closed; otherwise False - this defaults to True for the statistics state file.");
settings.Add("ReloadOnModify", false, "True if file records loaded in memory are to be re-loaded when file is modified on disk; otherwise False - this defaults to False for the statistics state file.");
settings["AutoSaveInterval"].Update(10000);
settings["LoadOnOpen"].Update(true);
settings["SaveOnClose"].Update(true);
settings["ReloadOnModify"].Update(false);
settings = configFile.Settings["statIntercomFile"];
settings.Add("FileName", string.Format("Statistics{0}scratch.dat", Path.DirectorySeparatorChar), "Name of the statistics intercom file including its path.");
settings.Add("AutoSaveInterval", 10000, "Interval in milliseconds at which the file records loaded in memory are to be saved automatically to disk. Use -1 to disable automatic saving - this defaults to 10,000 for the statistics intercom file.");
settings.Add("LoadOnOpen", true, "True if file records are to be loaded in memory when opened; otherwise False - this defaults to True for the statistics intercom file.");
settings.Add("SaveOnClose", true, "True if file records loaded in memory are to be saved to disk when file is closed; otherwise False - this defaults to True for the statistics intercom file.");
settings.Add("ReloadOnModify", false, "True if file records loaded in memory are to be re-loaded when file is modified on disk; otherwise False - this defaults to False for the statistics intercom file.");
settings["AutoSaveInterval"].Update(1000);
settings["LoadOnOpen"].Update(true);
settings["SaveOnClose"].Update(true);
settings["ReloadOnModify"].Update(false);
settings = configFile.Settings["statArchiveFile"];
settings.Add("FileName", string.Format("Statistics{0}stat_archive.d", Path.DirectorySeparatorChar), "Name of the statistics working archive file including its path.");
settings.Add("CacheWrites", true, "True if writes are to be cached for performance; otherwise False - this defaults to True for the statistics working archive file.");
settings.Add("ConserveMemory", false, "True if attempts are to be made to conserve memory; otherwise False - this defaults to False for the statistics working archive file.");
settings["CacheWrites"].Update(true);
settings["ConserveMemory"].Update(false);
settings = configFile.Settings["statMetadataService"];
settings.Add("Endpoints", "http.rest://localhost:6051/historian", "Semicolon delimited list of URIs where the web service can be accessed - this defaults to http.rest://localhost:6051/historian for the statistics meta-data service.");
settings = configFile.Settings["statTimeSeriesDataService"];
settings.Add("Endpoints", "http.rest://localhost:6052/historian", "Semicolon delimited list of URIs where the web service can be accessed - this defaults to http.rest://localhost:6052/historian for the statistics time-series data service.");
configFile.Save();
// Get the needed statistic related IDs
int statHistorianID = Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT ID FROM Historian WHERE Acronym='STAT' AND NodeID={0}", nodeIDQueryString)));
// Load the defined system statistics
IEnumerable<DataRow> statistics = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM Statistic ORDER BY Source, SignalIndex").AsEnumerable();
// Filter statistics to device, input stream and output stream types
IEnumerable<DataRow> deviceStatistics = statistics.Where(row => string.Compare(row.Field<string>("Source"), "Device", true) == 0).ToList();
IEnumerable<DataRow> inputStreamStatistics = statistics.Where(row => string.Compare(row.Field<string>("Source"), "InputStream", true) == 0).ToList();
// Define kinds of output signal that will designate a location in an output stream protocol frame - other non-mappable measurements will be removed from output stream measurements
SignalKind[] validOutputSignalKinds = { SignalKind.Angle, SignalKind.Magnitude, SignalKind.Frequency, SignalKind.DfDt, SignalKind.Status, SignalKind.Analog, SignalKind.Digital, SignalKind.Quality };
HashSet<int> measurementIDsToDelete = new HashSet<int>();
SignalReference deviceSignalReference;
string query, signalReference, pointTag, company, description, protocolIDs;
int adapterID, deviceID, signalIndex;
bool firstStatisticExisted;
int? historianID;
string[] trackedTables;
ulong changes;
try
{
// Determine the tables for which changes are tracked
if (trackingVersion != ulong.MinValue)
{
trackedTables = database.Connection.RetrieveData(database.AdapterType, "SELECT Name FROM TrackedTable").Select()
.Select(row => row["Name"].ToNonNullString())
.ToArray();
}
else
{
trackedTables = new string[0];
}
}
catch
{
trackedTables = new string[0];
}
statusMessage("Validating device protocols...");
// Extract IDs for phasor protocols
StringBuilder protocolIDList = new StringBuilder();
DataTable protocols = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM Protocol");
if (protocols.Columns.Contains("Category"))
{
// Make sure new protocol types exist
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='GatewayTransport'"))) == 0)
{
database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('GatewayTransport', 'Gateway Transport', 'Measurement', 'Gateway', 'GSF.TimeSeries.dll', 'GSF.TimeSeries.Transport.DataSubscriber', " + (protocols.Rows.Count + 1) + ")");
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='WAV'"))) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('WAV', 'Wave Form Input Adapter', 'Frame', 'Audio', 'WavInputAdapter.dll', 'WavInputAdapter.WavInputAdapter', " + (protocols.Rows.Count + 2) + ")");
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='IeeeC37_118V2'"))) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('IeeeC37_118V2', 'IEEE C37.118.2-2011', 'Frame', 'Phasor', 'PhasorProtocolAdapters.dll', 'PhasorProtocolAdapters.PhasorMeasurementMapper', 2)");
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='VirtualInput'"))) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('VirtualInput', 'Virtual Device', 'Frame', 'Virtual', 'TestingAdapters.dll', 'TestingAdapters.VirtualInputAdapter', " + (protocols.Rows.Count + 4) + ")");
}
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='Iec61850_90_5'"))) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('Iec61850_90_5', 'IEC 61850-90-5', 'Frame', 'Phasor', 'PhasorProtocolAdapters.dll', 'PhasorProtocolAdapters.PhasorMeasurementMapper', 12)");
foreach (DataRow protocol in protocols.Rows)
{
if (string.Compare(protocol.Field<string>("Category"), "Phasor", true) == 0)
{
if (protocolIDList.Length > 0)
protocolIDList.Append(", ");
protocolIDList.Append(protocol.ConvertField<int>("ID"));
}
}
}
else
{
// Older schemas do not include protocol categories and assembly info
foreach (DataRow protocol in protocols.Rows)
{
if (protocolIDList.Length > 0)
protocolIDList.Append(", ");
protocolIDList.Append(protocol.ConvertField<int>("ID"));
}
}
protocolIDs = protocolIDList.ToString();
try
{
// Determine how many changes were made to devices and measurements -
// if no changes were made, we can skip the next few steps
if (trackedTables.Contains("Device") && trackedTables.Contains("Measurement"))
changes = Convert.ToUInt64(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM TrackedChange WHERE (TableName = 'Device' OR TableName = 'Measurement') AND ID > {0}", trackingVersion)));
else
changes = ulong.MaxValue;
}
catch
{
changes = ulong.MaxValue;
}
if (skipOptimization || changes != 0L)
{
statusMessage("Validating device measurements...");
// Get protocol ID list for those protocols that support time quality flags
DataTable timeQualityProtocols = database.Connection.RetrieveData(database.AdapterType, "SELECT ID FROM Protocol WHERE Acronym = 'IeeeC37_118V1' OR Acronym = 'IeeeC37_118V2' OR Acronym = 'IeeeC37_118D6' OR Acronym = 'Iec61850_90_5'");
StringBuilder timeQualityProtocolIDList = new StringBuilder();
string timeQualityProtocolIDs;
foreach (DataRow timeQualityProtocol in timeQualityProtocols.Rows)
{
if (timeQualityProtocolIDList.Length > 0)
timeQualityProtocolIDList.Append(", ");
timeQualityProtocolIDList.Append(timeQualityProtocol.ConvertField<int>("ID"));
}
timeQualityProtocolIDs = timeQualityProtocolIDList.ToString();
int qualityFlagsSignalTypeID = Convert.ToInt32(database.Connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym='QUAL'"));
// Make sure one device quality flags measurement exists for each "connection" for devices that support time quality flags
foreach (DataRow device in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM Device WHERE ((IsConcentrator = 0 AND ParentID IS NULL) OR IsConcentrator = 1) AND NodeID = {0} AND ProtocolID IN ({1})", nodeIDQueryString, timeQualityProtocolIDs)).Rows)
{
deviceID = device.ConvertField<int>("ID");
acronym = device.Field<string>("Acronym");
signalReference = SignalReference.ToString(acronym, SignalKind.Quality);
// See if quality flags measurement exists for device
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Measurement WHERE SignalReference = '{0}' AND DeviceID = {1}", signalReference, deviceID))) == 0)
{
historianID = device.ConvertNullableField<int>("HistorianID");
company = (string)database.Connection.ExecuteScalar(string.Format("SELECT MapAcronym FROM Company WHERE ID = {0}", device.ConvertNullableField<int>("CompanyID") ?? 0));
if (string.IsNullOrEmpty(company))
company = configFile.Settings["systemSettings"]["CompanyAcronym"].Value.TruncateRight(3);
pointTag = CreatePointTag(company, acronym, null, "QUAL");
description = string.Format("{0} Time Quality Flags", device.Field<string>("Name"));
query = database.ParameterizedQueryString("INSERT INTO Measurement(HistorianID, DeviceID, PointTag, SignalTypeID, PhasorSourceIndex, " +
"SignalReference, Description, Enabled) VALUES({0}, {1}, {2}, {3}, NULL, {4}, {5}, 1)", "historianID", "deviceID", "pointTag",
"signalTypeID", "signalReference", "description");
database.Connection.ExecuteNonQuery(query, DataExtensions.DefaultTimeoutDuration, historianID.HasValue ? (object)historianID.Value : (object)DBNull.Value, deviceID, pointTag, qualityFlagsSignalTypeID, signalReference, description);
}
}
// Make sure needed device statistic measurements exist, currently statistics are only associated with phasor devices so we filter based on protocol
foreach (DataRow device in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM Device WHERE IsConcentrator = 0 AND NodeID = {0} AND ProtocolID IN ({1})", nodeIDQueryString, protocolIDs)).Rows)
{
foreach (DataRow statistic in deviceStatistics)
{
string oldAcronym;
string oldSignalReference;
signalIndex = statistic.ConvertField<int>("SignalIndex");
oldAcronym = device.Field<string>("Acronym");
acronym = oldAcronym + "!PMU";
oldSignalReference = SignalReference.ToString(oldAcronym, SignalKind.Statistic, signalIndex);
signalReference = SignalReference.ToString(acronym, SignalKind.Statistic, signalIndex);
// If the original format for device statistics is found in the database, update to new format
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Measurement WHERE SignalReference='{0}' AND HistorianID={1}", oldSignalReference, statHistorianID))) > 0)
database.Connection.ExecuteNonQuery(string.Format("UPDATE Measurement SET SignalReference='{0}' WHERE SignalReference='{1}' AND HistorianID={2}", signalReference, oldSignalReference, statHistorianID));
else if (!skipOptimization)
break;
}
}
statusMessage("Validating input stream measurements...");
// Make sure devices associated with a concentrator do not have any extraneous input stream statistic measurements - this can happen
// when a device was once a direct connect device but now is part of a concentrator...
foreach (DataRow inputStream in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM Device WHERE (IsConcentrator = 0 AND ParentID IS NOT NULL) AND NodeID = {0} AND ProtocolID IN ({1})", nodeIDQueryString, protocolIDs)).Rows)
{
firstStatisticExisted = false;
foreach (DataRow statistic in inputStreamStatistics)
{
acronym = inputStream.Field<string>("Acronym") + "!IS";
signalIndex = statistic.ConvertField<int>("SignalIndex");
signalReference = SignalReference.ToString(acronym, SignalKind.Statistic, signalIndex);
// To reduce time required to execute these steps, only first statistic is verified to exist
if (!skipOptimization && !firstStatisticExisted)
{
firstStatisticExisted = (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Measurement WHERE SignalReference='{0}'", signalReference))) > 0);
// If the first extraneous input statistic doesn't exist, we assume no others do as well
if (!firstStatisticExisted)
break;
}
// Remove extraneous input statistics
database.Connection.ExecuteNonQuery(string.Format("DELETE FROM Measurement WHERE SignalReference = '{0}'", signalReference));
}
}
}
try
{
// Determine how many changes were made to output streams, devices, and measurements -
// if no changes were made, we can skip the next few steps
if (trackedTables.Contains("OutputStream") && trackedTables.Contains("OutputStreamDevice") && trackedTables.Contains("OutputStreamMeasurement") && trackedTables.Contains("Measurement"))
changes = Convert.ToUInt64(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM TrackedChange WHERE (TableName = 'OutputStream' OR TableName = 'OutputStreamDevice' OR TableName = 'OutputStreamMeasurement' OR TableName = 'Measurement') AND ID > {0}", trackingVersion)));
else
changes = ulong.MaxValue;
}
catch
{
changes = ulong.MaxValue;
}
if (skipOptimization || changes != 0L)
{
statusMessage("Validating output stream measurements...");
// Make sure needed output stream statistic measurements exist
foreach (DataRow outputStream in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM OutputStream WHERE NodeID = {0}", nodeIDQueryString)).Rows)
{
adapterID = outputStream.ConvertField<int>("ID");
// Load devices acronyms associated with this output stream
List<string> deviceAcronyms =
database.Connection.RetrieveData(database.AdapterType,
string.Format("SELECT Acronym FROM OutputStreamDevice WHERE AdapterID = {0} AND NodeID = {1}", adapterID, nodeIDQueryString))
.AsEnumerable()
.Select(row => row.Field<string>("Acronym"))
.ToList();
// Since measurements can be added to the output stream device itself (e.g., quality flags) - we add it as a valid mapping destination as well
deviceAcronyms.Add(outputStream.Field<string>("Acronym"));
// Sort list so binary search can be used to speed lookups
deviceAcronyms.Sort(StringComparer.OrdinalIgnoreCase);
// Validate measurements associated with this output stream
foreach (DataRow outputStreamMeasurement in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM OutputStreamMeasurement WHERE AdapterID = {0} AND NodeID = {1}", adapterID, nodeIDQueryString)).Rows)
{
// Parse output stream measurement signal reference
deviceSignalReference = new SignalReference(outputStreamMeasurement.Field<string>("SignalReference"));
// Validate that the signal reference is associated with one of the output stream's devices
if (deviceAcronyms.BinarySearch(deviceSignalReference.Acronym, StringComparer.OrdinalIgnoreCase) < 0)
{
// This measurement has a signal reference for a device that is not part of the associated output stream, so we mark it for deletion
measurementIDsToDelete.Add(outputStreamMeasurement.ConvertField<int>("ID"));
}
// Validate that the signal reference type is valid for an output stream
if (!validOutputSignalKinds.Any(validSignalKind => deviceSignalReference.Kind == validSignalKind))
{
// This measurement has a signal reference type that is not valid for an output stream, so we mark it for deletion
measurementIDsToDelete.Add(outputStreamMeasurement.ConvertField<int>("ID"));
}
}
}
}
if (measurementIDsToDelete.Count > 0)
{
statusMessage(string.Format("Removing {0} unused output stream device measurements...", measurementIDsToDelete.Count));
foreach (int measurementID in measurementIDsToDelete)
{
database.Connection.ExecuteNonQuery(string.Format("DELETE FROM OutputStreamMeasurement WHERE ID = {0} AND NodeID = {1}", measurementID, nodeIDQueryString));
}
}
if (renameAllPointTags)
{
statusMessage("Renaming all point tags...");
string device, vendor, signalAcronym;
char? phase;
int? vendorDeviceID;
SignalReference signal;
foreach (DataRow measurement in database.Connection.RetrieveData(database.AdapterType, "SELECT SignalID, CompanyAcronym, DeviceAcronym, VendorDeviceID, SignalReference, SignalAcronym, Phase FROM MeasurementDetail WHERE SignalAcronym <> 'STAT' AND Internal <> 0 AND Subscribed = 0").Rows)
{
company = measurement.ConvertField<string>("CompanyAcronym");
if (string.IsNullOrEmpty(company))
company = configFile.Settings["systemSettings"]["CompanyAcronym"].Value.TruncateRight(3);
device = measurement.ConvertField<string>("DeviceAcronym");
if ((object)device != null)
{
vendorDeviceID = measurement.ConvertNullableField<int>("VendorDeviceID");
if (vendorDeviceID.HasValue)
vendor = (string)database.Connection.ExecuteScalar("SELECT Acronym FROM Vendor WHERE ID = " + vendorDeviceID.Value);
else
vendor = null;
signalAcronym = measurement.ConvertField<string>("SignalAcronym");
try
{
signal = new SignalReference(measurement.ConvertField<string>("SignalReference"));
signalIndex = signal.Index;
if (signalIndex <= 0)
signalIndex = -1;
}
catch
{
signalIndex = -1;
}
phase = measurement.ConvertNullableField<char>("Phase");
database.Connection.ExecuteNonQuery(string.Format("UPDATE Measurement SET PointTag = '{0}' WHERE SignalID = '{1}'", CreatePointTag(company, device, vendor, signalAcronym, signalIndex, phase ?? '_'), database.Guid(measurement, "SignalID")));
}
}
}
if (skipOptimization || renameAllPointTags)
{
// If skipOptimization is set to true, automatically set it back to false
const string clearParametersQuery =
"UPDATE DataOperation SET Arguments = '' " +
"WHERE AssemblyName = 'PhasorProtocolAdapters.dll' " +
"AND TypeName = 'PhasorProtocolAdapters.CommonPhasorServices' " +
"AND MethodName = 'PhasorDataSourceValidation'";
database.Connection.ExecuteNonQuery(clearParametersQuery);
}
}
// 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();
}
}
/// <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>
/// 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>
/// 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();
}
}
private static OutputStreamMeasurement GetOutputMeasurementDetails(AdoDataConnection database, string signalReference, int adapterID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query = database.ParameterizedQueryString("SELECT * FROM OutputStreamMeasurement WHERE SignalReference = {0} AND AdapterID = {1}", "signalReference", "adapterID");
DataRow row = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, signalReference, adapterID).Rows[0];
OutputStreamMeasurement outputStreamMeasurement = new OutputStreamMeasurement
{
NodeID = row.ConvertField<Guid>("NodeID"),
AdapterID = row.Field<int>("AdapterID"),
ID = row.Field<int>("ID"),
HistorianID = row.Field<int>("HistorianID"),
PointID = row.Field<int>("PointID"),
SignalReference = row.ConvertField<string>("SignalReference"),
CreatedOn = row.ConvertField<DateTime>("CreatedOn"),
CreatedBy = row.Field<string>("CreatedBy"),
UpdatedOn = row.ConvertField<DateTime>("UpdatedOn"),
UpdatedBy = row.Field<string>("UpdatedBy")
};
return outputStreamMeasurement;
}
catch (Exception ex)
{
CommonFunctions.LogException(database, "OutputStreamDevicePhasor.GetOutputMeasurementDetails", ex);
throw;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Handles meta-data synchronization to local system.
/// </summary>
/// <remarks>
/// This function should only be initiated from call to <see cref="SynchronizeMetadata(DataSet)"/> to make
/// sure only one meta-data synchronization happens at once. Users can override this method to customize
/// process of meta-data synchronization.
/// </remarks>
protected virtual void SynchronizeMetadata()
{
bool dataMonitoringEnabled = false;
// TODO: This function is complex and very closely tied to the current time-series data schema - perhaps it should be moved outside this class and referenced
// TODO: as a delegate that can be assigned and called to allow other schemas as well. DataPublisher is already very flexible in what data it can deliver.
try
{
DataSet metadata = m_receivedMetadata;
// Only perform database synchronization if meta-data has changed since last update
if (!SynchronizedMetadataChanged(metadata))
return;
if ((object)metadata == null)
{
OnStatusMessage("WARNING: Meta-data synchronization was not performed, deserialized dataset was empty.");
return;
}
// Reset data stream monitor while meta-data synchronization is in progress
if ((object)m_dataStreamMonitor != null && m_dataStreamMonitor.Enabled)
{
m_dataStreamMonitor.Enabled = false;
dataMonitoringEnabled = true;
}
// Track total meta-data synchronization process time
Ticks startTime = DateTime.UtcNow.Ticks;
DateTime updateTime;
DateTime latestUpdateTime = DateTime.MinValue;
// Open the configuration database using settings found in the config file
using (AdoDataConnection database = new AdoDataConnection("systemSettings"))
using (IDbCommand command = database.Connection.CreateCommand())
{
IDbTransaction transaction = null;
if (m_useTransactionForMetadata)
transaction = database.Connection.BeginTransaction(database.DefaultIsloationLevel);
try
{
if ((object)transaction != null)
command.Transaction = transaction;
// Query the actual record ID based on the known run-time ID for this subscriber device
int parentID = Convert.ToInt32(command.ExecuteScalar($"SELECT SourceID FROM Runtime WHERE ID = {ID} AND SourceTable='Device'", m_metadataSynchronizationTimeout));
// Validate that the subscriber device is marked as a concentrator (we are about to associate children devices with it)
if (!command.ExecuteScalar($"SELECT IsConcentrator FROM Device WHERE ID = {parentID}", m_metadataSynchronizationTimeout).ToString().ParseBoolean())
command.ExecuteNonQuery($"UPDATE Device SET IsConcentrator = 1 WHERE ID = {parentID}", m_metadataSynchronizationTimeout);
// Get any historian associated with the subscriber device
object historianID = command.ExecuteScalar($"SELECT HistorianID FROM Device WHERE ID = {parentID}", m_metadataSynchronizationTimeout);
// Determine the active node ID - we cache this since this value won't change for the lifetime of this class
if (m_nodeID == Guid.Empty)
m_nodeID = Guid.Parse(command.ExecuteScalar($"SELECT NodeID FROM IaonInputAdapter WHERE ID = {(int)ID}", m_metadataSynchronizationTimeout).ToString());
// Determine the protocol record auto-inc ID value for the gateway transport protocol (GEP) - this value is also cached since it shouldn't change for the lifetime of this class
if (m_gatewayProtocolID == 0)
m_gatewayProtocolID = int.Parse(command.ExecuteScalar("SELECT ID FROM Protocol WHERE Acronym='GatewayTransport'", m_metadataSynchronizationTimeout).ToString());
// Ascertain total number of actions required for all meta-data synchronization so some level feed back can be provided on progress
InitSyncProgress(metadata.Tables.Cast<DataTable>().Select(dataTable => (long)dataTable.Rows.Count).Sum() + 3);
// Prefix all children devices with the name of the parent since the same device names could appear in different connections (helps keep device names unique)
string sourcePrefix = Name + "!";
Dictionary<string, int> deviceIDs = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
string deviceAcronym, signalTypeAcronym;
decimal longitude, latitude;
decimal? location;
object originalSource;
int deviceID;
// Check to see if data for the "DeviceDetail" table was included in the meta-data
if (metadata.Tables.Contains("DeviceDetail"))
{
DataTable deviceDetail = metadata.Tables["DeviceDetail"];
List<Guid> uniqueIDs = new List<Guid>();
DataRow[] deviceRows;
// Define SQL statement to query if this device is already defined (this should always be based on the unique guid-based device ID)
string deviceExistsSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE UniqueID = {0}", "uniqueID");
// Define SQL statement to insert new device record
string insertDeviceSql = database.ParameterizedQueryString("INSERT INTO Device(NodeID, ParentID, HistorianID, Acronym, Name, ProtocolID, FramesPerSecond, OriginalSource, AccessID, Longitude, Latitude, ContactList, IsConcentrator, Enabled) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, 0, 1)", "nodeID", "parentID", "historianID", "acronym", "name", "protocolID", "framesPerSecond", "originalSource", "accessID", "longitude", "latitude", "contactList");
// Define SQL statement to update device's guid-based unique ID after insert
string updateDeviceUniqueIDSql = database.ParameterizedQueryString("UPDATE Device SET UniqueID = {0} WHERE Acronym = {1}", "uniqueID", "acronym");
// Define SQL statement to query if a device can be safely updated
string deviceIsUpdatableSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE UniqueID = {0} AND (ParentID <> {1} OR ParentID IS NULL)", "uniqueID", "parentID");
// Define SQL statement to update existing device record
string updateDeviceSql = database.ParameterizedQueryString("UPDATE Device SET Acronym = {0}, Name = {1}, OriginalSource = {2}, ProtocolID = {3}, FramesPerSecond = {4}, HistorianID = {5}, AccessID = {6}, Longitude = {7}, Latitude = {8}, ContactList = {9} WHERE UniqueID = {10}",
"acronym", "name", "originalSource", "protocolID", "framesPerSecond", "historianID", "accessID", "longitude", "latitude", "contactList", "uniqueID");
// Define SQL statement to retrieve device's auto-inc ID based on its unique guid-based ID
string queryDeviceIDSql = database.ParameterizedQueryString("SELECT ID FROM Device WHERE UniqueID = {0}", "uniqueID");
// Define SQL statement to retrieve all unique device ID's for the current parent to check for mismatches
string queryUniqueDeviceIDsSql = database.ParameterizedQueryString("SELECT UniqueID FROM Device WHERE ParentID = {0}", "parentID");
// Define SQL statement to remove device records that no longer exist in the meta-data
string deleteDeviceSql = database.ParameterizedQueryString("DELETE FROM Device WHERE UniqueID = {0}", "uniqueID");
// Determine which device rows should be synchronized based on operational mode flags
if (ReceiveInternalMetadata && ReceiveExternalMetadata)
deviceRows = deviceDetail.Select();
else if (ReceiveInternalMetadata)
deviceRows = deviceDetail.Select("OriginalSource IS NULL");
else if (ReceiveExternalMetadata)
deviceRows = deviceDetail.Select("OriginalSource IS NOT NULL");
else
deviceRows = new DataRow[0];
// Check existence of optional meta-data fields
DataColumnCollection deviceDetailColumns = deviceDetail.Columns;
bool accessIDFieldExists = deviceDetailColumns.Contains("AccessID");
bool longitudeFieldExists = deviceDetailColumns.Contains("Longitude");
bool latitudeFieldExists = deviceDetailColumns.Contains("Latitude");
bool companyAcronymFieldExists = deviceDetailColumns.Contains("CompanyAcronym");
bool protocolNameFieldExists = deviceDetailColumns.Contains("ProtocolName");
bool vendorAcronymFieldExists = deviceDetailColumns.Contains("VendorAcronym");
bool vendorDeviceNameFieldExists = deviceDetailColumns.Contains("VendorDeviceName");
bool interconnectionNameFieldExists = deviceDetailColumns.Contains("InterconnectionName");
bool updatedOnFieldExists = deviceDetailColumns.Contains("UpdatedOn");
// Older versions of GEP did not include the AccessID field, so this is treated as optional
int accessID = 0;
foreach (DataRow row in deviceRows)
{
Guid uniqueID = Guid.Parse(row.Field<object>("UniqueID").ToString());
bool recordNeedsUpdating;
// Track unique device Guids in this meta-data session, we'll need to remove any old associated devices that no longer exist
uniqueIDs.Add(uniqueID);
// Determine if record has changed since last synchronization
if (updatedOnFieldExists)
{
try
{
updateTime = Convert.ToDateTime(row["UpdatedOn"]);
recordNeedsUpdating = updateTime > m_lastMetaDataRefreshTime;
if (updateTime > latestUpdateTime)
latestUpdateTime = updateTime;
}
catch
{
recordNeedsUpdating = true;
}
}
else
{
recordNeedsUpdating = true;
}
// We will synchronize meta-data only if the source owns this device and it's not defined as a concentrator (these should normally be filtered by publisher - but we check just in case).
if (!row["IsConcentrator"].ToNonNullString("0").ParseBoolean())
{
if (accessIDFieldExists)
accessID = row.ConvertField<int>("AccessID");
// Get longitude and latitude values if they are defined
longitude = 0M;
latitude = 0M;
if (longitudeFieldExists)
{
location = row.ConvertNullableField<decimal>("Longitude");
if (location.HasValue)
longitude = location.Value;
}
if (latitudeFieldExists)
{
location = row.ConvertNullableField<decimal>("Latitude");
if (location.HasValue)
latitude = location.Value;
}
// Save any reported extraneous values from device meta-data in connection string formatted contact list - all fields are considered optional
Dictionary<string, string> contactList = new Dictionary<string, string>();
if (companyAcronymFieldExists)
contactList["companyAcronym"] = row.Field<string>("CompanyAcronym") ?? string.Empty;
if (protocolNameFieldExists)
contactList["protocolName"] = row.Field<string>("ProtocolName") ?? string.Empty;
if (vendorAcronymFieldExists)
contactList["vendorAcronym"] = row.Field<string>("VendorAcronym") ?? string.Empty;
if (vendorDeviceNameFieldExists)
contactList["vendorDeviceName"] = row.Field<string>("VendorDeviceName") ?? string.Empty;
if (interconnectionNameFieldExists)
contactList["interconnectionName"] = row.Field<string>("InterconnectionName") ?? string.Empty;
// Determine if device record already exists
if (Convert.ToInt32(command.ExecuteScalar(deviceExistsSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID))) == 0)
{
// Insert new device record
command.ExecuteNonQuery(insertDeviceSql, m_metadataSynchronizationTimeout, database.Guid(m_nodeID), parentID, historianID, sourcePrefix + row.Field<string>("Acronym"), row.Field<string>("Name"), m_gatewayProtocolID, row.ConvertField<int>("FramesPerSecond"),
m_internal ? (object)DBNull.Value : string.IsNullOrEmpty(row.Field<string>("ParentAcronym")) ? sourcePrefix + row.Field<string>("Acronym") : sourcePrefix + row.Field<string>("ParentAcronym"), accessID, longitude, latitude, contactList.JoinKeyValuePairs());
// Guids are normally auto-generated during insert - after insertion update the Guid so that it matches the source data. Most of the database
// scripts have triggers that support properly assigning the Guid during an insert, but this code ensures the Guid will always get assigned.
command.ExecuteNonQuery(updateDeviceUniqueIDSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID), sourcePrefix + row.Field<string>("Acronym"));
}
else if (recordNeedsUpdating)
{
// Perform safety check to preserve device records which are not safe to overwrite
if (Convert.ToInt32(command.ExecuteScalar(deviceIsUpdatableSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID), parentID)) > 0)
continue;
// Gateway is assuming ownership of the device records when the "internal" flag is true - this means the device's measurements can be forwarded to another party. From a device record perspective,
// ownership is inferred by setting 'OriginalSource' to null. When gateway doesn't own device records (i.e., the "internal" flag is false), this means the device's measurements can only be consumed
// locally - from a device record perspective this means the 'OriginalSource' field is set to the acronym of the PDC or PMU that generated the source measurements. This field allows a mirrored source
// restriction to be implemented later to ensure all devices in an output protocol came from the same original source connection, if desired.
originalSource = m_internal ? (object)DBNull.Value : string.IsNullOrEmpty(row.Field<string>("ParentAcronym")) ? sourcePrefix + row.Field<string>("Acronym") : sourcePrefix + row.Field<string>("ParentAcronym");
// Update existing device record
command.ExecuteNonQuery(updateDeviceSql, m_metadataSynchronizationTimeout, sourcePrefix + row.Field<string>("Acronym"), row.Field<string>("Name"), originalSource, m_gatewayProtocolID, row.ConvertField<int>("FramesPerSecond"), historianID, accessID, longitude, latitude, contactList.JoinKeyValuePairs(), database.Guid(uniqueID));
}
}
// Capture local device ID auto-inc value for measurement association
deviceIDs[row.Field<string>("Acronym")] = Convert.ToInt32(command.ExecuteScalar(queryDeviceIDSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID)));
// Periodically notify user about synchronization progress
UpdateSyncProgress();
}
// Remove any device records associated with this subscriber that no longer exist in the meta-data
if (uniqueIDs.Count > 0)
{
// Sort unique ID list so that binary search can be used for quick lookups
uniqueIDs.Sort();
DataTable deviceUniqueIDs = command.RetrieveData(database.AdapterType, queryUniqueDeviceIDsSql, m_metadataSynchronizationTimeout, parentID);
Guid uniqueID;
foreach (DataRow deviceRow in deviceUniqueIDs.Rows)
{
uniqueID = database.Guid(deviceRow, "UniqueID");
// Remove any devices in the database that are associated with the parent device and do not exist in the meta-data
if (uniqueIDs.BinarySearch(uniqueID) < 0)
command.ExecuteNonQuery(deleteDeviceSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID));
}
UpdateSyncProgress();
}
}
// Check to see if data for the "MeasurementDetail" table was included in the meta-data
if (metadata.Tables.Contains("MeasurementDetail"))
{
DataTable measurementDetail = metadata.Tables["MeasurementDetail"];
List<Guid> signalIDs = new List<Guid>();
DataRow[] measurementRows;
// Define SQL statement to query if this measurement is already defined (this should always be based on the unique signal ID Guid)
string measurementExistsSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Measurement WHERE SignalID = {0}", "signalID");
// Define SQL statement to insert new measurement record
string insertMeasurementSql = database.ParameterizedQueryString("INSERT INTO Measurement(DeviceID, HistorianID, PointTag, AlternateTag, SignalTypeID, PhasorSourceIndex, SignalReference, Description, Internal, Subscribed, Enabled) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, 0, 1)", "deviceID", "historianID", "pointTag", "alternateTag", "signalTypeID", "phasorSourceIndex", "signalReference", "description", "internal");
// Define SQL statement to update measurement's signal ID after insert
string updateMeasurementSignalIDSql = database.ParameterizedQueryString("UPDATE Measurement SET SignalID = {0}, AlternateTag = NULL WHERE AlternateTag = {1}", "signalID", "alternateTag");
// Define SQL statement to update existing measurement record
string updateMeasurementSql = database.ParameterizedQueryString("UPDATE Measurement SET HistorianID = {0}, PointTag = {1}, SignalTypeID = {2}, PhasorSourceIndex = {3}, SignalReference = {4}, Description = {5}, Internal = {6} WHERE SignalID = {7}",
"historianID", "pointTag", "signalTypeID", "phasorSourceIndex", "signalReference", "description", "internal", "signalID");
// Define SQL statement to retrieve all measurement signal ID's for the current parent to check for mismatches - note that we use the ActiveMeasurements view
// since it associates measurements with their top-most parent runtime device ID, this allows us to easily query all measurements for the parent device
string queryMeasurementSignalIDsSql = database.ParameterizedQueryString("SELECT SignalID FROM ActiveMeasurement WHERE DeviceID = {0}", "deviceID");
// Define SQL statement to retrieve measurement's associated device ID, i.e., actual record ID, based on measurement's signal ID
string queryMeasurementDeviceIDSql = database.ParameterizedQueryString("SELECT DeviceID FROM Measurement WHERE SignalID = {0}", "signalID");
// Define SQL statement to remove device records that no longer exist in the meta-data
string deleteMeasurementSql = database.ParameterizedQueryString("DELETE FROM Measurement WHERE SignalID = {0}", "signalID");
// Load signal type ID's from local database associated with their acronym for proper signal type translation
Dictionary<string, int> signalTypeIDs = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
foreach (DataRow row in command.RetrieveData(database.AdapterType, "SELECT ID, Acronym FROM SignalType").Rows)
{
signalTypeAcronym = row.Field<string>("Acronym");
if (!string.IsNullOrWhiteSpace(signalTypeAcronym))
signalTypeIDs[signalTypeAcronym] = row.ConvertField<int>("ID");
}
// Determine which measurement rows should be synchronized based on operational mode flags
if (ReceiveInternalMetadata && ReceiveExternalMetadata)
measurementRows = measurementDetail.Select();
else if (ReceiveInternalMetadata)
measurementRows = measurementDetail.Select("Internal <> 0");
else if (ReceiveExternalMetadata)
measurementRows = measurementDetail.Select("Internal = 0");
else
measurementRows = new DataRow[0];
// Check existence of optional meta-data fields
DataColumnCollection measurementDetailColumns = measurementDetail.Columns;
bool phasorSourceIndexFieldExists = measurementDetailColumns.Contains("PhasorSourceIndex");
bool updatedOnFieldExists = measurementDetailColumns.Contains("UpdatedOn");
object phasorSourceIndex = DBNull.Value;
foreach (DataRow row in measurementRows)
{
bool recordNeedsUpdating;
// Determine if record has changed since last synchronization
if (updatedOnFieldExists)
{
try
{
updateTime = Convert.ToDateTime(row["UpdatedOn"]);
recordNeedsUpdating = updateTime > m_lastMetaDataRefreshTime;
if (updateTime > latestUpdateTime)
latestUpdateTime = updateTime;
}
catch
{
recordNeedsUpdating = true;
}
}
else
{
recordNeedsUpdating = true;
}
// Get device and signal type acronyms
deviceAcronym = row.Field<string>("DeviceAcronym") ?? string.Empty;
signalTypeAcronym = row.Field<string>("SignalAcronym") ?? string.Empty;
// Get phasor source index if field is defined
if (phasorSourceIndexFieldExists)
{
// Using ConvertNullableField extension since publisher could use SQLite database in which case
// all integers would arrive in data set as longs and need to be converted back to integers
int? index = row.ConvertNullableField<int>("PhasorSourceIndex");
phasorSourceIndex = index.HasValue ? (object)index.Value : (object)DBNull.Value;
}
// Make sure we have an associated device and signal type already defined for the measurement
if (!string.IsNullOrWhiteSpace(deviceAcronym) && deviceIDs.ContainsKey(deviceAcronym) && !string.IsNullOrWhiteSpace(signalTypeAcronym) && signalTypeIDs.ContainsKey(signalTypeAcronym))
{
Guid signalID = Guid.Parse(row.Field<object>("SignalID").ToString());
// Track unique measurement signal Guids in this meta-data session, we'll need to remove any old associated measurements that no longer exist
signalIDs.Add(signalID);
// Prefix the tag name with the "updated" device name
string pointTag = sourcePrefix + row.Field<string>("PointTag");
// Look up associated device ID (local DB auto-inc)
deviceID = deviceIDs[deviceAcronym];
// Determine if measurement record already exists
if (Convert.ToInt32(command.ExecuteScalar(measurementExistsSql, m_metadataSynchronizationTimeout, database.Guid(signalID))) == 0)
{
string alternateTag = Guid.NewGuid().ToString();
// Insert new measurement record
command.ExecuteNonQuery(insertMeasurementSql, m_metadataSynchronizationTimeout, deviceID, historianID, pointTag, alternateTag, signalTypeIDs[signalTypeAcronym], phasorSourceIndex, sourcePrefix + row.Field<string>("SignalReference"), row.Field<string>("Description") ?? string.Empty, database.Bool(m_internal));
// Guids are normally auto-generated during insert - after insertion update the Guid so that it matches the source data. Most of the database
// scripts have triggers that support properly assigning the Guid during an insert, but this code ensures the Guid will always get assigned.
command.ExecuteNonQuery(updateMeasurementSignalIDSql, m_metadataSynchronizationTimeout, database.Guid(signalID), alternateTag);
}
else if (recordNeedsUpdating)
{
// Update existing measurement record. Note that this update assumes that measurements will remain associated with a static source device.
command.ExecuteNonQuery(updateMeasurementSql, m_metadataSynchronizationTimeout, historianID, pointTag, signalTypeIDs[signalTypeAcronym], phasorSourceIndex, sourcePrefix + row.Field<string>("SignalReference"), row.Field<string>("Description") ?? string.Empty, database.Bool(m_internal), database.Guid(signalID));
}
}
// Periodically notify user about synchronization progress
UpdateSyncProgress();
}
// Remove any measurement records associated with existing devices in this session but no longer exist in the meta-data
if (signalIDs.Count > 0)
{
// Sort signal ID list so that binary search can be used for quick lookups
signalIDs.Sort();
// Query all the guid-based signal ID's for all measurement records associated with the parent device using run-time ID
DataTable measurementSignalIDs = command.RetrieveData(database.AdapterType, queryMeasurementSignalIDsSql, m_metadataSynchronizationTimeout, (int)ID);
Guid signalID;
// Walk through each database record and see if the measurement exists in the provided meta-data
foreach (DataRow measurementRow in measurementSignalIDs.Rows)
{
signalID = database.Guid(measurementRow, "SignalID");
// Remove any measurements in the database that are associated with received devices and do not exist in the meta-data
if (signalIDs.BinarySearch(signalID) < 0)
{
// Measurement was not in the meta-data, get the measurement's actual record based ID for its associated device
object measurementDeviceID = command.ExecuteScalar(queryMeasurementDeviceIDSql, m_metadataSynchronizationTimeout, database.Guid(signalID));
// If the unknown measurement is directly associated with a device that exists in the meta-data it is assumed that this measurement
// was removed from the publishing system and no longer exists therefore we remove it from the local measurement cache. If the user
// needs custom local measurements associated with a remote device, they should be associated with the parent device only.
if (measurementDeviceID != null && !(measurementDeviceID is DBNull) && deviceIDs.ContainsValue(Convert.ToInt32(measurementDeviceID)))
command.ExecuteNonQuery(deleteMeasurementSql, m_metadataSynchronizationTimeout, database.Guid(signalID));
}
}
UpdateSyncProgress();
}
}
// Check to see if data for the "PhasorDetail" table was included in the meta-data
if (metadata.Tables.Contains("PhasorDetail"))
{
Dictionary<int, int> maxSourceIndicies = new Dictionary<int, int>();
int sourceIndex;
// Phasor data is normally only needed so that the user can properly generate a mirrored IEEE C37.118 output stream from the source data.
// This is necessary since, in this protocol, the phasors are described (i.e., labeled) as a unit (i.e., as a complex number) instead of
// as two distinct angle and magnitude measurements.
// Define SQL statement to query if phasor record is already defined (no Guid is defined for these simple label records)
string phasorExistsSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Phasor WHERE DeviceID = {0} AND SourceIndex = {1}", "deviceID", "sourceIndex");
// Define SQL statement to insert new phasor record
string insertPhasorSql = database.ParameterizedQueryString("INSERT INTO Phasor(DeviceID, Label, Type, Phase, SourceIndex) VALUES ({0}, {1}, {2}, {3}, {4})", "deviceID", "label", "type", "phase", "sourceIndex");
// Define SQL statement to update existing phasor record
string updatePhasorSql = database.ParameterizedQueryString("UPDATE Phasor SET Label = {0}, Type = {1}, Phase = {2} WHERE DeviceID = {3} AND SourceIndex = {4}", "label", "type", "phase", "deviceID", "sourceIndex");
// Define SQL statement to delete a phasor record
string deletePhasorSql = database.ParameterizedQueryString("DELETE FROM Phasor WHERE DeviceID = {0} AND SourceIndex > {1}", "deviceID", "sourceIndex");
foreach (DataRow row in metadata.Tables["PhasorDetail"].Rows)
{
// Get device acronym
deviceAcronym = row.Field<string>("DeviceAcronym") ?? string.Empty;
// Make sure we have an associated device already defined for the phasor record
if (!string.IsNullOrWhiteSpace(deviceAcronym) && deviceIDs.ContainsKey(deviceAcronym))
{
bool recordNeedsUpdating;
// Determine if record has changed since last synchronization
try
{
updateTime = Convert.ToDateTime(row["UpdatedOn"]);
recordNeedsUpdating = updateTime > m_lastMetaDataRefreshTime;
if (updateTime > latestUpdateTime)
latestUpdateTime = updateTime;
}
catch
{
recordNeedsUpdating = true;
}
deviceID = deviceIDs[deviceAcronym];
// Determine if phasor record already exists
if (Convert.ToInt32(command.ExecuteScalar(phasorExistsSql, m_metadataSynchronizationTimeout, deviceID, row.ConvertField<int>("SourceIndex"))) == 0)
{
// Insert new phasor record
command.ExecuteNonQuery(insertPhasorSql, m_metadataSynchronizationTimeout, deviceID, row.Field<string>("Label") ?? "undefined", (row.Field<string>("Type") ?? "V").TruncateLeft(1), (row.Field<string>("Phase") ?? "+").TruncateLeft(1), row.ConvertField<int>("SourceIndex"));
}
else if (recordNeedsUpdating)
{
// Update existing phasor record
command.ExecuteNonQuery(updatePhasorSql, m_metadataSynchronizationTimeout, row.Field<string>("Label") ?? "undefined", (row.Field<string>("Type") ?? "V").TruncateLeft(1), (row.Field<string>("Phase") ?? "+").TruncateLeft(1), deviceID, row.ConvertField<int>("SourceIndex"));
}
// Track largest source index for each device
maxSourceIndicies.TryGetValue(deviceID, out sourceIndex);
if (row.ConvertField<int>("SourceIndex") > sourceIndex)
maxSourceIndicies[deviceID] = row.ConvertField<int>("SourceIndex");
}
// Periodically notify user about synchronization progress
UpdateSyncProgress();
}
// Remove any phasor records associated with existing devices in this session but no longer exist in the meta-data
if (maxSourceIndicies.Count > 0)
{
foreach (KeyValuePair<int, int> deviceIndexPair in maxSourceIndicies)
{
command.ExecuteNonQuery(deletePhasorSql, m_metadataSynchronizationTimeout, deviceIndexPair.Key, deviceIndexPair.Value);
}
}
}
if ((object)transaction != null)
transaction.Commit();
// Update local in-memory synchronized meta-data cache
m_synchronizedMetadata = metadata;
}
catch (Exception ex)
{
OnProcessException(new InvalidOperationException("Failed to synchronize meta-data to local cache: " + ex.Message, ex));
if ((object)transaction != null)
{
try
{
transaction.Rollback();
}
catch (Exception rollbackException)
{
OnProcessException(new InvalidOperationException("Failed to roll back database transaction due to exception: " + rollbackException.Message, rollbackException));
}
}
return;
}
finally
{
if ((object)transaction != null)
transaction.Dispose();
}
}
// New signals may have been defined, take original remote signal index cache and apply changes
if (m_remoteSignalIndexCache != null)
m_signalIndexCache = new SignalIndexCache(DataSource, m_remoteSignalIndexCache);
m_lastMetaDataRefreshTime = latestUpdateTime > DateTime.MinValue ? latestUpdateTime : DateTime.UtcNow;
OnStatusMessage("Meta-data synchronization completed successfully in {0}", (DateTime.UtcNow.Ticks - startTime).ToElapsedTimeString(2));
// Send notification that system configuration has changed
OnConfigurationChanged();
}
catch (Exception ex)
{
OnProcessException(new InvalidOperationException("Failed to synchronize meta-data to local cache: " + ex.Message, ex));
}
finally
{
// Restart data stream monitor after meta-data synchronization if it was originally enabled
if (dataMonitoringEnabled && (object)m_dataStreamMonitor != null)
m_dataStreamMonitor.Enabled = true;
}
}
/// <summary>
/// Deletes specified <see cref="OutputStream"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, int outputStreamID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
// Get Acronym of output stream we need to delete and save it
DataTable outputStreamAcronym = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT Acronym FROM OutputStream WHERE ID = {0}", "outputStreamID"), DefaultTimeout, outputStreamID);
// Delete output stream from database
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStream WHERE ID = {0}", "outputStreamID"), DefaultTimeout, outputStreamID);
// Delete statistic measurements from database using the output stream acronym we have just deleted
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Measurement WHERE SignalReference LIKE '" + outputStreamAcronym.Rows[0].Field<string>("Acronym") + "!OS-ST%'"), DefaultTimeout);
CommonFunctions.SendCommandToService("ReloadConfig");
return "Output Stream Deleted Successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Removed measurement groups from <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 measurement groups to be removed.</param>
/// <param name="measurementGroupsToBeRemoved">List of <see cref="MeasurementGroup"/> IDs to be removed.</param>
/// <returns>string, indicating success for UI display.</returns>
public static string RemoveMeasurementGroups(AdoDataConnection database, Guid subscriberID, List<int> measurementGroupsToBeRemoved)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
foreach (int id in measurementGroupsToBeRemoved)
{
query = database.ParameterizedQueryString("DELETE FROM SubscriberMeasurementGroup WHERE SubscriberID = {0} AND MeasurementGroupID = {1}", "subscriberID", "measurementGroupID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriberID), id);
}
return "Measurement groups removed from allowed measurement groups list for subscriber successfully";
}
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>
/// 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>
/// Loads <see cref="OutputStreamDevicePhasor"/> 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 measuremnets to be loaded from the database</param>
/// <returns>Collection of <see cref="OutputStreamDevicePhasor"/>.</returns>
public static ObservableCollection<OutputStreamDevicePhasor> Load(AdoDataConnection database, IList<int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
OutputStreamDevicePhasor[] outputStreamDevicePhasorList = null;
DataTable outputStreamDevicePhasorTable;
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, OutputStreamDeviceID, ID, Label, Type, Phase, ScalingValue, LoadOrder " +
"FROM OutputStreamDevicePhasor WHERE ID IN ({0})", commaSeparatedKeys));
outputStreamDevicePhasorTable = database.Connection.RetrieveData(database.AdapterType, query);
outputStreamDevicePhasorList = new OutputStreamDevicePhasor[outputStreamDevicePhasorTable.Rows.Count];
foreach (DataRow row in outputStreamDevicePhasorTable.Rows)
{
id = row.ConvertField<int>("ID");
outputStreamDevicePhasorList[keys.IndexOf(id)] = new OutputStreamDevicePhasor()
{
NodeID = database.Guid(row, "NodeID"),
OutputStreamDeviceID = row.ConvertField<int>("OutputStreamDeviceID"),
ID = id,
Label = row.Field<string>("Label"),
Type = row.Field<string>("Type"),
Phase = row.Field<string>("Phase"),
ScalingValue = row.ConvertField<int>("ScalingValue"),
LoadOrder = row.ConvertField<int>("LoadOrder"),
m_phaseType = row.Field<string>("Phase") == "+" ? "Positive Sequence" : row.Field<string>("Phase") == "-" ? "Negative Sequence" :
row.Field<string>("Phase") == "0" ? "Zero Sequence" : row.Field<string>("Phase") == "A" ? "Phase A" :
row.Field<string>("Phase") == "B" ? "Phase B" : "Phase C",
m_phasorType = row.Field<string>("Type") == "V" ? "Voltage" : "Current"
};
}
}
return new ObservableCollection<OutputStreamDevicePhasor>(outputStreamDevicePhasorList ?? new OutputStreamDevicePhasor[0]);
}
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="deviceID">ID of the <see cref="Device"/> to filter data.</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 deviceID, string sortMember = "", string sortDirection = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList<int> phasorList = new List<int>();
DataTable phasorTable;
string query;
string sortClause = string.Empty;
if (!string.IsNullOrEmpty(sortMember))
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
query = database.ParameterizedQueryString(string.Format("SELECT ID From PhasorDetail WHERE DeviceID = {{0}} {0}", sortClause), "deviceID");
phasorTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, deviceID);
foreach (DataRow row in phasorTable.Rows)
{
phasorList.Add(row.ConvertField<int>("ID"));
}
return phasorList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="OutputStreamDevicePhasor"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamDeviceID">ID of the output stream device 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 Label of OutputStreamDevicePhasors defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, int outputStreamDeviceID, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> OutputStreamDevicePhasorList = new Dictionary<int, string>();
if (isOptional)
OutputStreamDevicePhasorList.Add(0, "Select OutputStreamDevicePhasor");
string query = database.ParameterizedQueryString("SELECT ID, Label FROM OutputStreamDevicePhasor " +
"WHERE OutputStreamDeviceID = {0} ORDER BY LoadOrder", "outputStreamDeviceID");
DataTable OutputStreamDevicePhasorTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, outputStreamDeviceID);
foreach (DataRow row in OutputStreamDevicePhasorTable.Rows)
OutputStreamDevicePhasorList[row.ConvertField<int>("ID")] = row.Field<string>("Label");
return OutputStreamDevicePhasorList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Phasor"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="phasor">Information about <see cref="Phasor"/>.</param>
/// <param name="oldSourceIndex">The old source index of the phasor.</param>
/// <param name="skipMeasurementUpdate">Skips associated measurement update if this is already being handled.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string SaveAndReorder(AdoDataConnection database, Phasor phasor, int oldSourceIndex, bool skipMeasurementUpdate = false)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (phasor.SourceIndex == 0)
phasor.SourceIndex = database.ExecuteScalar<int>("SELECT MAX(SourceIndex) FROM Phasor WHERE DeviceID = {0}", phasor.DeviceID) + 1;
// Since phasors could be reordered in the source device, this test could inadvertently throw an exception when it should not - so the validation has been removed
//if (database.ExecuteScalar<int>("SELECT COUNT(*) FROM Phasor WHERE ID <> {0} AND DeviceID = {1} AND SourceIndex = {2}", phasor.ID, phasor.DeviceID, phasor.SourceIndex) > 0)
// throw new InvalidOperationException("Phasor source index must be unique per device.");
if (phasor.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO Phasor (DeviceID, Label, Type, Phase, SourceIndex, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8})", "deviceID", "label", "type", "phase", "sourceIndex", "updatedBy", "updatedOn", "createdBy",
"createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, phasor.DeviceID, phasor.Label, phasor.Type, phasor.Phase, phasor.SourceIndex,
CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE Phasor SET DeviceID = {0}, Label = {1}, Type = {2}, Phase = {3}, SourceIndex = {4}, " +
"UpdatedBy = {5}, UpdatedOn = {6} WHERE ID = {7}", "deviceID", "label", "type", "phase", "sourceIndex", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, phasor.DeviceID, phasor.Label, phasor.Type,
phasor.Phase, phasor.SourceIndex, CommonFunctions.CurrentUser, database.UtcNow, phasor.ID);
}
// Get reference to the device to which phasor is being added.
Device device = Device.GetDevice(database, "WHERE ID = " + phasor.DeviceID);
// Get Phasor signal types.
ObservableCollection<SignalType> signals;
if (phasor.Type == "V")
signals = SignalType.GetVoltagePhasorSignalTypes();
else
signals = SignalType.GetCurrentPhasorSignalTypes();
// Get reference to phasor which has just been added.
Phasor addedPhasor = GetPhasor(database, "WHERE DeviceID = " + phasor.DeviceID + " AND SourceIndex = " + phasor.SourceIndex);
foreach (SignalType signal in signals)
{
Measurement measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + phasor.DeviceID + " AND SignalTypeSuffix = '" + signal.Suffix + "' AND PhasorSourceIndex = " + oldSourceIndex);
if ((object)measurement == null)
{
measurement = new Measurement();
measurement.DeviceID = device.ID;
measurement.HistorianID = device.HistorianID;
measurement.PointTag = CommonPhasorServices.CreatePointTag(device.CompanyAcronym, device.Acronym, device.VendorAcronym, signal.Acronym, addedPhasor.SourceIndex, addedPhasor.Phase[0]);
measurement.SignalReference = device.Acronym + "-" + signal.Suffix + addedPhasor.SourceIndex;
measurement.SignalTypeID = signal.ID;
measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.Phase + " " + signal.Name;
measurement.PhasorSourceIndex = addedPhasor.SourceIndex;
measurement.Enabled = true;
Measurement.Save(database, measurement);
}
else if (!skipMeasurementUpdate || addedPhasor.SourceIndex != oldSourceIndex) // || measurement.SignalTypeID != signal.ID
{
// Update existing record when needed or when phasor source index has changed
measurement.HistorianID = device.HistorianID;
measurement.PointTag = CommonPhasorServices.CreatePointTag(device.CompanyAcronym, device.Acronym, device.VendorAcronym, signal.Acronym, addedPhasor.SourceIndex, addedPhasor.Phase[0]);
measurement.SignalReference = device.Acronym + "-" + signal.Suffix + addedPhasor.SourceIndex;
measurement.SignalTypeID = signal.ID;
measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.Phase + " " + signal.Name;
measurement.PhasorSourceIndex = addedPhasor.SourceIndex;
Measurement.Save(database, measurement);
}
}
return "Phasor 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 = 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>
/// 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();
}
}
/// <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>
/// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> denied for <see cref="Subscriber"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of ID and Name of <see cref="MeasurementGroup"/>.</returns>
public static Dictionary<int, string> GetDeniedMeasurementGroups(AdoDataConnection database, Guid subscriberID)
{
Dictionary<int, string> deniedMeasurementGroups;
DataTable deniedMeasurementGroupTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
deniedMeasurementGroups = new Dictionary<int, string>();
query = database.ParameterizedQueryString("SELECT MeasurementGroupID, MeasurementGroupName FROM SubscriberMeasGroupDetail WHERE SubscriberID = {0} AND Allowed = {1} ORDER BY MeasurementGroupName", "subscriberID", "allowed");
deniedMeasurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID), database.Bool(false));
foreach (DataRow row in deniedMeasurementGroupTable.Rows)
deniedMeasurementGroups[row.ConvertField<int>("MeasurementGroupID")] = row.Field<string>("MeasurementGroupName");
return deniedMeasurementGroups;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// 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();
}
}
/// <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>
/// Deletes specified <see cref="Device"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="device">Device to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, Device device)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
// Setup current user context for any delete triggers
CommonFunctions.SetCurrentUserContext(database);
// Does not delete the Parent Device
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("UPDATE Device SET ParentID = null WHERE ParentID = {0}", "OldParentID", "NewParentID"), DefaultTimeout, device.ID);
// Deletes the Parent Device
//database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Device WHERE ParentID = {0}", "ParentID"), DefaultTimeout, device.ID);
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Device WHERE ID = {0}", "deviceID"), DefaultTimeout, device.ID);
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamDevice WHERE Acronym = {0}", "deviceAcronym"), DefaultTimeout, device.Acronym);
return "Device deleted successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Deletes specified <see cref="Subscriber"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="id">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, Guid id)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
// Setup current user context for any delete triggers
CommonFunctions.SetCurrentUserContext(database);
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Subscriber WHERE ID = {0}", "id"), DefaultTimeout, database.Guid(id));
try
{
CommonFunctions.SendCommandToService("ReloadConfig");
}
catch (Exception ex)
{
return "Subscriber deleted successfully. Failed to send ReloadConfig command to backend service." + Environment.NewLine + ex.Message;
}
return "Subscriber deleted successfully";
}
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();
}
}