public Models.Enrollment EnrollStudent(StudentEnrollmentRequest request) { using var connection = new SqlConnection(_databaseString); connection.Open(); var transaction = connection.BeginTransaction(); var studiesId = getStudiesId(request.Studies, connection, transaction); if (studiesId == -1) { return(null); } var enrollmentId = getEnrollmentId(studiesId, connection, transaction); if (isIndexNumberOccupied(connection, transaction, request.IndexNumber)) { return(null); } insertStudent(connection, transaction, request, enrollmentId); var enrollment = new Models.Enrollment(); enrollment.Semester = 1; enrollment.Studies = request.Studies; return(enrollment); }
public async Task <IActionResult> Enrollment(StudentEnrollmentRequest request) { var enrollment = await _db.EnrollStudentWithExistingStudy(request); try { await _db.SaveAll(); return(Ok(enrollment)); } catch (DbServiceExceptionHandler e) { if (e.Type == ExceptionHandlerEnumType.NotFound) { return(NotFound(e.Message)); } else if (e.Type == ExceptionHandlerEnumType.NotUnique) { return(BadRequest(e.Message)); } } catch (Exception e) { return(StatusCode(StatusCodes.Status500InternalServerError, e.Message)); } return(BadRequest("Noting")); }
public IActionResult PostEnrollStudent([FromBody] StudentEnrollmentRequest s) { Enrollment e = _dbService.PostEnrollStudent(s); if (e == null) { return(BadRequest("X")); } return(Created("", e)); }
public ActionResult EnrollStudent([FromBody] StudentEnrollmentRequest request) { if (!isRequestValid(request)) { return(BadRequest("Request is wrong!")); } var enrollment = _service.EnrollStudent(request); if (enrollment == null) { return(BadRequest("Wrong input data!")); } return(Created("localhost", enrollment)); }
public async Task <Enrollment> EnrollStudentWithExistingStudy(StudentEnrollmentRequest request) { var ExistedStudy = await _studentContext.Studies .Where(opt => opt.Name == request.Name) .FirstOrDefaultAsync(); if (ExistedStudy == null) { throw new DbServiceExceptionHandler(ExceptionHandlerEnumType.NotFound, "Study does not exist!"); } var latestEnrollment = await _studentContext.Enrollment.Include(opt => opt.IdStudyNavigation) .Where(opt => opt.Semester == 1 && opt.IdStudyNavigation.Name == request.Name) .OrderByDescending(opt => opt.IdEnrollment) .FirstAsync(); if (latestEnrollment == null) { latestEnrollment = new Enrollment { IdEnrollment = _studentContext.Enrollment.Count() + 1, Semester = 1, IdStudy = ExistedStudy.IdStudy, StartDate = DateTime.Today, } } ; var studentFound = await _studentContext.Student .AnyAsync(opt => opt.IndexNumber == request.IndexNumber); if (studentFound) { throw new DbServiceExceptionHandler(ExceptionHandlerEnumType.NotUnique, "Student already exist"); } var newStudent = new Student { IndexNumber = request.IndexNumber, FirstName = request.FirstName, LastName = request.LastName, BirthDate = request.BirthDate, IdEnrollment = latestEnrollment.IdEnrollment }; AddStudent(newStudent); return(latestEnrollment); }
public IActionResult EnrollStudent(StudentEnrollmentRequest request) { // Końcówka powinna najpierw sprawdzić czy przekazane zostały wszystkie dane. // W przeciwnym razie zwracamy błąd 400 if (!ModelState.IsValid) { return(BadRequest()); } try { var studentEnrollment = _studentsDbService.EnrollStudent(request); return(Created("", studentEnrollment)); } catch (ArgumentException exception) { return(BadRequest(exception.Message)); } }
private bool isRequestValid(StudentEnrollmentRequest request) { if (request.IndexNumber == null) { return(false); } if (request.FirstName == null) { return(false); } if (request.LastName == null) { return(false); } if (request.BirthDate == null) { return(false); } if (request.Studies == null) { return(false); } return(true); }
private void insertStudent(SqlConnection connection, SqlTransaction transaction, StudentEnrollmentRequest request, int idEnrollment) { using (var command = new SqlCommand()) { bindCommand(connection, command, transaction); command.CommandText = "INSERT INTO Student (IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) " + "VALUES ( @IndexNumber , @FirstName , @LastName , @BirthDate , @IdEnrollment )"; command.Parameters.AddWithValue("IndexNumber", request.IndexNumber); command.Parameters.AddWithValue("FirstName", request.FirstName); command.Parameters.AddWithValue("LastName", request.LastName); command.Parameters.AddWithValue("BirthDate", request.BirthDate); command.Parameters.AddWithValue("IdEnrollment", idEnrollment); command.ExecuteNonQuery(); transaction.Commit(); } }
public Enrollment PostEnrollStudent([FromBody] StudentEnrollmentRequest s) { Enrollment res = null; // later if still null in controller response will be 400 using (var con = new SqlConnection(ConString)) using (var com = new SqlCommand()) { com.Connection = con; con.Open(); var tran = con.BeginTransaction(); com.Transaction = tran; try // try for all possible errors { // studies com.Parameters.AddWithValue("StudiesName", s.StudiesName); com.CommandText = "SELECT IdStudy FROM Studies WHERE Name=@StudiesName"; var dr = com.ExecuteReader(); dr.Read(); int IdStudy = Convert.ToInt32(dr["IdStudy"]); dr.Close(); com.Parameters.AddWithValue("IdStudy", IdStudy); // enrollment (newest enroll for semester = 1 and IdStudy = @IdStudy) com.CommandText = "SELECT IdEnrollment, StartDate FROM Enrollment" + " WHERE IdStudy = @IdStudy AND Semester = 1" + " AND StartDate = (SELECT MAX(StartDate) FROM Enrollment WHERE IdStudy = @IdStudy AND Semester = 1);"; dr = com.ExecuteReader(); dr.Read(); var IdEnrollment = dr["IdEnrollment"]; // maybe not exists dr.Close(); int EId = 0; // IdEnrollment for insert if (IdEnrollment == null) { // new Enrollment string StartDate = (DateTime.Now).ToString("YYYY-mm-DD"); com.Parameters.AddWithValue("@StartDate", StartDate); com.CommandText = "INSERT INTO Enrollment VALUES((SELECT MAX(IdEnrollment) + 1 FROM Enrollment), 1, @IdStudy, @StartDate);"; com.ExecuteNonQuery(); com.CommandText = "SELECT MAX(IdEnrollment) FROM Enrollment"; dr = com.ExecuteReader(); dr.Read(); EId = Convert.ToInt32(dr["IdEnrollment"]); dr.Close(); } else { EId = Convert.ToInt32(IdEnrollment); } com.Parameters.AddWithValue("IdEnrollment", EId); // student com.Parameters.AddWithValue("Index", s.IndexNumber); com.Parameters.AddWithValue("FName", s.FirstName); com.Parameters.AddWithValue("LName", s.LastName); com.Parameters.AddWithValue("BDate", DateTime.Parse(s.BirthDate.ToString())); com.CommandText = "INSERT INTO Student VALUES(@Index, @Fname, @LName, @BDate, @IdEnrollment);"; com.ExecuteNonQuery(); tran.Commit(); tran.Dispose(); // no exception thrown so ok 201 res = new Enrollment { Semester = 1, Studies = s.StudiesName }; } catch (SqlException) { tran.Rollback(); } return(res); } }
public Enrollment PostEnrollStudent([FromBody] StudentEnrollmentRequest s) { var db = new s18915Context(); // StudentEnrollRequest has fields [Required] - so check for fields done // check if studies exists if (!db.Studies.Any(x => x.Name == s.StudiesName)) { return(null); } // check if student's index is unique if (db.Student.Any(x => x.IndexNumber == s.IndexNumber)) { return(null); } var studies = db.Studies.FirstOrDefault(x => x.Name == s.StudiesName); var enrollment = db.Enrollment.FirstOrDefault(x => x.Semester == 1 && x.IdStudy == studies.IdStudy); var id = 0; // enrollment does not exist if (enrollment == null) { // get the latest one var maxId = db.Enrollment .OrderByDescending(x => x.IdEnrollment) .FirstOrDefault().IdEnrollment; id = maxId + 1; db.Enrollment.Add(new Enrollment { IdEnrollment = id, Semester = 1, IdStudy = studies.IdStudy, StartDate = DateTime.Now }); } if (enrollment != null) { id = enrollment.IdEnrollment; } db.Student.Add(new Student { IndexNumber = s.IndexNumber, FirstName = s.FirstName, LastName = s.LastName, BirthDate = s.BirthDate, IdEnrollment = id }); db.SaveChanges(); return(new Enrollment { IdEnrollment = id, Semester = 1, IdStudy = studies.IdStudy, StartDate = enrollment == null ? DateTime.Now : enrollment.StartDate }); }
public StudentEnrollment EnrollStudent(StudentEnrollmentRequest request) { using (var connection = new SqlConnection("Data Source=db-mssql;Initial Catalog=s17179;Integrated Security=True")) using (var command = new SqlCommand()) { command.Connection = connection; connection.Open(); // Wszystkie opisane czynności powinni się odbyć w ramach pojedynczej transakcja var transaction = connection.BeginTransaction(); command.Transaction = transaction; try { var now = DateTime.Now; var studiesName = request.Studies; // Następnie sprawdzamy czy istnieją studia w tabeli Studies zgodne z wartością przesłaną przez klienta. // W przeciwnym wypadku zwracamy błąd 400 command.CommandText = "SELECT IdStudy FROM Studies WHERE Name = @name"; command.Parameters.AddWithValue("name", studiesName); var reader = command.ExecuteReader(); if (!reader.Read()) { throw new ArgumentException("Studies " + studiesName + " not found"); } int idStudy = (int)reader["IdStudy"]; reader.Close(); // Następnie odnajdujemy najnowszy wpis w tabeli Enrollments zgodny ze studiami studenta i wartością Semester=1 command.CommandText = "SELECT TOP 1 IdEnrollment FROM Enrollment WHERE IdStudy = @idStudy AND Semester = 1 ORDER BY StartDate DESC"; command.Parameters.Clear(); command.Parameters.AddWithValue("idStudy", idStudy); reader = command.ExecuteReader(); // Jeśli tak wpis nie istnieje to dodajemy go do bazy danych (StartDate ustawiamy na aktualną datę) int idEnrollment; if (!reader.Read()) { reader.Close(); idEnrollment = 1; command.CommandText = "SELECT TOP 1 IdEnrollment FROM Enrollment ORDER BY IdEnrollment DESC"; reader = command.ExecuteReader(); if (reader.Read()) { idEnrollment = (int)reader["IdEnrollment"] + 1; } reader.Close(); command.CommandText = "INSERT INTO Enrollment (IdEnrollment, Semester, IdStudy, StartDate) VALUES (@newIdEnrollment, @semester, @idStudy, @startDate)"; command.Parameters.Clear(); command.Parameters.AddWithValue("newIdEnrollment", idEnrollment); command.Parameters.AddWithValue("semester", 1); command.Parameters.AddWithValue("idStudy", idStudy); command.Parameters.AddWithValue("startDate", now); command.ExecuteNonQuery(); } else { idEnrollment = (int)reader["IdEnrollment"]; reader.Close(); } // Na końcu dodajemy wpis w tabeli Students // Pamiętamy o tym, aby sprawdzić czy indeks podany przez studenta jest unikalny. // W przeciwnym wypadku zgłaszamy błąd command.CommandText = "SELECT 1 FROM Student WHERE IndexNumber = @indexNumber"; command.Parameters.Clear(); command.Parameters.AddWithValue("indexNumber", request.IndexNumber); reader = command.ExecuteReader(); if (reader.HasRows) { reader.Close(); transaction.Rollback(); throw new ArgumentException($"Student with index {request.IndexNumber} already exists"); } reader.Close(); command.CommandText = "INSERT INTO Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES (@indexNumber, @firstName, @lastName, @birthDate, @idEnrollment)"; command.Parameters.Clear(); command.Parameters.AddWithValue("indexNumber", request.IndexNumber); command.Parameters.AddWithValue("firstName", request.FirstName); command.Parameters.AddWithValue("lastName", request.LastName); command.Parameters.AddWithValue("birthDate", request.BirthDate); command.Parameters.AddWithValue("idEnrollment", idEnrollment); command.ExecuteNonQuery(); transaction.Commit(); var studentEnrollment = new StudentEnrollment(); studentEnrollment.IdEnrollment = idEnrollment; studentEnrollment.Semester = 1; studentEnrollment.StartDate = now; studentEnrollment.Study = new Study { IdStudy = idStudy, Name = studiesName }; // Jeśli student został poprawnie zapisany na semestr to zwracamy kod 201. // W ciele żądania zwracamy przypisany do studenta obiekt Enrollment reprezentujący semestr na który został wpisany return(studentEnrollment); } catch (SqlException exception) { // Jeśli zaszedł jakikolwiek błąd chcemy wycofać (rollback) wszystkie zmiany transaction.Rollback(); throw new ArgumentException(exception.Message); } } }