Refactored code and added method in controller for getting readings from last week.

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