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