/// <summary> /// Deletes a specific ShopList in the ShoppingList (SQLite) database table, if it exists. /// </summary> /// <param name="shopList"></param> /// <returns> /// Returns a Tuple<bool, string>, with a processing message. /// </returns> public static Tuple <bool, string> DeleteShopList(ShopList shopList) { Tuple <bool, string> result = new Tuple <bool, string>(false, ""); using (SQLiteConnection connection = new SQLiteConnection(LoadConnectionString(), SQLiteOpenFlags.ReadWrite)) { try { if (SADatabaseReader.DoesShopListExist(shopList)) { _ = connection.Delete(shopList); if (!(SADatabaseReader.DoesShopListExist(shopList))) { result = new Tuple <bool, string>(true, $"ShopList {shopList.Name}({shopList.ShopListID}) has been deleted."); } else { result = new Tuple <bool, string>(false, $"ShopList {shopList.Name}({shopList.ShopListID}) still exists."); } } else { result = new Tuple <bool, string>(false, $"ShopList {shopList.Name}({shopList.ShopListID}) hasn't been saved yet or doesn't exist ."); } } catch (SQLiteException ex) { // TODO : SADatabaseReader.DeleteShopList: Error logging naar log.db throw; } } return(result); }
/// <summary> /// Deletes a ShopList member from the ShopListMember table. /// </summary> /// <param name="shopList"></param> /// <param name="user"></param> /// <returns> /// Returns a Tuple<bool, string>, with a processing message. /// </returns> public static Tuple <bool, string> DeleteShopListMember(ShopList shopList, AppUser user) { var result = new Tuple <bool, string>(false, ""); using (var connection = new SQLiteConnection(LoadConnectionString())) { try { connection.Execute($"DELETE FROM ShopListMember WHERE AppUser_ID = {user.ID} AND ShopList_ID = {shopList.SHOPLISTID}"); if (!(SADatabaseReader.DoesShopListMemberExist(shopList, user))) { result = new Tuple <bool, string>(true, $"ShopListMember {user.UserName}({user.ID}) is verwijderd."); } else { result = new Tuple <bool, string>(false, $"ShopListMember {user.UserName}({user.ID}) bestaat nog."); } } catch (SQLiteException ex) { // TODO : SADatabaseReader.DeleteShopListMember: Error logging naar log.db throw; } } return(result); }
/// <summary> /// Saves the changes made on the passed ShopList object to the ShoppingList (SQLite) database table. And if the shoplist already exists, that list will get updated instead of insterted. /// </summary> /// <param name="shopList"></param> /// <returns> /// Returns a Tuple<bool, string>, with a processing message. /// </returns> public static Tuple <bool, string> SaveShopList(ShopList shopList) { Tuple <bool, string> result = new Tuple <bool, string>(false, ""); using (SQLiteConnection connection = new SQLiteConnection(LoadConnectionString(), SQLiteOpenFlags.ReadWrite)) { try { if (SADatabaseReader.DoesShopListExist(shopList)) { _ = connection.Update(shopList); result = new Tuple <bool, string>(true, $"ShopList {shopList.Name}({shopList.ShopListID}) has been updated."); } else { _ = connection.Insert(shopList); result = new Tuple <bool, string>(true, $"ShopList {shopList.Name}({shopList.ShopListID}) has been added."); } } catch (SQLiteException ex) { // TODO : SADatabaseReader.SaveShopList: Error logging naar log.db throw; } } return(result); }
/// <summary> /// Updates or creates a shoplist owner record in the ShopListOwner table. /// </summary> /// <param name="shopList"></param> /// <param name="user"></param> /// <returns> /// Returns a Tuple<bool, string> with a processing message. /// </returns> public static Tuple <bool, string> UpdateShopListOwner(ShopList shopList, AppUser user) { Tuple <bool, string> result = new Tuple <bool, string>(false, ""); using (SQLiteConnection connection = new SQLiteConnection(LoadConnectionString(), SQLiteOpenFlags.ReadWrite)) { try { if (SADatabaseReader.DoesShopListExist(shopList)) { connection.Execute( "INSERT OR REPLACE INTO ShopListOwner (AppUser_ID, ShopList_ID, DateTimeModified) " + $"VALUES ({user.ID}, {shopList.SHOPLISTID}, '{DateTimeStamp.Stamp()}')" ); result = new Tuple <bool, string>(false, $"De owner van shoplist {shopList.Name}({shopList.SHOPLISTID}) is bijgewerkt."); } else { result = new Tuple <bool, string>(false, $"ShopList {shopList.Name}({shopList.SHOPLISTID}) bestaat niet."); } } catch (SQLiteException ex) { // TODO : SADatabaseReader.UpdateShopListOwner: Error logging naar log.db throw; } } return(result); }
/// <summary> /// Deletes a Shoplist item from the ShopListItem table. /// </summary> /// <param name="shopList"></param> /// <param name="item"></param> /// <returns> /// Returns a Tuple<bool, string> with a processing message. /// </returns> public static Tuple <bool, string> DeleteShopListItem(ShopList shopList, ShopListItem item) { Tuple <bool, string> result = new Tuple <bool, string>(false, ""); using (SQLiteConnection connection = new SQLiteConnection(LoadConnectionString())) { try { if (SADatabaseReader.DoesShopListItemExist((int)shopList.SHOPLISTID, item)) { connection.Execute($"DELETE FROM ShopListItem WHERE ShopList_ID = {shopList.SHOPLISTID} AND ShopListProduct_ID = {item.ShopListProductID}"); result = new Tuple <bool, string>(true, $"ShopList item {item.ProductName}({item.ShopListProductID}) succesvol verwijderd."); } else { result = new Tuple <bool, string>(false, $"ShopList item {item.ProductName}({item.ShopListProductID}) bestaat niet."); } } catch (SQLiteException ex) { // TODO : SADatabaseReader.DeleteShopListItem: Error logging naar log.db throw; } } return(result); }
/// <summary> /// Adds a record to the ShopListMember table, with a foreign keys to the AppUser and ShopList tables. /// </summary> /// <param name="shopList"></param> /// <param name="user"></param> /// <returns> /// Returns a Tuple<bool, string>, with a processing message. /// </returns> public static Tuple <bool, string> AddShopListMember(ShopList shopList, AppUser user) { var result = new Tuple <bool, string>(false, ""); using (var connection = new SQLiteConnection(LoadConnectionString())) { try { if (SADatabaseReader.DoesShopListExist(shopList)) { var AppUserExists = connection.Query <AppUser>($"SELECT * FROM AppUser WHERE ID = {user.ID}").Count; if (AppUserExists > 0) { if (!(SADatabaseReader.DoesShopListMemberExist(shopList, user))) { connection.Execute( "INSERT OR REPLACE INTO ShopListMember (AppUser_ID, ShopList_ID, DateTimeAdded) " + $"VALUES ({user.ID}, {shopList.SHOPLISTID}, '{DateTimeStamp.Stamp()}')" ); result = new Tuple <bool, string>(false, $"Member {user.UserName}({user.ID}) is toegevoegd aan shoplist {shopList.Name}({shopList.SHOPLISTID})."); } else { result = new Tuple <bool, string>(false, $"Gebruiker {user.UserName}({user.ID}) bestaat al."); } } else { result = new Tuple <bool, string>(false, $"Gebruiker {user.UserName}({user.ID}) bestaat niet of is nog niet opgeslagen."); } } else { result = new Tuple <bool, string>(false, $"ShopList {shopList.Name}({shopList.SHOPLISTID}) bestaat niet of is nog niet opgeslagen."); } } catch (SQLiteException ex) { // TODO : SADatabaseReader.AddShopListMember: Error logging naar log.db throw; } } return(result); }
/// <summary> /// Saves or updates a shopList item in the ShopListItem table. /// </summary> /// <param name="shopList"></param> /// <param name="item"></param> /// <returns> /// Returns a Tuple<bool, string>, with a processing message. /// </returns> public static Tuple <bool, string> SaveShopListItem(ShopList shopList, ShopListItem item) { Tuple <bool, string> result = new Tuple <bool, string>(false, ""); using (SQLiteConnection connection = new SQLiteConnection(LoadConnectionString(), SQLiteOpenFlags.ReadWrite)) { try { if (SADatabaseReader.DoesShopListExist(shopList)) { if (SADatabaseReader.DoesShopListItemExist((int)shopList.SHOPLISTID, item)) { string updateQuery = $"UPDATE ShopListItem SET Unit = {item.Unit}, Amount = {item.Amount}, ItemAcquired = {item.Acquired}, DateTimeModified = '{item.ITEMUPDATED}' " + $"WHERE ShopList_ID = {shopList.SHOPLISTID} AND ShopListProduct_ID = {item.ShopListProductID}"; connection.Execute(updateQuery); result = new Tuple <bool, string>(true, $"ShopList item {item.ShopListProductID} is bijgewerkt."); } else { string insertQuery = "INSERT INTO ShopListItem (ShopList_ID, ShopListProduct_ID, Unit, Amount, ItemAcquired, DateTimeAdded) " + $"VALUES ({shopList.SHOPLISTID}, {item.ShopListProductID}, {item.Unit}, {item.Amount}, {item.Acquired}, '{item.ItemAdded}')"; connection.Execute(insertQuery); result = new Tuple <bool, string>(true, $"ShopList item {item.ShopListProductID} is toegevoegd aan {shopList.Name}({shopList.SHOPLISTID})."); } } else { result = new Tuple <bool, string>(false, $"ShopList {shopList.Name}({shopList.SHOPLISTID}) bestaat niet of is nog niet opgeslagen."); } } catch (SQLiteException ex) { // TODO : SADatabaseReader.SaveShopListItem: Error logging naar log.db throw; } } return(result); }
static void Main() { // TODO : Integreer in ShopAssist app. #region Reader Console.WriteLine("Products:"); IList <Products> products = SADatabaseReader.LoadProducts(); foreach (Products product in products) { Console.WriteLine(product.ToString()); } Console.WriteLine("\nAppUsers:"); IList <AppUser> users = SADatabaseReader.LoadAppUsers(); foreach (AppUser user in users) { Console.WriteLine(user.ToString()); } Console.WriteLine("\nBrands:"); IList <Brand> brands = SADatabaseReader.LoadBrands(); foreach (Brand brand in brands) { Console.WriteLine(brand.ToString()); } Console.WriteLine("\nShoppinglists:"); IList <ShopList> shopLists = SADatabaseReader.LoadShopLists(); foreach (ShopList shopList in shopLists) { Console.WriteLine(shopList.ToString()); } Console.WriteLine("\nShoppinglist members:"); IList <ShopListMember> shopListMembers = SADatabaseReader.LoadShopListMembers(1); foreach (ShopListMember shopListMember in shopListMembers) { Console.WriteLine(shopListMember.ToString()); } Console.WriteLine("\nShoppinglist content:"); IList <ShopListItem> items = SADatabaseReader.LoadShopListItems(1); foreach (ShopListItem item in items) { Console.WriteLine(item.ToString()); } #endregion #region UpdateShopList test /* * ShopList list = new ShopList(); * //list.ShopListID = SADatabaseReader.GetNewShopListID(); * list.Name = "Kerst kerkdienst middelen"; * list.ListCategory = 2; * list.DateTimeAdded = "00:00:00 03-01-2021"; // TODO : Method voor het vullen van een datetime in de voorgestelde format; * // TODO : DateTimeModified instellen, mits changed property op true staat; * * var result = SADatabaseWriter.SaveShopList(list); * Console.WriteLine($"\nUpdate result is: {result.Item2}"); * * Console.WriteLine("\nUpdated shoppinglists:"); * shopLists = SADatabaseReader.LoadShopLists(); * * foreach (ShopList shopList in shopLists) * { * Console.WriteLine(shopList.ToString()); * } */ #endregion #region DeleteShopList test /* * ShopList list = new ShopList(); * list.ShopListID = 2; * * var result = SADatabaseWriter.DeleteShopList(list); * Console.WriteLine($"\nDelete result is: {result.Item2}"); * * Console.WriteLine("\nUpdated shoppinglists:"); * shopLists = SADatabaseReader.LoadShopLists(); * * foreach (ShopList shopList in shopLists) * { * Console.WriteLine(shopList.ToString()); * } */ #endregion #region SaveShopListItem test /* * ShopList list = new ShopList(); * list.ShopListID = 3; * * ShopListItem shopListItem = new ShopListItem(); * shopListItem.ShopListProductID = 1; * shopListItem.Unit = 2; * shopListItem.Amount = 10; * shopListItem.Acquired = 0; * shopListItem.ItemAdded = DateTimeStamp.Stamp(); * shopListItem.ShopListItemChanged = true; * * var result = SADatabaseWriter.SaveShopListItem(list, shopListItem); * Console.WriteLine($"\nShopListItem Save result is: {result.Item2}"); * * Console.WriteLine("\nShoppinglist content:"); * IList<ShopListItem> shopListItems = SADatabaseReader.LoadShopListItems(3); * * foreach (ShopListItem item in shopListItems) * { * Console.WriteLine(item.ToString()); * } */ #endregion #region DeleteShopListItem test /* * ShopList list = new ShopList(); * list.ShopListID = 4; * * ShopListItem shopListItem = new ShopListItem(); * shopListItem.ShopListProductID = 1; * * var result = SADatabaseWriter.DeleteShopListItem(list, shopListItem); * Console.WriteLine($"\nShopListItem Delete result is: {result.Item2}"); * * Console.WriteLine("\nShoppinglist content:"); * IList<ShopListItem> shopListItems = SADatabaseReader.LoadShopListItems(3); * * foreach (ShopListItem item in shopListItems) * { * Console.WriteLine(item.ToString()); * } */ #endregion #region Save ShopListOwner test /* * AppUser user1 = new AppUser(); * user1.ID = 2; * * AppUser user2 = new AppUser(); * user2.ID = 1; * * ShopList list = new ShopList(); * list.ShopListID = 1; * * var result = SADatabaseWriter.UpdateShopListOwner(list, user1); * Console.WriteLine($"\nShopList owner save result: {result.Item2}"); * * var IsUser1ListOwner = SADatabaseReader.GetShopListOwnerID(list, user1); * Console.WriteLine($"Is user1 the listOwner: {IsUser1ListOwner}"); * * var IsUser2ListOwner = SADatabaseReader.GetShopListOwnerID(list, user2); * Console.WriteLine($"Is user2 the listOwner: {IsUser2ListOwner}"); */ #endregion #region Delete ShopList owner /* * AppUser user1 = new AppUser(); * user1.ID = 2; * user1.UserName = "******"; * * ShopList list = new ShopList(); * list.ShopListID = 1; * * var result = SADatabaseWriter.DeleteShopListOwner(list, user1); * Console.WriteLine($"\nShopList owner delete result: {result.Item2}"); * * var IsUser1ListOwner = SADatabaseReader.GetShopListOwnerID(list, user1); * Console.WriteLine($"Is user1 nog steeds een listOwner: {IsUser1ListOwner}"); */ #endregion #region Add ShopList member test /* * AppUser user1 = new AppUser(); * user1.ID = 2; * user1.UserName = "******"; * * AppUser user2 = new AppUser(); * user2.ID = 3; * user2.UserName = "******"; * * ShopList list = new ShopList(); * list.ShopListID = 1; * * var result = SADatabaseWriter.AddShopListMember(list, user1); * Console.WriteLine($"\nShopList member add result: {result.Item2}"); * * var IsUser1ListMember = SADatabaseReader.GetShopListMemberID(list, user1); * Console.WriteLine($"Is user1 the listmember: {IsUser1ListMember}"); * * var IsUser2ListMember = SADatabaseReader.GetShopListMemberID(list, user2); * Console.WriteLine($"Is user2 the listmember: {IsUser2ListMember}"); */ #endregion #region Delete ShopList member /* * AppUser user1 = new AppUser(); * user1.ID = 2; * user1.UserName = "******"; * * ShopList list = new ShopList(); * list.ShopListID = 1; * * var result = SADatabaseWriter.DeleteShopListMember(list, user1); * Console.WriteLine($"\nShopList member delete result: {result.Item2}"); * * var IsUser1ListMember = SADatabaseReader.GetShopListMemberID(list, user1); * Console.WriteLine($"Is user1 nog steeds een listmember: {IsUser1ListMember}"); */ #endregion }