private int AddOrUpdate(Device device, SqlCommand command) { int deviceCount; int deviceID; // Determine if the device already exists command.CommandText = "SELECT COUNT(*) FROM Device WHERE FLEID = @fleID"; command.Parameters.AddWithValue("@fleID", device.ID); deviceCount = Convert.ToInt32(command.ExecuteScalar()); if (deviceCount > 0) { // Get device ID command.CommandText = "SELECT ID FROM Device WHERE FLEID = @fleID"; deviceID = Convert.ToInt32(command.ExecuteScalar()); command.Parameters.Clear(); // Update device command.CommandText = "UPDATE Device SET FLEID = @fleID, Make = @make, Model = @model, StationID = @stationID, StationName = @stationName WHERE ID = @deviceID"; command.Parameters.AddWithValue("@fleID", device.ID); command.Parameters.AddWithValue("@make", device.Make); command.Parameters.AddWithValue("@model", device.Model); command.Parameters.AddWithValue("@stationID", device.StationID); command.Parameters.AddWithValue("@stationName", device.StationName); command.Parameters.AddWithValue("@deviceID", deviceID); command.ExecuteNonQuery(); command.Parameters.Clear(); } else { command.Parameters.Clear(); // Insert device into Device table command.CommandText = "INSERT INTO Device (FLEID, Make, Model, StationID, StationName) VALUES (@fleID, @make, @model, @stationID, @stationName)"; command.Parameters.AddWithValue("@fleID", device.ID); command.Parameters.AddWithValue("@make", device.Make); command.Parameters.AddWithValue("@model", device.Model); command.Parameters.AddWithValue("@stationID", device.StationID); command.Parameters.AddWithValue("@stationName", device.StationName); command.ExecuteNonQuery(); command.Parameters.Clear(); // Get device ID command.CommandText = "SELECT @@IDENTITY"; deviceID = Convert.ToInt32(command.ExecuteScalar()); } return deviceID; }
/// <summary> /// Writes the results to the output source. /// </summary> /// <param name="disturbanceRecorder">The device that collected the disturbance data.</param> /// <param name="disturbanceFiles">Information about the data files collected during the disturbance.</param> /// <param name="lineDataSets">The data sets used for analysis to determine fault location.</param> public override void WriteResults(Device disturbanceRecorder, ICollection<DisturbanceFile> disturbanceFiles, ICollection<Tuple<Line, FaultLocationDataSet>> lineDataSets) { const string DateTimeFormat = "yyyy-MM-dd HH:mm:ss.fff"; XElement sourceFiles; Line lineDefinition; FaultLocationDataSet faultDataSet; int firstFaultCycleIndex; CycleData firstFaultCycle; DateTime firstFaultCycleTime; int faultCalculationIndex; CycleData faultCalculationCycle; double iaFault; double ibFault; double icFault; double vaFault; double vbFault; double vcFault; XDocument resultsDocument; XElement results; string resultsFileName; string resultsFilePath; // Create XML element to contain // fault information for each line results = new XElement("results", new XElement("meterStationName", disturbanceRecorder.StationName) ); // Add information about source files to the results sourceFiles = new XElement("disturbanceFiles"); foreach (DisturbanceFile disturbanceFile in disturbanceFiles) sourceFiles.Add(new XElement("sourceFilePath", disturbanceFile.SourcePath)); results.Add(sourceFiles); // Add information about each faulted line to the results foreach (Tuple<Line, FaultLocationDataSet> lineDataSet in lineDataSets) { lineDefinition = lineDataSet.Item1; faultDataSet = lineDataSet.Item2; // Get the timestamp of the first fault cycle firstFaultCycleIndex = faultDataSet.FaultedCycles.First(); firstFaultCycle = faultDataSet.Cycles[firstFaultCycleIndex]; firstFaultCycleTime = firstFaultCycle.StartTime; // Get the fault calculation cycle faultCalculationIndex = faultDataSet.FaultCalculationCycle; if (faultCalculationIndex >= 0 && faultCalculationIndex < faultDataSet.Cycles.Count) { faultCalculationCycle = faultDataSet.Cycles[faultCalculationIndex]; // Get values for current and voltage during fault iaFault = faultCalculationCycle.AN.I.RMS; ibFault = faultCalculationCycle.BN.I.RMS; icFault = faultCalculationCycle.CN.I.RMS; vaFault = faultCalculationCycle.AN.V.RMS; vbFault = faultCalculationCycle.BN.V.RMS; vcFault = faultCalculationCycle.CN.V.RMS; } else { // Initialize to default values since // we don't know the actual fault values iaFault = 0.0D; ibFault = 0.0D; icFault = 0.0D; vaFault = 0.0D; vbFault = 0.0D; vcFault = 0.0D; } // Add fault information for the current line to the results element results.Add( new XElement("line", new XElement("name", lineDefinition.Name), new XElement("length", faultDataSet.LineDistance), new XElement("faultType", faultDataSet.FaultType.ToString()), new XElement("faultDistance", faultDataSet.FaultDistance), new XElement("cyclesOfData", faultDataSet.Cycles.Count), new XElement("faultCycles", faultDataSet.FaultCycleCount), new XElement("faultCalculationCycle", faultDataSet.FaultCalculationCycle), new XElement("firstFaultCycleTime", firstFaultCycleTime.ToString(DateTimeFormat)), new XElement("iaFault", iaFault), new XElement("ibFault", ibFault), new XElement("icFault", icFault), new XElement("vaFault", vaFault), new XElement("vbFault", vbFault), new XElement("vcFault", vcFault), new XElement("iaMax", faultDataSet.Cycles.Max(cycle => cycle.AN.I.RMS)), new XElement("ibMax", faultDataSet.Cycles.Max(cycle => cycle.BN.I.RMS)), new XElement("icMax", faultDataSet.Cycles.Max(cycle => cycle.CN.I.RMS)), new XElement("vaMin", faultDataSet.Cycles.Min(cycle => cycle.AN.V.RMS)), new XElement("vbMin", faultDataSet.Cycles.Min(cycle => cycle.BN.V.RMS)), new XElement("vcMin", faultDataSet.Cycles.Min(cycle => cycle.CN.V.RMS)) )); } // Create the XML document resultsDocument = new XDocument( new XElement("openFLE", results) ); if (!Directory.Exists(m_resultsDirectory)) Directory.CreateDirectory(m_resultsDirectory); // Create file path based on drop file name and avoid file name collisions resultsFileName = string.Format("{0}.xml", FilePath.GetFileNameWithoutExtension(disturbanceFiles.First().DestinationPath)); resultsFilePath = FilePath.GetUniqueFilePathWithBinarySearch(Path.Combine(m_resultsDirectory, resultsFileName)); resultsDocument.Save(resultsFilePath); }
/// <summary> /// Writes the results to the output source. /// </summary> /// <param name="disturbanceRecorder">The device that collected the disturbance data.</param> /// <param name="disturbanceFiles">Information about the data files collected during the disturbance.</param> /// <param name="lineDataSets">The data sets used for analysis to determine fault location.</param> public override void WriteResults(Device disturbanceRecorder, ICollection<DisturbanceFile> disturbanceFiles, ICollection<Tuple<Line, FaultLocationDataSet>> lineDataSets) { int deviceID; int fileGroupID; using (SqlConnection connection = new SqlConnection(m_connectionString)) using (SqlCommand command = connection.CreateCommand()) { connection.Open(); using (SqlTransaction transaction = connection.BeginTransaction()) { command.Transaction = transaction; try { // Get the ID of the device that captured the fault data command.CommandText = "SELECT ID FROM Device WHERE FLEID = @fleID"; command.Parameters.AddWithValue("@fleID", disturbanceRecorder.ID); deviceID = Convert.ToInt32(command.ExecuteScalar()); command.Parameters.Clear(); // Create a group for the disturbance files command.CommandText = "INSERT INTO FileGroup (DeviceID) VALUES (@deviceID)"; command.Parameters.AddWithValue("@deviceID", deviceID); command.ExecuteNonQuery(); command.Parameters.Clear(); // Get the identifier for the file group command.CommandText = "SELECT @@IDENTITY"; fileGroupID = Convert.ToInt32(command.ExecuteScalar()); // Insert disturbance file information into the database foreach (DisturbanceFile disturbanceFile in disturbanceFiles) { command.CommandText = "INSERT INTO DisturbanceFile (FileGroupID, SourcePath, DestinationPath, FileSize, " + "CreationTime, LastWriteTime, LastAccessTime, FileWatcherStartTime, FileWatcherEndTime, " + "FLEStartTime, FLEEndTime) VALUES (@fileGroupID, @sourcePath, @destinationPath, @fileSize, " + "@creationTime, @lastWriteTime, @lastAccessTime, @fileWatcherStartTime, @fileWatcherEndTime, " + "@fleStartTime, @fleEndTime)"; command.Parameters.AddWithValue("@fileGroupID", fileGroupID); command.Parameters.AddWithValue("@sourcePath", disturbanceFile.SourcePath); command.Parameters.AddWithValue("@destinationPath", disturbanceFile.DestinationPath); command.Parameters.AddWithValue("@fileSize", disturbanceFile.FileSize); command.Parameters.AddWithValue("@creationTime", disturbanceFile.CreationTime); command.Parameters.AddWithValue("@lastWriteTime", disturbanceFile.LastWriteTime); command.Parameters.AddWithValue("@lastAccessTime", disturbanceFile.LastAccessTime); command.Parameters.AddWithValue("@fileWatcherStartTime", disturbanceFile.FileWatcherTimeStarted); command.Parameters.AddWithValue("@fileWatcherEndTime", disturbanceFile.FileWatcherTimeProcessed); command.Parameters.AddWithValue("@fleStartTime", disturbanceFile.FLETimeStarted); command.Parameters.AddWithValue("@fleEndTime", disturbanceFile.FLETimeProcessed); command.ExecuteNonQuery(); command.Parameters.Clear(); } // Insert information about the faults into the database foreach (Tuple<Line, FaultLocationDataSet> lineDataSet in lineDataSets) InsertFaultData(lineDataSet.Item1, lineDataSet.Item2, command, fileGroupID); transaction.Commit(); } catch (Exception commitException) { try { // Commit failed - attempt to rollback transaction.Rollback(); } catch (Exception rollbackException) { // Throw exception to indicate that rollback failed throw new InvalidOperationException(string.Format("Unable to rollback database transaction after failure to commit: {0}", rollbackException.Message), rollbackException); } // Throw exception to indicate commit failed throw new InvalidOperationException(string.Format("Unable to commit database transaction due to exception: {0}", commitException.Message), commitException); } } } }