1 files changed, 84 insertions, 0 deletions
diff --git a/PollutometerWebApi/SqlOperator.cs b/PollutometerWebApi/SqlOperator.cs
new file mode 100644
index 0000000..d85aa3d
--- /dev/null
+++ b/PollutometerWebApi/SqlOperator.cs
@@ -0,0 +1,84 @@
+using System;
+using System.Collections.Generic;
+using System.Data.SqlClient;
+using PollutometerWebApi.Models;
+
+namespace PollutometerWebApi.Singletons
+{
+	public static class SqlOperator
+	{
+		const string ConnectionString = 
+            "Server=tcp:forschool.database.windows.net,1433;" +
+            "Initial Catalog=schooldb;" +
+            "Persist Security Info=False;" +
+            "User ID=***REMOVED***;" +
+            "Password=***REMOVED***;" +
+            "MultipleActiveResultSets=False;" +
+            "Encrypt=True;" +
+            "TrustServerCertificate=False;" +
+            "Connection Timeout=30;";
+
+        public static List<Reading> GetReadings(string command)
+		{
+			var readings = new List<Reading>();
+
+			using (SqlConnection databaseConnection = new SqlConnection(ConnectionString))
+			{
+				databaseConnection.Open();
+				SqlCommand selectCommand = new SqlCommand(command, databaseConnection);
+				var reader = selectCommand.ExecuteReader();
+				while (reader.Read())
+				{
+					readings.Add(new Reading
+					{
+                        Id = reader.GetInt32(0),
+                        TimeStamp = reader.GetInt32(1),
+                        Co = reader.GetDouble(2),
+						No = reader.GetDouble(3),
+						So = reader.GetDouble(4)
+					});
+				}
+			}
+
+            return readings;
+		}
+
+        public static void PutReading(int id, Reading reading)
+		{
+			using (SqlConnection dbCon = new SqlConnection(ConnectionString))
+			{
+				dbCon.Open();
+                string query = $"UPDATE Readings SET TimeStamp='{reading.TimeStamp}'," +
+                    $"Co='{reading.Co}', No='{reading.No}', So='{reading.So}' WHERE Id={id};";
+				var cmd = new SqlCommand(query, dbCon);
+				cmd.ExecuteNonQuery();
+				dbCon.Close();
+			}
+		}
+
+        public static void PostReading(Reading reading)
+		{
+			using (SqlConnection dbCon = new SqlConnection(ConnectionString))
+			{
+				dbCon.Open();
+                string query = $"INSERT INTO Readings (TimeStamp,Co,No,So)" +
+                    $"VALUES('{reading.TimeStamp}',{reading.Co},{reading.No},{reading.So});";
+				var cmd = new SqlCommand(query, dbCon);
+				cmd.ExecuteNonQuery();
+				dbCon.Close();
+			}
+		}
+
+        public static void DeleteReading(int id)
+		{
+			using (SqlConnection dbCon = new SqlConnection(ConnectionString))
+			{
+				dbCon.Open();
+				string query = $"DELETE FROM Readings WHERE Id={id};";
+				var cmd = new SqlCommand(query, dbCon);
+				cmd.ExecuteNonQuery();
+				dbCon.Close();
+			}
+		}
+	}
+}
+\ No newline at end of file