public IActionResult EnrollStudent(StudentEnrollmentReq request)
{
var response = _dbService.EnrollStudent(request);
if (response.Error != null)
{
return(BadRequest(response.Error));
}
return(Ok(response));
}
public StudentEnrollmentRes EnrollStudent(StudentEnrollmentReq request)
{
using SqlConnection connection = new SqlConnection(ConnectionString);
using SqlCommand command = new SqlCommand
{
Connection = connection,
CommandText = "SELECT * FROM Studies WHERE Name=@Name"
};
command.Parameters.AddWithValue("Name", request.Studies);
connection.Open();
var transaction = connection.BeginTransaction();
command.Transaction = transaction;
int idEnrollment;
try
{
var dr = command.ExecuteReader();
// Check if the Study exists
if (!dr.Read())
{
dr.Close();
transaction.Rollback();
return(new StudentEnrollmentRes
{
Error = "Study does not exist"
});
}
int idStudy = (int)dr["iDstudy"];
command.CommandText = "Select * from Student where IndexNumber=@IndexNumber";
command.Parameters.AddWithValue("IndexNumber", request.IndexNumber);
dr.Close();
dr = command.ExecuteReader();
// Check student with given index exists
if (dr.Read())
{
dr.Close();
transaction.Rollback();
return(new StudentEnrollmentRes
{
Error = "Student with given index exists"
});
}
command.CommandText = "SELECT * FROM Enrollment WHERE Semester=1 AND IdStudy=@IdStudy";
command.Parameters.AddWithValue("IdStudy", idStudy);
dr.Close();
dr = command.ExecuteReader();
if (dr.Read())
{
idEnrollment = (int)dr["IdEnrollment"];
dr.Close();
}
// If there is no Enrollment with a given Study add new Enrollment
else
{
command.CommandText = "SELECT MAX(idEnrollment) as IdEnrollment FROM Enrollment";
dr.Close();
dr = command.ExecuteReader();
var nextIdEnrollment = 1;
if (dr.Read())
{
nextIdEnrollment = (int)dr["IdEnrollment"] + 1;
}
dr.Close();
var startDate = DateTime.Now;
command.CommandText = "INSERT INTO Enrollment(idEnrollment, idStudy, semester, startDate) " +
"values(@NextIdEnrollment, @IdStudy, 1, @StartDate)";
command.Parameters.AddWithValue("NextIdEnrollment", nextIdEnrollment);
command.Parameters.AddWithValue("StartDate", startDate);
command.ExecuteNonQuery();
idEnrollment = nextIdEnrollment;
}
command.CommandText = "INSERT INTO Student(IndexNumber, firstname, lastname, birthdate, idenrollment) " +
"values(@IndexNumber, @FirstName, @LastName, @BirthDate, @IdEnrollment)";
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();
}
catch (SqlException)
{
transaction.Rollback();
return(new StudentEnrollmentRes
{
Error = "SQLException"
});
}
return(new StudentEnrollmentRes
{
IdEnrollment = idEnrollment,
Semester = 1
});
}