// in arbeit /// <summary> /// Ändert einen Trainingsplan in der Datenbank /// </summary> /// <param name="trainingsplan">Nimmt den bearbeiteten Trainingsplan entgegen (Typ Trainingsplan)</param> /// <returns>Gibt bei erfolgreichem Ausführen true zurück und bei einem Fehler false</returns> public bool Edit(Trainingsplan trainingsplan) { try { StaticDB.Connect(); string editTP_Base = $"UPDATE TP_Base SET Titel = '{trainingsplan.Titel}' WHERE ID={trainingsplan.ID}"; StaticDB.RunSQL(editTP_Base); string delLink = $"DELETE FROM TP_Link_BaseUebung WHERE ID_Base={trainingsplan.ID}"; StaticDB.RunSQL(delLink); foreach (var item in trainingsplan.UebungList) { string com = $"SELECT * FROM TP_Uebungen WHERE Name='{item.Name}' AND Gewicht='{item.Menge.ToString().Replace(",", ".")}' AND Repetition={item.Wiederholungen} AND Sets={item.Sätze}"; if (StaticDB.CheckExistenz(com) == true) { SqlCommand sqlCommand = new SqlCommand(com, StaticDB.Connection); StaticDB.Connection.Open(); int ID = (int)sqlCommand.ExecuteScalar(); StaticDB.Connection.Close(); com = $"INSERT INTO TP_Link_BaseUebung (ID_Base, ID_Uebung) VALUES('{trainingsplan.ID}', '{ID}')"; StaticDB.RunSQL(com); } else { com = $"INSERT INTO TP_Uebungen (Name, Gewicht, Repetition, Sets) VALUES ('{item.Name}', '{item.Menge.ToString().Replace(",", ".")}', '{item.Wiederholungen}', '{item.Sätze}'); " + "SELECT CAST(SCOPE_IDENTITY() AS INT)"; StaticDB.Connection.Open(); SqlCommand command = new SqlCommand(com, StaticDB.Connection); int ID = (int)command.ExecuteScalar(); StaticDB.Connection.Close(); com = $"INSERT INTO TP_Link_BaseUebung (ID_Base, ID_Uebung) VALUES('{trainingsplan.ID}', '{ID}')"; StaticDB.RunSQL(com); } } string editTP_Info = $"UPDATE TP_Info SET GeaendertAm='{DateTime.Now}' WHERE ID={trainingsplan.ID}"; StaticDB.RunSQL(editTP_Info); return(true); } catch (Exception ex) { _ = ex.Message; if (StaticDB.Connection != null) { if (StaticDB.Connection.State != ConnectionState.Closed) { StaticDB.Connection.Close(); } } return(false); } }
public bool Edit(Ernährungsplan ernährungsplan) { try { StaticDB.Connect(); string editEP_Base = $"UPDATE EP_Base SET Titel='{ernährungsplan.Titel}' WHERE ID={ernährungsplan.ID}"; StaticDB.RunSQL(editEP_Base); string delLink = $"DELETE FROM EP_Link_BaseMahlzeiten WHERE ID_Base={ernährungsplan.ID}"; StaticDB.RunSQL(delLink); foreach (var item in ernährungsplan.MahlzeitenList) { string com = $"SELECT * FROM EP_Mahlzeiten WHERE Nahrungsmittel='{item.Nahrungsmittel}' AND Menge='{item.Menge.ToString().Replace(",", ".")}' AND Einheit='{item.Einheit}'"; if (StaticDB.CheckExistenz(com) == true) { SqlCommand sqlCommand = new SqlCommand(com, StaticDB.Connection); StaticDB.Connection.Open(); int ID = (int)sqlCommand.ExecuteScalar(); StaticDB.Connection.Close(); com = $"INSERT INTO EP_Link_BaseMahlzeiten (ID_Base, ID_Mahlzeit) VALUES('{ernährungsplan.ID}', '{ID}')"; StaticDB.RunSQL(com); } else { com = $"INSERT INTO EP_Mahlzeiten (Nahrungsmittel, Menge, Einheit) VALUES ('{item.Nahrungsmittel}', '{item.Menge.ToString().Replace(",", ".")}', '{item.Einheit}');" + $"SELECT CAST(SCOPE_IDENTITY() AS INT)"; StaticDB.Connection.Open(); SqlCommand sqlCommand = new SqlCommand(com, StaticDB.Connection); int ID = (int)sqlCommand.ExecuteScalar(); StaticDB.Connection.Close(); com = $"INSERT INTO EP_Link_BaseMahlzeiten (ID_Base, ID_Mahlzeit) VALUES('{ernährungsplan.ID}', '{ID}')"; StaticDB.RunSQL(com); } } string editEP_Info = $"UPDATE EP_Info SET GeaendertAm='{DateTime.Now}' WHERE ID={ernährungsplan.ID}"; StaticDB.RunSQL(editEP_Info); return(true); } catch (Exception ex) { _ = ex.Message; if (StaticDB.Connection != null) { if (StaticDB.Connection.State != ConnectionState.Closed) { StaticDB.Connection.Close(); } } return(false); } }
internal bool CheckIfFavo(string key, User user) { string[] keys = key.Split(';'); string com = $"SELECT * FROM User_Favo WHERE Nutzername = '{user.Nutzername}' AND Typ = '{keys[0]}' AND Plan_ID = '{keys[1]}'"; bool? x = StaticDB.CheckExistenz(com); if (x == true) { return(true); } else { return(false); } }
/// <summary> /// Profilbild hochladen /// </summary> /// <param name="user">Benutzer</param> /// <param name="bild">Profilbild</param> /// <returns></returns> internal bool UploadProfilBild(User user, byte[] bild) { try { StaticDB.Connect(); string com = $"SELECT * FROM User_Bild WHERE Nutzername = '{user.Nutzername}'"; bool? existenz = StaticDB.CheckExistenz(com); if (existenz == null) { return(false); } else { if (existenz == true) { com = $"UPDATE User_Bild SET Bild = @bildBytes WHERE Nutzername = '{user.Nutzername}'"; } else if (existenz == false) { com = $"INSERT INTO User_Bild VALUES('{user.Nutzername}', @bildBytes)"; } SqlCommand command = new SqlCommand(com, StaticDB.Connection); StaticDB.Connection.Open(); command.Parameters.Add("@bildBytes", System.Data.SqlDbType.VarBinary).Value = bild; command.ExecuteNonQuery(); StaticDB.Connection.Close(); return(true); } } catch (Exception ex) { _ = ex.Message; if (StaticDB.Connection != null) { if (StaticDB.Connection.State != System.Data.ConnectionState.Closed) { StaticDB.Connection.Close(); } } return(false); } }
internal bool?Exists(string user) { string com = $"SELECT Nutzername FROM User_Base WHERE Nutzername = '{user}'"; return(StaticDB.CheckExistenz(com)); }
/// <summary> /// Fügt einen Trainingsplan zu der Datenbank hinzu. /// </summary> /// <param name="trainingsplan">Objekt vom Typ Trainingsplan</param> /// <returns>Gibt bei erfolgreichem Ausführen true zurück und bei einem Fehler false</returns> public bool AddTrainingsplan(Trainingsplan trainingsplan) { try { StaticDB.Connect(); string com = $"INSERT INTO TP_Base (Titel) values ('{trainingsplan.Titel}'); " + "SELECT CAST(SCOPE_IDENTITY() AS INT)"; SqlCommand command = new SqlCommand(com, StaticDB.Connection); StaticDB.Connection.Open(); int lastID = (int)command.ExecuteScalar(); StaticDB.Connection.Close(); if (trainingsplan.GeAendertAm != default) { com = $"INSERT INTO TP_Info (ID, ErstelltAm, ErstelltVon, GeaendertAm, Kategorie) VALUES ({lastID}, '{trainingsplan.ErstelltAm:yyyy-dd-MM HH:mm:ss}', '{trainingsplan.Ersteller.Nutzername}', '{trainingsplan.GeAendertAm:yyyy-dd-MM HH:mm:ss}', '{trainingsplan.Kategorie}');"; } else { com = $"INSERT INTO TP_Info (ID, ErstelltAm, ErstelltVon, Kategorie) VALUES ({lastID}, '{trainingsplan.ErstelltAm:yyyy-dd-MM HH:mm:ss}', '{trainingsplan.Ersteller.Nutzername}', '{trainingsplan.Kategorie}');"; } bool result = StaticDB.RunSQL(com); if (result == false) { com = $"DELETE FROM TP_Base WHERE ID = '{lastID}'"; StaticDB.RunSQL(com); return(false); } foreach (var uebung in trainingsplan.UebungList) { string checkEx = $"SELECT * FROM TP_Uebungen WHERE Name='{uebung.Name}' AND Gewicht={uebung.Menge.ToString().Replace(",", ".")} AND Repetition={uebung.Wiederholungen} AND Sets={uebung.Sätze}"; if (StaticDB.CheckExistenz(checkEx) == true) { int uebID = StaticDB.GetID(checkEx); string comTpLink = $"INSERT INTO TP_Link_BaseUebung (ID_Base, ID_Uebung) VALUES({lastID}, {uebID})"; StaticDB.RunSQL(comTpLink); } else { com = $"INSERT INTO TP_Uebungen (Name, Gewicht, Repetition, Sets, Einheit) VALUES ('{uebung.Name}', {uebung.Menge.ToString().Replace(",", ".")}, {uebung.Wiederholungen}, {uebung.Sätze}, '{uebung.Einheit}'); " + "SELECT CAST(SCOPE_IDENTITY() AS INT)"; SqlCommand insertUeb = new SqlCommand(com, StaticDB.Connection); StaticDB.Connection.Open(); int lastUebID = (int)insertUeb.ExecuteScalar(); StaticDB.Connection.Close(); string comTpLink = $"INSERT INTO TP_Link_BaseUebung (ID_Base, ID_Uebung) VALUES({lastID}, {lastUebID})"; StaticDB.RunSQL(comTpLink); } } StaticDB.Connection.Close(); return(true); } catch (Exception ex) { _ = ex.Message; if (StaticDB.Connection != null) { if (StaticDB.Connection.State != ConnectionState.Closed) { StaticDB.Connection.Close(); } } return(false); } }
public bool AddErnährungsplan(Ernährungsplan ernährungsplan) { try { StaticDB.Connect(); StaticDB.Connection.Open(); string com = $"INSERT INTO EP_Base (Titel) VALUES ('{ernährungsplan.Titel}'); SELECT CAST(SCOPE_IDENTITY() AS INT)"; SqlCommand sqlCommand = new SqlCommand(com, StaticDB.Connection); int ID = (int)sqlCommand.ExecuteScalar(); StaticDB.Connection.Close(); if (ernährungsplan.GeAendertAm != default) { com = $"INSERT INTO EP_Info (ID, ErstelltAm, ErstelltVon, GeaendertAm, Kategorie) VALUES ({ID}, '{ernährungsplan.ErstelltAm:yyyy-dd-MM HH:mm:ss}', '{ernährungsplan.Ersteller.Nutzername}', '{ernährungsplan.GeAendertAm:yyyy-dd-MM HH:mm:ss}', '{ernährungsplan.Kategorie}');"; } else { com = $"INSERT INTO EP_Info (ID, ErstelltAm, ErstelltVon, Kategorie) VALUES ({ID}, '{ernährungsplan.ErstelltAm:yyyy-dd-MM HH:mm:ss}', '{ernährungsplan.Ersteller.Nutzername}', '{ernährungsplan.Kategorie}');"; } bool result = StaticDB.RunSQL(com); if (result == false) { com = $"DELETE FROM EP_Base WHERE ID = '{ID}'"; StaticDB.RunSQL(com); return(false); } foreach (var mahlzeit in ernährungsplan.MahlzeitenList) { string checkEx = $"SELECT * FROM EP_Mahlzeiten WHERE Nahrungsmittel='{mahlzeit.Nahrungsmittel}' AND Menge={mahlzeit.Menge.ToString().Replace(",", ".")} AND Einheit='{mahlzeit.Einheit}'"; if (StaticDB.CheckExistenz(checkEx) == true) { int mahlID = StaticDB.GetID(checkEx); string comEpLink = $"INSERT INTO EP_Link_BaseMahlzeiten (ID_Base, ID_Mahlzeit) VALUES({ID}, {mahlID})"; StaticDB.RunSQL(comEpLink); } else { com = $"INSERT INTO EP_Mahlzeiten (Nahrungsmittel, Menge, Einheit) VALUES ('{mahlzeit.Nahrungsmittel}', {mahlzeit.Menge.ToString().Replace(",", ".")}, '{mahlzeit.Einheit}'); " + "SELECT CAST(SCOPE_IDENTITY() AS INT)"; SqlCommand insertMahl = new SqlCommand(com, StaticDB.Connection); StaticDB.Connection.Open(); int lastMahlID = (int)insertMahl.ExecuteScalar(); StaticDB.Connection.Close(); string comTpLink = $"INSERT INTO EP_Link_BaseMahlzeiten (ID_Base, ID_Mahlzeit) VALUES({ID}, {lastMahlID})"; StaticDB.RunSQL(comTpLink); } } StaticDB.Connection.Close(); return(true); } catch (Exception ex) { _ = ex.Message; if (StaticDB.Connection != null) { if (StaticDB.Connection.State != ConnectionState.Closed) { StaticDB.Connection.Close(); } } return(false); } }