public bool Login(int Id, string pass) { pass = Hash(pass); string SQLQuery = "SELECT Password FROM NovaRoomRegistrationTest.User where NNumber like " + Convert.ToString(Id) + " and Password Like '" + Convert.ToString(pass) + "'"; string output = ""; using (MySqlConnection connection = new MySqlConnection(SQL_String.GetRDSConnectionString())) { connection.Open(); using (MySqlCommand command = new MySqlCommand(SQLQuery, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { output = reader.GetString(0); } } } } if (output != "") { return(true); } else { return(false); } }
public Room GetReservationByUsername(int NNumber) { string SQLQuery = "SELECT * from NovaRoomRegistrationTest.Project " + "WHERE Occupant1 = " + NNumber + " || Occupant2 = " + NNumber + " || Occupant3 = " + NNumber + " || Occupant4 =" + NNumber; Room output = new Room(); using (MySqlConnection connection = new MySqlConnection(SQL_String.GetRDSConnectionString())) { connection.Open(); using (MySqlCommand command = new MySqlCommand(SQLQuery, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { output.RoomNumber = (int)reader["RoomNumber"]; output.Price = (int)reader["Price"]; if (reader["Occupant1"] == DBNull.Value) { output.Occupant1 = null; } else { output.Occupant1 = (int)reader["Occupant1"]; } if (reader["Occupant2"] == DBNull.Value) { output.Occupant2 = null; } else { output.Occupant2 = (int)reader["Occupant2"]; } if (reader["Occupant3"] == DBNull.Value) { output.Occupant3 = null; } else { output.Occupant3 = (int)reader["Occupant3"]; } if (reader["Occupant4"] == DBNull.Value) { output.Occupant4 = null; } else { output.Occupant4 = (int)reader["Occupant4"]; } } } } } return(output); }
public bool ChangePass(int Id, string newPass) { string Pass = newPass; newPass = Hash(newPass); string SQLQuery = "UPDATE NovaRoomRegistrationTest.User SET Password = '******' where NNumber = " + Convert.ToString(Id); using (MySqlConnection connection = new MySqlConnection(SQL_String.GetRDSConnectionString())) { connection.Open(); MySqlCommand command = new MySqlCommand(SQLQuery, connection); command.ExecuteReader(); } return(Login(Id, Pass)); }
public Student GetStudent(int Id) { string SQLQuery = "Select * from Student where NNumber = " + Id; Student output = new Student(); using (MySqlConnection connection = new MySqlConnection(SQL_String.GetRDSConnectionString())) { connection.Open(); using (MySqlCommand command = new MySqlCommand(SQLQuery, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { output.FirstName = reader["FirstName"].ToString(); output.LastName = reader["LastName"].ToString(); output.RazorsEdge = Convert.ToBoolean(reader["RazorsEdge"]); output.Athlete = Convert.ToBoolean(reader["Athlete"]); output.Sex = reader["Sex"].ToString(); output.Level = reader["Level"].ToString(); output.NNumber = Id; } } } } return(output); /* * using (var ctx = new NovaRoomRegistrationTestEntities()) * { * * try * { * return ctx.Student.SqlQuery("Select * from Student where NNumber = " + Id).FirstOrDefault(); * } * catch (Exception e) * { * Student se = new Student(); * se.FirstName = Convert.ToString(e); * return (se); * } * }*/ }
public List <Room> GetAvailableRoomsByFloor(string BuildingName, int FloorNumber) { //Lines 16-57 checks for all rooms except for rooms with no null occupants to the listOfOutputs string SQLQuery = "SELECT RoomNumber, Price, Occupant1, Occupant2, Occupant3, Occupant4 " + "FROM NovaRoomRegistrationTest.Project " + "WHERE (Occupant1 IS null || Occupant2 IS null || Occupant3 IS null || Occupant4 IS null) " + "AND BuildingName LIKE \"" + BuildingName + "\" and Left(RoomNumber, 1) LIKE " + FloorNumber; List <Room> listOfOutputs = new List <Room>(); using (MySqlConnection connection = new MySqlConnection(SQL_String.GetRDSConnectionString())) { connection.Open(); using (MySqlCommand command = new MySqlCommand(SQLQuery, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Room output = new Room(); output.RoomNumber = (int)reader["RoomNumber"]; output.Price = (int)reader["Price"]; if (reader["Occupant1"] == DBNull.Value) { output.Occupant1 = null; } else { output.Occupant1 = (int)reader["Occupant1"]; } if (reader["Occupant2"] == DBNull.Value) { output.Occupant2 = null; } else { output.Occupant2 = (int)reader["Occupant2"]; } if (reader["Occupant3"] == DBNull.Value) { output.Occupant3 = null; } else { output.Occupant3 = (int)reader["Occupant3"]; } if (reader["Occupant4"] == DBNull.Value) { output.Occupant4 = null; } else { output.Occupant4 = (int)reader["Occupant4"]; } listOfOutputs.Add(output); } } } } return(listOfOutputs); }
public bool ReserveRoom(int NNumber, string BuildingName, int RoomNum) { //Lines 62-94 check if the student already exists in the table, returning false if they do string SQLQuery = "SELECT Occupant1,Occupant2,Occupant3,Occupant4 " + "FROM NovaRoomRegistrationTest.Project " + "WHERE Occupant1 = " + NNumber + " || Occupant2 = " + NNumber + " || Occupant3 = " + NNumber + " || Occupant4 = " + NNumber; using (MySqlConnection connection = new MySqlConnection(SQL_String.GetRDSConnectionString())) { connection.Open(); using (MySqlCommand command = new MySqlCommand(SQLQuery, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { if (reader["Occupant1"] != DBNull.Value) { return(false); } else if (reader["Occupant2"] != DBNull.Value) { return(false); } else if (reader["Occupant3"] != DBNull.Value) { return(false); } else if (reader["Occupant4"] != DBNull.Value) { return(false); } } } } } //Line 97-130 gets all the occupants of a room, sets them in the Room class called output SQLQuery = "SELECT Occupant1, Occupant2, Occupant3, Occupant4 " + "FROM NovaRoomRegistrationTest.Project " + "WHERE BuildingName LIKE \"" + BuildingName + "\" and RoomNumber LIKE " + RoomNum; Room output = new Room(); using (MySqlConnection connection = new MySqlConnection(SQL_String.GetRDSConnectionString())) { connection.Open(); using (MySqlCommand command = new MySqlCommand(SQLQuery, connection)) { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { if (reader["Occupant1"] == DBNull.Value) { output.Occupant1 = null; } else { output.Occupant1 = (int)reader["Occupant1"]; } if (reader["Occupant2"] == DBNull.Value) { output.Occupant2 = null; } else { output.Occupant2 = (int)reader["Occupant2"]; } if (reader["Occupant3"] == DBNull.Value) { output.Occupant3 = null; } else { output.Occupant3 = (int)reader["Occupant3"]; } if (reader["Occupant4"] == DBNull.Value) { output.Occupant4 = null; } else { output.Occupant4 = (int)reader["Occupant4"]; } } } } } //133- 143 Checks to see which room is available, returning false if it's ever not available (though it should never reach that point) int OccNum = 0; if (output.Occupant1 == null) { OccNum = 1; } else if (output.Occupant2 == null) { OccNum = 2; } else if (output.Occupant3 == null) { OccNum = 3; } else if (output.Occupant4 == null) { OccNum = 4; } else { return(false); } //146-155 Adds the student to the occupant column that is available. SQLQuery = "UPDATE NovaRoomRegistrationTest.Project SET Occupant" + OccNum + " = '" + NNumber + "' " + "WHERE (BuildingName = '" + BuildingName + "' ) and (`RoomNumber` = '" + RoomNum + "')"; using (MySqlConnection connection = new MySqlConnection(SQL_String.GetRDSConnectionString())) { connection.Open(); MySqlCommand command = new MySqlCommand(SQLQuery, connection); try { command.ExecuteReader(); } catch (Exception e) { return(false); }; return(true); } }