From 8f083ddb74fb74d6c2b5d347472aa26f18a4b15c Mon Sep 17 00:00:00 2001 From: marcinzelent Date: Thu, 23 Nov 2017 11:19:37 +0100 Subject: Rewrote almost everything, got rid of singleton. --- AirPollutionWebApi.userprefs | 25 +---- AirPollutionWebApi/AirPollutionWebApi.csproj | 3 +- .../Controllers/ReadingsController.cs | 74 +++++++------- AirPollutionWebApi/Models/Reading.cs | 4 +- AirPollutionWebApi/Singletons/Singleton.cs | 99 ------------------- AirPollutionWebApi/SqlOperator.cs | 110 +++++++++++++++++++++ 6 files changed, 148 insertions(+), 167 deletions(-) delete mode 100644 AirPollutionWebApi/Singletons/Singleton.cs create mode 100644 AirPollutionWebApi/SqlOperator.cs diff --git a/AirPollutionWebApi.userprefs b/AirPollutionWebApi.userprefs index 6cd10e6..68dadb7 100644 --- a/AirPollutionWebApi.userprefs +++ b/AirPollutionWebApi.userprefs @@ -1,24 +1 @@ - - - - - - - - - - - - - - - - - - - - - - - - \ No newline at end of file + \ No newline at end of file diff --git a/AirPollutionWebApi/AirPollutionWebApi.csproj b/AirPollutionWebApi/AirPollutionWebApi.csproj index a59670c..5a20af0 100644 --- a/AirPollutionWebApi/AirPollutionWebApi.csproj +++ b/AirPollutionWebApi/AirPollutionWebApi.csproj @@ -78,7 +78,6 @@ - @@ -89,7 +88,7 @@ - + diff --git a/AirPollutionWebApi/Controllers/ReadingsController.cs b/AirPollutionWebApi/Controllers/ReadingsController.cs index c8c8087..e8eb0fa 100644 --- a/AirPollutionWebApi/Controllers/ReadingsController.cs +++ b/AirPollutionWebApi/Controllers/ReadingsController.cs @@ -1,9 +1,5 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Web; -using System.Web.Http; -using System.Web.Mvc; +using System.Collections.Generic; +using System.Web.Http; using AirPollutionWebApi.Models; using AirPollutionWebApi.Singletons; @@ -15,49 +11,49 @@ namespace AirPollutionWebApi.Controllers public IEnumerable GetAllReadings() { - Singleton.Instance.GetData(); - return Singleton.Instance.Readings; + var readings = SqlOperator.GetAllReadings(); + return readings; } - //public IHttpActionResult GetReading(int id) - //{ - // var customer = Singleton.Instance.Readings.FirstOrDefault((p) => p.TimeStamp == id); - - // if (customer != null) return Ok(customer); - // else return NotFound(); - //} - - //public IHttpActionResult PutReading(int id, Reading customer) - //{ - // if (customer != null) - // { - // Singleton.Instance.PutData(id, customer); - // return Ok(); - // } - // else return BadRequest(); - //} - - public IHttpActionResult PostReading(Reading customer) + public IHttpActionResult GetReading(int id) { - if (customer != null) + var reading = SqlOperator.GetReadingById(id); + + if (reading != null) return Ok(reading); + else return NotFound(); + } + + public IHttpActionResult PutReading(int id, Reading reading) + { + if (reading != null) + { + SqlOperator.PutReading(id, reading); + return Ok(); + } + else return BadRequest(); + } + + public IHttpActionResult PostReading(Reading reading) + { + if (reading != null) { - Singleton.Instance.PostData(customer); + SqlOperator.PostReading(reading); return Ok(); } else return BadRequest(); } - //public IHttpActionResult DeleteReading(int id) - //{ - // Reading customer = Singleton.Instance.Readings.Find((p) => p.Id == id); - // if (customer == null) - // { - // return NotFound(); - // } + public IHttpActionResult DeleteReading(int id) + { + Reading reading = SqlOperator.GetReadingById(id); + if (reading == null) + { + return NotFound(); + } - // Singleton.Instance.DeleteData(id); + SqlOperator.DeleteReading(id); - // return Ok(customer); - //} + return Ok(reading); + } } } \ No newline at end of file diff --git a/AirPollutionWebApi/Models/Reading.cs b/AirPollutionWebApi/Models/Reading.cs index 4880659..07f4599 100644 --- a/AirPollutionWebApi/Models/Reading.cs +++ b/AirPollutionWebApi/Models/Reading.cs @@ -1,6 +1,4 @@ -using System; - -namespace AirPollutionWebApi.Models +namespace AirPollutionWebApi.Models { public class Reading { diff --git a/AirPollutionWebApi/Singletons/Singleton.cs b/AirPollutionWebApi/Singletons/Singleton.cs deleted file mode 100644 index 00269ad..0000000 --- a/AirPollutionWebApi/Singletons/Singleton.cs +++ /dev/null @@ -1,99 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Data.SqlClient; -using AirPollutionWebApi.Models; - -namespace AirPollutionWebApi.Singletons -{ - public class Singleton - { - static Singleton instance; - 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 List Readings = new List(); - - Singleton() {} - - public static Singleton Instance - { - get - { - if (instance == null) - { - instance = new Singleton(); - } - return instance; - } - } - - public void GetData() - { - Readings = new List(); - 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) - }); - } - } - } - - //public void PutData(int id, Reading reading) - //{ - // using (SqlConnection dbCon = new SqlConnection(ConnectionString)) - // { - // dbCon.Open(); - // string query = $"UPDATE Readings SET FirstName='{reading.FirstName}', LastName='{reading.LastName}', Year='{reading.Year}' WHERE Id={id};"; - // var cmd = new SqlCommand(query, dbCon); - // cmd.ExecuteNonQuery(); - // dbCon.Close(); - // } - // GetData(); - //} - - public void PostData(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(); - } - GetData(); - } - - public void DeleteData(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(); - } - GetData(); - } - } -} \ No newline at end of file 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 GetAllReadings() + { + var readings = new List(); + + 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 -- cgit v1.2.3