/// <summary> /// GET: api/AlarmReads/5 /// Accesses the database through Rest Api /// </summary> /// <returns>IHttpActionResult 'OK' with the alarm with the given ID if it exists or IHttpActionResult 'Not Found' if it does not exists</returns> public IHttpActionResult GetAlarmById(int id) { AlarmComplete alarm = null; try { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "SELECT * FROM Alarms a join SensorsParameters sp on a.Sensor_Parameter_Id = sp.Read_Id WHERE Id=@id;"; cmd.Parameters.AddWithValue("@id", id); cmd.Connection = conn; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { alarm = new AlarmComplete ( (int)reader["Id"], (int)reader["Sensor_Parameter_Id"], (string)reader["Parameter"], (int)reader["Read_Value"], (string)reader["Location"], (DateTime)reader["DateTime"], (string)reader["Alarm_Message"] ); } reader.Close(); conn.Close(); } catch (Exception ex) { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } throw ex; } if (alarm == null) { return(NotFound()); } else { return(Ok(alarm)); } }
public IEnumerable <AlarmComplete> GetAlarmsBetweenTwoDatesOnCity(string date1, string date2, string city) { List <AlarmComplete> lista = new List <AlarmComplete>(); AlarmComplete alarm = null; try { conn.Open(); //11-30-2017 SqlCommand cmd = new SqlCommand(); //cmd.CommandText = "SELECT * FROM SensorsParameters WHERE CONVERT(varchar(15), DateTime, 112) = '20171130' AND Location = @city"; cmd.CommandText = "SELECT * FROM SensorsParameters s JOIN Alarms a ON s.Read_Id = a.Sensor_Parameter_Id WHERE CONVERT(varchar(15), s.DateTime, 112) BETWEEN @date1 AND @date2 AND s.Location = @city;"; cmd.Parameters.AddWithValue("@date1", date1); cmd.Parameters.AddWithValue("@date2", date2); cmd.Parameters.AddWithValue("@city", city); cmd.Connection = conn; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { alarm = new AlarmComplete ( (int)reader["Id"], (int)reader["Sensor_Parameter_Id"], (string)reader["Parameter"], (int)reader["Read_Value"], (string)reader["Location"], (DateTime)reader["DateTime"], (string)reader["Alarm_Message"] ); lista.Add(alarm); } reader.Close(); conn.Close(); } catch (Exception ex) { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } throw ex; } return(lista); }
/// <summary> /// GET: api/AlarmReads /// Accesses the database through Rest Api /// </summary> /// <returns>IEnumerable with all the alarms</returns> public IEnumerable <AlarmComplete> GetAllAlarms() { List <AlarmComplete> alarms = new List <AlarmComplete>(); AlarmComplete alarm = null; try { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "SELECT * FROM Alarms a join SensorsParameters sp on a.Sensor_Parameter_Id = sp.Read_Id;"; cmd.Connection = conn; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { alarm = new AlarmComplete ( (int)reader["Id"], (int)reader["Sensor_Parameter_Id"], (string)reader["Parameter"], (int)reader["Read_Value"], (string)reader["Location"], (DateTime)reader["DateTime"], (string)reader["Alarm_Message"] ); alarms.Add(alarm); } reader.Close(); conn.Close(); } catch (Exception ex) { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } throw ex; } return(alarms); }