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