internal static bool GetHighsAndLows(DateTime observationDate, ref HighLowObservation highsAndLows) { SqliteConnection connection = createConnection(); SqliteCommand command = connection.CreateCommand(); command.CommandText = "SELECT EntryID," + "ObservationDate," + "HighTemp," + "LowTemp," + "HighHeatIndex," + "LowHeatIndex," + "HighWindChill," + "LowWindChill," + "HighWindSpeed," + "HighHumidity," + "LowHumidity," + "HighDewPoint," + "LowDewPoint," + "HighPressure," + "LowPressure," + "DayRain," + "HighRainRate " + "FROM HighsAndLows WHERE Date(ObservationDate) = Date('" + observationDate.ToString("yyyy-MM-dd") + "')"; SqliteDataReader reader = command.ExecuteReader(); bool resultFound; if (reader.Read()) { highsAndLows.ObservationDate = DateTime.Parse(reader.GetString(1)); highsAndLows.HighTemp = reader.GetFloat(2); highsAndLows.LowTemp = reader.GetFloat(3); highsAndLows.HighHeatIndex = reader.GetFloat(4); highsAndLows.LowHeatIndex = reader.GetFloat(5); highsAndLows.HighWindChill = reader.GetFloat(6); highsAndLows.LowWindChill = reader.GetFloat(7); highsAndLows.HighWindSpeed = reader.GetFloat(8); highsAndLows.HighHumidity = reader.GetFloat(9); highsAndLows.LowHumidity = reader.GetFloat(10); highsAndLows.HighDewPoint = reader.GetFloat(11); highsAndLows.LowDewPoint = reader.GetFloat(12); highsAndLows.HighPressure = reader.GetFloat(13); highsAndLows.LowPressure = reader.GetFloat(14); highsAndLows.DayRain = reader.GetFloat(15); highsAndLows.HighRainRate = reader.GetFloat(16); resultFound = true; } else { resultFound = false; } connection.Close(); return(resultFound); }
internal static List <HighLowObservation> GetHighsAndLows(DateTime observationStartDate, DateTime observationEndDate) { SqliteConnection connection = createConnection(); SqliteCommand command = connection.CreateCommand(); command.CommandText = "SELECT EntryID," + "ObservationDate," + "HighTemp," + "LowTemp," + "HighHeatIndex," + "LowHeatIndex," + "HighWindChill," + "LowWindChill," + "HighWindSpeed," + "HighHumidity," + "LowHumidity," + "HighDewPoint," + "LowDewPoint," + "HighPressure," + "LowPressure," + "DayRain," + "HighRainRate " + "FROM HighsAndLows WHERE Date(ObservationDate) BETWEEN " + "Date('" + observationStartDate.ToString("yyyy-MM-dd") + "') AND Date('" + observationEndDate.ToString("yyyy-MM-dd") + "')"; SqliteDataReader reader = command.ExecuteReader(); List <HighLowObservation> results = new List <HighLowObservation>(); while (reader.Read()) { HighLowObservation highsAndLows = new HighLowObservation(); highsAndLows.ObservationDate = DateTime.Parse(reader.GetString(1)); highsAndLows.HighTemp = reader.GetFloat(2); highsAndLows.LowTemp = reader.GetFloat(3); highsAndLows.HighHeatIndex = reader.GetFloat(4); highsAndLows.LowHeatIndex = reader.GetFloat(5); highsAndLows.HighWindChill = reader.GetFloat(6); highsAndLows.LowWindChill = reader.GetFloat(7); highsAndLows.HighWindSpeed = reader.GetFloat(8); highsAndLows.HighHumidity = reader.GetFloat(9); highsAndLows.LowHumidity = reader.GetFloat(10); highsAndLows.HighDewPoint = reader.GetFloat(11); highsAndLows.LowDewPoint = reader.GetFloat(12); highsAndLows.HighPressure = reader.GetFloat(13); highsAndLows.LowPressure = reader.GetFloat(14); highsAndLows.DayRain = reader.GetFloat(15); highsAndLows.HighRainRate = reader.GetFloat(16); results.Add(highsAndLows); } connection.Close(); return(results); }
internal static void CreateHighsAndLows(HighLowObservation highsAndLows) { SqliteConnection connection = createConnection(); SqliteCommand command = connection.CreateCommand(); command.CommandText = "INSERT INTO HighsAndLows (" + "ObservationDate," + "HighTemp," + "LowTemp," + "HighHeatIndex," + "LowHeatIndex," + "HighWindChill," + "LowWindChill," + "HighWindSpeed," + "HighHumidity," + "LowHumidity," + "HighDewPoint," + "LowDewPoint," + "HighPressure," + "LowPressure," + "DayRain," + "HighRainRate " + ") VALUES(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16)"; command.Prepare(); command.Parameters.AddWithValue("@1", highsAndLows.ObservationDate.ToString("yyyy-MM-dd")); command.Parameters.AddWithValue("@2", highsAndLows.HighTemp.ToString()); command.Parameters.AddWithValue("@3", highsAndLows.LowTemp.ToString()); command.Parameters.AddWithValue("@4", highsAndLows.HighHeatIndex.ToString()); command.Parameters.AddWithValue("@5", highsAndLows.LowHeatIndex.ToString()); command.Parameters.AddWithValue("@6", highsAndLows.HighWindChill.ToString()); command.Parameters.AddWithValue("@7", highsAndLows.LowWindChill.ToString()); command.Parameters.AddWithValue("@8", highsAndLows.HighWindSpeed.ToString()); command.Parameters.AddWithValue("@9", highsAndLows.HighHumidity.ToString()); command.Parameters.AddWithValue("@10", highsAndLows.LowHumidity.ToString()); command.Parameters.AddWithValue("@11", highsAndLows.HighDewPoint.ToString()); command.Parameters.AddWithValue("@12", highsAndLows.LowDewPoint.ToString()); command.Parameters.AddWithValue("@13", highsAndLows.HighPressure.ToString()); command.Parameters.AddWithValue("@14", highsAndLows.LowPressure.ToString()); command.Parameters.AddWithValue("@15", highsAndLows.DayRain.ToString()); command.Parameters.AddWithValue("@16", highsAndLows.HighRainRate.ToString()); command.ExecuteNonQuery(); connection.Close(); }
internal static void UpdateHighsAndLows(HighLowObservation highsAndLows) { SqliteConnection connection = createConnection(); SqliteCommand command = connection.CreateCommand(); command.CommandText = "UPDATE HighsAndLows SET " + "HighTemp = @1," + "LowTemp = @2," + "HighHeatIndex = @3," + "LowHeatIndex = @4," + "HighWindChill = @5," + "LowWindChill = @6," + "HighWindSpeed = @7," + "HighHumidity = @8," + "LowHumidity = @9," + "HighDewPoint = @10," + "LowDewPoint = @11," + "HighPressure = @12," + "LowPressure = @13," + "DayRain = @14," + "HighRainRate = @15 " + "WHERE Date(ObservationDate) = Date('" + highsAndLows.ObservationDate.ToString("yyyy-MM-dd") + "')"; command.Prepare(); command.Parameters.AddWithValue("@1", highsAndLows.HighTemp.ToString()); command.Parameters.AddWithValue("@2", highsAndLows.LowTemp.ToString()); command.Parameters.AddWithValue("@3", highsAndLows.HighHeatIndex.ToString()); command.Parameters.AddWithValue("@4", highsAndLows.LowHeatIndex.ToString()); command.Parameters.AddWithValue("@5", highsAndLows.HighWindChill.ToString()); command.Parameters.AddWithValue("@6", highsAndLows.LowWindChill.ToString()); command.Parameters.AddWithValue("@7", highsAndLows.HighWindSpeed.ToString()); command.Parameters.AddWithValue("@8", highsAndLows.HighHumidity.ToString()); command.Parameters.AddWithValue("@9", highsAndLows.LowHumidity.ToString()); command.Parameters.AddWithValue("@10", highsAndLows.HighDewPoint.ToString()); command.Parameters.AddWithValue("@11", highsAndLows.LowDewPoint.ToString()); command.Parameters.AddWithValue("@12", highsAndLows.HighPressure.ToString()); command.Parameters.AddWithValue("@13", highsAndLows.LowPressure.ToString()); command.Parameters.AddWithValue("@14", highsAndLows.DayRain.ToString()); command.Parameters.AddWithValue("@15", highsAndLows.HighRainRate.ToString()); command.ExecuteNonQuery(); connection.Close(); }
private static void CheckHighsAndLows(Observation observation) { MainWindow.LogMessage("Checking Highs and Lows"); HighLowObservation highsAndLows = new HighLowObservation(); bool exists = DatabaseManager.GetHighsAndLows(observation.ObservationDate, ref highsAndLows); if (exists && highsAndLows.CompareToObservation(observation)) { MainWindow.LogMessage("New high/low values found, updating"); DatabaseManager.UpdateHighsAndLows(highsAndLows); MainWindow.LogMessage("New high/low update complete"); } else if (!exists) { MainWindow.LogMessage("No high/low entry found, creating"); highsAndLows.Initialize(observation); DatabaseManager.CreateHighsAndLows(highsAndLows); MainWindow.LogMessage("New high/low entry created"); } else { MainWindow.LogMessage("No changes to highs and lows detected"); } }