public int addCake(CakeModel cakeModel) // without cake ID { if (cakeModel.Category.ID >= 0) { string insertSQLstring = $"INSERT INTO CAKE(NAME,IMPORTPRICE,SELLINGPRICE,AMOUNT,CATEGORYID,UNIT,INFORMATION) " + $"VALUES (@Name,@ImportPrice,@SellingPrice,@Amount,{cakeModel.Category.ID},@Unit,@Information)"; using (var cnn = new SQLiteConnection(_connectionString)) { // insert try { cnn.Execute(insertSQLstring, cakeModel); } catch (Exception ex) { Console.WriteLine(ex.Message); return(0); } } } else { // add new category string newCategorryName = cakeModel.Category.Name; var categoeyServices = new CategoryServices(); categoeyServices.AddCategory(new CategoryModel { Name = newCategorryName }); // load new category ID string sqlLoadId = "SELECT IFNULL(MAX(ID), 0) FROM CATEGORY"; int currentMaxCategoryId; using (var cnn = new SQLiteConnection(_connectionString)) { currentMaxCategoryId = cnn.QueryFirst <int>(sqlLoadId, new DynamicParameters()); // add cake normally string insertSQLstring = $"INSERT INTO CAKE(NAME,IMPORTPRICE,SELLINGPRICE,AMOUNT,CATEGORYID,UNIT,INFORMATION) " + $"VALUES (@Name,@ImportPrice,@SellingPrice,@Amount,{currentMaxCategoryId},@Unit,@Information)"; cnn.Execute(insertSQLstring, cakeModel); } } string SQLoadMaxIDCake = "SELECT ifnull(max(id),0) from cake"; int currentMaxCakeId = 0; using (var cnn = new SQLiteConnection(_connectionString)) { currentMaxCakeId = cnn.QueryFirst <int>(SQLoadMaxIDCake, new DynamicParameters()); } return(currentMaxCakeId); }
public CakeModel loadSingleCake(int IdToLoad) { var result = new CakeModel(); var catetoryService = new CategoryServices(); string sqlString = $"SELECT * FROM CAKE WHERE ID = {IdToLoad}"; using (var cnn = new SQLiteConnection(_connectionString)) { var output = cnn.QueryFirst <CakeModel>(sqlString, new DynamicParameters()); int categoryID = cnn.QueryFirst <int>($"SELECT CategoryID FROM CAKE WHERE ID = {IdToLoad}"); result = output; var categoryService = new CategoryServices(); result.Category = categoryService.LoadSingleCategory(categoryID); } return(result); }