public ActionResult <TemperatureLogRs> Get(int id)
{
var result = new TemperatureLogRs();
_logger.LogInformation($" [*********************** >][Request - id] : {id}");
try {
using (MySqlConnection connection = new MySqlConnection(_appSettings.GetDataBaseConnectionString())) {
var value = id;
connection.Open();
var query = $"SELECT * FROM TEMPERATURE_LOGS " +
$"WHERE id = @id ";
var command = new MySqlCommand(query, connection);
command.Parameters.Add("@id", MySqlDbType.Int32);
command.Parameters["@id"].Value = value;
var sqlReader = command.ExecuteReader();
if (sqlReader.HasRows)
{
result = new TemperatureLogRs();
while (sqlReader.Read())
{
result.Id = sqlReader.GetInt32(sqlReader.GetOrdinal("id"));
result.Value = sqlReader.GetFloat(sqlReader.GetOrdinal("value"));
result.Timestamp = sqlReader.GetDateTime(sqlReader.GetOrdinal("timestamp"));
}
}
else
{
_logger.LogInformation($" No results .");
return(StatusCode(204));
}
}
} catch (System.Exception ex) {
_logger.LogError($"{ex.Message}");
return(StatusCode(500));
}
_logger.LogInformation($" Result - id : {JsonConvert.SerializeObject(result)}");
return(result);
}
public ActionResult <TemperatureDataTableRs> GetDataTable(TemperatureDataTableRq request)
{
if (request == null)
{
return(StatusCode(400));
}
else
{
_logger.LogInformation($" [*********************** >][Request] : {JsonConvert.SerializeObject(request)}");
}
var result = new TemperatureDataTableRs();
List <TemperatureLogRs> DataBaseResult = new List <TemperatureLogRs> ();
try {
using (MySqlConnection connection = new MySqlConnection(_appSettings.GetDataBaseConnectionString())) {
var queryConditions = "";
if (request.order != null && request.order.Any())
{
foreach (var item in request.order)
{
foreach (var itemOfDicctionary in item)
{
if (itemOfDicctionary.Key.Equals("column"))
{
queryConditions += $" ORDER BY id ";
}
if (itemOfDicctionary.Key.Equals("dir"))
{
queryConditions += $" {itemOfDicctionary.Value.ToUpper()} ";
}
}
}
queryConditions += $"";
}
if (request.length > 0)
{
queryConditions += $" LIMIT {request.start},{request.length} ";
}
connection.Open();
var query = $"SELECT * FROM TEMPERATURE_LOGS " +
$"WHERE 1=1 " +
$"{queryConditions}" +
$"";
var command = new MySqlCommand(query, connection);
var sqlReader = command.ExecuteReader();
if (sqlReader.HasRows)
{
while (sqlReader.Read())
{
var log = new TemperatureLogRs();
log.Id = sqlReader.GetInt32(sqlReader.GetOrdinal("id"));
log.Value = sqlReader.GetFloat(sqlReader.GetOrdinal("value"));
log.Timestamp = sqlReader.GetDateTime(sqlReader.GetOrdinal("timestamp"));
DataBaseResult.Add(log);
}
// satisfy datatable model
result.Data = DataBaseResult;
result.Draw = request.draw;
if (DataBaseResult != null && DataBaseResult.Any())
{
result.RecordsTotal = this.Get().Value.ToList().Count;
result.RecordsFiltered = this.Get().Value.ToList().Count;
}
}
else
{
_logger.LogInformation($" No results .");
return(StatusCode(204));
}
}
} catch (System.Exception ex) {
_logger.LogError($"{ex.Message}");
return(StatusCode(500));
}
_logger.LogInformation($" [*********************** >][Result - count] : {result.RecordsTotal}");
return(result);
}