public IActionResult EnrollStudent(PostStudentRequest student) { var response = _service.PostStudent(student); switch (response) { case "Not Found": return(NotFound()); case "BadRequest": return(BadRequest()); default: return(Ok(response)); } }
public string PostStudent(PostStudentRequest student) { Enrollment enrollment = new Enrollment(); using (var connection = new SqlConnection(_connStr)) using (var query = new SqlCommand()) { query.Connection = connection; connection.Open(); var transaction = connection.BeginTransaction(); try { query.CommandText = "SELECT IdStudy FROM Studies where Name=@name"; query.Parameters.AddWithValue("name", student.Studies); query.Transaction = transaction; var studs = query.ExecuteReader(); if (!studs.Read()) { return("Not Found"); } int idstudies = (int)studs["IdStudy"]; studs.Close(); query.CommandText = "SELECT IdEnrollment FROM Enrollment WHERE IdEnrollment >= (SELECT MAX(IdEnrollment) FROM Enrollment)"; var idenr = query.ExecuteReader(); int idEnroll = (int)idenr["IdEnrollment"] + 10; idenr.Close(); query.CommandText = "SELECT idEnrollment, StartDate from Enrollment WHERE idStudy=@idStudy AND Semester=1 ORDER BY StartDate"; query.Parameters.AddWithValue("idStudy", idstudies); DateTime enrollDate; var enrol = query.ExecuteReader(); if (!enrol.Read()) { enrollDate = DateTime.Now; query.CommandText = "INSERT INTO Enrollment VALUES(@id, @Semester, @IdStud, @StartDate)"; query.Parameters.AddWithValue("id", idEnroll); query.Parameters.AddWithValue("Semester", 1); query.Parameters.AddWithValue("IdStud", idstudies); query.Parameters.AddWithValue("StartDate", enrollDate); enrol.Close(); query.ExecuteNonQuery(); } else { idEnroll = (int)enrol["IdEnrollment"]; enrollDate = (DateTime)enrol["StartDate"]; enrol.Close(); } enrollment.IdEnrollment = idEnroll; enrollment.Semester = 1; enrollment.IdStudy = idstudies; enrollment.StartDate = enrollDate; query.CommandText = "SELECT IndexNumber FROM Student WHERE IndexNumber=@indexNum"; query.Parameters.AddWithValue("indexNum", student.IndexNumber); DateTime bDate = Convert.ToDateTime(student.BirthDate); string formattedDate = bDate.ToString("yyyy-MM-dd HH:mm:ss.fff"); try { query.CommandText = "INSERT INTO Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) VALUES (@index, @fName, @lName, @birthDate, @idEnrollment)"; query.Parameters.AddWithValue("index", student.IndexNumber); query.Parameters.AddWithValue("fName", student.FirstName); query.Parameters.AddWithValue("lName", student.LastName); query.Parameters.AddWithValue("birthDate", formattedDate); query.Parameters.AddWithValue("idEnrollment", idEnroll); query.ExecuteNonQuery(); transaction.Commit(); } catch (SqlException ex) { transaction.Rollback(); return("BadRequest"); } } catch (SqlException exc) { transaction.Rollback(); return("BadRequest"); } return(enrollment.ToString()); } }