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);
        }
Ejemplo n.º 2
0
        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);
        }