public async Task <BeanDTO> GetBeanById(int id)
{
var bean = new BeanDTO();
using (var cmd = new NpgsqlCommand(
$"SELECT bean_name, colour, price, imageurl, aroma " +
$"FROM beans " +
$"WHERE bean_id = {id}", _sqlConnection))
{
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
bean = new BeanDTO
{
Aroma = reader["aroma"].ToString(),
Colour = reader["colour"].ToString(),
CostPer100g = (decimal)reader["price"],
ImageUrl = reader["imageurl"].ToString(),
Name = reader["bean_name"].ToString()
};
}
cmd.Dispose();
_sqlConnection.Close();
}
}
return(bean);
}
public async Task <BeanDTO> GetAdvert(DateTime date)
{
var formattedDate = date.ToString("yyyy MM dd");
var formattedString = formattedDate.Replace(" ", "-");
var bean = new BeanDTO();
// SQL
try
{
using (var cmd = new NpgsqlCommand(
$"SELECT bean_name, aroma, colour, imageurl, price " +
$"FROM beans " +
$"INNER JOIN beanadverts ON beans.bean_id = beanadverts.bean_id " +
$"WHERE beanadverts.advert_date = '{formattedString}'",
_sqlConnection))
{
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
bean.Name = reader["bean_name"].ToString();
bean.Aroma = reader["aroma"].ToString();
bean.Colour = reader["colour"].ToString();
bean.ImageUrl = reader["imageurl"].ToString();
bean.CostPer100g = (decimal)reader["price"];
}
}
_sqlConnection.Close();
}
}
catch (Exception ex)
{
// Post errors to a logging service
Console.WriteLine(ex.InnerException);
}
return(bean.IsValid() ? bean : null);
}