/// <summary> /// Gets the latest created event ID /// </summary> /// <returns>If record exists return a int that is not 0 or negative</returns> private static int GetLatestEventID() { int Check = 0; if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "SELECT MAX(ID) AS Max FROM Event"; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Check = Convert.ToInt32(reader["Max"]); } } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(Check); }
/// <summary> /// asign all selected materials to an event /// </summary> /// <param name="MaterialList">List of materials</param> /// <param name="EventID">EventID integer</param> /// <returns>true if database allows changes false if not</returns> public static bool UpdateMaterial(List <Material> MaterialList, int EventID) { foreach (Material M in MaterialList) { if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "UPDATE Materiaal SET EventID = @EventID WHERE ID = @ID"; cmd.Parameters.Add(new SqlParameter("ID", M.ID)); cmd.Parameters.Add(new SqlParameter("EventID", EventID)); cmd.ExecuteNonQuery(); return(true); } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); return(false); } finally { DatabaseConnectie.CloseConnection(); } } return(true); } return(true); }
/// <summary> /// returns amount of people in a reservation /// </summary> /// <param name="Reservation">reservation</param> /// <returns>gets amount of all people in a reservation</returns> public static int GetReservationAmountReservation(Reservation Reservation) { int reservationCount = 0; if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "SELECT COUNT(AccountID) As count FROM Account_Reservering WHERE ReserveringID = @ReserveringID"; cmd.Parameters.Add(new SqlParameter("ReserveringID", Reservation.ID)); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { reservationCount = Convert.ToInt32(reader["count"]); } return(reservationCount); } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(reservationCount); }
/// <summary> /// Removes the account to an event /// </summary> /// <param name="Account">Account that has to be checked out</param> /// <param name="EventID">ID of event that has to be updated</param> /// <returns>True if Database allows the changes false if not</returns> public static bool UpdateEventID_checkuit(Account Account, int EventID) { bool Check = false; if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "UPDATE Account SET EventID = null WHERE Account.ID = @Accountid "; cmd.Parameters.Add(new SqlParameter("EventID", EventID)); cmd.Parameters.Add(new SqlParameter("Accountid", Account.ID)); cmd.ExecuteNonQuery(); Check = true; } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); Check = false; } finally { DatabaseConnectie.CloseConnection(); } } return(Check); }
/// <summary> /// Changes a account /// </summary> /// <param name="account">Account has to be edited</param> /// <returns>True if Database allows the changes false if not</returns> public static bool EditAccount(Account Account) { bool Check = false; if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "UPDATE Account SET Gebruikersnaam = @UserName, Wachtwoord = @Password, Naam = @Name, Functie = @Function WHERE ID = @ID"; cmd.Parameters.Add(new SqlParameter("Name", Account.Name)); cmd.Parameters.Add(new SqlParameter("UserName", Account.Username)); cmd.Parameters.Add(new SqlParameter("Password", Account.Password)); cmd.Parameters.Add(new SqlParameter("Function", Account.Function.ToString())); cmd.Parameters.Add(new SqlParameter("ID", Account.ID)); cmd.ExecuteNonQuery(); Check = true; } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); Check = false; } finally { DatabaseConnectie.CloseConnection(); } } return(Check); }
/// <summary> /// Gets list of reservations that belongs to a specific event /// </summary> /// <param name="EventID">EventID integer</param> /// <returns>A list of reservations</returns> public static List <Reservation> GetReservation(int EventID) { List <Reservation> ReservationList = new List <Reservation>(); Place Place; if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "SELECT * FROM Reservering Where EventID = @EventID"; cmd.Parameters.Add(new SqlParameter("EventID", EventID)); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int ID = Convert.ToInt32(reader["ID"]); int PlaceID = (reader["PlaatsID"] != DBNull.Value) ? Convert.ToInt32(reader["PlaatsID"]) : 0; int Amount = Convert.ToInt32(reader["BetalingBedrag"]); bool Status = Convert.ToBoolean(reader["BetalingStatus"]); List <Account> Accountlist = DatabaseGetAccounts.GetAccountsReservation(ID); Event newEvent = DatabaseGetEvents.GetSingleEvent(EventID); if (PlaceID == 0) { Place = null; } else { Place = DatabaseGetPlace.GetPlace(PlaceID); } Reservation Reservation = new Reservation(ID, newEvent, new Payment(Amount, Status), Place, Accountlist); ReservationList.Add(Reservation); } return(ReservationList); } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(ReservationList); }
/// <summary> /// Get all materials that are linked with a event and a account /// </summary> /// <param name="EventID">EventID integer</param> /// <param name="AccoutnID">AccountID integer</param> /// <returns>A list of materials that is linked with the event and a account</returns> public static List <Material> GetMaterialforAccountonEvent(int EventID, int AccountID) { List <Material> MaterialList = new List <Material>(); if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "SELECT * FROM Materiaal WHERE EventID = @EventID AND AccountID = @AccountID"; cmd.Parameters.Add(new SqlParameter("EventID", EventID)); cmd.Parameters.Add(new SqlParameter("AccountID", AccountID)); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int ID = Convert.ToInt32(reader["ID"]); int Price = Convert.ToInt32(reader["Prijs"]); string Type = (reader["Type"].ToString()); int? eventid = (reader["EventID"] != DBNull.Value) ? Convert.ToInt32(reader["EventID"]) : 0; int? accountID = (reader["AccountID"] != DBNull.Value) ? Convert.ToInt32(reader["AccountID"]) : 0; if (eventid == 0) { eventid = null; } if (accountID == 0) { accountID = null; } Material Material = new Material(ID, Type, Price, eventid, accountID); MaterialList.Add(Material); } return(MaterialList); } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(MaterialList); }
/// <summary> /// When a Event is being creater so does a timeline /// </summary> /// <returns>True if Database allows the changes false if not</returns> public static bool CreateTimeline() { bool Check = false; int ID = DatabaseGetHighestID.GetHighestID("Tijdlijn"); int EventID = GetLatestEventID(); if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "INSERT INTO Tijdlijn (ID, EventID) VALUES (@ID, @EventID)"; cmd.Parameters.Add(new SqlParameter("ID", ID)); cmd.Parameters.Add(new SqlParameter("EventID", EventID)); if (EventID > 0) { cmd.ExecuteNonQuery(); } Check = true; } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); Check = false; } finally { DatabaseConnectie.CloseConnection(); } } return(Check); }
/// <summary> /// Check if a user exist in the database /// </summary> /// <param name="Password">Currently submitted password</param> /// <param name="Username">Currently submitted Username</param> /// <returns>If record exists return a int that is not 0 or negative</returns> public static int CheckUser(string Password, string Username) { int Check = 0; if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "SELECT * FROM Account WHERE Wachtwoord = @Password AND GebruikersNaam = @UserName"; cmd.Parameters.Add(new SqlParameter("Password", Password)); cmd.Parameters.Add(new SqlParameter("UserName", Username)); //cmd.ExecuteNonQuery(); //OracleDataReader reader = cmd.ExecuteReader(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Check = Convert.ToInt32(reader["ID"]); return(Check); } } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(Check); }
/// <summary> /// Get string that is requested /// </summary> /// <param name="ID">ID from account integer</param> /// <param name="Data">Data you want to get string</param> /// <returns>A string</returns> public static string GetString(int ID, string Data) { string Check = ""; if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "SELECT * FROM Account WHERE ID = @ID"; cmd.Parameters.Add(new SqlParameter("ID", ID)); //cmd.ExecuteNonQuery(); //OracleDataReader reader = cmd.ExecuteReader(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Check = (reader[Data].ToString()); return(Check); } } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(Check); }