public static void DeleteBatch(BatchDataModel batch) { using (SQLiteConnection connection = DatabaseUtility.GetNewConnection()) { foreach (GravityReadingDataModel gravityReading in batch.RecordedGravityReadings) DeleteGravityReading(gravityReading.GravityReadingId, connection); using (SQLiteCommand deleteBatchCommand = connection.CreateCommand()) { deleteBatchCommand.CommandText = "DELETE FROM Batches WHERE id = @id"; deleteBatchCommand.Parameters.AddWithValue("id", batch.BatchId); deleteBatchCommand.ExecuteNonQuery(); } } }
public static BatchDataModel CreateBatch(RecipeDataModel recipe) { BatchDataModel batch = null; using (SQLiteConnection connection = DatabaseUtility.GetNewConnection()) { DateTime currentDate = DateTime.Now; using (SQLiteCommand insertCommand = connection.CreateCommand()) { insertCommand.CommandText = "INSERT INTO Batches (brewerName, assistantBrewerName, brewingDate, recipeInfo) VALUES ('', '', @brewingDate, @recipeInfo)"; insertCommand.Parameters.AddWithValue("brewingDate", currentDate.ToString()); insertCommand.Parameters.AddWithValue("recipeInfo", recipe.RecipeId); insertCommand.ExecuteNonQuery(); } batch = new BatchDataModel(DatabaseUtility.GetLastInsertedRowId(connection)) { BrewingDate = currentDate, Recipe = recipe }; connection.Close(); } return batch; }
private void DeleteBatch(BatchDataModel batch) { BatchUtility.DeleteBatch(batch); if (CurrentBatch == batch) { int previousBatchIndex = SavedBatches.IndexOf(batch) - 1; CurrentBatch = previousBatchIndex >= 0 ? SavedBatches[previousBatchIndex] : SavedBatches.FirstOrDefault(); } SavedBatches.Remove(batch); }
public static IEnumerable<BatchDataModel> GetSavedBatches(IList<RecipeDataModel> availableRecipes) { using (SQLiteConnection connection = DatabaseUtility.GetNewConnection()) { using (SQLiteCommand getBatchesCommand = connection.CreateCommand()) { getBatchesCommand.CommandText = "SELECT * FROM Batches"; using (SQLiteDataReader reader = getBatchesCommand.ExecuteReader()) { while (reader.Read()) { int batchId = reader.GetInt32(0); int recipeId = reader.GetInt32(4); BatchDataModel batch = new BatchDataModel(batchId) { BrewerName = reader[1].ToString(), AssistantBrewerName = reader[2].ToString(), Recipe = availableRecipes.FirstOrDefault(recipe => recipe.RecipeId == recipeId) }; DateTime brewingDate; if (DateTime.TryParse(reader.GetString(3), out brewingDate)) batch.BrewingDate = brewingDate; // get gravity readings for batch using (SQLiteCommand getGravityReadingsForBatchCommand = connection.CreateCommand()) { getGravityReadingsForBatchCommand.CommandText = "SELECT GravityReadings.id, GravityReadings.specificGravity, GravityReadings.date FROM GravityReadings " + "JOIN GravityReadingsInBatch ON GravityReadingsInBatch.gravityReading = GravityReadings.id AND GravityReadingsInBatch.batch = @batchId"; getGravityReadingsForBatchCommand.Parameters.AddWithValue("batchId", batchId); using (SQLiteDataReader gravityReadingsReader = getGravityReadingsForBatchCommand.ExecuteReader()) { while (gravityReadingsReader.Read()) { GravityReadingDataModel gravityReading = new GravityReadingDataModel(gravityReadingsReader.GetInt32(0)) { Value = gravityReadingsReader.GetDouble(1) }; DateTime gravityReadingDate; if (DateTime.TryParse(gravityReadingsReader.GetString(2), out gravityReadingDate)) gravityReading.Date = gravityReadingDate; gravityReading.PropertyChanged += batch.Ingredient_PropertyChanged; batch.RecordedGravityReadings.Add(gravityReading); } } } yield return batch; } } } connection.Close(); } }
public static void SaveBatch(BatchDataModel batch) { using (SQLiteConnection connection = DatabaseUtility.GetNewConnection()) { using (SQLiteCommand updateBatchCommand = connection.CreateCommand()) { updateBatchCommand.CommandText = "UPDATE Batches SET brewerName = @brewerName, assistantBrewerName = @assistantBrewerName, brewingDate = @brewingDate WHERE id = @id"; updateBatchCommand.Parameters.AddWithValue("id", batch.BatchId); updateBatchCommand.Parameters.AddWithValue("brewerName", batch.BrewerName); updateBatchCommand.Parameters.AddWithValue("assistantBrewerName", batch.AssistantBrewerName); updateBatchCommand.Parameters.AddWithValue("brewingDate", batch.BrewingDate.ToString()); updateBatchCommand.ExecuteNonQuery(); } foreach (GravityReadingDataModel gravityReading in batch.RecordedGravityReadings) { using (SQLiteCommand updateGravityReadingCommand = connection.CreateCommand()) { updateGravityReadingCommand.CommandText = "UPDATE GravityReadings SET specificGravity = @value, date = @date WHERE id = @id"; updateGravityReadingCommand.Parameters.AddWithValue("id", gravityReading.GravityReadingId); updateGravityReadingCommand.Parameters.AddWithValue("value", gravityReading.Value); updateGravityReadingCommand.Parameters.AddWithValue("date", gravityReading.Date); updateGravityReadingCommand.ExecuteNonQuery(); } } connection.Close(); } }