public IActionResult EnrollStudent(EnrollPromoteStudent request)
{
var st = new Student();
st.Subject = request.Studies;
st.Semester = "1";
st.LastName = request.LastName;
using (var connection = new SqlConnection("Data Source=db-mssql;Initial Catalog=s18705;Integrated Security=True"))
using (var command = new SqlCommand())
{
command.Connection = connection;
connection.Open();
var transaction = connection.BeginTransaction();
try
{
command.CommandText = "select IdStudies from Studies where name=@name";
command.Parameters.AddWithValue("name", request.Studies);
var dr = command.ExecuteReader();
if (!dr.Read())
{
transaction.Rollback();
return(BadRequest("Studia nie istnieja."));
}
int idStudies = (int)dr["IdStudies"];
command.CommandText = "select * from Enrollment " +
"where StartDate = (select MAX(StartDate) from Enrollment where idStudy=" + idStudies + ") " +
"AND Semester=1";
var execread = command.ExecuteReader();
.
if (!execread.Read())
{
command.CommandText = "Insert into Enrollment values (" +
"(SELECT MAX(idEnrollment)+1 from Enrollment),1," + idStudies + ",GetDate()";
}
command.CommandText = "Select * from Student WHERE IndexNumber=@indexNumber";
command.Parameters.AddWithValue("indexNumber", request.IndexNumber);
var studentread = command.ExecuteReader();
if (!studentread.Read())
{
transaction.Rollback();
return(BadRequest("Index jest nieunikalny."));
}
command.CommandText = "Insert into Student values(" +
request.IndexNumber + "," +
request.FirstName + "," +
request.LastName + "," +
request.BirthDate + "," +
idStudies +
")";
command.ExecuteNonQuery();
transaction.Commit();
}
public IActionResult EnrollStudent(EnrollPromoteStudent request)
{
var st = new Student();
st.Subject = request.Studies;
st.Semester = "1";
st.LastName = request.LastName;
using (var connection = new SqlConnection("Data Source=db-mssql;Initial Catalog=s18967;Integrated Security=True"))
using (var command = new SqlCommand())
{
command.Connection = connection;
connection.Open();
var transaction = connection.BeginTransaction();
//1. Czy studia istnieja?
try
{
command.CommandText = "select IdStudies from Studies where name=@name";
command.Parameters.AddWithValue("name", request.Studies);
var dr = command.ExecuteReader();
if (!dr.Read())
{
transaction.Rollback();
return(BadRequest("Studia nie istnieja."));
}
int idStudies = (int)dr["IdStudies"];
//Następnie odnajdujemy najnowszy wpis w tabeli Enrollments
//zgodny ze studiami studenta i wartością Semester = 1(student zapisuje się na pierwszy
//semestr).
command.CommandText = "select * from Enrollment " +
"where StartDate = (select MAX(StartDate) from Enrollment where idStudy=" + idStudies + ") " +
"AND Semester=1";
var execread = command.ExecuteReader();
//Jeśli tak wpis nie istnieje to dodajemy go do bazy danych (StartDate ustawiamy na aktualną datę).
//Na końcu dodajemy wpis w tabeli Students.
if (!execread.Read())
{
command.CommandText = "Insert into Enrollment values (" +
"(SELECT MAX(idEnrollment)+1 from Enrollment),1," + idStudies + ",GetDate()";
}
//Pamiętamy o tym, aby sprawdzić czy indeks podany przez studenta jest unikalny. W przeciwnym
//wypadku zgłaszamy błąd.
command.CommandText = "Select * from Student WHERE IndexNumber=@indexNumber";
command.Parameters.AddWithValue("indexNumber", request.IndexNumber);
var studentread = command.ExecuteReader();
if (!studentread.Read())
{
transaction.Rollback();
return(BadRequest("Index jest nieunikalny."));
}
command.CommandText = "Insert into Student values(" +
request.IndexNumber + "," +
request.FirstName + "," +
request.LastName + "," +
request.BirthDate + "," +
idStudies +
")";
command.ExecuteNonQuery();
transaction.Commit();
}
catch (SqlException ex)
{
transaction.Rollback();
}
}
var response = new EnrollStudentResponse();
response.StudiesName = st.Subject;
response.Semester = int.Parse(st.Semester);
response.LastName = st.LastName;
return(Ok(response + ". Nie wiem jak zwrocic Created"));
}
