public static string SaveOutputStreamDevice(DataConnection connection, OutputStreamDevice outputStreamDevice, bool isNew, string originalAcronym) { //DataConnection connection = new DataConnection(); bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; if (isNew) command.CommandText = "Insert Into OutputStreamDevice (NodeID, AdapterID, IDCode, Acronym, BpaAcronym, Name, LoadOrder, Enabled, PhasorDataFormat, FrequencyDataFormat, AnalogDataFormat, CoordinateFormat, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " + "Values (@nodeID, @adapterID, @idCode, @acronym, @bpaAcronym, @name, @loadOrder, @enabled, @phasorDataFormat, @frequencyDataFormat, @analogDataFormat, @coordinateFormat, @updatedBy, @updatedOn, @createdBy, @createdOn)"; else command.CommandText = "Update OutputStreamDevice Set NodeID = @nodeID, AdapterID = @adapterID, IDCode = @idCode, Acronym = @acronym, " + "BpaAcronym = @bpaAcronym, Name = @name, LoadOrder = @loadOrder, Enabled = @enabled, PhasorDataFormat = @phasorDataFormat, " + "FrequencyDataFormat = @frequencyDataFormat, AnalogDataFormat = @analogDataFormat, CoordinateFormat = @coordinateFormat, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id"; command.Parameters.Add(AddWithValue(command, "@nodeID", outputStreamDevice.NodeID)); command.Parameters.Add(AddWithValue(command, "@adapterID", outputStreamDevice.AdapterID)); command.Parameters.Add(AddWithValue(command, "@idCode", outputStreamDevice.IdCode)); command.Parameters.Add(AddWithValue(command, "@acronym", outputStreamDevice.Acronym.Replace(" ", "").ToUpper())); command.Parameters.Add(AddWithValue(command, "@bpaAcronym", outputStreamDevice.BpaAcronym.Replace(" ", "").ToUpper())); command.Parameters.Add(AddWithValue(command, "@name", outputStreamDevice.Name)); command.Parameters.Add(AddWithValue(command, "@loadOrder", outputStreamDevice.LoadOrder)); command.Parameters.Add(AddWithValue(command, "@enabled", outputStreamDevice.Enabled)); command.Parameters.Add(AddWithValue(command, "@phasorDataFormat", outputStreamDevice.PhasorDataFormat)); command.Parameters.Add(AddWithValue(command, "@frequencyDataFormat", outputStreamDevice.FrequencyDataFormat)); command.Parameters.Add(AddWithValue(command, "@analogDataFormat", outputStreamDevice.AnalogDataFormat)); command.Parameters.Add(AddWithValue(command, "@coordinateFormat", outputStreamDevice.CoordinateFormat)); command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); if (isNew) { command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); } else { command.Parameters.Add(AddWithValue(command, "@id", outputStreamDevice.ID)); //if output stream device is updated then modify signal references in the measurement table //to reflect changes in the acronym of the device. Do this only if new and original acronyms are different. if (!string.IsNullOrEmpty(originalAcronym) && originalAcronym != outputStreamDevice.Acronym) { //Microsoft Access does not support REPLACE function in SQL statement. if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB")) { List<OutputStreamMeasurement> outputStreamMeasurements = GetOutputStreamMeasurementList(connection, outputStreamDevice.AdapterID); foreach (OutputStreamMeasurement osm in outputStreamMeasurements) { if (osm.SignalReference.StartsWith(originalAcronym + "-")) { osm.SignalReference = osm.SignalReference.Replace(originalAcronym, outputStreamDevice.Acronym); SaveOutputStreamMeasurement(connection, osm, false); } } } else { IDbCommand command1 = connection.Connection.CreateCommand(); command1.CommandType = CommandType.Text; command1.CommandText = "Update OutputStreamMeasurement Set SignalReference = Replace(SignalReference, @originalAcronym, @newAcronym) Where AdapterID = @adapterID"; // and SignalReference LIKE @signalReference"; command1.Parameters.Add(AddWithValue(command1, "@originalAcronym", originalAcronym)); command1.Parameters.Add(AddWithValue(command1, "@newAcronym", outputStreamDevice.Acronym)); command1.Parameters.Add(AddWithValue(command1, "@adapterID", outputStreamDevice.AdapterID)); command1.ExecuteNonQuery(); } } } command.ExecuteNonQuery(); if (isNew && outputStreamDevice.IdCode == 0) { //TODO: update IDCode to auto generated ID value. OutputStreamDevice deviceJustAdded = GetOutputStreamDevice(connection, outputStreamDevice.AdapterID, outputStreamDevice.Acronym.Replace(" ", "").ToUpper()); command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "Update OutputStreamDevice SET IDCode = @idCode Where ID = @id"; command.Parameters.Add(AddWithValue(command, "@idCode", deviceJustAdded.ID)); command.Parameters.Add(AddWithValue(command, "@id", deviceJustAdded.ID)); command.ExecuteNonQuery(); } return "Output Stream Device Information Saved Successfully"; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static List<Company> GetCompanyList(DataConnection connection) { //DataConnection connection = new DataConnection(); bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } List<Company> companyList = new List<Company>(); IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "SELECT ID, Acronym, MapAcronym, Name, URL, LoadOrder FROM Company ORDER BY LoadOrder"; DataTable resultTable = new DataTable(); resultTable.Load(command.ExecuteReader()); companyList = (from item in resultTable.AsEnumerable() select new Company() { ID = Convert.ToInt32(item.Field<object>("ID")), Acronym = item.Field<string>("Acronym"), MapAcronym = item.Field<string>("MapAcronym"), Name = item.Field<string>("Name"), URL = item.Field<string>("URL"), LoadOrder = Convert.ToInt32(item.Field<object>("LoadOrder")) }).ToList(); return companyList; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static ObservableCollection<User> GetCurrentGroupUsers(DataConnection connection, string groupID) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } ObservableCollection<User> users = new ObservableCollection<User>(); IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "Select * From SecurityGroupUserAccountDetail WHERE SecurityGroupID = @groupID Order By UserName"; if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB")) command.Parameters.Add(AddWithValue(command, "@groupID", "{" + groupID + "}")); else command.Parameters.Add(AddWithValue(command, "@groupID", groupID)); DataTable resultTable = new DataTable(); resultTable.Load(command.ExecuteReader()); users = new ObservableCollection<User>((from item in resultTable.AsEnumerable() select new User() { ID = item.Field<object>("UserAccountID").ToString(), Name = item.Field<string>("UserName"), FirstName = item.Field<object>("FirstName") == null ? string.Empty : item.Field<string>("FirstName"), LastName = item.Field<object>("LastName") == null ? string.Empty : item.Field<string>("LastName"), Email = item.Field<object>("Email") == null ? string.Empty : item.Field<string>("Email") })); return users; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static string AddDevices(DataConnection connection, int outputStreamID, Dictionary<int, string> devicesToBeAdded, bool addDigitals, bool addAnalogs) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } foreach (KeyValuePair<int, string> deviceInfo in devicesToBeAdded) //loop through all the devices that needs to be added. { Device device = new Device(); device = GetDeviceByDeviceID(connection, deviceInfo.Key); //Get all the information about the device to be added. OutputStreamDevice outputStreamDevice = new OutputStreamDevice(); outputStreamDevice.NodeID = device.NodeID; outputStreamDevice.AdapterID = outputStreamID; outputStreamDevice.Acronym = device.Acronym; outputStreamDevice.BpaAcronym = string.Empty; outputStreamDevice.Name = device.Name; outputStreamDevice.LoadOrder = device.LoadOrder; outputStreamDevice.Enabled = true; outputStreamDevice.PhasorDataFormat = string.Empty; outputStreamDevice.FrequencyDataFormat = string.Empty; outputStreamDevice.AnalogDataFormat = string.Empty; outputStreamDevice.CoordinateFormat = string.Empty; outputStreamDevice.IdCode = device.AccessID; SaveOutputStreamDevice(connection, outputStreamDevice, true, string.Empty); //save in to OutputStreamDevice Table. int savedOutputStreamDeviceID = GetOutputStreamDevice(connection, outputStreamID, device.Acronym).ID; //******************************************** List<Phasor> phasorList = new List<Phasor>(); phasorList = GetPhasorList(connection, deviceInfo.Key); //Get all the phasor information for the device to be added. foreach (Phasor phasor in phasorList) { OutputStreamDevicePhasor outputStreamDevicePhasor = new OutputStreamDevicePhasor(); //Add all phasors one by one into OutputStreamDevicePhasor table. outputStreamDevicePhasor.NodeID = device.NodeID; outputStreamDevicePhasor.OutputStreamDeviceID = savedOutputStreamDeviceID; outputStreamDevicePhasor.Label = phasor.Label; outputStreamDevicePhasor.Type = phasor.Type; outputStreamDevicePhasor.Phase = phasor.Phase; outputStreamDevicePhasor.LoadOrder = phasor.SourceIndex; outputStreamDevicePhasor.ScalingValue = 0; SaveOutputStreamDevicePhasor(connection, outputStreamDevicePhasor, true); } //******************************************** //******************************************** List<Measurement> measurementList = new List<Measurement>(); measurementList = GetMeasurementsByDevice(connection, deviceInfo.Key); int analogIndex = 0; foreach (Measurement measurement in measurementList) { if (measurement.SignalAcronym != "STAT") { OutputStreamMeasurement outputStreamMeasurement = new OutputStreamMeasurement(); outputStreamMeasurement.NodeID = device.NodeID; outputStreamMeasurement.AdapterID = outputStreamID; outputStreamMeasurement.HistorianID = measurement.HistorianID; outputStreamMeasurement.PointID = measurement.PointID; outputStreamMeasurement.SignalReference = measurement.SignalReference; if (measurement.SignalAcronym == "ALOG") { if (addAnalogs) { SaveOutputStreamMeasurement(connection, outputStreamMeasurement, true); OutputStreamDeviceAnalog outputStreamDeviceAnalog = new OutputStreamDeviceAnalog(); outputStreamDeviceAnalog.NodeID = device.NodeID; outputStreamDeviceAnalog.OutputStreamDeviceID = savedOutputStreamDeviceID; outputStreamDeviceAnalog.Label = device.Acronym.Length > 12 ? device.Acronym.Substring(0, 12) + ":A" + analogIndex.ToString() : device.Acronym + ":A" + analogIndex.ToString(); // measurement.PointTag; outputStreamDeviceAnalog.Type = 0; //default outputStreamDeviceAnalog.LoadOrder = Convert.ToInt32(measurement.SignalReference.Substring((measurement.SignalReference.LastIndexOf("-") + 3))); outputStreamDeviceAnalog.ScalingValue = 0; SaveOutputStreamDeviceAnalog(connection, outputStreamDeviceAnalog, true); analogIndex += 1; } } else if (measurement.SignalAcronym == "DIGI") { if (addDigitals) { SaveOutputStreamMeasurement(connection, outputStreamMeasurement, true); OutputStreamDeviceDigital outputStreamDeviceDigital = new OutputStreamDeviceDigital(); outputStreamDeviceDigital.NodeID = device.NodeID; outputStreamDeviceDigital.OutputStreamDeviceID = savedOutputStreamDeviceID; outputStreamDeviceDigital.Label = digitalLabel; // measurement.PointTag; outputStreamDeviceDigital.LoadOrder = Convert.ToInt32(measurement.SignalReference.Substring((measurement.SignalReference.LastIndexOf("-") + 3))); outputStreamDeviceDigital.MaskValue = 0; SaveOutputStreamDeviceDigital(connection, outputStreamDeviceDigital, true); } } else SaveOutputStreamMeasurement(connection, outputStreamMeasurement, true); } } //******************************************** } return "Output Stream Device(s) Added Successfully"; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static List<CalculatedMeasurement> GetCalculatedMeasurementList(DataConnection connection, string nodeID) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } List<CalculatedMeasurement> calculatedMeasurementList = new List<CalculatedMeasurement>(); IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; if (string.IsNullOrEmpty(nodeID) || MasterNode(connection, nodeID)) command.CommandText = "Select * From CalculatedMeasurementDetail Order By LoadOrder"; else { command.CommandText = "Select * From CalculatedMeasurementDetail Where NodeID = @nodeID Order By LoadOrder"; //command.Parameters.Add(AddWithValue(command, "@nodeID", nodeID)); if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB")) command.Parameters.Add(AddWithValue(command, "@nodeID", "{" + nodeID + "}")); else command.Parameters.Add(AddWithValue(command, "@nodeID", nodeID)); } DataTable resultTable = new DataTable(); resultTable.Load(command.ExecuteReader()); calculatedMeasurementList = (from item in resultTable.AsEnumerable() select new CalculatedMeasurement() { NodeId = item.Field<object>("NodeID").ToString(), ID = Convert.ToInt32(item.Field<object>("ID")), Acronym = item.Field<string>("Acronym"), Name = item.Field<string>("Name"), AssemblyName = item.Field<string>("AssemblyName"), TypeName = item.Field<string>("TypeName"), ConnectionString = item.Field<string>("ConnectionString"), ConfigSection = item.Field<string>("ConfigSection"), InputMeasurements = item.Field<string>("InputMeasurements"), OutputMeasurements = item.Field<string>("OutputMeasurements"), MinimumMeasurementsToUse = Convert.ToInt32(item.Field<object>("MinimumMeasurementsToUse")), FramesPerSecond = Convert.ToInt32(item.Field<object>("FramesPerSecond") ?? 30), LagTime = item.Field<double>("LagTime"), LeadTime = item.Field<double>("LeadTime"), UseLocalClockAsRealTime = Convert.ToBoolean(item.Field<object>("UseLocalClockAsRealTime")), AllowSortsByArrival = Convert.ToBoolean(item.Field<object>("AllowSortsByArrival")), LoadOrder = Convert.ToInt32(item.Field<object>("LoadOrder")), Enabled = Convert.ToBoolean(item.Field<object>("Enabled")), IgnoreBadTimeStamps = Convert.ToBoolean(item.Field<object>("IgnoreBadTimeStamps")), TimeResolution = Convert.ToInt32(item.Field<object>("TimeResolution")), AllowPreemptivePublishing = Convert.ToBoolean(item.Field<object>("AllowPreemptivePublishing")), DownsamplingMethod = item.Field<string>("DownSamplingMethod"), NodeName = item.Field<string>("NodeName"), PerformTimestampReasonabilityCheck = Convert.ToBoolean(item.Field<object>("PerformTimestampReasonabilityCheck")) }).ToList(); return calculatedMeasurementList; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
static int GetSignalTypeID(DataConnection connection, string suffix) { int signalTypeID = 0; bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "Select ID From SignalType Where Suffix = @suffix"; command.Parameters.Add(AddWithValue(command, "@suffix", suffix)); signalTypeID = (int)command.ExecuteScalar(); } catch (Exception ex) { LogException(connection, "GetSignalTypeID", ex); } finally { if (createdConnection && connection != null) connection.Dispose(); } return signalTypeID; }
public static string DeleteUser(DataConnection connection, string userID) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } //Setup current users context for Delete trigger. SetCurrentUserContext(connection); IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "Delete From UserAccount Where ID = @id"; if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB")) command.Parameters.Add(AddWithValue(command, "@id", "{" + userID + "}")); else command.Parameters.Add(AddWithValue(command, "@id", userID)); command.ExecuteNonQuery(); return "User Deleted Successfully"; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static string SaveVendor(DataConnection connection, Vendor vendor, bool isNew) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; if (isNew) command.CommandText = "Insert Into Vendor (Acronym, Name, PhoneNumber, ContactEmail, URL, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values (@acronym, @name, @phoneNumber, @contactEmail, @url, @updatedBy, @updatedOn, @createdBy, @createdOn)"; else command.CommandText = "Update Vendor Set Acronym = @acronym, Name = @name, PhoneNumber = @phoneNumber, ContactEmail = @contactEmail, URL = @url, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id"; command.Parameters.Add(AddWithValue(command, "@acronym", vendor.Acronym.Replace(" ", "").ToUpper())); command.Parameters.Add(AddWithValue(command, "@name", vendor.Name)); command.Parameters.Add(AddWithValue(command, "@phoneNumber", vendor.PhoneNumber)); command.Parameters.Add(AddWithValue(command, "@contactEmail", vendor.ContactEmail)); command.Parameters.Add(AddWithValue(command, "@url", vendor.URL)); command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); if (isNew) { command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); } else command.Parameters.Add(AddWithValue(command, "@id", vendor.ID)); command.ExecuteNonQuery(); return "Vendor Information Saved Successfully"; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static string SaveVendorDevice(DataConnection connection, VendorDevice vendorDevice, bool isNew) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; if (isNew) command.CommandText = "Insert Into VendorDevice (VendorID, Name, Description, URL, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values (@vendorID, @name, @description, @url, @updatedBy, @updatedOn, @createdBy, @createdOn)"; else command.CommandText = "Update VendorDevice Set VendorID = @vendorID, Name = @name, Description = @description, URL = @url, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id"; command.Parameters.Add(AddWithValue(command, "@vendorID", vendorDevice.VendorID)); command.Parameters.Add(AddWithValue(command, "@name", vendorDevice.Name)); command.Parameters.Add(AddWithValue(command, "@description", vendorDevice.Description)); command.Parameters.Add(AddWithValue(command, "@url", vendorDevice.URL)); command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); if (isNew) { command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); } else command.Parameters.Add(AddWithValue(command, "@id", vendorDevice.ID)); command.ExecuteNonQuery(); return "Vendor Device Information Saved Successfully"; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static string SaveRole(DataConnection connection, Role role, bool isNew) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; if (isNew) command.CommandText = "Insert Into ApplicationRole (Name, Description, NodeID, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values (@name, @description, @nodeID, @updatedBy, @updatedOn, @createdBy, @createdOn)"; else command.CommandText = "Update ApplicationRole Set Name = @name, Description = @description, NodeID = @nodeID, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id"; command.Parameters.Add(AddWithValue(command, "@name", role.Name)); command.Parameters.Add(AddWithValue(command, "@description", role.Description)); if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB")) command.Parameters.Add(AddWithValue(command, "@nodeID", "{" + role.NodeID + "}")); else command.Parameters.Add(AddWithValue(command, "@nodeID", role.NodeID)); command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); if (isNew) { command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); } else command.Parameters.Add(AddWithValue(command, "@id", role.ID)); command.ExecuteNonQuery(); return "Role Information Saved Successfully"; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static string SaveUser(DataConnection connection, User user, bool isNew) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; if (isNew) if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB")) command.CommandText = "Insert Into UserAccount (Name, [Password], FirstName, LastName, DefaultNodeID, Phone, Email, LockedOut, UseADAuthentication, ChangePasswordOn, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " + "Values (@name, @password, @firstName, @lastName, @defaultNodeID, @phone, @email, @lockedOut, @useADAuthentication, @changePasswordOn, @updatedBy, @updatedOn, @createdBy, @createdOn)"; else command.CommandText = "Insert Into UserAccount (Name, Password, FirstName, LastName, DefaultNodeID, Phone, Email, LockedOut, UseADAuthentication, ChangePasswordOn, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " + "Values (@name, @password, @firstName, @lastName, @defaultNodeID, @phone, @email, @lockedOut, @useADAuthentication, @changePasswordOn, @updatedBy, @updatedOn, @createdBy, @createdOn)"; else if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB")) command.CommandText = "Update UserAccount Set Name = @name, [Password] = @password, FirstName = @firstName, LastName = @lastName, DefaultNodeID = @defaultNodeID, Phone = @phone, " + "Email = @email, LockedOut = @lockedOut, UseADAuthentication = @useADAuthentication, ChangePasswordOn = @changePasswordOn, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id"; else command.CommandText = "Update UserAccount Set Name = @name, Password = @password, FirstName = @firstName, LastName = @lastName, DefaultNodeID = @defaultNodeID, Phone = @phone, Email = @email, " + "LockedOut = @lockedOut, UseADAuthentication = @useADAuthentication, ChangePasswordOn = @changePasswordOn, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id"; command.Parameters.Add(AddWithValue(command, "@name", user.Name)); command.Parameters.Add(AddWithValue(command, "@password", user.Password)); command.Parameters.Add(AddWithValue(command, "@firstName", user.FirstName)); command.Parameters.Add(AddWithValue(command, "@lastName", user.LastName)); if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB")) command.Parameters.Add(AddWithValue(command, "@defaultNodeID", "{" + user.DefaultNodeID + "}")); else command.Parameters.Add(AddWithValue(command, "@defaultNodeID", user.DefaultNodeID)); command.Parameters.Add(AddWithValue(command, "@phone", user.Phone)); command.Parameters.Add(AddWithValue(command, "@email", user.Email)); command.Parameters.Add(AddWithValue(command, "@lockedOut", user.LockedOut)); command.Parameters.Add(AddWithValue(command, "@useADAuthentication", user.UseADAuthentication)); if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB")) command.Parameters.Add(AddWithValue(command, "@changePasswordOn", user.ChangePasswordOn == DateTime.MinValue ? DateTime.UtcNow.Date : user.ChangePasswordOn.Date)); else command.Parameters.Add(AddWithValue(command, "@changePasswordOn", user.ChangePasswordOn == DateTime.MinValue ? (object)DBNull.Value : user.ChangePasswordOn)); command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); if (isNew) { command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); } else { if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB")) command.Parameters.Add(AddWithValue(command, "@id", "{" + user.ID + "}")); else command.Parameters.Add(AddWithValue(command, "@id", user.ID)); } command.ExecuteNonQuery(); return "User Information Saved Successfully"; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static string SavePhasor(DataConnection connection, Phasor phasor, bool isNew) { //DataConnection connection = new DataConnection(); bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); if (isNew) command.CommandText = "Insert Into Phasor (DeviceID, Label, Type, Phase, DestinationPhasorID, SourceIndex, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values (@deviceID, @label, @type, @phase, " + "@destinationPhasorID, @sourceIndex, @updatedBy, @updatedOn, @createdBy, @createdOn)"; else command.CommandText = "Update Phasor Set DeviceID =@deviceID, Label = @label, Type = @type, Phase = @phase, DestinationPhasorID = @destinationPhasorID, " + "SourceIndex = @sourceIndex, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id"; command.Parameters.Add(AddWithValue(command, "@deviceID", phasor.DeviceID)); command.Parameters.Add(AddWithValue(command, "@label", phasor.Label)); command.Parameters.Add(AddWithValue(command, "@type", phasor.Type)); command.Parameters.Add(AddWithValue(command, "@phase", phasor.Phase)); command.Parameters.Add(AddWithValue(command, "@destinationPhasorID", phasor.DestinationPhasorID ?? (object)DBNull.Value)); command.Parameters.Add(AddWithValue(command, "@sourceIndex", phasor.SourceIndex)); command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); if (isNew) { command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); } else command.Parameters.Add(AddWithValue(command, "@id", phasor.ID)); command.ExecuteNonQuery(); Device device = new Device(); device = GetDeviceByDeviceID(connection, phasor.DeviceID); Measurement measurement; if (s_voltagePhasorSignalTypes == null || s_voltagePhasorSignalTypes.Rows.Count == 0) s_voltagePhasorSignalTypes = GetPhasorSignalTypes(connection, "V"); if (s_currentPhasorSignalTypes == null || s_currentPhasorSignalTypes.Rows.Count == 0) s_currentPhasorSignalTypes = GetPhasorSignalTypes(connection, "I"); if (phasor.Type == "V") s_phasorSignalTypes = s_voltagePhasorSignalTypes; else s_phasorSignalTypes = s_currentPhasorSignalTypes; Phasor addedPhasor = new Phasor(); //addedPhasor = GetPhasorByLabel(phasor.DeviceID, phasor.Label); addedPhasor = GetPhasorBySourceIndex(connection, phasor.DeviceID, phasor.SourceIndex); //we will try again just to make sure we get information back about the added phasor. As MS Access is very slow and sometimes fails to retrieve data. if (addedPhasor == null) { System.Threading.Thread.Sleep(500); //addedPhasor = GetPhasorByLabel(phasor.DeviceID, phasor.Label); addedPhasor = GetPhasorBySourceIndex(connection, phasor.DeviceID, phasor.SourceIndex); } foreach (DataRow row in s_phasorSignalTypes.Rows) { measurement = new Measurement(); measurement.HistorianID = device.HistorianID; measurement.DeviceID = device.ID; if (addedPhasor.DestinationPhasorID.HasValue) measurement.PointTag = device.CompanyAcronym + "_" + device.Acronym + "-" + GetPhasorByID(connection, addedPhasor.DeviceID, (int)addedPhasor.DestinationPhasorID).Label + ":" + device.VendorAcronym + row["Abbreviation"].ToString(); else measurement.PointTag = device.CompanyAcronym + "_" + device.Acronym + "-" + row["Suffix"].ToString() + addedPhasor.SourceIndex.ToString() + ":" + device.VendorAcronym + row["Abbreviation"].ToString(); measurement.AlternateTag = string.Empty; measurement.SignalTypeID = Convert.ToInt32(row["ID"]); measurement.PhasorSourceIndex = addedPhasor.SourceIndex; measurement.SignalReference = device.Acronym + "-" + row["Suffix"].ToString() + addedPhasor.SourceIndex.ToString(); measurement.Adder = 0.0d; measurement.Multiplier = 1.0d; measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.PhaseType + " " + row["Name"].ToString(); measurement.Enabled = true; if (isNew) //if it is a new phasor then add measurements as new. SaveMeasurement(connection, measurement, true); else //Check if measurement exists, if so then update them otherwise add new. { Measurement existingMeasurement = new Measurement(); existingMeasurement = GetMeasurementInfo(connection, measurement.DeviceID, row["Suffix"].ToString(), measurement.PhasorSourceIndex); if (existingMeasurement == null) SaveMeasurement(connection, measurement, true); else { measurement.SignalID = existingMeasurement.SignalID; SaveMeasurement(connection, measurement, false); } } } return "Phasor Information Saved Successfully"; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static string SaveOutputStreamMeasurement(DataConnection connection, OutputStreamMeasurement outputStreamMeasurement, bool isNew) { //DataConnection connection = new DataConnection(); bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; if (isNew) command.CommandText = "Insert Into OutputStreamMeasurement (NodeID, AdapterID, HistorianID, PointID, SignalReference, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " + "Values (@nodeID, @adapterID, @historianID, @pointID, @signalReference, @updatedBy, @updatedOn, @createdBy, @createdOn)"; else command.CommandText = "Update OutputStreamMeasurement Set NodeID = @nodeID, AdapterID = @adapterID, HistorianID = @historianID, " + "PointID = @pointID, SignalReference = @signalReference, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn WHERE ID = @id"; command.Parameters.Add(AddWithValue(command, "@nodeID", outputStreamMeasurement.NodeID)); command.Parameters.Add(AddWithValue(command, "@adapterID", outputStreamMeasurement.AdapterID)); command.Parameters.Add(AddWithValue(command, "@historianID", outputStreamMeasurement.HistorianID ?? (object)DBNull.Value)); command.Parameters.Add(AddWithValue(command, "@pointID", outputStreamMeasurement.PointID)); command.Parameters.Add(AddWithValue(command, "@signalReference", outputStreamMeasurement.SignalReference)); command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); if (isNew) { command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); } else { command.Parameters.Add(AddWithValue(command, "@id", outputStreamMeasurement.ID)); } command.ExecuteNonQuery(); return "Output Stream Measurement Information Saved Successfully"; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static string SaveOutputStreamDevicePhasor(DataConnection connection, OutputStreamDevicePhasor outputStreamDevicePhasor, bool isNew) { //DataConnection connection = new DataConnection(); bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; if (isNew) command.CommandText = "Insert Into OutputStreamDevicePhasor (NodeID, OutputStreamDeviceID, Label, Type, Phase, LoadOrder, ScalingValue, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " + "Values (@nodeID, @outputStreamDeviceID, @label, @type, @phase, @loadOrder, @scalingValue, @updatedBy, @updatedOn, @createdBy, @createdOn)"; else command.CommandText = "Update OutputStreamDevicePhasor Set NodeID = @nodeID, OutputStreamDeviceID = @outputStreamDeviceID, Label = @label, " + "Type = @type, Phase = @phase, LoadOrder = @loadOrder, ScalingValue = @scalingValue, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id"; command.Parameters.Add(AddWithValue(command, "@nodeID", outputStreamDevicePhasor.NodeID)); command.Parameters.Add(AddWithValue(command, "@outputStreamDeviceID", outputStreamDevicePhasor.OutputStreamDeviceID)); command.Parameters.Add(AddWithValue(command, "@label", outputStreamDevicePhasor.Label)); command.Parameters.Add(AddWithValue(command, "@type", outputStreamDevicePhasor.Type)); command.Parameters.Add(AddWithValue(command, "@phase", outputStreamDevicePhasor.Phase)); command.Parameters.Add(AddWithValue(command, "@loadOrder", outputStreamDevicePhasor.LoadOrder)); command.Parameters.Add(AddWithValue(command, "@scalingValue", outputStreamDevicePhasor.ScalingValue)); command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); if (isNew) { command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); } else { command.Parameters.Add(AddWithValue(command, "@id", outputStreamDevicePhasor.ID)); } command.ExecuteNonQuery(); return "Output Stream Device Phasor Information Saved Successfully"; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
//Do not use this method in WCF call or silverlight. It is for internal use only. static DataTable GetPmuSignalTypes(DataConnection connection) { bool createdConnection = false; DataTable resultTable = new DataTable(); try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "Select * From SignalType Where Source = 'PMU' AND Suffix IN ('FQ', 'DF', 'SF')"; resultTable.Load(command.ExecuteReader()); } catch (Exception ex) { LogException(connection, "GetPmuSignalTypes", ex); } finally { if (createdConnection && connection != null) connection.Dispose(); } return resultTable; }
public static string SaveWizardConfigurationInfo(DataConnection connection, string nodeID, List<WizardDeviceInfo> wizardDeviceInfoList, string connectionString, int? protocolID, int? companyID, int? historianID, int? interconnectionID, int? parentID, bool skipDisableRealTimeData) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } List<string> nondistinctAcronymList = new List<string>(); nondistinctAcronymList = (from item in wizardDeviceInfoList where item.Include == true group item by item.Acronym into grouped where grouped.Count() > 1 select grouped.Key).ToList(); if (nondistinctAcronymList.Count > 0) { StringBuilder sb = new StringBuilder("Duplicate Acronyms Exist"); foreach (string item in nondistinctAcronymList) { sb.AppendLine(); sb.Append(item); } throw new ArgumentException(sb.ToString()); } int loadOrder = 1; foreach (WizardDeviceInfo info in wizardDeviceInfoList) { if (info.Include) { Device device = new Device(); device.NodeID = nodeID; device.Acronym = info.Acronym; device.Name = info.Name; device.IsConcentrator = false; device.Longitude = info.Longitude; device.Latitude = info.Latitude; device.ConnectionString = parentID == null ? connectionString : string.Empty; device.ProtocolID = protocolID; device.CompanyID = companyID; device.HistorianID = historianID; device.InterconnectionID = interconnectionID; device.Enabled = true; device.VendorDeviceID = info.VendorDeviceID == null ? (int?)null : info.VendorDeviceID == 0 ? (int?)null : info.VendorDeviceID; device.ParentID = parentID; device.AccessID = info.AccessID; device.LoadOrder = loadOrder; device.SkipDisableRealTimeData = skipDisableRealTimeData; device.TimeZone = string.Empty; device.TimeAdjustmentTicks = 0; device.DataLossInterval = 5; device.MeasuredLines = 1; device.ContactList = string.Empty; device.AllowedParsingExceptions = 10; device.ParsingExceptionWindow = 5; device.DelayedConnectionInterval = 5; device.AllowUseOfCachedConfiguration = true; device.AutoStartDataParsingSequence = true; device.MeasurementReportingInterval = 100000; //If Add Digitals and Add Analogs is checked for the device then, if digitals and analogs are available i.e. count>0 then add them as measurements. int digitalCount = 0; if (info.AddDigitals && info.DigitalCount > 0) { digitalCount = info.DigitalCount; } int analogCount = 0; if (info.AddAnalogs && info.AnalogCount > 0) { analogCount = info.AnalogCount; } Device existingDevice = GetDeviceByAcronym(connection, info.Acronym); if (existingDevice != null) { device.ID = existingDevice.ID; device.TimeZone = existingDevice.TimeZone; device.TimeAdjustmentTicks = existingDevice.TimeAdjustmentTicks; device.DataLossInterval = existingDevice.DataLossInterval; device.MeasuredLines = existingDevice.MeasuredLines; device.ContactList = existingDevice.ContactList; device.AllowedParsingExceptions = existingDevice.AllowedParsingExceptions; device.ParsingExceptionWindow = existingDevice.ParsingExceptionWindow; device.DelayedConnectionInterval = existingDevice.DelayedConnectionInterval; device.AllowUseOfCachedConfiguration = existingDevice.AllowUseOfCachedConfiguration; device.AutoStartDataParsingSequence = existingDevice.AutoStartDataParsingSequence; device.MeasurementReportingInterval = existingDevice.MeasurementReportingInterval; SaveDevice(connection, device, false, digitalCount, analogCount); } else SaveDevice(connection, device, true, digitalCount, analogCount); Device addedDevice = GetDeviceByAcronym(connection, info.Acronym); int count = 1; foreach (PhasorInfo phasorInfo in info.PhasorList) { if (phasorInfo.Label.ToLower() != "unused") { Phasor phasor = new Phasor(); phasor.DeviceID = addedDevice.ID; phasor.Label = phasorInfo.Label; phasor.Type = phasorInfo.Type; phasor.Phase = phasorInfo.Phase; phasor.DestinationPhasorID = null; phasor.SourceIndex = count; Phasor existingPhasor = GetPhasorBySourceIndex(connection, addedDevice.ID, phasor.SourceIndex); if (existingPhasor != null) { phasor.ID = existingPhasor.ID; SavePhasor(connection, phasor, false); } else SavePhasor(connection, phasor, true); } count++; } } loadOrder++; } return "Configuration Information Saved Successfully"; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
private static string GetProtocolAcronymByID(DataConnection connection, int id) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "Select Acronym From Protocol Where ID = @id"; command.Parameters.Add(AddWithValue(command, "@id", id)); DataTable resultTable = new DataTable(); resultTable.Load(command.ExecuteReader()); if (resultTable.Rows.Count > 0) return resultTable.Rows[0]["Acronym"].ToString(); else return string.Empty; } catch (Exception ex) { LogException(connection, "GetProtocolAcronymByID", ex); return string.Empty; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
// we can just use ID column in the database for delete as it is auto increament. public static string DeleteOutputStreamMeasurement(DataConnection connection, int outputStreamMeasurementID) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } //Setup current users context for Delete trigger. SetCurrentUserContext(connection); IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "Delete From OutputStreamMeasurement Where ID = @id"; command.Parameters.Add(AddWithValue(command, "@id", outputStreamMeasurementID)); command.ExecuteNonQuery(); return "Output Stream Measurement Deleted Successfully"; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static void DeleteRoleUsers(DataConnection connection, string applicationRoleID, List<string> userIDsToBeDeleted) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command; foreach (string userID in userIDsToBeDeleted) { command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "Delete From ApplicationRoleUserAccount Where ApplicationRoleID = @applicationRoleID AND UserAccountID = @userID"; if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB")) { command.Parameters.Add(AddWithValue(command, "@applicationRoleID", "{" + applicationRoleID + "}")); command.Parameters.Add(AddWithValue(command, "@userID", "{" + userID + "}")); } else { command.Parameters.Add(AddWithValue(command, "@applicationRoleID", applicationRoleID)); command.Parameters.Add(AddWithValue(command, "@userID", userID)); } command.ExecuteNonQuery(); command.Parameters.Clear(); } } finally { if (createdConnection && connection != null) connection.Dispose(); } }
/// <summary> /// Purpose of this method is to supply current user information from the UI to DELETE trigger for change logging. /// This method must be called before any delete operation on the database in order to log who deleted this record. /// For SQL server it sets user name into CONTEXT_INFO(). /// For MySQL server it sets user name into session variable @context. /// MS Access is not supported for change logging. /// For any other database in the future, such as Oracle, this logic must be extended to support change log in the database. /// </summary> /// <param name="connection">Connection used to set user context before any delete operation.</param> public static void SetCurrentUserContext(DataConnection connection) { bool createdConnection = false; try { if (string.IsNullOrEmpty(s_currentUser)) s_currentUser = Thread.CurrentPrincipal.Identity.Name; if (!string.IsNullOrEmpty(s_currentUser)) { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command; //First of all set Current User for the database session for this connection. if (connection.Connection.GetType().Name.ToLower() == "sqlconnection") { string contextSql = "DECLARE @context VARBINARY(128)\n SELECT @context = CONVERT(VARBINARY(128), CONVERT(VARCHAR(128), @userName))\n SET CONTEXT_INFO @context"; command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = contextSql; command.Parameters.Add(AddWithValue(command, "@userName", s_currentUser)); command.ExecuteNonQuery(); } else if (connection.Connection.GetType().Name.ToLower() == "mysqlconnection") { try { command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "SET @context = '" + s_currentUser + "';"; command.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception(ex.Message); } } } } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static List<Adapter> GetAdapterList(DataConnection connection, bool enabledOnly, AdapterType adapterType, string nodeID) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } List<Adapter> adapterList = new List<Adapter>(); string viewName; if (adapterType == AdapterType.Action) viewName = "CustomActionAdapterDetail"; else if (adapterType == AdapterType.Input) viewName = "CustomInputAdapterDetail"; else viewName = "CustomOutputAdapterDetail"; IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; if (string.IsNullOrEmpty(nodeID) || MasterNode(connection, nodeID)) command.CommandText = "Select * From " + viewName + " Order By LoadOrder"; else { command.CommandText = "Select * From " + viewName + " Where NodeID = @nodeID Order By LoadOrder"; //command.Parameters.Add(AddWithValue(command, "@nodeID", nodeID)); if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB")) command.Parameters.Add(AddWithValue(command, "@nodeID", "{" + nodeID + "}")); else command.Parameters.Add(AddWithValue(command, "@nodeID", nodeID)); } DataTable resultTable = new DataTable(); resultTable.Load(command.ExecuteReader()); adapterList = (from item in resultTable.AsEnumerable() select new Adapter() { NodeID = item.Field<object>("NodeID").ToString(), ID = Convert.ToInt32(item.Field<object>("ID")), AdapterName = item.Field<string>("AdapterName"), AssemblyName = item.Field<string>("AssemblyName"), TypeName = item.Field<string>("TypeName"), ConnectionString = item.Field<string>("ConnectionString"), LoadOrder = Convert.ToInt32(item.Field<object>("LoadOrder")), Enabled = Convert.ToBoolean(item.Field<object>("Enabled")), NodeName = item.Field<string>("NodeName"), adapterType = adapterType }).ToList(); return adapterList; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static string UpdateOutputStreamStatistics(DataConnection connection, string nodeID, string oldAcronym, string newAcronym, string oldName, string newName) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } ////If device is updated then make sure all the statistical measurements get updated too to reflect any change in acronym. if (!string.IsNullOrEmpty(oldAcronym) && oldAcronym != newAcronym) { List<Measurement> measurementList = GetOutputStreamStatistics(connection, nodeID, oldAcronym); foreach (Measurement measurement in measurementList) { measurement.SignalReference = measurement.SignalReference.Replace(oldAcronym, newAcronym); measurement.PointTag = measurement.PointTag.Replace(oldAcronym, newAcronym); measurement.Description = System.Text.RegularExpressions.Regex.Replace(measurement.Description, oldName, newName, System.Text.RegularExpressions.RegexOptions.IgnoreCase); //measurement.Description.Replace(oldAcronym, newAcronym); SaveMeasurement(connection, measurement, false); } } return ""; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static Dictionary<int, BasicStatisticInfo> GetBasicStatisticInfoList(DataConnection connection, string nodeID) { return GetStatisticInfoList(connection, nodeID).ToDictionary(statistic => statistic.PointID, statistic => statistic.Statistics); }
private static Measurement GetMeasurementInfoBySignalReference(DataConnection connection, int? deviceID, string signalReference, int? phasorSourceIndex) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand commnad = connection.Connection.CreateCommand(); commnad.CommandType = CommandType.Text; commnad.CommandText = "Select * From MeasurementDetail Where SignalReference = @signalReference"; commnad.Parameters.Add(AddWithValue(commnad, "@signalReference", signalReference)); if (deviceID != null) { commnad.CommandText += " AND DeviceID = @deviceID"; commnad.Parameters.Add(AddWithValue(commnad, "@deviceID", deviceID ?? (object)DBNull.Value)); } if (phasorSourceIndex != null) { commnad.CommandText += " AND PhasorSourceIndex = @phasorSourceIndex"; commnad.Parameters.Add(AddWithValue(commnad, "@phasorSourceIndex", phasorSourceIndex ?? (object)DBNull.Value)); } DataTable resultTable = GetResultSet(commnad).Tables[0]; if (resultTable.Rows.Count == 0) return null; Measurement measurement = (Measurement)(from item in resultTable.AsEnumerable() select new Measurement() { SignalID = item.Field<object>("SignalID").ToString(), HistorianID = item.NullableInt("HistorianID"), PointID = Convert.ToInt32(item.Field<object>("PointID")), DeviceID = Convert.ToInt32(item.Field<object>("DeviceID")), PointTag = item.Field<string>("PointTag"), AlternateTag = item.Field<string>("AlternateTag"), SignalTypeID = Convert.ToInt32(item.Field<object>("SignalTypeID")), PhasorSourceIndex = item.NullableInt("PhasorSourceIndex"), SignalReference = item.Field<string>("SignalReference"), Adder = item.Field<double>("Adder"), Multiplier = item.Field<double>("Multiplier"), Description = item.Field<string>("Description"), Enabled = Convert.ToBoolean(item.Field<object>("Enabled")), HistorianAcronym = item.Field<string>("HistorianAcronym"), DeviceAcronym = item.Field<object>("DeviceAcronym") == null ? string.Empty : item.Field<string>("DeviceAcronym"), SignalName = item.Field<string>("SignalName"), SignalAcronym = item.Field<string>("SignalAcronym"), SignalSuffix = item.Field<string>("SignalTypeSuffix"), PhasorLabel = item.Field<string>("PhasorLabel") }).First(); return measurement; } catch (Exception ex) { LogException(connection, "GetMeasurementInfoBySignalReference", ex); return null; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static Dictionary<int, string> GetCompanies(DataConnection connection, bool isOptional) { //DataConnection connection = new DataConnection(); bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } Dictionary<int, string> companyList = new Dictionary<int, string>(); if (isOptional) companyList.Add(0, "Select Company"); IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "SELECT ID, Name FROM Company ORDER BY LoadOrder"; DataTable resultTable = new DataTable(); resultTable.Load(command.ExecuteReader()); int id; foreach (DataRow row in resultTable.Rows) { id = int.Parse(row["ID"].ToString()); if (!companyList.ContainsKey(id)) companyList.Add(id, row["Name"].ToString()); } return companyList; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
static Phasor GetPhasorBySourceIndex(DataConnection connection, int deviceID, int sourceIndex) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "Select * From PhasorDetail Where DeviceID = @deviceID and SourceIndex = @sourceIndex"; command.Parameters.Add(AddWithValue(command, "@deviceID", deviceID)); command.Parameters.Add(AddWithValue(command, "@sourceIndex", sourceIndex)); DataTable resultTable = GetResultSet(command).Tables[0]; if (resultTable.Rows.Count == 0) return null; Phasor phasor = (Phasor)(from item in resultTable.AsEnumerable() select new Phasor() { ID = Convert.ToInt32(item.Field<object>("ID")), DeviceID = Convert.ToInt32(item.Field<object>("DeviceID")), Label = item.Field<string>("Label"), Type = item.Field<string>("Type"), Phase = item.Field<string>("Phase"), DestinationPhasorID = item.NullableInt("DestinationPhasorID"), SourceIndex = Convert.ToInt32(item.Field<object>("SourceIndex")), DestinationPhasorLabel = item.Field<string>("DestinationPhasorLabel"), DeviceAcronym = item.Field<string>("DeviceAcronym"), PhasorType = item.Field<string>("Type") == "V" ? "Voltage" : "Current", PhaseType = item.Field<string>("Phase") == "+" ? "Positive Sequence" : item.Field<string>("Phase") == "-" ? "Negative Sequence" : item.Field<string>("Phase") == "0" ? "Zero Sequence" : item.Field<string>("Phase") == "A" ? "Phase A" : item.Field<string>("Phase") == "B" ? "Phase B" : "Phase C" }).First(); return phasor; } catch (Exception ex) { LogException(connection, "GetPhasorBySourceIndex", ex); return null; } finally { if (createdConnection && connection != null) connection.Dispose(); } }
public static Device GetConcentratorDevice(DataConnection connection, int deviceID) { try { Device device = new Device(); device = GetDeviceByDeviceID(connection, deviceID); if (device.IsConcentrator) return device; else return null; } catch (Exception ex) { LogException(connection, "GetConcentratorDevice", ex); return null; } }
static DataTable GetPhasorSignalTypes(DataConnection connection, string phasorType) { DataTable resultTable = new DataTable(); bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; if (phasorType == "V") command.CommandText = "Select * From SignalType Where Source = 'Phasor' AND Acronym LIKE 'V%'"; else command.CommandText = "Select * From SignalType Where Source = 'Phasor' AND Acronym LIKE 'I%'"; resultTable.Load(command.ExecuteReader()); } catch (Exception ex) { LogException(connection, "GetPhasorSignalTypes", ex); } finally { if (createdConnection && connection != null) connection.Dispose(); } return resultTable; }
public void SaveDevice(Device device, bool isNew, int digitalCount, int analogCount) { SystemMessages sm; DataConnection connection = new DataConnection(); try { string result = CommonFunctions.SaveDevice(connection, device, isNew, digitalCount, analogCount); //get the ID of the new device added to the system so we can associate that ID to the phasors of the original device. int deviceID; if (isNew) deviceID = CommonFunctions.GetDeviceByAcronym(connection, device.Acronym).ID; else deviceID = device.ID; if (m_deviceToCopy != null && isNew) //if we are copying device then make sure we copy phasors also. { List<Phasor> phasorList = CommonFunctions.GetPhasorList(connection, m_deviceToCopy.ID); foreach (Phasor phasor in phasorList) { phasor.DeviceID = deviceID; CommonFunctions.SavePhasor(connection, phasor, true); } } sm = new SystemMessages(new Message() { UserMessage = result, SystemMessage = string.Empty, UserMessageType = MessageType.Success }, ButtonType.OkOnly); sm.Owner = Window.GetWindow(this); sm.WindowStartupLocation = WindowStartupLocation.CenterOwner; sm.ShowPopup(); //update statistic measurement for a device if device is being updated and acronym has changed. try { if (!isNew && !string.IsNullOrEmpty(m_oldAcronym) && m_oldAcronym != device.Acronym) { CommonFunctions.UpdateDeviceStatistics(connection, deviceID, m_oldAcronym, device.Acronym, m_oldDeviceName, device.Name); //also if acronym has changed then make those changes } } catch (Exception ex) { CommonFunctions.LogException(connection, "WPF.UpdateDeviceStatistics", ex); sm = new SystemMessages(new Message() { UserMessage = "Failed to Update Device Statistics", SystemMessage = ex.Message, UserMessageType = MessageType.Error }, ButtonType.OkOnly); sm.Owner = Window.GetWindow(this); sm.WindowStartupLocation = WindowStartupLocation.CenterOwner; sm.ShowPopup(); } //Update Metadata in the openPDC Service. try { if (serviceClient != null && serviceClient.Helper.RemotingClient.CurrentState == TVA.Communication.ClientState.Connected) { if (device.Enabled) //if device is enabled then send initialize command otherwise send reloadconfig command. CommonFunctions.SendCommandToWindowsService(serviceClient, "Initialize " + CommonFunctions.GetRuntimeID(connection, "Device", deviceID)); // Convert.ToInt32(TextBlockRuntimeID.Text)); else CommonFunctions.SendCommandToWindowsService(serviceClient, "ReloadConfig"); //we do this to make sure all statistical measurements are in the system. if (device.HistorianID != null) //Update historian metadata { string runtimeID = CommonFunctions.GetRuntimeID(connection, "Historian", (int)device.HistorianID); CommonFunctions.SendCommandToWindowsService(serviceClient, "Invoke " + runtimeID + " RefreshMetadata"); } //now also update Stat historian metadata. Historian statHistorian = CommonFunctions.GetHistorianByAcronym(connection, "STAT"); if (statHistorian != null) { string statRuntimeID = CommonFunctions.GetRuntimeID(connection, "Historian", statHistorian.ID); CommonFunctions.SendCommandToWindowsService(serviceClient, "Invoke " + statRuntimeID + " RefreshMetadata"); } CommonFunctions.SendCommandToWindowsService(serviceClient, "Invoke 0 ReloadStatistics"); CommonFunctions.SendCommandToWindowsService(serviceClient, "RefreshRoutes"); } else { sm = new SystemMessages(new openPDCManager.Utilities.Message() { UserMessage = "Failed to Perform Configuration Changes", SystemMessage = "Application is disconnected from the openPDC Service.", UserMessageType = openPDCManager.Utilities.MessageType.Information }, ButtonType.OkOnly); sm.Owner = Window.GetWindow(this); sm.WindowStartupLocation = WindowStartupLocation.CenterOwner; sm.ShowPopup(); } } catch (Exception ex) { sm = new SystemMessages(new openPDCManager.Utilities.Message() { UserMessage = "Failed to Perform Configuration Changes", SystemMessage = ex.Message, UserMessageType = openPDCManager.Utilities.MessageType.Information }, ButtonType.OkOnly); sm.Owner = Window.GetWindow(this); sm.WindowStartupLocation = WindowStartupLocation.CenterOwner; sm.ShowPopup(); CommonFunctions.LogException(connection, "SaveDevice.RefreshMetadata", ex); } ClearForm(); //Navigate to Browse screen upon successful save. BrowseDevicesUserControl browseDevices = new BrowseDevicesUserControl(); ((MasterLayoutWindow)Window.GetWindow(this)).ContentFrame.Navigate(browseDevices); } catch (Exception ex) { CommonFunctions.LogException(connection, "WPF.SaveDevice", ex); sm = new SystemMessages(new Message() { UserMessage = "Failed to Save Device Information", SystemMessage = ex.Message, UserMessageType = MessageType.Error }, ButtonType.OkOnly); sm.Owner = Window.GetWindow(this); sm.WindowStartupLocation = WindowStartupLocation.CenterOwner; sm.ShowPopup(); } finally { if (connection != null) connection.Dispose(); } }
public static string SaveOutputStream(DataConnection connection, OutputStream outputStream, bool isNew) { bool createdConnection = false; try { if (connection == null) { connection = new DataConnection(); createdConnection = true; } IDbCommand command = connection.Connection.CreateCommand(); command.CommandType = CommandType.Text; if (isNew) command.CommandText = "INSERT INTO OutputStream (NodeID, Acronym, Name, Type, ConnectionString, IDCode, CommandChannel, DataChannel, AutoPublishConfigFrame, AutoStartDataChannel, NominalFrequency, FramesPerSecond, LagTime, LeadTime, " + "UseLocalClockAsRealTime, AllowSortsByArrival, LoadOrder, Enabled, IgnoreBadTimeStamps, TimeResolution, AllowPreemptivePublishing, DownsamplingMethod, DataFormat, CoordinateFormat, CurrentScalingValue, VoltageScalingValue, " + "AnalogScalingValue, DigitalMaskValue, PerformTimestampReasonabilityCheck, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES (@nodeID, @acronym, @name, @type, @connectionString, @idCode, @commandChannel, @dataChannel, @autoPublishConfigFrame, @autoStartDataChannel, @nominalFrequency, @framesPerSecond, " + "@lagTime, @leadTime, @useLocalClockAsRealTime, @allowSortsByArrival, @loadOrder, @enabled, @ignoreBadTimeStamps, @timeResolution, @allowPreemptivePublishing, @downsamplingMethod, @dataFormat, @coordinateFormat, " + "@currentScalingValue, @voltageScalingValue, @analogScalingValue, @digitalMaskValue, @performTimestampReasonabilityCheck, @updatedBy, @updatedOn, @createdBy, @createdOn)"; else command.CommandText = "UPDATE OutputStream SET NodeID = @nodeID, Acronym = @acronym, Name = @name, Type = @type, ConnectionString = @connectionString, IDCode = @idCode, CommandChannel = @commandChannel, DataChannel = @dataChannel, AutoPublishConfigFrame = @autoPublishConfigFrame, " + "AutoStartDataChannel = @autoStartDataChannel, NominalFrequency = @nominalFrequency, FramesPerSecond = @framesPerSecond, LagTime = @lagTime, LeadTime = @leadTime, UseLocalClockAsRealTime = @useLocalClockAsRealTime, " + "AllowSortsByArrival = @allowSortsByArrival, LoadOrder = @loadOrder, Enabled = @enabled, IgnoreBadTimeStamps = @ignoreBadTimeStamps, TimeResolution = @timeResolution, AllowPreemptivePublishing = @allowPreemptivePublishing, " + "DownsamplingMethod = @downsamplingMethod, DataFormat = @dataFormat, CoordinateFormat = @coordinateFormat, CurrentScalingValue = @currentScalingValue, VoltageScalingValue = @voltageScalingValue, " + "AnalogScalingValue = @analogScalingValue, DigitalMaskValue = @digitalMaskValue, PerformTimestampReasonabilityCheck = @performTimestampReasonabilityCheck, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn WHERE ID = @id"; command.Parameters.Add(AddWithValue(command, "@nodeID", outputStream.NodeID)); command.Parameters.Add(AddWithValue(command, "@acronym", outputStream.Acronym.Replace(" ", "").ToUpper())); command.Parameters.Add(AddWithValue(command, "@name", outputStream.Name)); command.Parameters.Add(AddWithValue(command, "@type", outputStream.Type)); command.Parameters.Add(AddWithValue(command, "@connectionString", outputStream.ConnectionString)); command.Parameters.Add(AddWithValue(command, "@idCode", outputStream.IDCode)); command.Parameters.Add(AddWithValue(command, "@commandChannel", outputStream.CommandChannel)); command.Parameters.Add(AddWithValue(command, "@dataChannel", outputStream.DataChannel)); command.Parameters.Add(AddWithValue(command, "@autoPublishConfigFrame", outputStream.AutoPublishConfigFrame)); command.Parameters.Add(AddWithValue(command, "@autoStartDataChannel", outputStream.AutoStartDataChannel)); command.Parameters.Add(AddWithValue(command, "@nominalFrequency", outputStream.NominalFrequency)); command.Parameters.Add(AddWithValue(command, "@framesPerSecond", outputStream.FramesPerSecond)); command.Parameters.Add(AddWithValue(command, "@lagTime", outputStream.LagTime)); command.Parameters.Add(AddWithValue(command, "@leadTime", outputStream.LeadTime)); command.Parameters.Add(AddWithValue(command, "@useLocalClockAsRealTime", outputStream.UseLocalClockAsRealTime)); command.Parameters.Add(AddWithValue(command, "@allowSortsByArrival", outputStream.AllowSortsByArrival)); command.Parameters.Add(AddWithValue(command, "@loadOrder", outputStream.LoadOrder)); command.Parameters.Add(AddWithValue(command, "@enabled", outputStream.Enabled)); command.Parameters.Add(AddWithValue(command, "@ignoreBadTimeStamps", outputStream.IgnoreBadTimeStamps)); command.Parameters.Add(AddWithValue(command, "@timeResolution", outputStream.TimeResolution)); command.Parameters.Add(AddWithValue(command, "@allowPreemptivePublishing", outputStream.AllowPreemptivePublishing)); command.Parameters.Add(AddWithValue(command, "@downsamplingMethod", outputStream.DownsamplingMethod)); command.Parameters.Add(AddWithValue(command, "@dataFormat", outputStream.DataFormat)); command.Parameters.Add(AddWithValue(command, "@coordinateFormat", outputStream.CoordinateFormat)); command.Parameters.Add(AddWithValue(command, "@currentScalingValue", outputStream.CurrentScalingValue)); command.Parameters.Add(AddWithValue(command, "@voltageScalingValue", outputStream.VoltageScalingValue)); command.Parameters.Add(AddWithValue(command, "@analogScalingValue", outputStream.AnalogScalingValue)); command.Parameters.Add(AddWithValue(command, "@digitalMaskValue", outputStream.DigitalMaskValue)); command.Parameters.Add(AddWithValue(command, "@performTimestampReasonabilityCheck", outputStream.PerformTimestampReasonabilityCheck)); command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); if (isNew) { command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser)); command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow)); } else { command.Parameters.Add(AddWithValue(command, "@id", outputStream.ID)); } command.ExecuteNonQuery(); try { // Generate Statistical Measurements for the device. //CommonPhasorServices.ValidateStatistics(connection.Connection, connection.AdapterType, "'" + outputStream.NodeID + "'", new Action<object, EventArgs<string>>(StatusMessageHandler), new Action<object, EventArgs<Exception>>(ProcessExceptionHandler)); } catch (Exception ex) { //Do not do anything. If this fails then we dont want to interrupt save operation. LogException(connection, "SaveOutputStream: PhasorDataSourceValidation", ex); } return "Output Stream Information Saved Successfully"; } finally { if (createdConnection && connection != null) connection.Dispose(); } }