public IActionResult AddStudent(Models.EnrollStudClass enroll)
{
return(_dbService.AddStudent(enroll));
}
public IActionResult AddStudent(Models.EnrollStudClass enroll)
{
var st = new Models.Student();
st.IndexNumber = enroll.IndexNumber;
st.FirstName = enroll.FirstName;
st.LastName = enroll.LastName;
st.BirthDate = DateTime.Parse(enroll.BirthDate);
using (var client = new SqlConnection(MyDatabase))
using (var command = new SqlCommand())
{
command.Connection = client;
var enrollment = new Models.Enrollment();
client.Open();
SqlTransaction tran = client.BeginTransaction();
try
{
command.CommandText = "SELECT Name FROM Studies where Name = @stud";
command.Parameters.AddWithValue("stud", enroll.Studies);
command.Parameters.AddWithValue("Fname", enroll.FirstName);
command.Parameters.AddWithValue("Lname", enroll.LastName);
command.Parameters.AddWithValue("Bdate", enroll.BirthDate);
command.Parameters.AddWithValue("id", enroll.IndexNumber);
command.Transaction = tran;
SqlDataReader qr = command.ExecuteReader();
while (!qr.Read())
{
tran.Rollback();
return(NotFound(400 + " - Nie ma takich studiów"));
}
qr.Close();
command.CommandText = "SELECT * FROM Enrollment inner join studies on Studies.IdStudy = Enrollment.IdStudy where Studies.Name = @stud and semester = 1";
qr = command.ExecuteReader();
while (!qr.HasRows)
{
qr.Close();
command.CommandText = "INSERT into enrollment(idEnrollment,semester,idstudy,startdate)values((select Max(idEnrollment)+1 from enrollment),1,(select idstudy from studies where name=@stud),GETDATE())";
command.ExecuteNonQuery();
tran.Commit();
}
qr.Close();
command.CommandText = "select * from student where IndexNumber=@id";
qr = command.ExecuteReader();
if (!qr.HasRows)
{
qr.Close();
command.CommandText = "insert into Student(IndexNumber,FirstName,LastName,BirthDate,IdEnrollment)values(@id,@Fname,@Lname, @Bdate,(select idEnrollment from enrollment inner join studies on enrollment.IdStudy=studies.IdStudy where studies.Name=@stud and semester=1))";
command.ExecuteNonQuery();
}
else
{
tran.Rollback();
return(NotFound("Id nie jest unikalne"));
}
qr.Close();
command.CommandText = "select IdEnrollment,StartDate,Semester from Enrollment inner join Studies on Enrollment.IdStudy=Studies.IdStudy where name=@stud and semester= 1";
qr = command.ExecuteReader();
if (qr.Read())
{
enrollment.idEnrollment = qr["idEnrollment"].ToString();
enrollment.Semester = qr["Semester"].ToString();
enrollment.StartDate = qr["StartDate"].ToString();
}
qr.Close();
tran.Commit();
}
catch (SqlException ex) {
tran.Rollback();
}
return(StatusCode(201, enrollment));
}
}
