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