public void Remove(Hop hop)
{
using (var context = new MicrobrewitContext())
{
var dbHop = context.Hops.SingleOrDefault(h => h.HopId == hop.HopId);
if(dbHop == null) throw new DbUpdateException("Hop does not exist.");
context.Hops.Remove(dbHop);
context.SaveChanges();
}
}
public void Add(Hop hop)
{
using (var context = new MicrobrewitContext())
{
if (hop.OriginId > 0)
{
hop.Origin = null;
}
foreach (var subs in hop.Substituts)
{
context.Entry(subs).State = EntityState.Unchanged;
}
context.Hops.Add(hop);
context.SaveChanges();
}
}
public async Task AddHop_Gets_Oils_Get_Added()
{
var newHop = new Hop
{
Name = "Test Hop",
AALow = 1,
AAHigh = 5,
Custom = true,
BetaLow = 1,
BetaHigh = 5,
Notes = "Notes",
OriginId = 1,
Purpose = "Test",
Aliases = "Test;test",
TotalOilHigh = 1,
BPineneHigh = 1,
LinaloolHigh = 1,
MyrceneHigh = 1,
CaryophylleneHigh = 1,
FarneseneHigh = 1,
HumuleneHigh = 1,
GeraniolHigh = 1,
OtherOilHigh = 1,
TotalOilLow = 1,
BPineneLow = 1,
LinaloolLow = 1,
MyrceneLow = 1,
CaryophylleneLow = 1,
FarneseneLow = 1,
HumuleneLow = 1,
GeraniolLow = 1,
OtherOilLow = 1,
AromaWheel = new List<HopFlavour>
{
new HopFlavour {FlavourId = 1},
new HopFlavour {FlavourId = 2}
},
Flavours = new List<HopFlavour>
{
new HopFlavour {FlavourId = 1},
new HopFlavour {FlavourId = 2}
}
};
await _hopRepository.AddAsync(newHop);
var hop = _hopRepository.GetSingleAsync(newHop.HopId).Result;
Assert.AreEqual(newHop.Purpose, hop.Purpose);
Assert.AreEqual(newHop.Aliases, hop.Aliases);
Assert.AreEqual(newHop.TotalOilHigh, hop.TotalOilHigh);
Assert.AreEqual(newHop.BPineneHigh, hop.BPineneHigh);
Assert.AreEqual(newHop.LinaloolHigh, hop.LinaloolHigh);
Assert.AreEqual(newHop.MyrceneHigh, hop.MyrceneHigh);
Assert.AreEqual(newHop.CaryophylleneHigh, hop.CaryophylleneHigh);
Assert.AreEqual(newHop.FarneseneHigh, hop.FarneseneHigh);
Assert.AreEqual(newHop.HumuleneHigh, hop.HumuleneHigh);
Assert.AreEqual(newHop.GeraniolHigh, hop.GeraniolHigh);
Assert.AreEqual(newHop.OtherOilHigh, hop.OtherOilHigh);
Assert.AreEqual(newHop.TotalOilLow, hop.TotalOilLow);
Assert.AreEqual(newHop.BPineneLow, hop.BPineneLow);
Assert.AreEqual(newHop.LinaloolLow, hop.LinaloolLow);
Assert.AreEqual(newHop.MyrceneLow, hop.MyrceneLow);
Assert.AreEqual(newHop.CaryophylleneLow, hop.CaryophylleneLow);
Assert.AreEqual(newHop.FarneseneLow, hop.FarneseneLow);
Assert.AreEqual(newHop.HumuleneLow, hop.HumuleneLow);
Assert.AreEqual(newHop.GeraniolLow, hop.GeraniolLow);
Assert.AreEqual(newHop.OtherOilLow, hop.OtherOilLow);
Assert.NotNull(hop);
}
public void AddHop_Gets_Added()
{
var newHop = new Hop
{
Name = "Test Hop",
AALow = 1,
AAHigh = 5,
Custom = true,
BetaLow = 1,
BetaHigh = 5,
Notes = "Notes",
OriginId = 1
};
_hopRepository.Add(newHop);
var hop = _hopRepository.GetSingle(newHop.HopId);
Assert.NotNull(hop);
}
public void AddHop_Hop_Substitute_Gets_Added()
{
var sub = _hopRepository.GetAll().FirstOrDefault();
var newHop = new Hop
{
Name = "Test Hop",
AALow = 1,
AAHigh = 5,
Custom = true,
BetaLow = 1,
BetaHigh = 5,
Notes = "Notes",
OriginId = 1,
Substituts = new List<Hop> {sub}
};
_hopRepository.Add(newHop);
var hop = _hopRepository.GetSingle(newHop.HopId);
Assert.True(hop.Substituts.Any());
}
public void AddHop_Hop_Flavour_Gets_Added()
{
var newHop = new Hop
{
Name = "Test Hop",
AALow = 1,
AAHigh = 5,
Custom = true,
BetaLow = 1,
BetaHigh = 5,
Notes = "Notes",
OriginId = 1,
Flavours = new List<HopFlavour> { new HopFlavour { FlavourId = 1} }
};
_hopRepository.Add(newHop);
var hop = _hopRepository.GetSingle(newHop.HopId);
Assert.True(hop.Flavours.Any());
}
public async Task RemoveAsync(Hop hop)
{
using (var context = new SqlConnection(SqlConnection))
{
context.Open();
using (var transaction = context.BeginTransaction())
{
try
{
var sql = @"DELETE FROM Hops WHERE HopId = @HopId";
await context.ExecuteAsync(sql, new { hop.HopId }, transaction);
transaction.Commit();
}
catch (Exception e)
{
Log.Error(e.ToString());
transaction.Rollback();
}
}
}
}
private async Task UpdateAromaWheelAsync(DbConnection context, SqlTransaction transaction, Hop hop)
{
var aromaWheels = (await context.QueryAsync<HopFlavour>(@"SELECT * FROM HopAromaWheels WHERE HopId = @HopId", new { hop.HopId },
transaction)).ToList();
var toDelete = aromaWheels.Where(h => hop.AromaWheel.All(f => f.FlavourId != h.FlavourId));
await context.ExecuteAsync("DELETE FROM HopAromaWheels WHERE HopId = @HopId and FlavourId = @FlavourId;",
toDelete.Select(h => new { h.HopId, h.FlavourId }), transaction);
var toAdd = hop.AromaWheel.Where(h => aromaWheels.All(f => f.FlavourId != h.FlavourId));
await context.ExecuteAsync(@"INSERT HopAromaWheels(FlavourId, HopId) VALUES(@FlavourId,@HopId);", toAdd.Select(h => new { h.HopId, h.FlavourId }), transaction);
}
private async Task UpdateHopSubstituteAsync(DbConnection context, SqlTransaction transaction, Hop hop)
{
var hopSubstitutes = (await context.QueryAsync<Substitute>(@"SELECT * FROM Substitute WHERE HopId = @HopId",
new { hop.HopId }, transaction)).ToList();
var toDelete = hopSubstitutes.Where(h => hop.Substituts.All(s => s.HopId != h.HopId && h.SubstituteId != s.HopId));
await context.ExecuteAsync("DELETE FROM Substitute WHERE HopId = @HopId", toDelete, transaction);
var toAdd = hop.Substituts.Where(h => hopSubstitutes.All(s => s.HopId != h.HopId && h.HopId != s.SubstituteId)).Select(c => new Substitute { HopId = hop.HopId, SubstituteId = c.HopId });
await context.ExecuteAsync(@"INSERT Substitute(SubstituteId, HopId) VALUES(@SubstituteId,@HopId);", toAdd, transaction);
}
public async Task<int> UpdateAsync(Hop hop)
{
using (var context = new SqlConnection(SqlConnection))
{
context.Open();
using (var transaction = context.BeginTransaction())
{
try
{
var sql =
@"Update Hops set Name = @name,AALow = @aaLow,AAHigh = @aaHigh, BetaLow = @betaLow,BetaHigh = @betaHigh,
Notes = @notes,FlavourDescription = @flavourDescription, Custom = @custom,OriginId = @originId,
Purpose = @purpose, Aliases = @aliases, TotalOilHigh = @totalOilHigh, BPineneHigh = @bPineneHigh, LinaloolHigh = @linaloolHigh,
MyrceneHigh = @myrceneHigh,CaryophylleneHigh = @caryophylleneHigh,FarneseneHigh =@farneseneHigh,HumuleneHigh = @humuleneHigh,
GeraniolHigh = @geraniolHigh,OtherOilHigh = @otherOilHigh,TotalOilLow = @totalOilLow,BPineneLow = @bPineneLow,LinaloolLow = @linaloolLow,
MyrceneLow = @myrceneLow,CaryophylleneLow = @caryophylleneLow,FarneseneLow = @farneseneLow,HumuleneLow = @humuleneLow,GeraniolLow = @geraniolLow,
OtherOilLow = @otherOilLow WHERE HopId = @hopId;";
var result = await context.ExecuteAsync(sql,
new
{
hop.Name,
hop.AALow,
hop.AAHigh,
hop.BetaLow,
hop.BetaHigh,
hop.Notes,
hop.FlavourDescription,
hop.Custom,
hop.OriginId,
hop.Purpose,
hop.Aliases,
hop.TotalOilHigh,
hop.BPineneHigh,
hop.LinaloolHigh,
hop.MyrceneHigh,
hop.CaryophylleneHigh,
hop.FarneseneHigh,
hop.HumuleneHigh,
hop.GeraniolHigh,
hop.OtherOilHigh,
hop.TotalOilLow,
hop.BPineneLow,
hop.LinaloolLow,
hop.MyrceneLow,
hop.CaryophylleneLow,
hop.FarneseneLow,
hop.HumuleneLow,
hop.GeraniolLow,
hop.OtherOilLow,
hop.HopId
}, transaction);
transaction.Commit();
await UpdateHopFlavourAsync(context, transaction, hop);
await UpdateHopSubstituteAsync(context, transaction, hop);
await UpdateAromaWheelAsync(context, transaction, hop);
return result;
}
catch (Exception e)
{
Log.Error(e);
throw;
}
}
}
}
public async Task AddAsync(Hop hop)
{
using (var context = new SqlConnection(SqlConnection))
{
context.Open();
using (var transaction = context.BeginTransaction())
{
try
{
var sql =
@"INSERT Hops(Name,AALow,AAHigh,BetaLow,BetaHigh,Notes,FlavourDescription,Custom,OriginId,
Purpose,Aliases,TotalOilHigh,BPineneHigh,LinaloolHigh,MyrceneHigh,CaryophylleneHigh,FarneseneHigh,HumuleneHigh,
GeraniolHigh,OtherOilHigh,TotalOilLow,BPineneLow,LinaloolLow,MyrceneLow,CaryophylleneLow,FarneseneLow,HumuleneLow,GeraniolLow,OtherOilLow)
VALUES(@name,@aaLow,@aaHigh,@betaLow,@betaHigh,@notes,@flavourDescription,@custom,@originId,
@purpose,@aliases,@totalOilHigh,@bPineneHigh,@linaloolHigh,@myrceneHigh,@caryophylleneHigh,@farneseneHigh,@humuleneHigh,
@geraniolHigh,@otherOilHigh,@totalOilLow,@bPineneLow,@linaloolLow,@myrceneLow,@caryophylleneLow,@farneseneLow,@humuleneLow,@geraniolLow,@otherOilLow)
SELECT CAST(SCOPE_IDENTITY() as int)";
var id = (await context.QueryAsync<int>(sql,
new
{
hop.Name,
hop.AALow,
hop.AAHigh,
hop.BetaLow,
hop.BetaHigh,
hop.Notes,
hop.FlavourDescription,
hop.Custom,
hop.OriginId,
hop.Purpose,
hop.Aliases,
hop.TotalOilHigh,
hop.BPineneHigh,
hop.LinaloolHigh,
hop.MyrceneHigh,
hop.CaryophylleneHigh,
hop.FarneseneHigh,
hop.HumuleneHigh,
hop.GeraniolHigh,
hop.OtherOilHigh,
hop.TotalOilLow,
hop.BPineneLow,
hop.LinaloolLow,
hop.MyrceneLow,
hop.CaryophylleneLow,
hop.FarneseneLow,
hop.HumuleneLow,
hop.GeraniolLow,
hop.OtherOilLow,
}, transaction)).Single();
if (hop.Flavours != null)
{
await context.ExecuteAsync(
@"INSERT HopFlavours(FlavourId, HopId) VALUES(@FlavourId,@HopId);",
hop.Flavours.Select(h => new { h.FlavourId, HopId = id }),
transaction);
}
if (hop.AromaWheel != null)
{
await context.ExecuteAsync(
@"INSERT HopAromaWheels(FlavourId, HopId) VALUES(@FlavourId,@HopId);",
hop.AromaWheel.Select(h => new { h.FlavourId, HopId = id }),
transaction);
}
if (hop.Substituts != null)
{
await context.ExecuteAsync(
@"INSERT Substitute(HopId,SubstituteId) VALUES(@HopId,@SubstituteId);",
hop.Substituts.Select(s => new { HopId = id, SubstituteId = s.HopId }),
transaction);
}
transaction.Commit();
hop.HopId = id;
}
catch (Exception e)
{
transaction.Rollback();
Log.Error(e.ToString());
throw;
}
}
}
}
private void UpdateHopFlavour(DbConnection context, SqlTransaction transaction, Hop hop)
{
var hopFlavours = context.Query<HopFlavour>(@"SELECT * FROM HopFlavours WHERE HopId = @HopId", new { hop.HopId },
transaction);
var toDelete = hopFlavours.Where(h => hop.Flavours.All(f => f.FlavourId != h.FlavourId));
context.Execute("DELETE FROM HopFlavours WHERE HopId = @HopId and FlavourId = @FlavourId;",
toDelete.Select(h => new { h.HopId, h.FlavourId }), transaction);
var toAdd = hop.Flavours.Where(h => hopFlavours.All(f => f.FlavourId != h.FlavourId));
context.Execute(@"INSERT HopFlavours(FlavourId, HopId) VALUES(@FlavourId,@HopId);", toAdd, transaction);
}
public void Update(Hop hop)
{
using (var context = new SqlConnection(SqlConnection))
{
context.Open();
using (var transaction = context.BeginTransaction())
{
try
{
var sql =
@"Update Hops set Name = @name,AALow = @aaLow,AAHigh = @aaHigh, BetaLow = @betaLow,BetaHigh = @betaHigh,
Notes = @notes,FlavourDescription = @flavourDescription, Custom = @custom,OriginId = @originId WHERE HopId = @hopId;";
context.Execute(sql,
new
{
hop.Name,
hop.AALow,
hop.AAHigh,
hop.BetaLow,
hop.BetaHigh,
hop.Notes,
hop.FlavourDescription,
hop.Custom,
hop.OriginId,
hop.HopId,
}, transaction);
UpdateHopFlavour(context, transaction, hop);
UpdateHopSubstitute(context, transaction, hop);
transaction.Commit();
}
catch (Exception e)
{
Log.Error(e);
throw;
}
}
}
}
public async Task RemoveAsync(Hop hop)
{
using (var context = new MicrobrewitContext())
{
var dbHop = context.Hops.SingleOrDefault(h => h.HopId == hop.HopId);
if (dbHop == null) throw new DbUpdateException("Hop does not exist.");
context.Entry(dbHop).CurrentValues.SetValues(hop);
await context.SaveChangesAsync();
}
}
