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