// DataTable FetchAll(Table table) /// <summary> /// Fetches an entire table from the database. /// </summary> /// <param name="table">The table you want to pull from</param> /// <returns> /// An entire database table as a DataTable if the query succeeds. Returns null otherwise. /// </returns> public DataTable FetchAll(Table table, int week = 0) { try { var cmd = new MySqlCommand { CommandText = (week == 0 ? Constants.SELECTTABLE.Replace("@Table", table.ToString()) : Constants.SELECTSESSIONSINWEEK.Replace("@Week", week.ToString()) ), Connection = _connection }; _connection.Open(); var record = new DataTable(); using (var reader = new MySqlDataAdapter(cmd)) { reader.Fill(record); } _connection.Close(); return(record); } catch (Exception err) { // If something went wrong, make sure we don't allow further record manipulation. ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(null); } }
// bool DeleteRegistration(int stuId, int sessId) /// <summary> /// Deletes a reservation if it exists. /// </summary> /// <param name="stuId">The student id that is canceling.</param> /// <param name="sessId">The session the student is canceling.</param> /// <returns> /// True if deletion is succesful. Returns false if the query was unsuccesful or invalid Id number were passed in. /// </returns> public bool DeleteRegistration(int stuId, int sessId) { if (!CheckExists(stuId, Table.STUDENT) || !CheckExists(sessId, Table.SESSION)) { return(false); } try { var cmd = new MySqlCommand { CommandText = Constants.DELRESERVATION, Connection = _connection }; cmd.Parameters.AddWithValue(Constants.STUID, stuId); cmd.Parameters.AddWithValue(Constants.SESSID, sessId); _connection.Open(); cmd.ExecuteNonQuery(); _connection.Close(); return(true); } catch (Exception err) { ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(false); } }
// bool DeleteRecord(int id,Table table) /// <summary> /// Deletes a record with the specified id in the specified table. /// </summary> /// <param name="id">The record id you want to delete.</param> /// <param name="table">The table you want to delete from.</param> /// <returns> /// True if deletion was successful. False otherwise. /// </returns> public bool DeleteRecord(int id, Table table) { try { var cmd = new MySqlCommand { CommandText = Constants.DELETERECORD.Replace("@Table", table.ToString()), Connection = _connection }; cmd.Parameters.AddWithValue(Constants.ID, id); _connection.Open(); using (cmd) { cmd.ExecuteNonQuery(); } _connection.Close(); return(true); } catch (Exception err) { ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(false); } }
// bool AddRegistration(int stuId, int sessId, int locId) /// <summary> /// Creates a registration record if the session isn't full. /// </summary> /// <param name="stuId">The student id requesting the reservation.</param> /// <param name="sessId">The session that we're trying to reserve.</param> /// <param name="locId">The location id of where the session is held.</param> /// <returns> /// True if reservation succeeds, false otherwise. /// </returns> public bool AddRegistration(int stuId, int sessId, int locId) { var chkSession = new Session(sessId, locId); if (CheckNumOpenSeats(chkSession) == MINSEATSAVAILABLE) { return(false); } try { _connection.Open(); var registerTransaction = _connection.BeginTransaction(IsolationLevel.ReadCommitted); try { var cmd = new MySqlCommand { CommandText = Constants.ADDRESERVATION, Connection = _connection, Transaction = registerTransaction }; cmd.Parameters.AddWithValue(Constants.STUID, stuId); cmd.Parameters.AddWithValue(Constants.SESSID, sessId); cmd.ExecuteNonQuery(); registerTransaction.Commit(); _connection.Close(); return(true); } catch (Exception err) { try { registerTransaction.Rollback(); ErrorLogger.AddError(err); return(false); } catch (Exception rollbackerr) { ErrorLogger.AddError(rollbackerr); return(false); } } } catch (Exception err) { ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(false); } }
// DBObject() /// <summary> /// Initalizes _connection to prepare it for queries. /// </summary> public DBObject() { try { _connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["MainDBConnectionString"].ToString()); } catch (Exception err) { ErrorLogger.AddError(err); } }
// bool AddRecord(Instructor newInstructor) /// <summary> /// Adds an instructor record. Returns false if record insertion was unsuccessful or if _database was not initalized. /// </summary> /// <param name="newInstructor">The instructor object you want to insert.</param> public static bool AddRecord(Instructor newInstructor) { try { return(_database.InsertRecord(newInstructor)); } catch (Exception err) { ErrorLogger.AddError(err); return(false); } }
// bool RemoveRegistrationRecord(int stuId,int sessId) /// <summary> /// Removes a reservation. If the reservation does not exist or another error occurs, nothing changes and the function returns false. /// </summary> /// <param name ="stuId">The Student Id that is trying to cancel a registration</param> /// <param name="sessId">The session Id that the student has signed up for</param> public static bool RemoveRegistrationRecord(int stuId, int sessId) { try { return(_database.DeleteRegistration(stuId, sessId)); } catch (Exception err) { ErrorLogger.AddError(err); return(false); } }
// bool UpdateRecord(Location newLoc) /// <summary> /// Updates an location record. Returns false if record Update was unsuccessful or if _database was not initalized. /// </summary> /// <param name="newLoc">The location object you want to Update.</param> public static bool UpdateRecord(Location newLoc) { try { return(_database.UpdateRecord(newLoc)); } catch (Exception err) { ErrorLogger.AddError(err); return(false); } }
// bool CheckExistingName(string name, Table table) /// <summary> /// Checks to see if the given name already exists in a record in the specified table. /// </summary> /// <param name="name"> /// The name you want to check for existance. /// </param> /// <param name="table"> /// The table you want to check for existance in. /// </param> public static bool CheckExistingName(string name, Table table) { try { return(false);//_database.CheckPreExistingName(name, table); } catch (Exception err) { ErrorLogger.AddError(err); return(false); } }
// bool RemoveRecord(int id, Table table) /// <summary> /// Removes a record from the indicated table. Returns false if record removal was unsuccessful or if _database was not initialized. /// </summary> /// <param name ="id"> The record id you want to delete. </param> public static bool RemoveRecord(int id, Table table) { try { return(_database.DeleteRecord(id, table)); } catch (Exception err) { ErrorLogger.AddError(err); return(false); } }
// DataTable FetchRecordList(Table table) /// <summary> /// Returns a DataTable populated with all records from the specified table. Returns null if retrieval fails. /// </summary> /// <param name ="table"> The table you want to return. Valid candidate are: STUDENT, LOCATION, SESSION, INSTRUCTOR, REG_QUEUE</param> /// <param name="week">If you are fetching a list of sessions, this is the week you want to look at. This parameter is optional.</param> public static DataTable FetchRecordList(Table table, int week = 0) { try { return(_database.FetchAll(table, week)); } catch (Exception err) { ErrorLogger.AddError(err); return(null); } }
// bool AddRecord(Session newSess) /// <summary> /// Adds an session record. Returns false if record insertion was unsuccessful or if _database was not initalized. /// </summary> /// <param name="newSess">The session object you want to insert.</param> public static bool AddRecord(Session newSess) { try { return(_database.InsertRecord(newSess)); } catch (Exception err) { ErrorLogger.AddError(err); return(false); } }
// int GetNumSeatsOpen(int sessId,int locId) /// <summary> /// Returns the number of seats still open in the provided session. /// </summary> /// <param name="session"> /// The session you want to query. /// </param> public static int GetNumSeatsOpen(int sessId, int locId) { try { var chkSessionSeats = new Session(sessId, locId); return(_database.CheckNumOpenSeats(chkSessionSeats)); } catch (Exception err) { ErrorLogger.AddError(err); return(0); } }
// bool RegisterStudent(string hashkey) /// <summary> /// Registers a new student; transfers their record from REG_QUEUE into STUDENT. /// </summary> /// <param name="hashkey">The hashed student email to register.</param> /// <returns> /// True if registration was succesful. False otherwise. /// </returns> public bool RegisterStudent(string hashkey) { try { var cmd = new MySqlCommand { CommandText = Constants.SELECTREGRECORD, Connection = _connection }; cmd.Parameters.AddWithValue(Constants.HASH, hashkey); _connection.Open(); var MySqlTransaction = _connection.BeginTransaction(); Student newStudent = null; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { newStudent = new Student(null, Convert.ToInt32(reader[Constants.STU_ID]), reader[Constants.STU_FNAME].ToString(), reader[Constants.STU_LNAME].ToString(), reader[Constants.STU_EMAIL].ToString()); } } _connection.Close(); if (InsertRecord(newStudent)) // We don't want to delete the record from REG_QUEUE if we couldn't insert it into STUDENT { DeleteRecord(newStudent.Id, Table.REG_QUEUE); return(true); } else { return(false); } } catch (Exception err) { // If something went wrong, make sure we don't allow further record manipulation. ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(false); } }
// List<string> SelectRecord(int id,Table table) /// <summary> /// Pulls a record from the database. /// </summary> /// <param name="id">The record id you want to fetch.</param> /// <param name="table">The table where the record exists in.</param> /// <returns> /// Formatted List of strings if query was successful. Returns null otherwise. /// </returns> public List <string> SelectRecord(int id, Table table) { try { var cmd = new MySqlCommand { CommandText = Constants.SELECTRECORD.Replace("@Table", table.ToString()), Connection = _connection }; cmd.Parameters.AddWithValue(Constants.ID, id); _connection.Open(); var record = new DataTable(); using (var reader = new MySqlDataAdapter(cmd)) { reader.Fill(record); } _connection.Close(); if (record.Rows.Count == 0) { return(null); } var selectedRecord = new List <string>(); foreach (DataRow row in record.Rows) { for (int i = 0; i < row.Table.Columns.Count; i++) { selectedRecord.Add(row[i].ToString()); } } return(selectedRecord); } catch (Exception err) { // If something went wrong, make sure we don't allow further record manipulation. ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(null); } }
// Location ReturnLocation(int id) /// <summary> /// Fetches a Location with the corresponding id as a Location object. Returns null if id does not exist or if _database is not initalized. /// </summary> /// <param name ="id"> The Location id you want to fetch. </param> public static Location ReturnLocation(int id) { try { var LocationData = _database.SelectRecord(id, Table.LOCATION); var rtnLocation = new Location(LocationData[Constants.LOC_NAME_INDEX], Convert.ToInt32(LocationData[Constants.LOC_ID_INDEX]), Convert.ToInt32(LocationData[Constants.LOC_CAP_INDEX])); return(rtnLocation); } catch (Exception err) { ErrorLogger.AddError(err); return(null); } }
// Instructor ReturnInstructor(int id) /// <summary> /// Fetches an Instructor with the corresponding id as an Instructor object. Returns null if id does not exist or if _database is not initalized. /// </summary> /// <param name ="id"> The Instructor id you want to fetch. </param> public static Instructor ReturnInstructor(int id) { try { var instructorData = _database.SelectRecord(id, Table.INSTRUCTOR); var rtnLocation = new Instructor(Convert.ToInt32(instructorData[Constants.INST_ID_INDEX]), instructorData[Constants.INST_FNAME_INDEX], instructorData[Constants.INST_LNAME_INDEX], instructorData[Constants.INST_EMAIL_INDEX]); return(rtnLocation); } catch (Exception err) { ErrorLogger.AddError(err); return(null); } }
// Session ReturnSession(int id) /// <summary> /// Fetches a Session with the corresponding id as a Session object. Returns null if id does not exist or if _database is not initalized. /// </summary> /// <param name ="id"> The Session id you want to fetch. </param> public static Session ReturnSession(int id) { try { var SessionData = _database.SelectRecord(id, Table.SESSION); var rtnSession = new Session(SessionData[1], SessionData[2], Convert.ToInt32(SessionData[0]), Convert.ToInt32(SessionData[3]), Convert.ToInt32(SessionData[4]), Convert.ToInt32(SessionData[5])); return(rtnSession); } catch (Exception err) { ErrorLogger.AddError(err); return(null); } }
// bool UpdateRecord(Session sess) /// <summary> /// Returns true if we are able to update a record in the database that has the same id as the object passed in. /// </summary> /// <param name="sess"> /// The session being updated. /// </param> /// <returns></returns> public bool UpdateRecord(Session sess) { if (sess == null) { return(false); } if (!CheckExists(sess.Id, Table.SESSION) || CheckPreExistingName(sess.Name, sess.Id, Table.SESSION)) { return(false); } try { var cmd = new MySqlCommand { Connection = _connection }; cmd.CommandText = Constants.UPDATESESSION; cmd.Parameters.AddWithValue(Constants.NAME, sess.Name); cmd.Parameters.AddWithValue(Constants.INSTID, sess.InstId); cmd.Parameters.AddWithValue(Constants.LOCID, sess.LocId); cmd.Parameters.AddWithValue(Constants.DESC, sess.Description); cmd.Parameters.AddWithValue(Constants.WEEK, sess.Week); cmd.Parameters.AddWithValue(Constants.ID, sess.Id); _connection.Open(); using (cmd) { cmd.ExecuteNonQuery(); } _connection.Close(); return(true); } catch (Exception err) { ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(false); } }
// Student ReturnStudent(int id) /// <summary> /// Fetches a student with the corresponding id as a Student object. Returns null if id does not exist or if _database is not initalized. /// </summary> /// <param name ="id"> The student id you want to fetch. </param> public static Student ReturnStudent(int id) { try { var studentData = _database.SelectRecord(id, Table.STUDENT); var reservations = _database.GetRegisteredSessions(id); var rtnStudent = new Student(reservations, Convert.ToInt32(studentData[Constants.STU_ID_INDEX]), studentData[Constants.STU_FNAME_INDEX], studentData[Constants.STU_LNAME_INDEX], studentData[Constants.STU_EMAIL_INDEX]); return(rtnStudent); } catch (Exception err) { ErrorLogger.AddError(err); return(null); } }
// bool UpdateRecord(Student stu) /// <summary> /// Returns true if we are able to update a record in the database that has the same id as the object passed in. /// </summary> /// <param name="stu"> /// The student being updated. /// </param> /// <returns></returns> public bool UpdateRecord(Student stu) { if (stu == null) { return(false); } if (!CheckExists(stu.Id, Table.STUDENT)) { return(false); } try { var cmd = new MySqlCommand { Connection = _connection }; cmd.CommandText = Constants.UPDATESTUDENT; cmd.Parameters.AddWithValue(Constants.ID, stu.Id); cmd.Parameters.AddWithValue(Constants.FNAME, stu.FirstName); cmd.Parameters.AddWithValue(Constants.LNAME, stu.LastName); cmd.Parameters.AddWithValue(Constants.EMAIL, stu.Email); _connection.Open(); using (cmd) { cmd.ExecuteNonQuery(); } _connection.Close(); return(true); } catch (Exception err) { ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(false); } }
// bool UpdateInsturctor(Instructor updateInstructor) /// <summary> /// Returns true if we are able to update a record in the database that has the same id as the object passed in. /// </summary> /// <param name="updateInstructor"> /// The instuctor you want to update. /// </param> /// <returns></returns> public bool UpdateRecord(Instructor updateInstructor) { if (updateInstructor == null) { return(false); } if (!CheckExists(updateInstructor.Id, Table.INSTRUCTOR)) { return(false); } try { var cmd = new MySqlCommand { Connection = _connection }; cmd.CommandText = Constants.UPDATEINSTRUCTOR; cmd.Parameters.AddWithValue(Constants.FNAME, updateInstructor.FirstName); cmd.Parameters.AddWithValue(Constants.LNAME, updateInstructor.LastName); cmd.Parameters.AddWithValue(Constants.EMAIL, updateInstructor.Email); _connection.Open(); using (cmd) { cmd.ExecuteNonQuery(); } _connection.Close(); return(true); } catch (Exception err) { ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(false); } }
// bool InsertRecord(Student stu,Session sess) /// <summary> /// Creates a new reservation record. /// </summary> /// <param name="stu"> /// The student that is reserving a position. /// </param> /// <param name="sess"> /// The session being reserved. /// </param> /// <returns></returns> public bool InsertRecord(Student stu, Session sess) { if (stu == null || sess == null) { return(false); } if (!CheckExists(stu.Id, Table.STUDENT) || !CheckExists(sess.Id, Table.SESSION)) { return(false); } try { var cmd = new MySqlCommand { Connection = _connection }; cmd.CommandText = Constants.INSERTINSTRUCTOR; cmd.Parameters.AddWithValue(Constants.STUID, stu.Id); cmd.Parameters.AddWithValue(Constants.SESSID, sess.Id); _connection.Open(); using (cmd) { cmd.ExecuteNonQuery(); } _connection.Close(); return(true); } catch (Exception err) { ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(false); } }
// bool UpdateRecord(Location newLoc) /// <summary> /// Returns true if we are able to update a record in the database that has the same id as the object passed in. /// </summary> /// <param name="updateLoc"> /// The location you want to update. /// </param> /// <returns></returns> public bool UpdateRecord(Location updateLoc) { if (updateLoc == null) { return(false); } if (!CheckExists(updateLoc.Id, Table.LOCATION) || CheckPreExistingName(updateLoc.Name, updateLoc.Id, Table.LOCATION)) { return(false); } try { var cmd = new MySqlCommand { Connection = _connection }; cmd.CommandText = Constants.UPDATELOCATION; cmd.Parameters.AddWithValue(Constants.NAME, updateLoc.Name); cmd.Parameters.AddWithValue(Constants.CAPACITY, updateLoc.Capacity); _connection.Open(); using (cmd) { cmd.ExecuteNonQuery(); } _connection.Close(); return(true); } catch (Exception err) { ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(false); } }
// List<Session> GetRegisteredSessions(int stuId) /// <summary> /// Gets a list of all sessions a student has registered for. /// </summary> /// <param name="stuId">The student id you want to check.</param> /// <returns> /// A list of sessions if stuId was valid and the query succeeds. Null otherwise. /// </returns> public DataTable GetRegisteredSessions(int stuId) { try { var sessions = new List <Session>(); // First we have to find all sessions that the student has signed up for var cmd = new MySqlCommand { CommandText = Constants.GETREGISTEREDSESS, Connection = _connection }; cmd.Parameters.AddWithValue(Constants.ID, stuId); _connection.Open(); var regSess = new DataTable(); using (var reader = new MySqlDataAdapter(cmd)) { reader.Fill(regSess); } _connection.Close(); if (regSess.Rows.Count == 0) { return(null); } return(regSess); } catch (Exception err) { ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(null); } }
public void btnAdd_Click(object sender, EventArgs e) { try { var newSess = new CHR.Session(txtSessionName.Text, txtDesc.Text, 0, Convert.ToInt32(hidLocId.Value), Convert.ToInt32(hidInstId.Value), Convert.ToInt32(ddlWeek.SelectedValue)); if (CHR.Master.AddRecord(newSess)) { Response.Redirect("adminsessionlist.aspx"); } else { litError.Text = ERROR_ONUPDATE; } } catch (Exception err) { ErrorLogger.AddError(err); litError.Text = ERROR_ONUPDATE; } }
// CheckExists(int id, Table table) /// <summary> /// Checks if an id number exists in the given table. /// </summary> /// <param name="id">The id number to check for.</param> /// <param name="table">The table to check in.</param> /// <returns> /// True if the id exists. False if it doesn't or the query fails. /// </returns> public bool CheckExists(int id, Table table) { try { _connection.Open(); var cmd = new MySqlCommand { CommandText = Constants.CHECKEXISTS.Replace("@Table", table.ToString()), // For some dumb reason you can't parameterize the table, so we have to set it here. CommandType = CommandType.Text, Connection = _connection }; cmd.Parameters.AddWithValue(Constants.ID, id); cmd.Prepare(); bool exists = false; using (var reader = cmd.ExecuteReader()) { reader.Read(); exists = reader.HasRows; } _connection.Close(); return(exists); } catch (Exception err) { // If something went wrong, make sure we don't allow further record manipulation. ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(false); } }
// int CheckNumOpenSeats(Session session) /// <summary> /// Get the number of open seats in the given session. /// </summary> /// <param name="session">The session you are checking</param> /// <returns> /// The number of open seats in the session. If the session's id is invalid, returns 0. /// </returns> public int CheckNumOpenSeats(Session session) { if (!(CheckExists(session.Id, Table.SESSION) && CheckExists(session.LocId, Table.LOCATION))) { return(0); } try { _connection.Open(); // We need to lock the records we are reading in the event another person tries to register for the same class at the same time. var chkNumSeatsTransaction = _connection.BeginTransaction(IsolationLevel.ReadCommitted); try { var cmdNumReservations = new MySqlCommand { CommandText = Constants.GETTOTALRESERVATIONS, Connection = _connection, Transaction = chkNumSeatsTransaction }; var cmdGetLocCapacity = new MySqlCommand { CommandText = Constants.GETLOCCAPACITY, Connection = _connection, Transaction = chkNumSeatsTransaction }; cmdNumReservations.Parameters.AddWithValue(Constants.SESSID, session.Id); cmdGetLocCapacity.Parameters.AddWithValue(Constants.LOCID, session.LocId); int numReservations = 0; int capacity = 0; using (var capacityReader = cmdGetLocCapacity.ExecuteReader()) { capacityReader.Read(); if (capacityReader.HasRows) { capacity = Convert.ToInt32(capacityReader[0].ToString()); } } using (var rsvpReader = cmdNumReservations.ExecuteReader()) { rsvpReader.Read(); if (rsvpReader.HasRows) { numReservations = Convert.ToInt32(rsvpReader[0].ToString()); } } chkNumSeatsTransaction.Commit(); _connection.Close(); return(capacity - numReservations); } catch (Exception err) { try { chkNumSeatsTransaction.Rollback(); ErrorLogger.AddError(err); return(0); } catch (Exception rollerr) { ErrorLogger.AddError(rollerr); return(0); } } } catch (Exception err) { ErrorLogger.AddError(err); if (_connection.State == ConnectionState.Open) { _connection.Close(); } return(0); } }