public ActionResult <IEnumerable <HistoryElement> > GetRange([FromQuery(Name = "primaryKey")] DateTime primaryKey, [FromQuery(Name = "takeAmount")] int takeAmount) { List <HistoryElement> returnval = new List <HistoryElement>(); using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable()) { using (NpgsqlCommand command = usable.Connection.CreateCommand()) { if (primaryKey == default(DateTime)) { command.CommandText = "select * from t_history order by msgtimestamp desc limit @amount;"; } else { command.CommandText = "select * from t_history where msgtimestamp <= @fromTime order by msgtimestamp desc limit @amount;"; command.Parameters.AddWithValue("@fromTime", NpgsqlDbType.Timestamp, primaryKey); } command.Parameters.AddWithValue("@amount", NpgsqlDbType.Integer, takeAmount); using (NpgsqlDataReader dataReader = command.ExecuteReader()) { while (dataReader.Read()) { returnval.Add(new HistoryElement((DateTime)dataReader[0], (string)dataReader[1], (string)dataReader[2], (string)dataReader[3])); } } } } return(Ok(returnval)); }
public static void AddLogEntry(HistoryElement element) { using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable()) { using (NpgsqlCommand command = usable.Connection.CreateCommand()) { command.CommandText = $"INSERT INTO t_history(msgtimestamp, source, status, message) Values (@time, '{element.Source}', '{element.Status}', '{element.Message}');"; command.Parameters.AddWithValue("@time", NpgsqlDbType.Timestamp, element.TimeStamp); command.ExecuteNonQuery(); } } }
public ActionResult Put([FromBody] SettingsData settingsData) { using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable()) { using (NpgsqlCommand command = usable.Connection.CreateCommand()) { command.Transaction = usable.Connection.BeginTransaction(); command.CommandText = "select 1 from t_settings;"; NpgsqlDataReader reader = command.ExecuteReader(); if (reader.Read() == false) { reader.Dispose(); command.CommandText = $@" INSERT INTO t_settings (id, enabled, location, location_friendly_name, override_value, weather_enabled) VALUES (DEFAULT, false, '', '', 100, false);"; command.ExecuteNonQuery(); } else { reader.Dispose(); } // select where id == 1 if none, then insert command.CommandText = $@" UPDATE t_settings SET enabled = {settingsData.Enabled}, location = '{settingsData.Location}', location_friendly_name = '{settingsData.LocationFriendlyName}', override_value = {settingsData.OverrideValue}, weather_enabled = {settingsData.WeatherEnabled} WHERE id = 1;"; command.ExecuteNonQuery(); command.CommandText = @" TRUNCATE TABLE ONLY t_humidity_sensor;"; command.ExecuteNonQuery(); command.CommandText = "INSERT INTO t_humidity_sensor (settings_id, friendly_name, real_name) VALUES (1, @friendlyName, @realName);"; foreach ((string realSensorName, string friendlySensorName) in settingsData.HumiditySensors) { command.Parameters.AddWithValue("@friendlyName", NpgsqlDbType.Text, friendlySensorName); command.Parameters.AddWithValue("@realName", NpgsqlDbType.Text, realSensorName); command.ExecuteNonQuery(); } command.Transaction.Commit(); } } return(NoContent()); }
public static T LoadFromDataBase <T>(Func <T> computeDefaultValue = null) { using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable()) using (NpgsqlCommand command = usable.Connection.CreateCommand()) { InitTableRow <T>(command); command.CommandText = "SELECT serialized_object FROM t_object_storage WHERE data_type_name = @type_name;"; command.Parameters.AddWithValue("@type_name", NpgsqlDbType.Text, GetTypeName <T>()); using (NpgsqlDataReader reader = command.ExecuteReader()) { return(reader.Read() == false ? (computeDefaultValue == null ? default(T) : computeDefaultValue()) : JsonConvert.DeserializeObject <T>(reader.GetString(0))); } } }
public ActionResult <int> GetAmount() { using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable()) { using (NpgsqlCommand command = usable.Connection.CreateCommand()) { command.CommandText = "select count(*) from t_history;"; using (NpgsqlDataReader dataReader = command.ExecuteReader()) { while (dataReader.Read()) { return(Ok(dataReader[0])); } } } } throw new Exception("Dieser Punkt darf nicht erreicht werden!"); }
private static double GetGlobalOverride() { using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable()) { using (NpgsqlCommand command = usable.Connection.CreateCommand()) { command.CommandText = "select override_value from t_settings;"; using (NpgsqlDataReader dataReader = command.ExecuteReader()) { if (dataReader.Read() == false) { throw new Exception("Es existieren keine SettingsDaten!"); } return((double)dataReader.GetInt32(0) / 100); } } } }
public ActionResult <HistoryElement> Get(DateTime primaryKey) { using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable()) { using (NpgsqlCommand command = usable.Connection.CreateCommand()) { command.CommandText = "select * from t_history where msgtimestamp == @primaryKey;"; command.Parameters.AddWithValue("@primaryKey", NpgsqlDbType.Timestamp, primaryKey); using (NpgsqlDataReader dataReader = command.ExecuteReader()) { while (dataReader.Read()) { return(Ok(new HistoryElement((DateTime)dataReader[0], (string)dataReader[1], (string)dataReader[2], (string)dataReader[3]))); } } } } throw new Exception("Dieser Punkt darf nicht erreicht werden!"); }
private static bool GetGlobalScheduleEnabled() { using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable()) { using (NpgsqlCommand command = usable.Connection.CreateCommand()) { command.CommandText = "select enabled from t_settings;"; using (NpgsqlDataReader dataReader = command.ExecuteReader()) { if (dataReader.Read() == false) { throw new Exception("Es existieren keine SettingsDaten!"); } return(dataReader.GetBoolean(0)); } } } }
public ActionResult CreateNewEntry(HistoryElement element) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable()) { using (NpgsqlCommand command = usable.Connection.CreateCommand()) { command.CommandText = $"INSERT INTO t_history(msgtimestamp, source, status, message) Values (@time, @source, @status, @message);"; command.Parameters.AddWithValue("@time", NpgsqlDbType.Timestamp, element.TimeStamp); command.Parameters.AddWithValue("@source", NpgsqlDbType.Text, element.Source); command.Parameters.AddWithValue("@status", NpgsqlDbType.Text, element.Status); command.Parameters.AddWithValue("@message", NpgsqlDbType.Text, element.Message); command.ExecuteNonQuery(); } } return(Ok()); }
public ActionResult <IEnumerable <HistoryElement> > GetSearched([FromQuery(Name = "searchString")] string searchString, [FromQuery(Name = "collumn")] int collumn, [FromQuery(Name = "amount")] int amount) { List <HistoryElement> returnval = new List <HistoryElement>(); using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable()) { using (NpgsqlCommand command = usable.Connection.CreateCommand()) { command.CommandText = "select * from t_history where lower(" + GetCollumnName(collumn) + "::text) like lower('%" + searchString + "%') order by msgtimestamp desc limit @amount;"; //command.Parameters.AddWithValue("@tableName", GetCollumnName(collumn)); //command.Parameters.AddWithValue("@searchstring", searchString); command.Parameters.AddWithValue("@amount", NpgsqlDbType.Integer, amount); using (NpgsqlDataReader dataReader = command.ExecuteReader()) { while (dataReader.Read()) { returnval.Add(new HistoryElement((DateTime)dataReader[0], (string)dataReader[1], (string)dataReader[2], (string)dataReader[3])); } } } } return(Ok(returnval)); }
public ActionResult <SettingsData> Get() { SettingsData data = new SettingsData { Valid = true, HumiditySensors = new Dictionary <string, string>() }; using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable()) { using (NpgsqlCommand command = usable.Connection.CreateCommand()) { command.CommandText = "select enabled, location, location_friendly_name, override_value, weather_enabled from t_settings;"; using (NpgsqlDataReader dataReader = command.ExecuteReader()) { if (dataReader.Read() == false) { return(NoContent()); } data.Enabled = dataReader.GetBoolean(0); data.Location = dataReader.GetString(1); data.LocationFriendlyName = dataReader.GetString(2); data.OverrideValue = dataReader.GetInt32(3); data.WeatherEnabled = dataReader.GetBoolean(4); } command.CommandText = "select friendly_name, real_name from t_humidity_sensor where settings_id = 1;"; using (NpgsqlDataReader dataReader = command.ExecuteReader()) { while (dataReader.Read()) { data.HumiditySensors.Add(dataReader.GetString(1), dataReader.GetString(0)); } } } } return(Ok(data)); }
public ActionResult <HistoryData> Get() { HistoryData data = new HistoryData { Valid = true }; using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable()) { using (NpgsqlCommand command = usable.Connection.CreateCommand()) { command.CommandText = "select * from t_history order by msgtimestamp desc;"; using (NpgsqlDataReader dataReader = command.ExecuteReader()) { while (dataReader.Read()) { data.HistoryElements.Add(new HistoryElement((DateTime)dataReader[0], (string)dataReader[1], (string)dataReader[2], (string)dataReader[3])); } } } return(Ok(data)); } }