/// <summary>
/// Saves <see cref="Historian"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="historian">Infomration about <see cref="Historian"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Historian historian)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (historian.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO Historian (NodeID, Acronym, Name, AssemblyName, TypeName, ConnectionString, IsLocal, " +
"MeasurementReportingInterval, Description, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, " +
"{5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14})", "nodeID", "acronym", "name", "assemblyName", "typeName", "connectionString", "isLocal",
"measurementReportingInterval", "description", "loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (historian.NodeID != Guid.Empty) ? database.Guid(historian.NodeID) : database.CurrentNodeID(),
historian.Acronym.Replace(" ", "").ToUpper(), historian.Name.ToNotNull(), historian.AssemblyName.ToNotNull(), historian.TypeName.ToNotNull(),
historian.ConnectionString.ToNotNull(), database.Bool(historian.IsLocal), historian.MeasurementReportingInterval, historian.Description.ToNotNull(),
historian.LoadOrder, database.Bool(historian.Enabled), CommonFunctions.CurrentUser, database.UtcNow(),
CommonFunctions.CurrentUser, database.UtcNow());
}
else
{
query = database.ParameterizedQueryString("UPDATE Historian SET NodeID = {0}, Acronym = {1}, Name = {2}, AssemblyName = {3}, TypeName = {4}, " +
"ConnectionString = {5}, IsLocal = {6}, MeasurementReportingInterval = {7}, Description = {8}, LoadOrder = {9}, Enabled = {10}, " +
"UpdatedBy = {11}, UpdatedOn = {12} WHERE ID = {13}", "nodeID", "acronym", "name", "assemblyName", "typeName", "connectionString",
"isLocal", "measurementReportingInterval", "description", "loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (historian.NodeID != Guid.Empty) ? database.Guid(historian.NodeID) : database.CurrentNodeID(),
historian.Acronym.Replace(" ", "").ToUpper(), historian.Name.ToNotNull(), historian.AssemblyName.ToNotNull(), historian.TypeName.ToNotNull(),
historian.ConnectionString.ToNotNull(), database.Bool(historian.IsLocal), historian.MeasurementReportingInterval, historian.Description.ToNotNull(),
historian.LoadOrder, database.Bool(historian.Enabled), CommonFunctions.CurrentUser, database.UtcNow(), historian.ID);
}
return "Historian information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="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>
/// 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>
/// 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>
/// 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();
}
}
/// <summary>
/// Saves <see cref="ApplicationRole"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="applicationRole">Information about <see cref="ApplicationRole"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, ApplicationRole applicationRole)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (applicationRole.ID == null || applicationRole.ID == Guid.Empty)
{
query = database.ParameterizedQueryString("INSERT INTO ApplicationRole (Name, Description, NodeID, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6})", "name", "description", "nodeID", "updatedBy", "updatedBy", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, applicationRole.Name, applicationRole.Description.ToNotNull(), database.CurrentNodeID(), CommonFunctions.CurrentUser, database.UtcNow(), CommonFunctions.CurrentUser, database.UtcNow());
}
else
{
query = database.ParameterizedQueryString("UPDATE ApplicationRole SET Name = {0}, Description = {1}, NodeID = {2}, UpdatedBy = {3}, UpdatedOn = {4} WHERE ID = {5}", "name", "description", "nodeID", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, applicationRole.Name, applicationRole.Description.ToNotNull(), applicationRole.NodeID, CommonFunctions.CurrentUser, database.UtcNow(), database.Guid(applicationRole.ID));
}
return "Application role information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Adapter"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="adapter">Information about <see cref="Adapter"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Adapter adapter)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string tableName;
if (adapter.Type == AdapterType.Action)
tableName = "CustomActionAdapter";
else if (adapter.Type == AdapterType.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();
}
}