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);
}
