public ConsumptionModel GetConsumptionById(int id)
{
try
{
ConsumptionModel consumption = null;
_connection.Open();
SqlCommand cmd = new SqlCommand($"SELECT id, date, time, owner FROM Consumption WHERE id = {id};", _connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
consumption = new ConsumptionModel
{
Id = reader.GetInt32(0),
Date = reader.GetDateTime(1).ToString("yyyy-MM-dd"),
Time = new DateTime(1, 1, 1, reader.GetTimeSpan(2).Hours, reader.GetTimeSpan(2).Minutes, 0).ToString("HH:mm"),
User = GetUserById(reader.GetInt32(3))
};
}
_connection.Close();
return(consumption);
}
catch (SqlException)
{
return(null);
}
}
public bool UpdateConsumption(ConsumptionModel consumption)
{
try
{
_connection.Open();
SqlCommand cmd = new SqlCommand($"UPDATE Consumption SET date = '{consumption.Date}', time = '{consumption.Time}', " +
$"owner = {consumption.User.Id} WHERE id = {consumption.Id};", _connection);
SqlDataAdapter adapter = new SqlDataAdapter
{
UpdateCommand = cmd
};
adapter.UpdateCommand.ExecuteNonQuery();
_connection.Close();
return(true);
}
catch (SqlException)
{
return(false);
}
}
public bool DeleteConsumption(ConsumptionModel consumption)
{
try
{
_connection.Open();
SqlCommand cmd = new SqlCommand($"DELETE FROM Food_Consumption WHERE consumption = {consumption.Id};" +
$"DELETE FROM Consumption WHERE id = {consumption.Id};", _connection);
SqlDataAdapter adapter = new SqlDataAdapter
{
DeleteCommand = cmd
};
adapter.DeleteCommand.ExecuteNonQuery();
_connection.Close();
return(true);
}
catch (SqlException)
{
return(false);
}
}
public bool InsertConsumption(ConsumptionModel consumption, int userId)
{
try
{
_connection.Open();
SqlCommand cmd = new SqlCommand($"INSERT INTO Consumption (date, time, owner) " +
$"VALUES ('{consumption.Date}', '{consumption.Time}', {userId});", _connection);
SqlDataAdapter adapter = new SqlDataAdapter
{
InsertCommand = cmd
};
adapter.InsertCommand.ExecuteNonQuery();
_connection.Close();
return(true);
}
catch (SqlException)
{
return(false);
}
}
