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