diff options
| -rw-r--r-- | AirPollutionWebApi.userprefs | 4 | ||||
| -rw-r--r-- | AirPollutionWebApi/AirPollutionWebApi.csproj | 2 | ||||
| -rw-r--r-- | AirPollutionWebApi/Controllers/ReadingsController.cs | 49 | ||||
| -rw-r--r-- | AirPollutionWebApi/SqlOperator.cs | 32 |
4 files changed, 37 insertions, 50 deletions
diff --git a/AirPollutionWebApi.userprefs b/AirPollutionWebApi.userprefs index fc3817b..9167a78 100644 --- a/AirPollutionWebApi.userprefs +++ b/AirPollutionWebApi.userprefs @@ -2,7 +2,9 @@ <MonoDevelop.Ide.ItemProperties.AirPollutionWebApi PreferredExecutionTarget="MonoDevelop.Default" /> <MonoDevelop.Ide.Workbench ActiveDocument="AirPollutionWebApi/Controllers/ReadingsController.cs"> <Files> - <File FileName="AirPollutionWebApi/Controllers/ReadingsController.cs" Line="26" Column="18" /> + <File FileName="AirPollutionWebApi/Controllers/ReadingsController.cs" Line="43" Column="66" /> + <File FileName="AirPollutionWebApi/SqlOperator.cs" Line="21" Column="64" /> + <File FileName="AirPollutionWebApi/Controllers/HomeController.cs" Line="1" Column="1" /> </Files> </MonoDevelop.Ide.Workbench> <MonoDevelop.Ide.Workspace ActiveConfiguration="Debug" /> diff --git a/AirPollutionWebApi/AirPollutionWebApi.csproj b/AirPollutionWebApi/AirPollutionWebApi.csproj index 5a20af0..1fc9e17 100644 --- a/AirPollutionWebApi/AirPollutionWebApi.csproj +++ b/AirPollutionWebApi/AirPollutionWebApi.csproj @@ -8,7 +8,7 @@ <OutputType>Library</OutputType>
<RootNamespace>AirPollutionWebApi</RootNamespace>
<AssemblyName>AirPollutionWebApi</AssemblyName>
- <TargetFrameworkVersion>v4.5</TargetFrameworkVersion>
+ <TargetFrameworkVersion>v4.6.2</TargetFrameworkVersion>
</PropertyGroup>
<PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
<DebugSymbols>true</DebugSymbols>
diff --git a/AirPollutionWebApi/Controllers/ReadingsController.cs b/AirPollutionWebApi/Controllers/ReadingsController.cs index 19b87e7..480d8c0 100644 --- a/AirPollutionWebApi/Controllers/ReadingsController.cs +++ b/AirPollutionWebApi/Controllers/ReadingsController.cs @@ -1,4 +1,4 @@ -using System.Collections.Generic;
+using System;
using System.Web.Http;
using AirPollutionWebApi.Models; using AirPollutionWebApi.Singletons; @@ -9,15 +9,19 @@ namespace AirPollutionWebApi.Controllers { public ReadingsController() { } - public IEnumerable<Reading> GetAllReadings() + public IHttpActionResult GetAllReadings() {
- var readings = SqlOperator.GetAllReadings(); - return readings; + var command = "SELECT * FROM Readings";
+ var readings = SqlOperator.GetReadings(command); + + if (readings.Count > 0) return Ok(readings);
+ else return NotFound(); } public IHttpActionResult GetReading(int id) - { - var reading = SqlOperator.GetReadingById(id); + {
+ var command = $"SELECT * FROM Readings WHERE Id={id}"; + var reading = SqlOperator.GetReadings(command)[0]; if (reading != null) return Ok(reading); else return NotFound(); @@ -25,20 +29,25 @@ namespace AirPollutionWebApi.Controllers [Route("api/Readings/latest")]
public IHttpActionResult GetLatestReading()
- {
- var readings = SqlOperator.GetAllReadings();
- Reading latestReading = null;
- - foreach(var reading in readings)
- {
- if (latestReading == null) latestReading = reading;
- if (reading.TimeStamp > latestReading.TimeStamp)
- latestReading = reading;
- }
+ { + var command = "SELECT MAX(TimeStamp) FROM Readings"; + var reading = SqlOperator.GetReadings(command)[0]; - if (latestReading != null) return Ok(latestReading); + if (reading != null) return Ok(reading); else return NotFound();
} +
+ [Route("api/Readings/lastweek")] + public IHttpActionResult GetReadingsFromLastWeek() + {
+ var timeNow = DateTimeOffset.Now.ToUnixTimeSeconds(); + var command = "SELECT * FROM Readings " +
+ $"WHERE TimeStamp BETWEEN {timeNow-7*24*3600} AND {timeNow}"; + var readings = SqlOperator.GetReadings(command); + + if (readings.Count > 0) return Ok(readings); + else return NotFound(); + } public IHttpActionResult PutReading(int id, Reading reading) { @@ -61,8 +70,10 @@ namespace AirPollutionWebApi.Controllers } public IHttpActionResult DeleteReading(int id) - { - Reading reading = SqlOperator.GetReadingById(id); + {
+ var command = $"SELECT * FROM Readings WHERE Id={id}";
+ + Reading reading = SqlOperator.GetReadings(command)[0]; if (reading == null) { return NotFound(); diff --git a/AirPollutionWebApi/SqlOperator.cs b/AirPollutionWebApi/SqlOperator.cs index 74ad2e8..bde60f8 100644 --- a/AirPollutionWebApi/SqlOperator.cs +++ b/AirPollutionWebApi/SqlOperator.cs @@ -7,7 +7,8 @@ namespace AirPollutionWebApi.Singletons { public static class SqlOperator { - const string ConnectionString = "Server=tcp:forschool.database.windows.net,1433;" + + const string ConnectionString = + "Server=tcp:forschool.database.windows.net,1433;" + "Initial Catalog=schooldb;" + "Persist Security Info=False;" + "User ID=***REMOVED***;" + @@ -17,13 +18,12 @@ namespace AirPollutionWebApi.Singletons "TrustServerCertificate=False;" + "Connection Timeout=30;"; - public static List<Reading> GetAllReadings() + public static List<Reading> GetReadings(string command) { var readings = new List<Reading>(); using (SqlConnection databaseConnection = new SqlConnection(ConnectionString)) { - string command = "SELECT * FROM Readings;"; databaseConnection.Open(); SqlCommand selectCommand = new SqlCommand(command, databaseConnection); var reader = selectCommand.ExecuteReader(); @@ -43,32 +43,6 @@ namespace AirPollutionWebApi.Singletons return readings; } - public static Reading GetReadingById(int id) - { - Reading reading = new Reading(); - - using (SqlConnection databaseConnection = new SqlConnection(ConnectionString)) - { - string command = $"SELECT * FROM Readings WHERE Id={id};"; - databaseConnection.Open(); - SqlCommand selectCommand = new SqlCommand(command, databaseConnection); - var reader = selectCommand.ExecuteReader(); - while (reader.Read()) - { - reading = new Reading - { - Id = reader.GetInt32(0), - TimeStamp = reader.GetInt32(1), - Co = reader.GetInt32(2), - No = reader.GetInt32(3), - So = reader.GetInt32(4) - }; - } - } - - return reading; - } - public static void PutReading(int id, Reading reading) { using (SqlConnection dbCon = new SqlConnection(ConnectionString)) |