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);
}
}
}
}
