public List <Meassurement> GetAll()
{
List <Meassurement> objList = new List <Meassurement>();
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
using (SqlCommand command = new SqlCommand("SELECT * FROM Meassurement ORDER BY Id", con))
using (SqlDataReader reader = command.ExecuteReader())
{
objList = new List <Meassurement>();
while (reader.Read())
{
Meassurement obj = new Meassurement();
obj.ID = Convert.ToInt32(reader["Id"]);
obj.Pressure = Convert.ToDouble(reader["Pressure"]);
obj.Humidity = Convert.ToDouble(reader["Humidity"]);
obj.Temperature = Convert.ToDouble(reader["Temperature"]);
obj.TimeOfEntry = Convert.ToDateTime(reader["TimeOfEntry"]);
if (objList.All(I => I.ID != Convert.ToInt32(reader["Id"])))
{
objList.Add(obj);
}
}
}
con.Close();
}
return(objList);
}
public void TestDelete()
{
// Arrange
MeassurementsController mc = new MeassurementsController();
// Act
// Insert new M and then delete it, then check if it exists, then check if it doesn't exist
Meassurement m = new Meassurement
{
Humidity = "20",
Pressure = "21",
Temperature = "22",
TimeStamp = DateTime.Now
};
int newMId = mc.Post(m);
Meassurement postedM = mc.Get(newMId);
// Assert
if (postedM != null)
{
// Delete returns id of the deleted.
int delid = mc.Delete(postedM.Id);
// Assert that the deleted id is the same as the id of the posted M
Assert.AreEqual(postedM.Id, delid);
}
else
{
// Failed, did not post to db
Assert.Fail();
}
}
public List <Meassurement> Get()
{
List <Meassurement> ml = new List <Meassurement>();
string sql = "SELECT * FROM Meassurement";
using (SqlConnection dbCon = new SqlConnection(conn))
{
dbCon.Open();
using (SqlCommand cmd = new SqlCommand(sql, dbCon))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
Meassurement m = new Meassurement(
reader.GetInt32(0),
reader.GetString(1),
reader.GetString(2),
reader.GetString(3),
reader.GetDateTime(4)
);
ml.Add(m);
}
}
}
}
}
return(ml);
}
public Meassurement Get(int id)
{
Meassurement m = null;
string sql = "SELECT * FROM Meassurement WHERE Id = " + id;
using (SqlConnection dbCon = new SqlConnection(conn))
{
dbCon.Open();
using (SqlCommand cmd = new SqlCommand(sql, dbCon))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
m = new Meassurement(
reader.GetInt32(0),
reader.GetString(1),
reader.GetString(2),
reader.GetString(3),
reader.GetDateTime(4)
);
}
}
}
}
}
return(m);
}
public async Task TestPost()
{
// Arrange
Meassurement m = new Meassurement(0, "300", "30", "23", DateTime.Now.ToString("dd, MM, yy"));
string uri = "https://easj-mock3.azurewebsites.net/api/Meassurement/";
List <Meassurement> ml = new List <Meassurement>();
// Action
using (HttpClient client = new HttpClient())
{
var jsonStr = JsonConvert.SerializeObject(m);
StringContent content = new StringContent(jsonStr, Encoding.UTF8, "application/json");
HttpResponseMessage response = await client.PostAsync(uri, content);
await response.Content.ReadAsStringAsync();
}
using (HttpClient client = new HttpClient())
{
string content = await client.GetStringAsync(uri);
ml = JsonConvert.DeserializeObject <List <Meassurement> >(content);
}
// Assert
Assert.AreEqual(m.ToString(), ml.Last().ToString());
}
public Meassurement GetById(int id)
{
conn.ConnectionString = dbConnectionstring;
conn.Open();
string sqlCommand = $"SELECT * FROM Meassurement WHERE Id = {id}";
Command = new SqlCommand(sqlCommand, conn);
SqlDataReader rdr = Command.ExecuteReader();
while (rdr.Read())
{
meassurement = new Meassurement(Int32.Parse(rdr.GetValue(0) + ""), Int32.Parse(rdr.GetValue(1) + ""), Int32.Parse(rdr.GetValue(2) + ""), Int32.Parse(rdr.GetValue(3) + ""), DateTime.Parse(rdr.GetValue(4) + ""));
}
return(meassurement);
#region udkommenterede versioner 2
// Laver outputtet som en string, kan ikke bruges hvis det skal sættes sammen med typescript
//while(rdr.Read())
//{
// output = rdr.GetValue(0) + " " +
// rdr.GetValue(1) + " " +
// rdr.GetValue(2) + " " +
// rdr.GetValue(3) + " " +
// rdr.GetValue(4);
//}
#endregion
}
public Meassurement GetOne(int id)
{
Meassurement tempObj = null;
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
using (SqlCommand command = new SqlCommand("SELECT * FROM Meassurement WHERE Id = " + id + " ORDER BY Id", con))
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Meassurement obj = new Meassurement();
obj.ID = Convert.ToInt32(reader["Id"]);
obj.Pressure = Convert.ToDouble(reader["Pressure"]);
obj.Humidity = Convert.ToDouble(reader["Humidity"]);
obj.Temperature = Convert.ToDouble(reader["Temperature"]);
obj.TimeOfEntry = Convert.ToDateTime(reader["TimeOfEntry"]);
tempObj = obj;
}
}
con.Close();
}
return(tempObj);
}
public Meassurement Get(int id)
{
string sqlQuery = $"SELECT * from Meassurement Where id = {id} ";
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sqlQuery, connection);
command.Connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
int mId = reader.GetInt32(0);
double pressure = reader.GetDouble(1);
double humidity = reader.GetDouble(2);
double temperatur = reader.GetDouble(3);
DateTime time = reader.GetDateTime(4);
var Meas = new Meassurement()
{
Id = mId,
Pressure = pressure,
Humidity = humidity,
Temperature = temperatur,
TimeStamp = time
};
return(Meas);
}
else
{
return(null);
}
}
}
public void OneIdCorrect()
{
Meassurement res = GetCustomersAsyncid(1).Result;
Thread.Sleep(1000);
var ress = res.Pressure;
Assert.AreEqual(20, ress);
}
public void TestAdd()
{
//Arrange
MeassurementController controller = new MeassurementController();
//Act
Meassurement returnedMeassurement = controller.PostMeassurement(new Meassurement((float)22.2, (float)75.3, (float)21.7, System.DateTime.Now));
//Assert
Assert.IsNotNull(returnedMeassurement);
}
public void Post([FromBody] Meassurement meassurement)
{
conn.ConnectionString = dbConnectionstring;
conn.Open();
string sqlCommand = $"INSERT INTO Meassurement VALUES({meassurement.Pressure},{meassurement.Humidity}," +
$"{meassurement.Temperature},{meassurement.TimeStamp})";
Command = new SqlCommand(sqlCommand, conn);
Command.ExecuteNonQuery();
}
public void TestGetSingle()
{
// Arrange
MeassurementsController mc = new MeassurementsController();
// Act
Meassurement m = mc.Get(1); // Kan være 1 ikke findes længere, ergo fail test (man tester normalt ikke på db)
// Assert
Assert.IsNotNull(m);
}
public void TestDelete()
{
//Arrange
MeassurementController controller = new MeassurementController();
//Act
controller.Delete(2);
Meassurement returnedMeassurement = controller.GetMeassurementById(2);
//Assert
Assert.AreEqual(0, returnedMeassurement.Id);
}
public void Post([FromBody] Meassurement value)
{
string sqlQuery = "INSERT INTO dbo.Meassurement (Preassure, Humidity, Temperatur) VALUES (@pressure, @humidity, @temperature)";
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
command.Parameters.Add("@pressure", SqlDbType.Float).Value = value.Pressure;
command.Parameters.Add("@humidity", SqlDbType.Float).Value = value.Humidity;
command.Parameters.Add("@temperature", SqlDbType.Float).Value = value.Temperature;
connection.Open();
command.ExecuteNonQuery();
}
}
public void PutOne([FromBody] Meassurement obj, int id)
{
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommand command = new SqlCommand("UPDATE Meassurement SET Pressure = @Pressure, Humidity = @Humidity, Temperature = @Temperature WHERE Id = @Id", con);
command.Parameters.AddWithValue("@Pressure", obj.Pressure);
command.Parameters.AddWithValue("@Humidity", obj.Humidity);
command.Parameters.AddWithValue("@Temperature", obj.Temperature);
command.Parameters.AddWithValue("@Id", id);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
}
public int InsertMeassurement(Meassurement Meassurement)
{
var sql = "INSERT INTO dbo.Meassurement (Pressure, Humidity, Temperature, [TimeStamp])" +
$"VALUES ('{Meassurement.Pressure}', '{Meassurement.Humidity}', '{Meassurement.Temperature}', '{Meassurement.TimeStamp}')";
// var db = new MySqlConnection(connection);
var db = new SqlConnection(connection);
db.Open();
// var command = new MySqlCommand(sql, db);
var command = new SqlCommand(sql, db);
var reader = command.ExecuteReader();
db.Dispose();
return(reader.RecordsAffected);
}
public void PostOne([FromBody] Meassurement obj)
{
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommand command = new SqlCommand("INSERT INTO Meassurement (Pressure, Humidity, Temperature, TimeOfEntry)" +
"VALUES (@Pressure, @Humidity, @Temperature, GETDATE())", con);
command.Parameters.AddWithValue("@Pressure", obj.Pressure);
command.Parameters.AddWithValue("@Humidity", obj.Humidity);
command.Parameters.AddWithValue("@Temperature", obj.Temperature);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
}
public int AddMeassurement([FromBody] Meassurement value)
{
const string insertString = "insert into meassurement (pressure, humidity, temperature) values (@pressure, @humidity, @temperature)";
using (SqlConnection databaseConnection = new SqlConnection(ConnectionString))
{
databaseConnection.Open();
using (SqlCommand insertCommand = new SqlCommand(insertString, databaseConnection))
{
insertCommand.Parameters.AddWithValue("@pressure", value.Pressure);
insertCommand.Parameters.AddWithValue("@humidity", value.Humidity);
insertCommand.Parameters.AddWithValue("@temperature", value.Temperature);
int rowsAffected = insertCommand.ExecuteNonQuery();
return(rowsAffected);
}
}
}
public async Task TestGetOne()
{
// Arrange
string uri = "https://easj-mock3.azurewebsites.net/api/Meassurement/";
var id = 1;
Meassurement ml = null;
// Action
using (HttpClient client = new HttpClient())
{
string content = await client.GetStringAsync(uri + id);
ml = JsonConvert.DeserializeObject <Meassurement>(content);
}
// Assert
Assert.AreEqual(id, ml.Id);
}
public int UpdateMeassurement(int id, [FromBody] Meassurement Meassurement)
{
var sql = $"UPDATE Meassurement SET Pressure = '{Meassurement.Pressure}', " +
$"Humidity = '{Meassurement.Humidity}', Temperature = '{Meassurement.Temperature}'" +
$"TimeStamp = '{Meassurement.TimeStamp}' WHERE id='{id}'";
// var db = new MySqlConnection(connection);
var db = new SqlConnection(connection);
db.Open();
// var command = new MySqlCommand(sql, db);
var command = new SqlCommand(sql, db);
var reader = command.ExecuteReader();
db.Dispose();
return(reader.RecordsAffected);
}
public HttpResponseMessage PostMeass([FromBody] Meassurement value)
{
const string insertString = "insert into dbo.Meassurement (pressure, humidity, temperature) values (@pressure, '@humidity', '@temperature')";
using (SqlConnection databaseConnection = new SqlConnection(ConnectionString))
{
databaseConnection.Open();
using (SqlCommand insertCommand = new SqlCommand(insertString, databaseConnection))
{
insertCommand.Parameters.AddWithValue("@pressure", value.Pressure);
insertCommand.Parameters.AddWithValue("@humidity", value.Humidity);
insertCommand.Parameters.AddWithValue("@temperature", value.Temperature);
int rowsAffected = insertCommand.ExecuteNonQuery();
return(new HttpResponseMessage(HttpStatusCode.OK));
}
}
}
public void TestGetById()
{
//Arrange
MeassurementController controller = new MeassurementController();
//Act
Meassurement returnedMeassurement = controller.GetMeassurementById(1);
//Assert
if (returnedMeassurement != null)
{
Assert.AreEqual(1, returnedMeassurement.Id);
}
else
{
Assert.IsTrue(true);
}
}
private static Meassurement ReadMeassurement(IDataRecord reader)
{
int id = reader.GetInt32(0);
decimal pressure = reader.GetDecimal(1);
decimal humidity = reader.GetDecimal(2);
decimal temperature = reader.GetDecimal(3);
DateTime timestamp = reader.GetDateTime(4);
Meassurement meassurement = new Meassurement
{
Id = id,
Pressure = pressure,
Humidity = humidity,
Temperature = temperature,
TimeStamp = timestamp
};
return(meassurement);
}
private static Meassurement ReadMes(IDataRecord reader)
{
int id = reader.GetInt32(0);
int pressure = reader.GetInt32(1);
int humidity = reader.GetInt32(2);
int temperature = reader.GetInt32(3);
DateTime timestamp = reader.GetDateTime(4);
Meassurement sensor = new Meassurement
{
MesId = id,
Pressure = pressure,
Humidity = humidity,
Temperature = temperature,
TimeStamp = timestamp
};
return(sensor);
}
public Meassurement PostMeassurement([FromBody] Meassurement meassurement)
{
string insertMeassurement = "INSERT INTO Meassurement (Pressure, Humidity, Temperature, TimeStamp) VALUES (@Pressure, @Humidity, @Temperature, @TimeStamp)";
SqlConnection connect = new SqlConnection(connectionString);
using (SqlCommand insertCommand = new SqlCommand(insertMeassurement, connect))
{
connect.Open();
insertCommand.Parameters.AddWithValue("@Pressure", meassurement.Pressure);
insertCommand.Parameters.AddWithValue("@Humidity", meassurement.Humidity);
insertCommand.Parameters.AddWithValue("@Temperature", meassurement.Temperature);
insertCommand.Parameters.AddWithValue("@TimeStamp", meassurement.TimeStamp);
insertCommand.ExecuteNonQuery();
}
return(meassurement);
}
private static Meassurement ReadMeassurements(IDataRecord reader)
{
int id = reader.GetInt32(0);
double pressure = reader.GetDouble(1);
double humidity = reader.GetDouble(2);
double temperature = reader.GetDouble(3);
DateTime timestamp = reader.GetDateTime(4);
Meassurement meassurement = new Meassurement(id, pressure, humidity, temperature, timestamp)
{
Id = id,
Pressure = pressure,
Humidity = humidity,
Temperature = temperature,
TimeStamp = timestamp
};
return(meassurement);
}
public List <Meassurement> GetMeassurements()
{
var meassurementList = new List <Meassurement>();
string sql = "SELECT * FROM Meassurement";
using (SqlConnection databaseConnection = new SqlConnection(connectionString))
{
databaseConnection.Open();
using (SqlCommand selectCommand = new SqlCommand(sql, databaseConnection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
int id = reader.GetInt32(0);
double pressure = reader.GetDouble(1);
double humidity = reader.GetDouble(2);
double temperature = reader.GetDouble(3);
DateTime timeStamp = reader.GetDateTime(4);
var meassurement = new Meassurement()
{
Id = id,
Pressure = pressure,
Humidity = humidity,
Temperature = temperature,
TimeStamp = timeStamp
};
meassurementList.Add(meassurement);
}
}
}
}
}
return(meassurementList);
}
public int UpdateMeassurement(int id, [FromBody] Meassurement value)
{
const string updateString =
"update Measurrement set pressure=@pressure, humidity=@humidity, temperature=@temperature, timestamp=@timestamp where id=@id;";
using (SqlConnection databaseConnection = new SqlConnection(ConnectionString))
{
databaseConnection.Open();
using (SqlCommand updateCommand = new SqlCommand(updateString, databaseConnection))
{
updateCommand.Parameters.AddWithValue("@pressure", value.Pressure);
updateCommand.Parameters.AddWithValue("@humidity", value.Humidity);
updateCommand.Parameters.AddWithValue("@temperature", value.Temperature);
updateCommand.Parameters.AddWithValue("@timestamp", value.TimeStamp);
updateCommand.Parameters.AddWithValue("@id", id);
int rowsAffected = updateCommand.ExecuteNonQuery();
return(rowsAffected);
}
}
}
public void TestPost()
{
// Arrange
MeassurementsController mc = new MeassurementsController();
// Act
// Insert new M then get it and check if it exists
Meassurement m = new Meassurement
{
Humidity = "20",
Pressure = "21",
Temperature = "22",
TimeStamp = DateTime.Now
};
int newMId = mc.Post(m);
Meassurement postedM = mc.Get(newMId);
// Assert
Assert.IsNotNull(postedM);
}
public IList <Meassurement> GetAll()
{
const string selectString = "select * from dbo.Meassurement";
using (SqlConnection databaseConnection = new SqlConnection(ConnectionString))
{
databaseConnection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectString, databaseConnection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
List <Meassurement> meassurementslList = new List <Meassurement>();
while (reader.Read())
{
Meassurement book = ReadMes(reader);
meassurementslList.Add(book);
}
return(meassurementslList);
}
}
}
}
