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); } }