public CoffeeVariety Get(int id)
{
using (var conn = Connection)
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT Id, Title, BeanVarietyId
FROM Coffee
WHERE Id = @id;";
cmd.Parameters.AddWithValue("@id", id);
var reader = cmd.ExecuteReader();
CoffeeVariety variety = null;
if (reader.Read())
{
variety = new CoffeeVariety()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Title = reader.GetString(reader.GetOrdinal("Title")),
};
}
reader.Close();
return(variety);
}
}
}
public List <CoffeeVariety> GetAll()
{
using (var conn = Connection)
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT Id, Title FROM Coffee;";
var reader = cmd.ExecuteReader();
var varieties = new List <CoffeeVariety>();
while (reader.Read())
{
var variety = new CoffeeVariety()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Title = reader.GetString(reader.GetOrdinal("Title")),
};
varieties.Add(variety);
}
reader.Close();
return(varieties);
}
}
}
public IActionResult Put(int id, CoffeeVariety coffeeVariety)
{
if (id != coffeeVariety.Id)
{
return(BadRequest());
}
_coffeeVarietyRepository.Update(coffeeVariety);
return(NoContent());
}
public void Add(CoffeeVariety variety)
{
using (var conn = Connection)
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
INSERT INTO Coffee (Title, BeanVarietyId)
OUTPUT INSERTED.ID
VALUES (@title, @BeanVarietyId)";
cmd.Parameters.AddWithValue("@title", variety.Title);
cmd.Parameters.AddWithValue("@BeanVarietyId", variety.BeanVarietyId);
variety.Id = (int)cmd.ExecuteScalar();
}
}
}
public void Update(CoffeeVariety variety)
{
using (var conn = Connection)
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
UPDATE Coffee
SET Title = @title,
BeanVarietyId = @BeanVarietyId
WHERE Id = @id";
cmd.Parameters.AddWithValue("@id", variety.Id);
cmd.Parameters.AddWithValue("@title", variety.Title);
cmd.Parameters.AddWithValue("@BeanVarietyId", variety.BeanVarietyId);
cmd.ExecuteNonQuery();
}
}
}
public IActionResult Post(CoffeeVariety coffeeVariety)
{
_coffeeVarietyRepository.Add(coffeeVariety);
return(CreatedAtAction("Get", new { id = coffeeVariety.Id }, coffeeVariety));
}
