private static void AddYeastData(SQLiteConnection connection)
{
XDocument yeasts = XDocument.Load(Path.Combine(c_beerDataLocation, @"yeast.xml"));
var yeastEntries = yeasts.Descendants("YEAST").ToList();
foreach (XElement yeastEntry in yeastEntries)
{
var yeastInfo = BeerXmlImportUtility.GetYeast(yeastEntry);
var insertCommand = connection.CreateCommand();
insertCommand.CommandText = "INSERT INTO Yeasts (name, type, form, laboratory, productId, minTemperature, maxTemperature, flocculation, attenuation, notes)"
+ "VALUES (@name, @type, @form, @laboratory, @productId, @minTemperature, @maxTemperature, @flocculation, @attenuation, @notes)";
insertCommand.Parameters.AddWithValue("name", yeastInfo.Name);
insertCommand.Parameters.AddWithValue("type", yeastInfo.Characteristics.Type.SaveToString());
insertCommand.Parameters.AddWithValue("form", yeastInfo.Characteristics.Form.SaveToString());
insertCommand.Parameters.AddWithValue("laboratory", yeastInfo.Laboratory);
insertCommand.Parameters.AddWithValue("productId", yeastInfo.ProductId);
insertCommand.Parameters.AddWithValue("minTemperature", yeastInfo.Characteristics.MinTemperature);
insertCommand.Parameters.AddWithValue("maxTemperature", yeastInfo.Characteristics.MaxTemperature);
insertCommand.Parameters.AddWithValue("flocculation", yeastInfo.Characteristics.Flocculation.SaveToString());
insertCommand.Parameters.AddWithValue("attenuation", yeastInfo.Characteristics.Attenuation);
insertCommand.Parameters.AddWithValue("notes", yeastInfo.Notes);
insertCommand.ExecuteNonQuery();
}
}
private static void AddFermentableData(SQLiteConnection connection)
{
var fermentables = XDocument.Load(Path.Combine(c_beerDataLocation, @"grain.xml"));
var fermentableEntries = fermentables.Descendants("FERMENTABLE").ToList();
foreach (XElement fermentableEntry in fermentableEntries)
{
var fermentableInfo = BeerXmlImportUtility.GetFermentable(fermentableEntry);
var maltCategoryElement = fermentableEntry.Element("malt-category");
if (maltCategoryElement != null)
{
fermentableInfo.Characteristics.MaltCategory = (MaltCategory?)EnumConverter.Parse <MaltCategory>(maltCategoryElement.Value);
}
var insertCommand = connection.CreateCommand();
insertCommand.CommandText = "INSERT INTO Fermentables (name, yield, yieldByWeight, color, origin, notes, diastaticPower, type, maltCategory, gravityPoint)"
+ "VALUES (@name, @yield, @yieldByWeight, @color, @origin, @notes, @diastaticPower, @type, @maltCategory, @gravityPoint)";
insertCommand.Parameters.AddWithValue("name", fermentableInfo.Name);
insertCommand.Parameters.AddWithValue("yield", fermentableInfo.Characteristics.Yield);
insertCommand.Parameters.AddWithValue("yieldByWeight", fermentableInfo.Characteristics.YieldByWeight);
insertCommand.Parameters.AddWithValue("color", fermentableInfo.Characteristics.Color);
insertCommand.Parameters.AddWithValue("origin", fermentableInfo.Origin);
insertCommand.Parameters.AddWithValue("notes", fermentableInfo.Notes);
insertCommand.Parameters.AddWithValue("diastaticPower", fermentableInfo.Characteristics.DiastaticPower);
insertCommand.Parameters.AddWithValue("type", fermentableInfo.Characteristics.Type.SaveToString());
insertCommand.Parameters.AddWithValue("maltCategory", fermentableInfo.Characteristics.MaltCategory != null ? fermentableInfo.Characteristics.MaltCategory.SaveToString() : null);
insertCommand.Parameters.AddWithValue("gravityPoint", fermentableInfo.Characteristics.GravityPoint);
insertCommand.ExecuteNonQuery();
}
}
private static void AddHopsData(SQLiteConnection connection)
{
XDocument hops = XDocument.Load(Path.Combine(c_beerDataLocation, @"hops.xml"));
var hopEntries = hops.Descendants("HOP").ToList();
foreach (XElement hopEntry in hopEntries)
{
var hopsInfo = BeerXmlImportUtility.GetHop(hopEntry);
var insertCommand = connection.CreateCommand();
insertCommand.CommandText = "INSERT INTO Hops (name, alpha, notes, beta, hsi, origin)"
+ "VALUES (@name, @alpha, @notes, @beta, @hsi, @origin)";
insertCommand.Parameters.AddWithValue("name", hopsInfo.Name);
insertCommand.Parameters.AddWithValue("alpha", hopsInfo.Characteristics.AlphaAcid);
insertCommand.Parameters.AddWithValue("notes", hopsInfo.Notes);
insertCommand.Parameters.AddWithValue("beta", hopsInfo.Characteristics.BetaAcid);
insertCommand.Parameters.AddWithValue("hsi", hopsInfo.Characteristics.Hsi);
insertCommand.Parameters.AddWithValue("origin", hopsInfo.Origin);
insertCommand.ExecuteNonQuery();
}
}
private static void AddStylesData(SQLiteConnection connection)
{
XDocument styles = XDocument.Load(Path.Combine(c_beerDataLocation, "style.xml"));
var styleEntries = styles.Descendants("STYLE").ToList();
var categoriesAdded = new List <StyleCategory>();
var classificationsAdded = new List <StyleClassification>();
foreach (XElement styleEntry in styleEntries)
{
var styleInfo = BeerXmlImportUtility.GetStyle(styleEntry);
var category = styleInfo.Category;
if (!categoriesAdded.Any(cat => cat.Name == category.Name && cat.Number == category.Number && cat.Type == category.Type))
{
categoriesAdded.Add(category);
// insert into StyleCategories table
var categoryInsertCommand = connection.CreateCommand();
categoryInsertCommand.CommandText = "INSERT INTO StyleCategories (name, number, type) VALUES(@name, @number, @type)";
categoryInsertCommand.Parameters.AddWithValue("name", category.Name);
categoryInsertCommand.Parameters.AddWithValue("number", category.Number);
categoryInsertCommand.Parameters.AddWithValue("type", category.Type.SaveToString());
categoryInsertCommand.ExecuteNonQuery();
}
var classification = styleInfo.Classification;
if (!classificationsAdded.Any(cls => cls.StyleGuide == classification.StyleGuide && cls.StyleLetter == classification.StyleLetter))
{
classificationsAdded.Add(classification);
// insert into StyleClassifications table
using var classificationInsertCommand = connection.CreateCommand();
classificationInsertCommand.CommandText = "INSERT INTO StyleClassifications (letter, guide) VALUES(@letter, @guide)";
classificationInsertCommand.Parameters.AddWithValue("letter", classification.StyleLetter);
classificationInsertCommand.Parameters.AddWithValue("guide", classification.StyleGuide);
classificationInsertCommand.ExecuteNonQuery();
}
// insert into Styles table
using var styleInsertCommand = connection.CreateCommand();
styleInsertCommand.CommandText = "INSERT INTO Styles (name, category, classification, notes, profile, ingredients, examples)"
+ " VALUES(@name, (SELECT id FROM StyleCategories WHERE name = @categoryName), "
+ "(SELECT id FROM StyleClassifications WHERE letter = @styleLetter AND guide = @styleGuide), @notes, @profile, @ingredients, @examples)";
styleInsertCommand.Parameters.AddWithValue("name", styleInfo.Name);
styleInsertCommand.Parameters.AddWithValue("categoryName", styleInfo.Category.Name);
styleInsertCommand.Parameters.AddWithValue("styleLetter", styleInfo.Classification.StyleLetter);
styleInsertCommand.Parameters.AddWithValue("styleGuide", styleInfo.Classification.StyleGuide);
styleInsertCommand.Parameters.AddWithValue("notes", styleInfo.Notes);
styleInsertCommand.Parameters.AddWithValue("profile", styleInfo.Profile);
styleInsertCommand.Parameters.AddWithValue("ingredients", styleInfo.Ingredients);
styleInsertCommand.Parameters.AddWithValue("examples", styleInfo.Examples);
styleInsertCommand.ExecuteNonQuery();
// style thresholds
foreach (var threshold in styleInfo.Thresholds)
{
using var thresholdInsertCommand = connection.CreateCommand();
thresholdInsertCommand.CommandText = "INSERT INTO StyleThresholds (value, minimum, maximum) VALUES(@value, @minimum, @maximum)";
thresholdInsertCommand.Parameters.AddWithValue("value", threshold.Value);
thresholdInsertCommand.Parameters.AddWithValue("minimum", threshold.Minimum);
thresholdInsertCommand.Parameters.AddWithValue("maximum", threshold.Maximum);
thresholdInsertCommand.ExecuteNonQuery();
// insert into junction table
using var thresholdJunctionInsertCommand = connection.CreateCommand();
thresholdJunctionInsertCommand.CommandText = "INSERT INTO ThresholdsInStyle (threshold, style) VALUES("
+ "(SELECT id FROM StyleThresholds WHERE value = @thresholdValue AND minimum = @minimum AND maximum = @maximum),"
+ "(SELECT id FROM Styles WHERE name = @name))";
thresholdJunctionInsertCommand.Parameters.AddWithValue("thresholdValue", threshold.Value);
thresholdJunctionInsertCommand.Parameters.AddWithValue("minimum", threshold.Minimum);
thresholdJunctionInsertCommand.Parameters.AddWithValue("maximum", threshold.Maximum);
thresholdJunctionInsertCommand.Parameters.AddWithValue("name", styleInfo.Name);
thresholdJunctionInsertCommand.ExecuteNonQuery();
}
}
}