public ActionResult Index(CourseOfferingViewModel viewModel) { try { // Populate viewModel = new CourseOfferingViewModelFactory(viewModel, _courseService).GetViewModel(); // Validate if (!ModelState.IsValid) return View(viewModel); // Bind var courseOffering = new CourseOffering { CourseId = viewModel.SelectedCourse, Semester = viewModel.Semester, Year = viewModel.SelectedYear }; // Save _courseService.AddCourseOffering(courseOffering); // Respond Success("The Course Offering has been created", true); } catch (CourseOfferingExistsException) { Danger("Course Offering already exists."); return View(viewModel); } // redirect back to the page index return RedirectToAction("Index"); }
public void AddCourseOffering(CourseOffering offering) { // If a course offering already exists, throw custom exception if (_courseOfferingRepository.CourseOfferingExists(offering)) throw new CourseOfferingExistsException(); // Insert the course offering _courseOfferingRepository.InsertCourseOffering(offering); }
/// <summary> /// Adds the course offering. /// </summary> /// <param name="courseOffering">The course offering.</param> public void AddCourseOffering(CourseOffering courseOffering) { using (_courseOfferingRepository) { if (_courseOfferingRepository.HasCourseOffering(courseOffering)) throw new CourseOfferingExistsException(); _courseOfferingRepository.InsertCourseOffering(courseOffering); } }
public List<CourseOffering> GetCourseOfferings() { var offerings = new List<CourseOffering>(); using (var conn = new SqlConnection(_connectionString)) using (var cmd = conn.CreateCommand()) { // Open a connection conn.Open(); // Build SQL query cmd.CommandText = "SELECT * from CourseOffering INNER JOIN Course ON CourseOffering.Course_CourseID = Course.CourseID"; // Create DataReader for storing the returning table into memory var dataReader = cmd.ExecuteReader(); // Check if the CourseOffering table has records if (dataReader.HasRows) { // Iterate through each record while (dataReader.Read()) { // Extract the course offering fields // Extract the course fields var number = dataReader["CourseID"].ToString(); var name = dataReader["CourseTitle"].ToString(); var weeklyHours = Convert.ToInt16(dataReader["HoursPerWeek"]); var year = Convert.ToInt16(dataReader["Year"]); var semester = dataReader["Semester"].ToString(); // Build the course object var course = new Course(number, name, weeklyHours); // Build the course offering object var offering = new CourseOffering(course, semester, year); // Append to the course offering list offerings.Add(offering); } } // Close the DataReader dataReader.Close(); // Execute the SELECT operation cmd.ExecuteNonQuery(); } return offerings; }
public void InsertCourseOffering(CourseOffering courseOffering) { using (var dbTransaction = _db.Database.BeginTransaction()) { try { _db.CourseOfferings.Add(courseOffering); _db.SaveChanges(); dbTransaction.Commit(); } catch (Exception) { dbTransaction.Rollback(); } } }
public void InsertCourseOffering(CourseOffering courseOffering) { using (var conn = new SqlConnection(_connectionString)) using (var cmd = conn.CreateCommand()) { // Open a connection conn.Open(); // Build SQL query cmd.CommandText = "INSERT INTO CourseOffering (Year, Semester, Course_CourseID) VALUES (@year, @Semester, @courseID)"; // Insert parameters into the course offering table cmd.Parameters.AddWithValue("@year", courseOffering.Year); cmd.Parameters.AddWithValue("@Semester", courseOffering.Semester); cmd.Parameters.AddWithValue("@courseID", courseOffering.CourseOffered.Number); // Perform the INSERT operation cmd.ExecuteNonQuery(); } }
public void AddStudent(Student student, CourseOffering offering) { // If the student does not yet exist if (!_studentRepository.StudentExists(student.Number)) { // Insert the student into the DB _studentRepository.InsertStudent(student); // Register the student to the course offering _studentRepository.RegisterStudent(student, offering); } else { // Check if student is registered in offering var studentExists = _studentRepository.GetStudentsByOffering(offering).Find(s => s.Number == student.Number); // If student is not registered if (studentExists == null) // Register the student _studentRepository.RegisterStudent(student, offering); } }
public void RegisterStudent(Student student, CourseOffering offering) { using (var conn = new SqlConnection(_connectionString)) using (var cmd = conn.CreateCommand()) { // Open a connection conn.Open(); // Build SQL query cmd.CommandText = "INSERT INTO Registration " + "(Student_StudentNum, CourseOffering_Course_CourseID, CourseOffering_Year, CourseOffering_Semester) " + "VALUES (@studentNum, @courseID, @year, @semester)"; // Insert parameters into the course table cmd.Parameters.AddWithValue("@studentNum", student.Number); cmd.Parameters.AddWithValue("@courseID", offering.CourseOffered.Number); cmd.Parameters.AddWithValue("@year", offering.Year); cmd.Parameters.AddWithValue("@semester", offering.Semester); // Perform the INSERT operation cmd.ExecuteNonQuery(); } }
public ActionResult Add(CourseOfferingViewModel input) { // Populate VM var viewModel = PopulateViewModel(input); // Validate model state if (!ModelState.IsValid) return View(viewModel); // Attempt to add the offering try { var offering = new CourseOffering(_courseService.GetCourseById(input.SelectedCourseId), input.Semester.ToString(), input.SelectedYear); _courseService.AddCourseOffering(offering); input.CourseOfferings.Add(offering); } catch (CourseOfferingExistsException) { // If the offering already exists, display error TempData["Error"] = "Course offering already exists!"; } return RedirectToAction("Add", input); }
public List<Student> GetAllStudentsByOffering(CourseOffering offering) => _studentRepository.GetStudentsByOffering(offering);
public int CompareTo(CourseOffering other) => other == null ? 1 : Year.CompareTo(other.Year);
public List<Student> GetStudentsByOffering(CourseOffering offering) { var students = new List<Student>(); using (var conn = new SqlConnection(_connectionString)) using (var cmd = conn.CreateCommand()) { // Open a connection conn.Open(); // Build SQL query cmd.CommandText = "SELECT s.StudentNum, s.Name, s.Type FROM Student s " + "JOIN Registration r ON s.StudentNum = r.Student_StudentNum " + "WHERE r.CourseOffering_Course_CourseID=@courseID " + " AND r.CourseOffering_Year = @year " + " AND r.CourseOffering_Semester = @Semester"; cmd.Parameters.AddWithValue("@courseID", offering.CourseOffered.Number); cmd.Parameters.AddWithValue("@year", offering.Year); cmd.Parameters.AddWithValue("@Semester", offering.Semester); // Create DataReader for storing the returning table into memory var dataReader = cmd.ExecuteReader(); // If records exist if (dataReader.HasRows) { // Iterate through each record while (dataReader.Read()) { // Extract the course fields var number = Convert.ToInt16(dataReader["StudentNum"]); var name = dataReader["Name"].ToString(); var type = dataReader["Type"].ToString(); // Get the created student from the factory var studentFactory = new StudentFactory(); var student = studentFactory.CreateStudent(number, name, type); // Append to the course list students.Add(student); } } // Close the DataReader dataReader.Close(); // Execute the SELECT operation cmd.ExecuteNonQuery(); } return students; }
public CourseOffering GetCourseOffering(string id, int year, string semester) { CourseOffering offering = null; using (var conn = new SqlConnection(_connectionString)) using (var cmd = conn.CreateCommand()) { // Open a connection conn.Open(); // Build SQL query cmd.CommandText = "SELECT * from CourseOffering INNER JOIN Course ON CourseOffering.Course_CourseID = Course.CourseID WHERE Course_CourseID = @courseID AND Year = @year AND Semester = @semester"; // Set the course ID cmd.Parameters.AddWithValue("@courseID", id); cmd.Parameters.AddWithValue("@year", year); cmd.Parameters.AddWithValue("@semester", semester); // Create DataReader for storing the returning table into memory var dataReader = cmd.ExecuteReader(); // Check if the CourseOffering table has records if (dataReader.HasRows) { // Iterate through each record while (dataReader.Read()) { // Extract the course fields var number = dataReader["CourseID"].ToString(); var name = dataReader["CourseTitle"].ToString(); var weeklyHours = Convert.ToInt16(dataReader["HoursPerWeek"]); // Build the course object var course = new Course(number, name, weeklyHours); // Build the course offering object offering = new CourseOffering(course, semester, year); } } // Close the DataReader dataReader.Close(); // Execute the SELECT operation cmd.ExecuteNonQuery(); } return offering; }
public bool CourseOfferingExists(CourseOffering offering) { var exists = false; using (var conn = new SqlConnection(_connectionString)) using (var cmd = conn.CreateCommand()) { // Open a connection conn.Open(); // Build SQL query cmd.CommandText = "SELECT * from CourseOffering WHERE Course_CourseID = @courseID AND Semester = @semester AND Year = @year"; // Set the course ID cmd.Parameters.AddWithValue("@courseID", offering.CourseOffered.Number); cmd.Parameters.AddWithValue("@semester", offering.Semester); cmd.Parameters.AddWithValue("@year", offering.Year); // Create DataReader for storing the returning table into memory var dataReader = cmd.ExecuteReader(); exists = dataReader.HasRows; // Close the DataReader dataReader.Close(); // Execute the SELECT operation cmd.ExecuteNonQuery(); } return exists; }
/// <summary> /// Check if a Course Offering exists /// </summary> /// <param name="courseOffering">The course offering.</param> /// <returns></returns> public bool HasCourseOffering(CourseOffering courseOffering) => _db.CourseOfferings.Any( offering => offering.Semester == courseOffering.Semester && offering.Year == courseOffering.Year && offering.CourseId == courseOffering.CourseId);