/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Historian"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <param name="includeStatHistorian">Indicates if statistical historian included in the collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of historians defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, bool isOptional = false, bool includeStatHistorian = true)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> historianList = new Dictionary<int, string>();
if (isOptional)
historianList.Add(0, "Select Historian");
string query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Historian WHERE Enabled = {0} AND NodeID = {1} ORDER BY LoadOrder", "enabled", "nodeID");
DataTable historianTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID());
foreach (DataRow row in historianTable.Rows)
{
if (!includeStatHistorian)
{
if (row.Field<string>("Acronym").ToUpper() != "STAT")
historianList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym");
}
else
historianList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym");
}
return historianList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Historian"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="historian">Infomration about <see cref="Historian"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Historian historian)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (historian.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO Historian (NodeID, Acronym, Name, AssemblyName, TypeName, ConnectionString, IsLocal, " +
"MeasurementReportingInterval, Description, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, " +
"{5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14})", "nodeID", "acronym", "name", "assemblyName", "typeName", "connectionString", "isLocal",
"measurementReportingInterval", "description", "loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (historian.NodeID != Guid.Empty) ? database.Guid(historian.NodeID) : database.CurrentNodeID(),
historian.Acronym.Replace(" ", "").ToUpper(), historian.Name.ToNotNull(), historian.AssemblyName.ToNotNull(), historian.TypeName.ToNotNull(),
historian.ConnectionString.ToNotNull(), database.Bool(historian.IsLocal), historian.MeasurementReportingInterval, historian.Description.ToNotNull(),
historian.LoadOrder, database.Bool(historian.Enabled), CommonFunctions.CurrentUser, database.UtcNow(),
CommonFunctions.CurrentUser, database.UtcNow());
}
else
{
query = database.ParameterizedQueryString("UPDATE Historian SET NodeID = {0}, Acronym = {1}, Name = {2}, AssemblyName = {3}, TypeName = {4}, " +
"ConnectionString = {5}, IsLocal = {6}, MeasurementReportingInterval = {7}, Description = {8}, LoadOrder = {9}, Enabled = {10}, " +
"UpdatedBy = {11}, UpdatedOn = {12} WHERE ID = {13}", "nodeID", "acronym", "name", "assemblyName", "typeName", "connectionString",
"isLocal", "measurementReportingInterval", "description", "loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (historian.NodeID != Guid.Empty) ? database.Guid(historian.NodeID) : database.CurrentNodeID(),
historian.Acronym.Replace(" ", "").ToUpper(), historian.Name.ToNotNull(), historian.AssemblyName.ToNotNull(), historian.TypeName.ToNotNull(),
historian.ConnectionString.ToNotNull(), database.Bool(historian.IsLocal), historian.MeasurementReportingInterval, historian.Description.ToNotNull(),
historian.LoadOrder, database.Bool(historian.Enabled), CommonFunctions.CurrentUser, database.UtcNow(), historian.ID);
}
return "Historian information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Measurement"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <param name="subscribedOnly">boolean flag to indicate if only subscribed measurements to be returned.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing PointID and SignalID of measurements defined in the database.</returns>
public static Dictionary<Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false, bool subscribedOnly = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<Guid, string> measurementList = new Dictionary<Guid, string>();
if (isOptional)
measurementList.Add(Guid.Empty, "Select Measurement");
DataTable measurementTable;
string query;
if (subscribedOnly)
{
// If subscribedOnly is set then return only those measurements which are not internal and subscribed flag is set to true.
query = database.ParameterizedQueryString("SELECT SignalID, PointTag FROM MeasurementDetail WHERE " +
"NodeID = {0} AND Internal = {1} AND Subscribed = {2} ORDER BY PointID", "nodeID", "internal", "subscribed");
measurementTable = database.Connection.RetrieveData(database.AdapterType, query,
DefaultTimeout, database.CurrentNodeID(), database.Bool(false), database.Bool(true));
}
else
{
query = database.ParameterizedQueryString("SELECT SignalID, PointTag FROM MeasurementDetail WHERE NodeID = {0} ORDER BY PointID", "nodeID");
measurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
}
foreach (DataRow row in measurementTable.Rows)
measurementList[database.Guid(row, "SignalID")] = row.Field<string>("PointTag");
return measurementList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves only subscribed <see cref="Measurement"/> collection.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns><see cref="ObservableCollection{T1}"/> type list of <see cref="Measurement"/>.</returns>
public static ObservableCollection<Measurement> GetSubscribedMeasurements(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<Measurement> measurementList = new ObservableCollection<Measurement>();
DataTable measurementTable;
string query;
query = database.ParameterizedQueryString("SELECT * FROM MeasurementDetail WHERE " +
"Subscribed = {0} ORDER BY PointTag", "subscribed");
measurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true));
foreach (DataRow row in measurementTable.Rows)
{
measurementList.Add(new Measurement()
{
SignalID = database.Guid(row, "SignalID"),
HistorianID = row.ConvertNullableField<int>("HistorianID"),
PointID = row.ConvertField<int>("PointID"),
DeviceID = row.ConvertNullableField<int>("DeviceID"),
PointTag = row.Field<string>("PointTag"),
AlternateTag = row.Field<string>("AlternateTag"),
SignalTypeID = row.ConvertField<int>("SignalTypeID"),
PhasorSourceIndex = row.ConvertNullableField<int>("PhasorSourceIndex"),
SignalReference = row.Field<string>("SignalReference"),
Adder = row.ConvertField<double>("Adder"),
Multiplier = row.ConvertField<double>("Multiplier"),
Internal = Convert.ToBoolean(row.Field<object>("Internal")),
Subscribed = Convert.ToBoolean(row.Field<object>("Subscribed")),
Description = row.Field<string>("Description"),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
m_historianAcronym = row.Field<string>("HistorianAcronym"),
m_deviceAcronym = row.Field<object>("DeviceAcronym") == null ? string.Empty : row.Field<string>("DeviceAcronym"),
m_signalName = row.Field<string>("SignalName"),
m_signalAcronym = row.Field<string>("SignalAcronym"),
m_signalSuffix = row.Field<string>("SignalTypeSuffix"),
m_phasorLabel = row.Field<string>("PhasorLabel"),
m_framesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30),
m_id = row.Field<string>("ID"),
m_companyAcronym = row.Field<object>("CompanyAcronym") == null ? string.Empty : row.Field<string>("CompanyAcronym"),
m_companyName = row.Field<object>("CompanyName") == null ? string.Empty : row.Field<string>("CompanyName"),
Selected = false
});
}
return measurementList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Node"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="node">Information about <see cref="Node"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Node node)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (node.ID == null || node.ID == Guid.Empty)
{
query = database.ParameterizedQueryString("INSERT INTO Node (Name, CompanyID, Longitude, Latitude, Description, ImagePath, Settings, MenuType, MenuData, " +
"Master, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, " +
"{13}, {14}, {15})", "name", "companyID", "longitude", "latitude", "description", "imagePath", "settings", "menuType", "menuData", "master",
"loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, node.Name, node.CompanyID.ToNotNull(), node.Longitude.ToNotNull(), node.Latitude.ToNotNull(),
node.Description.ToNotNull(), node.ImagePath.ToNotNull(), node.Settings.ToNotNull(), node.MenuType, node.MenuData, database.Bool(node.Master), node.LoadOrder,
database.Bool(node.Enabled), CommonFunctions.CurrentUser, database.UtcNow(), CommonFunctions.CurrentUser, database.UtcNow());
}
else
{
query = string.Format("SELECT Name FROM NodeDetail WHERE ID IN ('{0}')", node.ID);
DataTable nodeTable = database.Connection.RetrieveData(database.AdapterType, query);
query = string.Format("SELECT SignalIndex FROM Statistic WHERE Source = 'System'");
DataTable systemTable = database.Connection.RetrieveData(database.AdapterType, query);
query = database.ParameterizedQueryString("UPDATE Node SET Name = {0}, CompanyID = {1}, Longitude = {2}, Latitude = {3}, " +
"Description = {4}, ImagePath = {5}, Settings = {6}, MenuType = {7}, MenuData = {8}, Master = {9}, LoadOrder = {10}, Enabled = {11}, " +
"UpdatedBy = {12}, UpdatedOn = {13} WHERE ID = {14}", "name", "companyID", "longitude", "latitude", "description", "imagePath",
"Settings", "MenuType", "MenuData", "master", "loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, node.Name, node.CompanyID.ToNotNull(), node.Longitude.ToNotNull(), node.Latitude.ToNotNull(),
node.Description.ToNotNull(), node.ImagePath.ToNotNull(), node.Settings.ToNotNull(), node.MenuType, node.MenuData, database.Bool(node.Master), node.LoadOrder,
database.Bool(node.Enabled), CommonFunctions.CurrentUser, database.UtcNow(), database.Guid(node.ID));
if (nodeTable.Rows.Count > 0)
{
string newNodeName = node.Name
.RemoveCharacters(c => !char.IsLetterOrDigit(c))
.Replace(' ', '_')
.ToUpper();
string oldNodeName = nodeTable.Rows[0]["Name"].ToString()
.RemoveCharacters(c => !char.IsLetterOrDigit(c))
.Replace(' ', '_')
.ToUpper();
//SystemTable is read from the database.
for (int i = 0; i < systemTable.Rows.Count; i++)
{
string signalIndex = systemTable.Rows[i]["SignalIndex"].ToString();
string pointTag = string.Format("{0}!SYSTEM:ST{1}", newNodeName, signalIndex);
string newSignalReference = string.Format("{0}!SYSTEM-ST{1}", newNodeName, signalIndex);
string oldSignalReference = string.Format("{0}!SYSTEM-ST{1}", oldNodeName, signalIndex);
query = database.ParameterizedQueryString("UPDATE Measurement SET PointTag = {0}, SignalReference = {1} WHERE SignalReference = {2}", "name", "newSignalReference", "oldSignalReference");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, pointTag, newSignalReference, oldSignalReference);
}
}
}
return "Node information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Measurement"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="measurement">Information about <see cref="Measurement"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Measurement measurement)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (measurement.PointID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO Measurement (HistorianID, DeviceID, PointTag, AlternateTag, SignalTypeID, PhasorSourceIndex, " +
"SignalReference, Adder, Multiplier, Subscribed, Internal, Description, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, " +
"{3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16})", "historianID", "deviceID", "pointTag", "alternateTag", "signalTypeID",
"phasorSourceIndex", "signalReference", "adder", "multiplier", "subscribed", "internal", "description", "enabled", "updatedBy", "updatedOn",
"createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, measurement.HistorianID.ToNotNull(), measurement.DeviceID.ToNotNull(), measurement.PointTag,
measurement.AlternateTag.ToNotNull(), measurement.SignalTypeID, measurement.PhasorSourceIndex ?? measurement.PhasorSourceIndex.ToNotNull(), measurement.SignalReference,
measurement.Adder, measurement.Multiplier, database.Bool(measurement.Subscribed), database.Bool(measurement.Internal), measurement.Description.ToNotNull(),
database.Bool(measurement.Enabled), CommonFunctions.CurrentUser, database.UtcNow(), CommonFunctions.CurrentUser, database.UtcNow());
}
else
{
query = database.ParameterizedQueryString("Update Measurement Set HistorianID = {0}, DeviceID = {1}, PointTag = {2}, AlternateTag = {3}, " +
"SignalTypeID = {4}, PhasorSourceIndex = {5}, SignalReference = {6}, Adder = {7}, Multiplier = {8}, Description = {9}, Subscribed = {10}, " +
"Internal = {11}, Enabled = {12}, UpdatedBy = {13}, UpdatedOn = {14} Where PointID = {15}", "historianID", "deviceID", "pointTag",
"alternateTag", "signalTypeID", "phasorSourceINdex", "signalReference", "adder", "multiplier", "description", "subscribed", "internal",
"enabled", "updatedBy", "updatedOn", "pointID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, measurement.HistorianID.ToNotNull(), measurement.DeviceID.ToNotNull(), measurement.PointTag,
measurement.AlternateTag.ToNotNull(), measurement.SignalTypeID, measurement.PhasorSourceIndex ?? measurement.PhasorSourceIndex.ToNotNull(), measurement.SignalReference,
measurement.Adder, measurement.Multiplier, measurement.Description.ToNotNull(), database.Bool(measurement.Subscribed), database.Bool(measurement.Internal),
database.Bool(measurement.Enabled), CommonFunctions.CurrentUser, database.UtcNow(), measurement.PointID);
}
return "Measurement information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// 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>
/// Handles auto-connection metadata synchronization to local system.
/// </summary>
/// <param name="state"><see cref="DataSet"/> metadata collection passed into state parameter.</param>
/// <remarks>
/// This function is normally called from thread pool since synchronization can take some time.
/// </remarks>
protected virtual void SynchronizeMetadata(object state)
{
const int MetadataSynchronizationTimeout = 0;
try
{
DataSet metadata = state as DataSet;
if ((object)metadata != null)
{
bool dataMonitoringEnabled = false;
// 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;
// Open the configuration database using settings found in the config file
using (AdoDataConnection database = new AdoDataConnection("systemSettings"))
using (IDbCommand command = database.Connection.CreateCommand())
using (IDbTransaction transaction = database.Connection.BeginTransaction())
{
try
{
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(string.Format("SELECT SourceID FROM Runtime WHERE ID = {0} AND SourceTable='Device'", ID), MetadataSynchronizationTimeout));
// Validate that the subscriber device is marked as a concentrator (we are about to associate children devices with it)
if (!command.ExecuteScalar(string.Format("SELECT IsConcentrator FROM Device WHERE ID = {0}", parentID), MetadataSynchronizationTimeout).ToString().ParseBoolean())
command.ExecuteNonQuery(string.Format("UPDATE Device SET IsConcentrator = 1 WHERE ID = {0}", parentID), MetadataSynchronizationTimeout);
// Get any historian associated with the subscriber device
object historianID = command.ExecuteScalar(string.Format("SELECT HistorianID FROM Device WHERE ID = {0}", parentID), 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(string.Format("SELECT NodeID FROM IaonInputAdapter WHERE ID = {0}", ID), 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'", MetadataSynchronizationTimeout).ToString());
// 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.InvariantCultureIgnoreCase);
string selectSql, insertSql, updateSql, deleteSql, deviceAcronym, signalTypeAcronym;
int deviceID;
// Check to see if data for the "DeviceDetail" table was included in the meta-data
if (metadata.Tables.Contains("DeviceDetail"))
{
List<Guid> uniqueIDs = new List<Guid>();
foreach (DataRow row in metadata.Tables["DeviceDetail"].Rows)
{
Guid uniqueID = Guid.Parse(row.Field<object>("UniqueID").ToString()); // adoDatabase.Guid(row, "UniqueID"); // row.Field<Guid>("UniqueID");
// 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);
// We will synchronize metadata 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())
{
// Define query to determine if this device is already defined (this should always be based on the unique device Guid)
selectSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE UniqueID = {0}", "deviceGuid");
if (Convert.ToInt32(command.ExecuteScalar(selectSql, MetadataSynchronizationTimeout, database.Guid(uniqueID))) == 0)
{
// Insert new device record
insertSql = database.ParameterizedQueryString("INSERT INTO Device(NodeID, ParentID, HistorianID, Acronym, Name, ProtocolID, IsConcentrator, Enabled, OriginalSource) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, 0, 1, {6})", "nodeID", "parentID", "historianID", "acronym", "name", "protocolID", "originalSource");
command.ExecuteNonQuery(insertSql, MetadataSynchronizationTimeout, database.Guid(m_nodeID), parentID, historianID, sourcePrefix + row.Field<string>("Acronym"), row.Field<string>("Name"), m_gatewayProtocolID,
m_internal ? (object)DBNull.Value : string.IsNullOrEmpty(row.Field<string>("ParentAcronym")) ? sourcePrefix + row.Field<string>("Acronym") : sourcePrefix + row.Field<string>("ParentAcronym"));
// 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.
updateSql = database.ParameterizedQueryString("UPDATE Device SET UniqueID = {0} WHERE Acronym = {1}", "uniqueID", "acronym");
command.ExecuteNonQuery(updateSql, MetadataSynchronizationTimeout, database.Guid(uniqueID), sourcePrefix + row.Field<string>("Acronym"));
}
else
{
selectSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE UniqueID = {0} AND (ParentID <> {1} OR ParentID IS NULL)", "deviceGuid", "parentID");
// Update existing device record
if (Convert.ToInt32(command.ExecuteScalar(selectSql, MetadataSynchronizationTimeout, database.Guid(uniqueID), parentID)) > 0)
continue;
if (m_internal)
{
// 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.
updateSql = database.ParameterizedQueryString("UPDATE Device SET Acronym = {0}, Name = {1}, OriginalSource = {2}, ProtocolID = {3}, HistorianID = {4} WHERE UniqueID = {5}", "acronym", "name", "originalSource", "protocolID", "historianID", "uniqueID");
command.ExecuteNonQuery(updateSql, MetadataSynchronizationTimeout, sourcePrefix + row.Field<string>("Acronym"), row.Field<string>("Name"), (object)DBNull.Value, m_gatewayProtocolID, historianID, database.Guid(uniqueID));
}
else
{
// 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.
updateSql = database.ParameterizedQueryString("UPDATE Device SET Acronym = {0}, Name = {1}, ProtocolID = {2}, HistorianID = {3} WHERE UniqueID = {4}", "acronym", "name", "protocolID", "historianID", "uniqueID");
command.ExecuteNonQuery(updateSql, MetadataSynchronizationTimeout, sourcePrefix + row.Field<string>("Acronym"), row.Field<string>("Name"), m_gatewayProtocolID, historianID, database.Guid(uniqueID));
}
}
}
// Capture local device ID auto-inc value for measurement association
selectSql = database.ParameterizedQueryString("SELECT ID FROM Device WHERE UniqueID = {0}", "deviceGuid");
deviceIDs[row.Field<string>("Acronym")] = Convert.ToInt32(command.ExecuteScalar(selectSql, MetadataSynchronizationTimeout, database.Guid(uniqueID)));
}
// Remove any device records associated with this subscriber that no longer exist in the meta-data
if (uniqueIDs.Count > 0)
{
deleteSql = string.Format("DELETE FROM Device WHERE ParentID = {0} AND UniqueID NOT IN ({1})", parentID, uniqueIDs.Select(uniqueID => string.Format("'{0}'", uniqueID.ToString().ToLower())).ToDelimitedString(", "));
command.ExecuteNonQuery(deleteSql, MetadataSynchronizationTimeout);
}
}
// Check to see if data for the "MeasurementDetail" table was included in the meta-data
if (metadata.Tables.Contains("MeasurementDetail"))
{
List<Guid> signalIDs = new List<Guid>();
DataRow[] measurementRows;
// 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.InvariantCultureIgnoreCase);
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");
}
foreach (DataRow row in metadata.Tables["MeasurementDetail"].Rows)
{
// Get device and signal type acronyms
deviceAcronym = row.Field<string>("DeviceAcronym") ?? string.Empty;
signalTypeAcronym = row.Field<string>("SignalAcronym") ?? string.Empty;
// 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))
{
// Prefix the tag name with the "updated" device name
deviceID = deviceIDs[deviceAcronym];
string pointTag = sourcePrefix + row.Field<string>("PointTag");
Guid signalID = Guid.Parse(row.Field<object>("SignalID").ToString()); // adoDatabase.Guid(row, "SignalID"); // row.Field<Guid>("SignalID");
// 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);
// Define query to determine if this measurement is already defined (this should always be based on the unique signal ID Guid)
selectSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Measurement WHERE SignalID = {0}", "signalID");
if (Convert.ToInt32(command.ExecuteScalar(selectSql, MetadataSynchronizationTimeout, database.Guid(signalID))) == 0)
{
string alternateTag = Guid.NewGuid().ToString();
// Insert new measurement record
insertSql = database.ParameterizedQueryString("INSERT INTO Measurement (DeviceID, HistorianID, PointTag, AlternateTag, SignalTypeID, SignalReference, Description, Internal, Subscribed, Enabled) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, 0, 1)", "deviceID", "historianID", "pointTag", "alternateTag", "signalTypeID", "signalReference", "description", "internal");
command.ExecuteNonQuery(insertSql, MetadataSynchronizationTimeout, deviceID, historianID, pointTag, alternateTag, signalTypeIDs[signalTypeAcronym], 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.
updateSql = database.ParameterizedQueryString("UPDATE Measurement SET SignalID = {0}, AlternateTag = NULL WHERE AlternateTag = {1}", "signalID", "alternateTag");
command.ExecuteNonQuery(updateSql, MetadataSynchronizationTimeout, database.Guid(signalID), alternateTag);
}
else
{
// Update existing measurement record. Note that this update assumes that measurements will remain associated with a static source device.
updateSql = database.ParameterizedQueryString("UPDATE Measurement SET HistorianID = {0}, PointTag = {1}, SignalTypeID = {2}, SignalReference = {3}, Description = {4}, Internal = {5} WHERE SignalID = {6}", "historianID", "pointTag", "signalTypeID", "signalReference", "description", "internal", "signalID");
command.ExecuteNonQuery(updateSql, MetadataSynchronizationTimeout, historianID, pointTag, signalTypeIDs[signalTypeAcronym], sourcePrefix + row.Field<string>("SignalReference"), row.Field<string>("Description") ?? string.Empty, database.Bool(m_internal), database.Guid(signalID));
}
}
}
// Remove any measurement records associated with existing devices in this session but no longer exist in the meta-data
if (deviceIDs.Count > 0 && signalIDs.Count > 0)
{
deleteSql = string.Format("DELETE FROM Measurement WHERE DeviceID IN ({0}) AND SignalID NOT IN ({1})", deviceIDs.Values.ToDelimitedString(", "), signalIDs.Select(uniqueID => string.Format("'{0}'", uniqueID.ToString())).ToDelimitedString(", "));
command.ExecuteNonQuery(deleteSql, MetadataSynchronizationTimeout);
}
}
// 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 property 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.
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))
{
deviceID = deviceIDs[deviceAcronym];
// Define query to determine if this phasor record is already defined, this is no Guid for these simple label records
selectSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Phasor WHERE DeviceID = {0} AND SourceIndex = {1}", "deviceID", "sourceIndex");
if (Convert.ToInt32(command.ExecuteScalar(selectSql, MetadataSynchronizationTimeout, deviceID, row.ConvertField<int>("SourceIndex"))) == 0)
{
// Insert new phasor record
insertSql = database.ParameterizedQueryString("INSERT INTO Phasor (DeviceID, Label, Type, Phase, SourceIndex) VALUES ({0}, {1}, {2}, {3}, {4})", "deviceID", "label", "type", "phase", "sourceIndex");
command.ExecuteNonQuery(insertSql, 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
{
// Update existing phasor record
updateSql = database.ParameterizedQueryString("UPDATE Phasor SET Label = {0}, Type = {1}, Phase = {2} WHERE DeviceID = {3} AND SourceIndex = {4}", "label", "type", "phase", "deviceID", "sourceIndex");
command.ExecuteNonQuery(updateSql, 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");
}
}
// 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)
{
deleteSql = string.Format("DELETE FROM Phasor WHERE DeviceID = {0} AND SourceIndex > {1}", deviceIndexPair.Key, deviceIndexPair.Value);
command.ExecuteNonQuery(deleteSql, MetadataSynchronizationTimeout);
}
}
}
transaction.Commit();
}
catch (Exception ex)
{
OnProcessException(new InvalidOperationException("Failed to synchronize meta-data to local cache: " + ex.Message, ex));
try
{
transaction.Rollback();
}
catch (Exception rollbackException)
{
OnProcessException(new InvalidOperationException("Failed to roll back database transaction due to exception: " + rollbackException.Message, rollbackException));
}
return;
}
}
// 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);
OnStatusMessage("Meta-data synchronization completed successfully in {0}", (DateTime.UtcNow.Ticks - startTime).ToElapsedTimeString(3));
// Restart data stream monitor after meta-data synchronization if it was originally enabled
if (dataMonitoringEnabled && (object)m_dataStreamMonitor != null)
m_dataStreamMonitor.Enabled = true;
}
else
{
OnStatusMessage("WARNING: Meta-data synchronization was not performed, deserialized dataset was empty.");
}
}
catch (Exception ex)
{
OnProcessException(new InvalidOperationException("Failed to synchronize meta-data to local cache: " + ex.Message, ex));
}
}
static int Main(string[] args)
{
// System settings
ConfigurationFile configFile = ConfigurationFile.Current;
CategorizedSettingsElementCollection systemSettings = configFile.Settings["systemSettings"];
systemSettings.Add("NodeID", Guid.NewGuid().ToString(), "Unique Node ID");
Guid nodeID = systemSettings["NodeID"].ValueAs<Guid>();
string connectionString = systemSettings["ConnectionString"].Value;
string nodeIDQueryString = null;
string parameterizedQuery;
// Define guid with query string delimeters according to database needs
Dictionary<string, string> settings = connectionString.ParseKeyValuePairs();
string setting;
if (settings.TryGetValue("Provider", out setting))
{
// Check if provider is for Access since it uses braces as Guid delimeters
if (setting.StartsWith("Microsoft.Jet.OLEDB", StringComparison.OrdinalIgnoreCase))
nodeIDQueryString = "{" + nodeID + "}";
}
if (string.IsNullOrWhiteSpace(nodeIDQueryString))
nodeIDQueryString = "'" + nodeID + "'";
AdoDataConnection database = new AdoDataConnection("systemSettings");
IDbConnection connection = database.Connection;
if (Convert.ToInt32(connection.ExecuteScalar("SELECT COUNT(*) FROM Protocol WHERE Acronym='WAV'")) == 0)
{
try
{
connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, [Type], Category, AssemblyName, TypeName) VALUES('WAV', 'Wave Form Input Adapter', 'Frame', 'Audio', 'WavInputAdapter.dll', 'WavInputAdapter.WavInputAdapter')");
}
catch (Exception ex)
{
if (ex.GetType().Name.ToLower().Contains("mysql"))
connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName) VALUES('WAV', 'Wave Form Input Adapter', 'Frame', 'Audio', 'WavInputAdapter.dll', 'WavInputAdapter.WavInputAdapter')");
else
throw ex;
}
}
int protocolID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM Protocol WHERE Acronym='WAV'"));
int signalTypeID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym='ALOG'"));
string pathRoot = FilePath.GetDirectoryName((args.Length > 0) ? args[0] : systemSettings["MusicDirectory"].Value);
string sourcePath = pathRoot + "*\\*.wav";
foreach (string sourceFileName in FilePath.GetFileList(sourcePath))
{
WaveFile sourceWave = null;
string fileName = FilePath.GetFileName(sourceFileName);
char[] invalidChars = new char[] { '\'', '[', ']', '(', ')', ',', '-', '.' };
Console.WriteLine("Loading metadata for \"{0}\"...\r\n", fileName);
sourceWave = WaveFile.Load(sourceFileName, false);
fileName = FilePath.GetFileNameWithoutExtension(fileName).RemoveDuplicateWhiteSpace().RemoveCharacters(c => invalidChars.Contains(c)).Trim();
string acronym = fileName.Replace(' ', '_').ToUpper() + "_" + (int)(sourceWave.SampleRate / SI.Kilo) + "KHZ";
string name = GenerateSongName(sourceWave, fileName);
Console.WriteLine(" Acronym = {0}", acronym);
Console.WriteLine(" Name = {0}", name);
Console.WriteLine("");
// Check to see if device exists
if (Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE Acronym = {0}", "acronym"), acronym)) == 0)
{
parameterizedQuery = database.ParameterizedQueryString("INSERT INTO Device(NodeID, Acronym, Name, ProtocolID, FramesPerSecond, " +
"MeasurementReportingInterval, ConnectionString, Enabled) VALUES(" + nodeIDQueryString + ", {0}, {1}, {2}, {3}, {4}, {5}, {6})",
"acronym", "name", "protocolID", "framesPerSecond", "measurementReportingInterval",
"connectionString", "enabled");
// Insert new device record
connection.ExecuteNonQuery(parameterizedQuery, acronym, name, protocolID, sourceWave.SampleRate, 1000000, string.Format("wavFileName={0}; connectOnDemand=true; outputSourceIDs={1}", FilePath.GetAbsolutePath(sourceFileName), acronym), database.Bool(true));
int deviceID = Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT ID FROM Device WHERE Acronym = {0}", "acronym"), acronym));
string pointTag;
// Add a measurement for each defined wave channel
for (int i = 0; i < sourceWave.Channels; i++)
{
int index = i + 1;
pointTag = acronym + ":WAVA" + index;
parameterizedQuery = database.ParameterizedQueryString("INSERT INTO Measurement(DeviceID, PointTag, SignalTypeID, SignalReference, Description, " +
"Enabled) VALUES({0}, {1}, {2}, {3}, {4}, {5})", "deviceID", "pointTag", "signalTypeID", "signalReference", "description", "enabled");
// Insert new measurement record
connection.ExecuteNonQuery(parameterizedQuery, (object)deviceID, pointTag, signalTypeID, acronym + "-AV" + index, name + " - channel " + index, database.Bool(true));
index = Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT PointID FROM Measurement WHERE PointTag = {0}", "pointTag"), pointTag));
}
// Disable all non analog measurements that may be associated with this device
connection.ExecuteNonQuery(database.ParameterizedQueryString("UPDATE Measurement SET Enabled = {0} WHERE DeviceID = {1} AND SignalTypeID <> {2}", "enabled", "deviceID", "signalTypeID"), database.Bool(false), deviceID, signalTypeID);
}
}
connection.Close();
return 0;
}
/// <summary>
/// Saves <see cref="Adapter"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="adapter">Information about <see cref="Adapter"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Adapter adapter)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string tableName;
if (adapter.Type == AdapterType.Action)
tableName = "CustomActionAdapter";
else if (adapter.Type == AdapterType.Input)
tableName = "CustomInputAdapter";
else
tableName = "CustomOutputAdapter";
if (adapter.ID == 0)
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("INSERT INTO " + tableName + " (NodeID, AdapterName, AssemblyName, TypeName, ConnectionString, LoadOrder, " +
"Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10})",
"nodeID", "adapterName", "assemblyName", "typeName", "connectionString", "loadOrder",
"enabled", "updatedBy", "updatedOn", "createdBy", "createdOn"), DefaultTimeout,
(adapter.NodeID != Guid.Empty) ? database.Guid(adapter.NodeID) : database.CurrentNodeID(), adapter.AdapterName, adapter.AssemblyName,
adapter.TypeName, adapter.ConnectionString.ToNotNull(), adapter.LoadOrder, database.Bool(adapter.Enabled), CommonFunctions.CurrentUser,
database.UtcNow(), CommonFunctions.CurrentUser,
database.UtcNow());
else
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("UPDATE " + tableName + " SET NodeID = {0}, AdapterName = {1}, AssemblyName = {2}, " +
"TypeName = {3}, ConnectionString = {4}, LoadOrder = {5}, Enabled = {6}, UpdatedBy = {7}, " +
"UpdatedOn = {8} WHERE ID = {9}", "nodeID", "adapterName", "assemblyName", "typeName", "connectionString",
"loadOrder", "enabled", "updatedBy", "updatedOn", "id"), DefaultTimeout, (adapter.NodeID != Guid.Empty) ? database.Guid(adapter.NodeID) : database.CurrentNodeID(),
adapter.AdapterName, adapter.AssemblyName, adapter.TypeName, adapter.ConnectionString.ToNotNull(), adapter.LoadOrder, database.Bool(adapter.Enabled),
CommonFunctions.CurrentUser, database.UtcNow(), adapter.ID);
return "Adapter information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Adapter"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="adapterType">Type of the <see cref="Adapter"/>.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of adapters defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, AdapterType adapterType, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> adapterList = new Dictionary<int, string>();
if (isOptional)
adapterList.Add(0, "Select Adapter");
string tableName;
if (adapterType == AdapterType.Action)
tableName = "CustomActionAdapter";
else if (adapterType == AdapterType.Input)
tableName = "CustomInputAdapter";
else
tableName = "CustomOutputAdapter";
string query = database.ParameterizedQueryString("SELECT ID, Name FROM " + tableName + " WHERE Enabled = {0} ORDER BY LoadOrder", "enabled");
DataTable adapterTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true));
foreach (DataRow row in adapterTable.Rows)
adapterList[row.ConvertField<int>("ID")] = row.Field<string>("Name");
return adapterList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}