1 files changed, 110 insertions, 0 deletions
diff --git a/AirPollutionWebApi/SqlOperator.cs b/AirPollutionWebApi/SqlOperator.cs
new file mode 100644
index 0000000..74ad2e8
--- /dev/null
+++ b/AirPollutionWebApi/SqlOperator.cs
@@ -0,0 +1,110 @@
+using System;
+using System.Collections.Generic;
+using System.Data.SqlClient;
+using AirPollutionWebApi.Models;
+
+namespace AirPollutionWebApi.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> GetAllReadings()
+		{
+			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();
+				while (reader.Read())
+				{
+					readings.Add(new Reading
+					{
+                        Id = reader.GetInt32(0),
+                        TimeStamp = reader.GetInt32(1),
+                        Co = reader.GetInt32(2),
+						No = reader.GetInt32(3),
+						So = reader.GetInt32(4)
+					});
+				}
+			}
+
+            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))
+			{
+				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