From 08e7a82cc7b237e550c0236bebd4f41c3c3f4acf Mon Sep 17 00:00:00 2001
From: marcinzelent <zelent.marcin@gmail.com>
Date: Fri, 16 Mar 2018 21:40:28 +0100
Subject: Moved API to separate folder.

---
 pollutometer-api/PollutometerWebApi/SqlOperator.cs | 83 ++++++++++++++++++++++
 1 file changed, 83 insertions(+)
 create mode 100644 pollutometer-api/PollutometerWebApi/SqlOperator.cs

(limited to 'pollutometer-api/PollutometerWebApi/SqlOperator.cs')

diff --git a/pollutometer-api/PollutometerWebApi/SqlOperator.cs b/pollutometer-api/PollutometerWebApi/SqlOperator.cs
new file mode 100644
index 0000000..1e296a1
--- /dev/null
+++ b/pollutometer-api/PollutometerWebApi/SqlOperator.cs
@@ -0,0 +1,83 @@
+using System.Collections.Generic;
+using System.Data.SqlClient;
+using PollutometerWebApi.Models;
+
+namespace PollutometerWebApi
+{
+	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
-- 
cgit v1.2.3