public void Add(BeanVariety variety)
{
using (var conn = Connection)
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
INSERT INTO BeanVariety ([Name], Region, Notes)
OUTPUT INSERTED.ID
VALUES (@name, @region, @notes)";
cmd.Parameters.AddWithValue("@name", variety.Name);
cmd.Parameters.AddWithValue("@region", variety.Region);
if (variety.Notes == null)
{
cmd.Parameters.AddWithValue("@notes", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@notes", variety.Name);
}
variety.Id = (int)cmd.ExecuteScalar();
}
}
}
public void Update(BeanVariety variety)
{
using (var conn = Connection)
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
UPDATE BeanVariety
SET [Name] = @name,
Region = @region,
Notes = @notes
WHERE Id = @id";
cmd.Parameters.AddWithValue("@id", variety.Id);
cmd.Parameters.AddWithValue("@name", variety.Name);
cmd.Parameters.AddWithValue("@region", variety.Region);
if (variety.Notes == null)
{
cmd.Parameters.AddWithValue("@notes", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@notes", variety.Name);
}
cmd.ExecuteNonQuery();
}
}
}
public List <BeanVariety> GetAll()
{
using (var conn = Connection)
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT Id, [Name], Region, Notes FROM BeanVariety;";
var reader = cmd.ExecuteReader();
var varieties = new List <BeanVariety>();
while (reader.Read())
{
var variety = new BeanVariety()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name")),
Region = reader.GetString(reader.GetOrdinal("Region")),
};
if (!reader.IsDBNull(reader.GetOrdinal("Notes")))
{
variety.Notes = reader.GetString(reader.GetOrdinal("Notes"));
}
varieties.Add(variety);
}
reader.Close();
return(varieties);
}
}
}
public BeanVariety Get(int id)
{
using (var conn = Connection)
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT Id, [Name], Region, Notes
FROM BeanVariety
WHERE Id = @id;";
cmd.Parameters.AddWithValue("@id", id);
var reader = cmd.ExecuteReader();
BeanVariety variety = null;
if (reader.Read())
{
variety = new BeanVariety()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name")),
Region = reader.GetString(reader.GetOrdinal("Region"))
};
if (!reader.IsDBNull(reader.GetOrdinal("Notes")))
{
variety.Notes = reader.GetString(reader.GetOrdinal("Notes"));
}
}
reader.Close();
return(variety);
}
}
}
private Coffee NewCoffeeFromReader(SqlDataReader reader)
{
var coffee = new Coffee()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Title = reader.GetString(reader.GetOrdinal("Title")),
BeanVarietyId = reader.GetInt32(reader.GetOrdinal("BeanVarietyId"))
};
BeanVariety variety = new BeanVariety()
{
Id = reader.GetInt32(reader.GetOrdinal("BeanVarietyId")),
Name = reader.GetString(reader.GetOrdinal("Name")),
Region = reader.GetString(reader.GetOrdinal("Region"))
};
if (!reader.IsDBNull(reader.GetOrdinal("Notes")))
{
variety.Notes = reader.GetString(reader.GetOrdinal("Notes"));
}
;
coffee.BeanVariety = variety;
return(coffee);
}
public IActionResult Put(int id, BeanVariety beanVariety)
{
if (id != beanVariety.Id)
{
return(BadRequest());
}
_beanVarietyRepository.Update(beanVariety);
return(NoContent());
}
public IActionResult Put(int id, BeanVariety beanVariety)
{
if (id != beanVariety.Id)
{
return(BadRequest());
}
_beanVarietyRepository.Update(beanVariety);
//NoContent() indicates action was successful, but there is nothing to return
return(NoContent());
}
public List <Coffee> GetAll()
{
using (var conn = Connection)
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT
c.Id,
c.[Title],
c.BeanVarietyId,
b.Id, b.[Name],
b.Region,
b.Notes
FROM Coffee c
JOIN BeanVariety b
ON c.BeanVarietyId = b.Id;";
var reader = cmd.ExecuteReader();
var coffees = new List <Coffee>();
while (reader.Read())
{
var beanVariety = new BeanVariety()
{
Id = reader.GetInt32(reader.GetOrdinal("b.Id")),
Name = reader.GetString(reader.GetOrdinal("b.Name")),
Region = reader.GetString(reader.GetOrdinal("b.Region"))
};
if (!reader.IsDBNull(reader.GetOrdinal("Notes")))
{
beanVariety.Notes = reader.GetString(reader.GetOrdinal("Notes"));
}
var coffee = new Coffee()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Title = reader.GetString(reader.GetOrdinal("Title")),
beanVarietyId = reader.GetInt32(reader.GetOrdinal("BeanVarietyId")),
beanVariety = beanVariety
};
}
reader.Close();
return(coffees);
}
}
}
public Coffee Get(int id)
{
using (var conn = Connection)
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT c.Id, c.Title, c.BeanVarietyId,
b.[Name], b.Region, b.Notes
FROM Coffee c
JOIN BeanVariety b ON b.Id = c.BeanVarietyId
WHERE c.Id = @id;";
cmd.Parameters.AddWithValue("@id", id);
var reader = cmd.ExecuteReader();
Coffee coffee = null;
if (reader.Read())
{
coffee = new Coffee()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Title = reader.GetString(reader.GetOrdinal("Title")),
BeanVarietyId = reader.GetInt32(reader.GetOrdinal("BeanVarietyId"))
};
BeanVariety variety = new BeanVariety()
{
Id = reader.GetInt32(reader.GetOrdinal("BeanVarietyId")),
Name = reader.GetString(reader.GetOrdinal("Name")),
Region = reader.GetString(reader.GetOrdinal("Region")),
};
if (!reader.IsDBNull(reader.GetOrdinal("Notes")))
{
variety.Notes = reader.GetString(reader.GetOrdinal("Notes"));
}
coffee.BeanVariety = variety;
}
reader.Close();
return(coffee);
}
}
}
public Coffee Get(int coffeeId)
{
using (var conn = Connection)
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT coffee.Id As CoffeeId, Title, BeanVarietyId, Name, Region, Notes
FROM Coffee
LEFT JOIN BeanVariety ON coffee.BeanVarietyId = BeanVariety.Id
WHERE CoffeeId = @coffeeId
";
cmd.Parameters.AddWithValue("@coffeeId", coffeeId);
var reader = cmd.ExecuteReader();
Coffee coffee = null;
if (reader.Read())
{
var variety = new BeanVariety()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name")),
Region = reader.GetString(reader.GetOrdinal("Region")),
};
if (!reader.IsDBNull(reader.GetOrdinal("Notes")))
{
variety.Notes = reader.GetString(reader.GetOrdinal("Notes"));
}
coffee = new Coffee()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Title = reader.GetString(reader.GetOrdinal("Title")),
BeanVarietyId = reader.GetInt32(reader.GetOrdinal("BeanVarietyId")),
};
}
reader.Close();
return(coffee);
}
}
}
public List <Coffee> GetAll()
{
using (var conn = Connection)
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT c.Id as CoffeeId, c.Title as CoffeeTitle, c.BeanVarietyId,
b.Id as VarietyId, b.Name as BeanVarietyName, b.Region as BeanVarietyRegion,
b.Notes as BeanVarietyNotes FROM Coffee c
JOIN BeanVariety b on c.BeanVarietyId = b.Id;";
var reader = cmd.ExecuteReader();
var coffeeList = new List <Coffee>();
BeanVariety variety = null;
while (reader.Read())
{
variety = new BeanVariety()
{
Id = reader.GetInt32(reader.GetOrdinal("VarietyId")),
Name = reader.GetString(reader.GetOrdinal("BeanVarietyName")),
Region = reader.GetString(reader.GetOrdinal("BeanVarietyRegion")),
};
if (!reader.IsDBNull(reader.GetOrdinal("BeanVarietyNotes")))
{
variety.Notes = reader.GetString(reader.GetOrdinal("BeanVarietyNotes"));
}
var coffee = new Coffee()
{
Id = reader.GetInt32(reader.GetOrdinal("CoffeeId")),
Title = reader.GetString(reader.GetOrdinal("CoffeeTitle")),
BeanVarietyId = reader.GetInt32(reader.GetOrdinal("BeanVarietyId")),
BeanVariety = variety
};
coffeeList.Add(coffee);
}
reader.Close();
return(coffeeList);
}
}
}
public List <Coffee> GetAll()
{
using (var conn = Connection)
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT coffee.Id As CoffeeId, Title, BeanVarietyId, Name, Region, Notes
FROM Coffee
LEFT JOIN BeanVariety ON coffee.BeanVarietyId = BeanVariety.Id
";
var reader = cmd.ExecuteReader();
var coffees = new List <Coffee>();
while (reader.Read())
{
var variety = new BeanVariety()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name")),
Region = reader.GetString(reader.GetOrdinal("Region")),
};
if (!reader.IsDBNull(reader.GetOrdinal("Notes")))
{
variety.Notes = reader.GetString(reader.GetOrdinal("Notes"));
}
var coffee = new Coffee()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Title = reader.GetString(reader.GetOrdinal("Title")),
BeanVarietyId = reader.GetInt32(reader.GetOrdinal("BeanVarietyId")),
BeanVariety = variety
};
coffees.Add(coffee);
}
reader.Close();
return(coffees);
}
}
}
public IActionResult Post(BeanVariety beanVariety)
{
_beanVarietyRepository.Add(beanVariety);
return(CreatedAtAction("Get", new { id = beanVariety.Id }, beanVariety));
}
