/// <summary>
/// Saves <see cref="Vendor"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="vendor">Information about <see cref="Vendor"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Vendor vendor)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (vendor.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO Vendor (Acronym, Name, PhoneNumber, ContactEmail, URL, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8})", "acronym", "name", "phoneNumber", "contactEmail", "url", "updatedBy", "updatedOn", "createdBy",
"createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, vendor.Acronym.Replace(" ", "").ToUpper(),
vendor.Name, vendor.PhoneNumber.ToNotNull(), vendor.ContactEmail.ToNotNull(), vendor.URL.ToNotNull(), CommonFunctions.CurrentUser, database.UtcNow(),
CommonFunctions.CurrentUser, database.UtcNow());
}
else
{
query = database.ParameterizedQueryString("UPDATE Vendor SET Acronym = {0}, Name = {1}, PhoneNumber = {2}, ContactEmail = {3}, " +
"URL = {4}, UpdatedBy = {5}, UpdatedOn = {6} WHERE ID = {7}", "acronym", "name", "phoneNumber", "contactEmail", "url", "updatedBy",
"updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, vendor.Acronym.Replace(" ", "").ToUpper(), vendor.Name,
vendor.PhoneNumber.ToNotNull(), vendor.ContactEmail.ToNotNull(), vendor.URL.ToNotNull(), CommonFunctions.CurrentUser, database.UtcNow(), vendor.ID);
}
return "Vendor information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static Methods
/// <summary>
/// Loads <see cref="Historian"/> IDs as an <see cref="IList{T}"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="sortMember">The field to sort by.</param>
/// <param name="sortDirection"><c>ASC</c> or <c>DESC</c> for ascending or descending respectively.</param>
/// <returns>Collection of <see cref="Int32"/>.</returns>
public static IList<int> LoadKeys(AdoDataConnection database, string sortMember = "", string sortDirection = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList<int> historianList = new List<int>();
string sortClause = string.Empty;
DataTable historianTable;
string query;
if (!string.IsNullOrEmpty(sortMember) || !string.IsNullOrEmpty(sortDirection))
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
query = database.ParameterizedQueryString(string.Format("SELECT ID FROM HistorianDetail WHERE NodeID = {{0}} {0}", sortClause), "nodeID");
historianTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in historianTable.Rows)
{
historianList.Add(row.ConvertField<int>("ID"));
}
return historianList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Historian"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <param name="includeStatHistorian">Indicates if statistical historian included in the collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of historians defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, bool isOptional = false, bool includeStatHistorian = true)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> historianList = new Dictionary<int, string>();
if (isOptional)
historianList.Add(0, "Select Historian");
string query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Historian WHERE Enabled = {0} AND NodeID = {1} ORDER BY LoadOrder", "enabled", "nodeID");
DataTable historianTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID());
foreach (DataRow row in historianTable.Rows)
{
if (!includeStatHistorian)
{
if (row.Field<string>("Acronym").ToUpper() != "STAT")
historianList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym");
}
else
historianList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym");
}
return historianList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Loads <see cref="Measurement"/> signal IDs as an <see cref="IList{T}"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="filterExpression">SQL expression by which to filter the data coming from the database.</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="Guid"/>.</returns>
/// <remarks>
/// This method does not validate <paramref name="filterExpression"/> for SQL injection.
/// Developers should validate their inputs before entering a filter expression.
/// </remarks>
public static List<Guid> LoadSignalIDs(AdoDataConnection database, string filterExpression = "", string searchText = "", string sortMember = "", string sortDirection = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
List<Guid> signalIDList = new List<Guid>();
DataTable measurementTable;
string query;
object[] parameters;
string searchParam = null;
string searchQuery = string.Empty;
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("DESCRIPTION LIKE '{0}' OR DeviceID LIKE '{0}' OR SignalReference LIKE '{0}' " +
"OR SignalAcronym LIKE '{0}' OR SignalID LIKE '{0}' OR PointTag LIKE '{0}' OR CompanyName LIKE '{0}' " +
"OR CompanyAcronym LIKE '{0}'OR DeviceAcronym LIKE '{0}' OR SignalName LIKE '{0}' OR ID LIKE '{0}'", searchParam.Replace("'", "''"));
}
else
{
searchQuery = database.ParameterizedQueryString("UPPER(DESCRIPTION) LIKE UPPER({0}) OR DeviceID LIKE {0} OR UPPER(SignalReference) LIKE UPPER({0}) " +
"OR UPPER(SignalAcronym) LIKE UPPER({0}) OR UPPER(SignalID) LIKE UPPER({0}) OR UPPER(PointTag) LIKE UPPER({0}) OR UPPER(CompanyName) LIKE UPPER({0}) " +
"OR UPPER(CompanyAcronym) LIKE UPPER({0}) OR UPPER(DeviceAcronym) LIKE UPPER({0}) OR UPPER(SignalName) LIKE UPPER({0}) OR UPPER(ID) LIKE UPPER({0})", "searchParam");
}
}
if (!string.IsNullOrEmpty(sortMember) || !string.IsNullOrEmpty(sortDirection))
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
if (!string.IsNullOrEmpty(filterExpression) && !string.IsNullOrEmpty(searchText))
query = string.Format("SELECT SignalID FROM MeasurementDetail WHERE ({0}) AND ({1}) {2}", filterExpression, searchQuery, sortClause);
else if (!string.IsNullOrEmpty(filterExpression))
query = string.Format("SELECT SignalID FROM MeasurementDetail WHERE ({0}) {1}", filterExpression, sortClause);
else if (!string.IsNullOrEmpty(searchText))
query = string.Format("SELECT SignalID FROM MeasurementDetail WHERE ({0}) {1}", searchQuery, sortClause);
else
query = string.Format("SELECT SignalID FROM MeasurementDetail {0}", sortClause);
parameters = !string.IsNullOrEmpty(searchText) && !database.IsJetEngine ? new object[] { searchParam } : new object[0];
measurementTable = database.Connection.RetrieveData(database.AdapterType, query, parameters);
foreach (DataRow row in measurementTable.Rows)
{
signalIDList.Add(database.Guid(row, "SignalID"));
}
return signalIDList;
}
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>
/// 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 specified <see cref="Measurement"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="signalID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, Guid signalID)
{
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 Measurement WHERE SignalID = {0}", "signalID"), DefaultTimeout, database.Guid(signalID));
return "Measurement deleted successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Deletes specified <see cref="VendorDevice"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="vendorDeviceID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, int vendorDeviceID)
{
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 VendorDevice WHERE ID = {0}", "vendorDeviceID"), DefaultTimeout, vendorDeviceID);
return "Vendor Device deleted successfully";
}
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));
}
}
/// <summary>
/// Loads <see cref="IaonTree"/> 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="IaonTree"/>.</returns>
public static ObservableCollection<IaonTree> Load(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<IaonTree> iaonTreeList;
DataTable rootNodesTable = new DataTable();
rootNodesTable.Columns.Add(new DataColumn("AdapterType", Type.GetType("System.String")));
DataRow row;
row = rootNodesTable.NewRow();
row["AdapterType"] = "Input Adapters";
rootNodesTable.Rows.Add(row);
row = rootNodesTable.NewRow();
row["AdapterType"] = "Action Adapters";
rootNodesTable.Rows.Add(row);
row = rootNodesTable.NewRow();
row["AdapterType"] = "Output Adapters";
rootNodesTable.Rows.Add(row);
DataSet resultSet = new DataSet();
resultSet.Tables.Add(rootNodesTable);
DataTable iaonTreeTable = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT * FROM IaonTreeView WHERE NodeID = {0}", "nodeID"), database.CurrentNodeID());
resultSet.EnforceConstraints = false;
resultSet.Tables.Add(iaonTreeTable.Copy());
resultSet.Tables[0].TableName = "RootNodesTable";
resultSet.Tables[1].TableName = "AdapterData";
iaonTreeList = new ObservableCollection<IaonTree>(from item in resultSet.Tables["RootNodesTable"].AsEnumerable()
select new IaonTree()
{
m_adapterType = item.Field<string>("AdapterType"),
m_adapterList = new ObservableCollection<Adapter>(from obj in resultSet.Tables["AdapterData"].AsEnumerable()
where obj.Field<string>("AdapterType") == item.Field<string>("AdapterType")
select new Adapter()
{
NodeID = database.Guid(obj, "NodeID"),
ID = obj.ConvertField<int>("ID"),
AdapterName = obj.Field<string>("AdapterName"),
AssemblyName = obj.Field<string>("AssemblyName"),
TypeName = obj.Field<string>("TypeName"),
ConnectionString = obj.Field<string>("ConnectionString")
})
});
return iaonTreeList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="VendorDevice"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="vendorDevice">Information about <see cref="VendorDevice"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, VendorDevice vendorDevice)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (vendorDevice.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO VendorDevice (VendorID, Name, Description, URL, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " +
"Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})", "vendorID", "name", "description", "url", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, vendorDevice.VendorID, vendorDevice.Name, vendorDevice.Description.ToNotNull(),
vendorDevice.URL.ToNotNull(), CommonFunctions.CurrentUser, database.UtcNow(), CommonFunctions.CurrentUser, database.UtcNow());
}
else
{
query = database.ParameterizedQueryString("UPDATE VendorDevice SET VendorID = {0}, Name = {1}, Description = {2}, URL = {3}, UpdatedBy = {4}, " +
"UpdatedOn = {5} WHERE ID = {6}", "vendorID", "name", "description", "url", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, vendorDevice.VendorID, vendorDevice.Name, vendorDevice.Description.ToNotNull(),
vendorDevice.URL.ToNotNull(), CommonFunctions.CurrentUser, database.UtcNow(), vendorDevice.ID);
}
return "Vendor Device information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Stores exception in the database
/// </summary>
/// <param name="connection"><see cref="AdoDataConnection"/> object to connect to database</param>
/// <param name="source">Source of exception</param>
/// <param name="ex">Exception to be logged</param>
public static void LogException(AdoDataConnection connection, string source, Exception ex)
{
bool createdConnection = false;
try
{
string query;
createdConnection = DataModelBase.CreateConnection(ref connection);
query = connection.ParameterizedQueryString("INSERT INTO ErrorLog (Source, Message, Detail) VALUES ({0}, {1}, {2})", "source", "message", "detail");
connection.Connection.ExecuteNonQuery(query, DataModelBase.DefaultTimeout, source, ex.Message, ex.ToString());
}
catch
{
//Do nothing. Don't worry about it
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
/// <summary>
/// Returns min and max point id values for a given node.
/// </summary>
/// <param name="connection"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="nodeID">ID of the node to filter data.</param>
/// <returns>KeyValuePair containing min and max point id.</returns>
public static KeyValuePair<int?, int?> GetMinMaxPointIDs(AdoDataConnection connection, Guid nodeID)
{
KeyValuePair<int?, int?> minMaxPointIDs = new KeyValuePair<int?, int?>(1, 5000);
bool createdConnection = false;
try
{
createdConnection = DataModelBase.CreateConnection(ref connection);
string query = connection.ParameterizedQueryString("SELECT MIN(PointID) AS MinPointID, MAX(PointID) AS MaxPointID FROM MeasurementDetail WHERE NodeID = {0}", "nodeID");
DataTable results = connection.Connection.RetrieveData(connection.AdapterType, query, connection.Guid(nodeID));
foreach (DataRow row in results.Rows)
{
minMaxPointIDs = new KeyValuePair<int?, int?>(row.ConvertNullableField<int>("MinPointID"), row.ConvertNullableField<int>("MaxPointID"));
}
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
return minMaxPointIDs;
}
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>
/// 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>
/// Logs information about an encountered exception to the backend datastore.
/// </summary>
/// <param name="source">Source of exception.</param>
/// <param name="message">Message section of the exception.</param>
/// <param name="detail">Detailed description of exception.</param>
/// <param name="type">Type of exception.</param>
public static void LogErrorToDatabase(String source, String message, String detail, String type)
{
try
{
string parameterizedQueryString;
using (AdoDataConnection dbConnection = new AdoDataConnection("systemSettings"))
{
// Remove old entry for keeping table size limited to 2000.
RestoreLogSize(dbConnection.Connection);
parameterizedQueryString = dbConnection.ParameterizedQueryString("INSERT INTO ErrorLog (Source, Type, Message, Detail) VALUES ({0}, {1}, {2}, {3})", "source", "type", "message", "detail");
dbConnection.Connection.ExecuteNonQuery(parameterizedQueryString, source, type, message, detail);
}
}
catch
{
// Do nothing, if exceptions are raised during error logging.
}
}
/// <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>
/// Retrieves collection of <see cref="UserAccount"/>s currently 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 assigned to <see cref="SecurityGroup"/>.</returns>
public static Dictionary<Guid, string> GetCurrentUsers(AdoDataConnection database, Guid groupID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<Guid, string> currentUsers = new Dictionary<Guid, string>();
DataTable currentUsersTable = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT * FROM SecurityGroupUserAccountDetail WHERE SecurityGroupID = {0} ORDER BY UserName", "groupID"), groupID);
foreach (DataRow row in currentUsersTable.Rows)
currentUsers[database.Guid(row, "UserAccountID")] = row.Field<string>("UserName");
return currentUsers;
}
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>
/// 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, groupID);
foreach (DataRow row in possibleUsersTable.Rows)
possibleGroupUsers[database.Guid(row, "ID")] = row.Field<string>("Name");
return possibleGroupUsers;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves statistic measurements for output stream.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamAcronym">Acronym of the output stream to filter data.</param>
/// <returns><see cref="ObservableCollection{T}"/> type collection of Measurement.</returns>
public static ObservableCollection<Measurement> GetOutputStatisticMeasurements(AdoDataConnection database, string outputStreamAcronym)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<Measurement> measurementList = new ObservableCollection<Measurement>();
string query = database.ParameterizedQueryString("SELECT * FROM StatisticMeasurement WHERE NodeID = {0} AND DeviceID IS NULL ORDER BY SignalReference", "nodeID");
DataTable measurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in measurementTable.Rows)
{
if (row.Field<string>("SignalAcronym") == "STAT") // Just one more filter.
{
bool continueProcess = false;
if (!string.IsNullOrEmpty(outputStreamAcronym))
{
if (row.Field<string>("SignalReference").StartsWith(outputStreamAcronym + "!OS"))
continueProcess = true;
}
else
{
continueProcess = true;
}
if (continueProcess)
{
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 = row.ConvertField<bool>("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>
/// 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"), id).ToNonNullString();
securityGroupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), 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}\".", userName, securityGroupName, CommonFunctions.CurrentUser), 9);
}
return "User accounts deleted from group successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Loads information about <see cref="Measurement"/> assigned to Subscriber as <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberId">ID of the Subscriber to filter data.</param>
/// <returns>Collection of <see cref="Measurement"/>.</returns>
public static ObservableCollection<Measurement> GetMeasurementsBySubscriber(AdoDataConnection database, Guid subscriberId)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
if (subscriberId == null || subscriberId == Guid.Empty)
return Load(database);
ObservableCollection<Measurement> possibleMeasurements = new ObservableCollection<Measurement>();
string query = database.ParameterizedQueryString("SELECT * FROM MeasurementDetail WHERE SignalID NOT IN " +
"(SELECT SignalID FROM SubscriberMeasurement WHERE SubscriberID = {0}) ORDER BY PointTag", "subscriberID");
DataTable possibleMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberId));
foreach (DataRow row in possibleMeasurementTable.Rows)
{
possibleMeasurements.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_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 possibleMeasurements;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="SecurityGroup"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="securityGroup">Information about <see cref="SecurityGroup"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, SecurityGroup securityGroup)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (securityGroup.ID == Guid.Empty)
{
query = database.ParameterizedQueryString("INSERT INTO SecurityGroup (Name, Description, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, " +
"{2}, {3}, {4}, {5})", "name", "description", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, securityGroup.Name, securityGroup.Description.ToNotNull(),
CommonFunctions.CurrentUser, database.UtcNow(), CommonFunctions.CurrentUser, database.UtcNow());
CommonFunctions.LogEvent(string.Format("Security group \"{0}\" created successfully by user \"{1}\".", securityGroup.Name, CommonFunctions.CurrentUser), 6);
}
else
{
query = database.ParameterizedQueryString("UPDATE SecurityGroup SET Name = {0}, Description = {1}, UpdatedBy = {2}, UpdatedOn = {3} " +
"WHERE ID = {4}", "name", "description", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, securityGroup.Name, securityGroup.Description.ToNotNull(),
CommonFunctions.CurrentUser, database.UtcNow(), database.Guid(securityGroup.ID));
CommonFunctions.LogEvent(string.Format("Information about security group \"{0}\" updated successfully by user \"{1}\".", securityGroup.Name, CommonFunctions.CurrentUser), 7);
}
return "Security group information saved successfully";
}
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>
/// 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}\".", securityGroupName, CommonFunctions.CurrentUser), 13);
return "Security group deleted successfully";
}
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>
/// <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);
ObservableCollection<Historian> historianList = new ObservableCollection<Historian>();
DataTable historianTable;
string query;
string commaSeparatedKeys;
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());
foreach (DataRow row in historianTable.Rows)
{
historianList.Add(new Historian()
{
NodeID = database.Guid(row, "NodeID"),
ID = row.ConvertField<int>("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 historianList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves a <see cref="Node"/> defined in the database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns><see cref="Node"/> information.</returns>
public static Node GetCurrentNode(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
DataTable nodeTable;
string query;
query = database.ParameterizedQueryString("Select ID, Name, CompanyID, Longitude, Latitude, Description, " +
"ImagePath, Settings, MenuData, MenuType, Master, LoadOrder, Enabled, CompanyName " +
"From NodeDetail WHERE ID = {0} ORDER BY LoadOrder", "id");
nodeTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
if (nodeTable.Rows.Count == 0)
return null;
DataRow row = nodeTable.Rows[0];
Node node = new Node()
{
ID = database.Guid(row, "ID"),
Name = row.Field<string>("Name"),
CompanyID = row.ConvertNullableField<int>("CompanyID"),
Longitude = row.ConvertNullableField<decimal>("Longitude"),
Latitude = row.ConvertNullableField<decimal>("Latitude"),
Description = row.Field<string>("Description"),
ImagePath = row.Field<string>("ImagePath"),
Settings = row.Field<string>("Settings"),
MenuType = row.Field<string>("MenuType"),
MenuData = row.Field<string>("MenuData"),
Master = Convert.ToBoolean(row.Field<object>("Master")),
LoadOrder = row.ConvertField<int>("LoadOrder"),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
m_companyName = row.Field<string>("CompanyName")
};
return node;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="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>
/// Logs information about an encountered exception to the backend datastore.
/// </summary>
/// <param name="source">Source of the exception.</param>
/// <param name="message">Detailed description of the exception.</param>
/// <returns>true if logging was successful, otherwise false.</returns>
protected virtual bool LogError(string source, string message)
{
if (!string.IsNullOrWhiteSpace(SettingsCategory) && !string.IsNullOrWhiteSpace(source) && !string.IsNullOrWhiteSpace(message))
{
try
{
AdoDataConnection database = new AdoDataConnection(SettingsCategory);
using (IDbConnection connection = database.Connection)
{
connection.ExecuteNonQuery(database.ParameterizedQueryString("INSERT INTO ErrorLog (Source, Message) VALUES ({0}, {1})", "source", "message"), source, message);
}
return true;
}
catch (Exception ex)
{
// All we can do is track last exception in this case
m_lastException = ex;
}
}
return false;
}