public IActionResult EnrollStudent(EnrollStudentsRequest request) { // Check if data was delivered corectly if (!ModelState.IsValid) { return(BadRequest("Data not delivered")); } var enrollment = _service.EnrollStudent(request); if (enrollment != null) { return(Ok(enrollment)); } else { return(BadRequest("bad request")); } }
public EnrollStudentResponse EnrollStudent(EnrollStudentsRequest request) { using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=2019SBD;Integrated Security=True")) { using (var com = new SqlCommand()) { con.Open(); com.Connection = con; var tran = con.BeginTransaction(); com.Transaction = tran; // Check if studies exists com.CommandText = "select * FROM Studies WHERE Name=@Name"; com.Parameters.AddWithValue("Name", request.Studies); var reader = com.ExecuteReader(); if (!reader.Read()) { return(null); } int idStudies = (int)reader["IdStudy"]; reader.Close(); // check if enrollment with semster 1 exists com.CommandText = "select * FROM Enrollment WHERE Semester=1 AND IdStudy=@IdStudy"; com.Parameters.AddWithValue("IdStudy", idStudies); var idEnrollment = 0; var reader2 = com.ExecuteReader(); if (!reader2.Read()) { // add new enrollment with semster 1 com.CommandText = "SELECT * FROM Enrollment WHERE IdEnrollment = (SELECT MAX(IdEnrollment) FROM Enrollment)"; reader2.Close(); var reader3 = com.ExecuteReader(); reader3.Read(); idEnrollment = reader3.GetInt32(0); reader3.Close(); com.CommandText = "INSERT INTO Enrollment (IdEnrollment, Semester, IdStudy, StartDate) VALUES(@IdEnrollment, @Semester, @ids, @StartDate);"; com.Parameters.AddWithValue("IdEnrollment", idEnrollment + 1); com.Parameters.AddWithValue("Semester", 1); com.Parameters.AddWithValue("ids", idStudies); com.Parameters.AddWithValue("StartDate", DateTime.Now.ToString()); var reader4 = com.ExecuteReader(); reader4.Close(); } else { idEnrollment = (int)reader2["IdEnrollment"]; reader2.Close(); } // check if student with index number exists com.CommandText = "select * FROM Student WHERE IndexNumber=@IndexNumber"; com.Parameters.AddWithValue("IndexNumber", request.IndexNumber); var reader5 = com.ExecuteReader(); if (!reader5.Read()) { reader5.Close(); // create new student com.CommandText = "INSERT INTO Student(IndexNumber, FirstName, LastName, Birthdate, IdEnrollment) VALUES (@IndexNumber2, @FirstName2, @LastName2, @Birthdate2, @IdEnrollment2)"; com.Parameters.AddWithValue("IndexNumber2", request.IndexNumber); com.Parameters.AddWithValue("FirstName2", request.FirstName); com.Parameters.AddWithValue("LastName2", request.LastName); com.Parameters.AddWithValue("Birthdate2", request.DateOfBirth.ToString()); com.Parameters.AddWithValue("IdEnrollment2", idEnrollment); var reader6 = com.ExecuteReader(); reader6.Close(); } else { reader5.Close(); } tran.Commit(); } } var response = new EnrollStudentResponse { LastName = request.LastName, Semester = 1 }; return(response); }