/// <summary> /// Changes the Status of the payment to true /// </summary> /// <param name="ReservationID">ReservationID integer</param> /// <returns>True if Database allows the changes false if not</returns> public static bool ChangePayment(int ReservationID) { DatabaseConnectie.OpenConnection(); try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "UPDATE Reservering SET BetalingStatus = 1 WHERE ID = @ID"; cmd.Parameters.Add(new SqlParameter("ID", ReservationID)); cmd.ExecuteNonQuery(); return(true); } catch (Exception e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); return(false); } finally { DatabaseConnectie.CloseConnection(); } }
/// <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 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> /// Gets Highest ID For a specific Table /// </summary> /// <param name="Tablename">Name from a table in the database</param> /// <returns>If record exists return a int that is not 0 or negative</returns> public static int GetHighestID(string TableName) { 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 " + TableName; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Check = (reader["Max"] != DBNull.Value) ? Convert.ToInt32(reader["Max"]) : 0; } } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(Check + 1); }
/// <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> /// Gets a single event from the database /// </summary> /// <param name="EventID">EventID integer</param> /// <returns>a specific event from the database</returns> public static Event GetSingleEvent(int EventID) { Event newEvent = null; if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "SELECT * FROM Event WHERE ID = @EventID"; cmd.Parameters.Add(new SqlParameter("EventID", EventID)); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int ID = Convert.ToInt32(reader["ID"]); string Name = (reader["Naam"].ToString()); DateTime Date = Convert.ToDateTime(reader["Datum"]); string Discription = (reader["Beschrijving"].ToString()); string Location = (reader["Locatie"].ToString()); int Maxvisitors = Convert.ToInt32(reader["Maxbezoekers"]); int CampingID = (reader["CampingID"] != DBNull.Value) ? Convert.ToInt32(reader["CampingID"]) : 0; int AccountID = Convert.ToInt32(reader["AccountID"]); /* * TimeLine Timeline = DatabaseGetTimeLine.GetTimeline(ID); * Account Account = DatabaseGetAccounts.GetSingleAccountID(AccountID); * Camping Camping; * * * if (CampingID != 0) * { * Camping = DatabaseGetCamping.GetSingleCamping(CampingID); * } * else * { * Camping = null; * } */ newEvent = new Event(ID, Name, Discription, Location, Maxvisitors, Date); } return(newEvent); } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(newEvent); }
/// <summary> /// Gets every event from the database /// </summary> /// <returns>A list with all event</returns> public static List <Event> GetEvents() { List <Event> EventList = new List <Event>(); if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "SELECT * FROM Event"; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int ID = Convert.ToInt32(reader["ID"]); string Name = (reader["Naam"].ToString()); DateTime Date = Convert.ToDateTime(reader["Datum"]); string Discription = (reader["Beschrijving"].ToString()); string Location = (reader["Locatie"].ToString()); int Maxvisitors = Convert.ToInt32(reader["Maxbezoekers"]); int CampingID = (reader["CampingID"] != DBNull.Value) ? Convert.ToInt32(reader["CampingID"]) : 0; int AccountID = Convert.ToInt32(reader["AccountID"]); TimeLine Timeline = DatabaseGetTimeLine.GetTimeline(ID); Account Account = DatabaseGetAccounts.GetSingleAccountID(AccountID); Camping Camping; if (CampingID != 0) { Camping = DatabaseGetCamping.GetSingleCamping(CampingID); } else { Camping = null; } Event Event = new Event(ID, Name, Discription, Location, Maxvisitors, Date, Account, Camping, Timeline); EventList.Add(Event); } return(EventList); } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(EventList); }
/// <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> /// Creates a new event for the database /// </summary> /// <param name="Event">Event that has to be created</param> /// <returns>True if Database allows the changes false if not</returns> public static bool CreateEvent(Event Event) { bool Check = false; int ID = DatabaseGetHighestID.GetHighestID("Event"); if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "INSERT INTO Event (ID, Naam, Datum, Beschrijving, Locatie, Maxbezoekers, CampingID, AccountID) VALUES (@ID, @Name, @Date, @Description, @location, @MaxVisitors, @CampingID, @AccountID)"; cmd.Parameters.Add(new SqlParameter("ID", ID)); cmd.Parameters.Add(new SqlParameter("Name", Event.Name)); cmd.Parameters.Add(new SqlParameter("Date", Event.Date)); cmd.Parameters.Add(new SqlParameter("Description", Event.Description)); cmd.Parameters.Add(new SqlParameter("Location", Event.Location)); cmd.Parameters.Add(new SqlParameter("MaxVisitors", Event.MaxVisitors)); cmd.Parameters.Add(new SqlParameter("AccountID", Event.Account.ID)); if (Event.Camping == null) { cmd.Parameters.Add(new SqlParameter("CampingID", DBNull.Value)); } else { cmd.Parameters.Add(new SqlParameter("CampingID", Event.Camping.ID)); } cmd.ExecuteNonQuery(); DatabaseUpdateMaterial.UpdateMaterial(Event.MaterialList, ID); Check = true; } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); Check = false; } finally { DatabaseConnectie.CloseConnection(); } } return(Check); }
/// <summary> /// Gets a single Account /// </summary> /// <param name = "AccountFuntion" > The AccountFunction Function</param> /// <returns>A list of accounts</returns> public static List <Account> GetAccountsFunction(Function AccountFuntion) { List <Account> AccountList = new List <Account>(); if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "SELECT * FROM Account WHERE Functie = @Functie"; cmd.Parameters.Add(new SqlParameter("Functie", AccountFuntion.ToString())); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int ID = Convert.ToInt32(reader["ID"]); string Username = (reader["Gebruikersnaam"].ToString()); string Password = (reader["Wachtwoord"].ToString()); string Function = (reader["Functie"].ToString()); string Name = (reader["Naam"].ToString()); int? EventID = (reader["EventID"] != DBNull.Value) ? Convert.ToInt32(reader["EventID"]) : 0; if (EventID == 0) { EventID = null; } Account Account = new Account(ID, Name, Username, Password, CurrentAccount.TranslateFunction(Function), EventID); AccountList.Add(Account); } return(AccountList); } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(AccountList); }
/// <summary> /// Get accounts that belong with a reservation /// </summary> /// <param name="ReservationID">Reservation ID integer</param> /// <returns>A list of al accounts that are in a reservation</returns> public static List <Account> GetAccountsReservation(int ReservationID) { List <Account> AccountList = new List <Account>(); if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "select * from Account_Reservering ar join Account a on ar.AccountID = a.ID where ar.ReserveringID = @ReservationID"; cmd.Parameters.Add(new SqlParameter("@ReservationID", ReservationID)); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int ID = Convert.ToInt32(reader["ID"]); string Username = (reader["Gebruikersnaam"].ToString()); string Password = (reader["Wachtwoord"].ToString()); string Function = (reader["Functie"].ToString()); string Name = (reader["Naam"].ToString()); Account Account = new Account(ID, Name, Username, Password, CurrentAccount.TranslateFunction(Function)); AccountList.Add(Account); } return(AccountList); } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(AccountList); }
/// <summary> /// Returns the Account in a list with the RFID. (bestemd voor het uitchecken) /// </summary> /// <param name="RFID">RFID string.</param> /// <param name="EventID">Gets account that has to be checked out</param> /// <returns></returns> public static Account GetAccountRFID_Checkuit(string RFID, int EventID) { Account account = null; if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "Select * from account a where a.RFID = @RFID and a.EventID = @EventID"; cmd.Parameters.Add(new SqlParameter("RFID", RFID)); cmd.Parameters.Add(new SqlParameter("EventID", EventID)); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int ID = Convert.ToInt32(reader["ID"]); string Username = (reader["Gebruikersnaam"].ToString()); string Password = (reader["Wachtwoord"].ToString()); string Function = (reader["Functie"].ToString()); string Name = (reader["Naam"].ToString()); account = new Account(ID, Name, Username, Password, CurrentAccount.TranslateFunction(Function)); } return(account); } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(account); }
/// <summary> /// Gets a list of all places that belong to a camping /// </summary> /// <param name="CampingID">EventID int?</param> /// <returns>Returns a list of places</returns> public static List <Place> GetPlacesCamping(int?CampingID) { List <Place> placeList = new List <Place>(); if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "SELECT * FROM Plaats Where CampingID = @CampingID"; cmd.Parameters.Add(new SqlParameter("CampingID", CampingID)); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int ID = Convert.ToInt32(reader["ID"]); string Terrain = (reader["Terrein"].ToString()); int Space = Convert.ToInt32(reader["Oppervlakte"]); string Categorie = (reader["Categorie"].ToString()); Place Place = new Place(ID, Terrain, Space, CurrentAccount.TranslateCategorie(Categorie)); placeList.Add(Place); } return(placeList); } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(placeList); }
/// <summary> /// Creates an account with RFID attached /// </summary> /// <param name="RFIDTag">Tag that has to be added to the account</param> /// <param name="Acccount">Account that has to be created</param> /// <returns>True if Database allows the changes false if not</returns> public static bool CreateAccountWithRFID(Account Account, string RFIDTag) { bool Check = false; int ID = DatabaseGetHighestID.GetHighestID("Account"); if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "INSERT INTO Account (ID, Gebruikersnaam, Wachtwoord, Functie, Naam, RFID) VALUES (@ID, @UserName, @Password, @Function, @Name, @RFID)"; 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("@ID", ID)); cmd.Parameters.Add(new SqlParameter("Function", Account.Function.ToString())); cmd.Parameters.Add(new SqlParameter("@RFID", RFIDTag)); 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 a camping from the database (the ID is a Foreign key from another table) /// </summary> /// Reservation /// <param name="ID">ID for camping</param> /// <returns>Gets a camping that exists in the database null if not</returns> public static Camping GetSingleCamping(int ID) { Camping Camping = null; if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "SELECT * FROM Camping WHERE ID = @ID"; cmd.Parameters.Add(new SqlParameter("ID", ID)); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { string Name = (reader["Naam"].ToString()); string Streetname = (reader["Straatnaam"].ToString()); string Location = (reader["Locatie"].ToString()); Camping = new Camping(ID, Name, Streetname, Location); } return(Camping); } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(Camping); }
/// <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> /// Changes the attributes for an Event /// </summary> /// <param name="Event">Event that has to be edited</param> /// <returns>True if Database allows the changes false if not</returns> public static bool EditEvent(Event Event) { bool Check = false; if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "UPDATE Event SET Naam = @Name, Datum = @Date, Beschrijving = @Description, Locatie = @Location WHERE ID = @ID"; cmd.Parameters.Add(new SqlParameter("ID", Event.ID)); cmd.Parameters.Add(new SqlParameter("Name", Event.Name)); cmd.Parameters.Add(new SqlParameter("Date", Event.Date)); cmd.Parameters.Add(new SqlParameter("Description", Event.Description)); cmd.Parameters.Add(new SqlParameter("Location", Event.Location)); cmd.ExecuteNonQuery(); Check = true; } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); Check = false; } 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); }
/// <summary> /// Gets the timeLine To post on /// </summary> /// <param name="EventID">EventID integer</param> /// <returns>A timeLine</returns> public static TimeLine GetTimeline(int EventID) { TimeLine Timeline = null; if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "SELECT * FROM Tijdlijn WHERE EventID = @EventID"; cmd.Parameters.Add(new SqlParameter("EventID", EventID)); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int ID = Convert.ToInt32(reader["ID"]); Timeline = new TimeLine(ID); } return(Timeline); } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); } finally { DatabaseConnectie.CloseConnection(); } } return(Timeline); }
/// <summary> /// Creates a new column in the reservation table. /// </summary> /// <param name="Reservation">Reservation that need to be created</param> /// <returns>Returns true if the reservation is added and false if it failed.</returns> public static bool CreateReservation(Reservation Reservation) { bool Check = false; int ID = DatabaseGetHighestID.GetHighestID("Reservering"); if (DatabaseConnectie.OpenConnection()) { try { DatabaseConnectie.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = DatabaseConnectie.connect; cmd.CommandText = "INSERT INTO Reservering (ID, PlaatsID, EventID, BetalingBedrag, BetalingStatus) VALUES (@ID, @PlaatsID, @EventID, @BetalingBedrag, @BetalingStatus)"; cmd.Parameters.Add(new SqlParameter("ID", Reservation.ID)); if (Reservation.Place != null) { cmd.Parameters.Add(new SqlParameter("PlaatsID", Reservation.Place.ID)); } else { cmd.Parameters.Add(new SqlParameter("PlaatsID", null)); } cmd.Parameters.Add(new SqlParameter("EventID", Reservation.Event.ID)); cmd.Parameters.Add(new SqlParameter("BetalingBedrag", Reservation.Payment.Amount)); //Converts the bool to a bit for the database. int paymentStatus; if (Reservation.Payment.Paid) { paymentStatus = 1; } else { paymentStatus = 0; } cmd.Parameters.Add(new SqlParameter("BetalingStatus", paymentStatus)); cmd.ExecuteNonQuery(); foreach (var account in Reservation.Accounts) { cmd.CommandText = "INSERT INTO Account_Reservering (AccountID, ReserveringID) VALUES (@AccountID, @ReserveringID)"; var para1 = cmd.Parameters.Add(new SqlParameter("AccountID", account.ID)); var para2 = cmd.Parameters.Add(new SqlParameter("ReserveringID", Reservation.ID)); cmd.ExecuteNonQuery(); cmd.Parameters.Remove(para1); cmd.Parameters.Remove(para2); } Check = true; } catch (SqlException e) { Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString()); Check = false; } finally { DatabaseConnectie.CloseConnection(); } } return(Check); }