/* public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request) * { * var idStudy = -1; * var nextIdEnrollment = -1; * * var startDate = DateTime.Now; * var semester = 1; * * using (var connection = new SqlConnection(sqlConnecionStr)) * using (var command = new SqlCommand()) * { * command.Connection = connection; * connection.Open(); * var transaction = connection.BeginTransaction(); * command.Transaction = transaction; * * try * { * //1. Check wether studies with given name exist * command.CommandText = "SELECT IdStudy from Studies where Name = @name" + ";"; * command.Parameters.AddWithValue("@name", request.Studies); * var dr = command.ExecuteReader(); * if (!dr.Read()) * { * dr.Close(); * transaction.Rollback(); * throw new Exception("Studia o podanej nazwie nie istnieją!"); * } * idStudy = (int)dr["IdStudy"]; * dr.Close(); * * //2. Check wether indexNumber is unique (it's a PK in Student) * command.CommandText = "SELECT IndexNumber from Student where IndexNumber = @number" + ";"; * command.Parameters.AddWithValue("@number", request.IndexNumber); * var dr2 = command.ExecuteReader(); * if (dr2.Read()) * { * dr2.Close(); * transaction.Rollback(); * throw new Exception("Student o podanym nr indeksu juz istnieje!"); * } * dr2.Close(); * * * //3. Check latest IdEnrollment * command.CommandText = "SELECT Max(IdEnrollment) as max from Enrollment;"; * var dr3 = command.ExecuteReader(); * if (!dr3.Read()) * { * dr3.Close(); * transaction.Rollback(); * throw new Exception("Problem with fetching id enrollemnt"); * } * var maxIdEnrollment = (int)dr3["max"]; * nextIdEnrollment = ++maxIdEnrollment; * dr3.Close(); * * * //4. Create new Enrollment for student * command.CommandText = "INSERT INTO Enrollment (IdEnrollment, Semester, IdStudy, StartDate) " + "VALUES(@nextId, @semester, @idStudy, CAST(@date AS DATETIME));"; + command.Parameters.AddWithValue("@nextId", nextIdEnrollment); + command.Parameters.AddWithValue("@semester", semester); + command.Parameters.AddWithValue("@idStudy", idStudy); + command.Parameters.AddWithValue("@date", startDate); + + command.ExecuteNonQuery(); + + //5. Add student to Studyies + command.CommandText = "INSERT INTO Student (IndexNumber, FirstName, LastName, BirthDate, IdEnrollment) " + "VALUES(@indexNumber, @studentFirstName, @studnetLastName, CAST(@bdate AS DATE), @idEnrollment)"; + command.Parameters.AddWithValue("@indexNumber", request.IndexNumber); + command.Parameters.AddWithValue("@studentFirstName", request.FirstName); + command.Parameters.AddWithValue("@studnetLastName", request.LastName); + command.Parameters.AddWithValue("@bdate", request.BirthDate); + command.Parameters.AddWithValue("@idEnrollment", nextIdEnrollment); + + command.ExecuteNonQuery(); + + transaction.Commit(); + } + catch (SqlException exc) + { + transaction.Rollback(); + Console.WriteLine(exc.Message); + throw new Exception("SqlException " + exc.Message); + } + } + + var response = new EnrollStudentResponse(); + response.IdEnrollment = nextIdEnrollment; + response.Semester = semester; + response.IdStudy = idStudy; + response.StartDate = startDate; + + return response; + }*/ public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request) { var idStudy = -1; var semester = 1; var startDate = DateTime.Now; //1. Check wether studies with given name exist var studiesWithNameRetrived = _dbcontext.Studies.Where(s => s.Name.Equals(request.Studies)).FirstOrDefault(); if (studiesWithNameRetrived == null) { throw new Exception("Studia o podanej nazwie nie istnieją!"); } idStudy = studiesWithNameRetrived.IdStudy; //2. Check wether indexNumber is unique (it's a PK in Student) var studentWithNewIndexNumber = _dbcontext.Student.Where(s => s.IndexNumber.Equals(request.IndexNumber)).FirstOrDefault(); if (studentWithNewIndexNumber != null) { throw new Exception("Student o podanym nr indeksu juz istnieje!"); } //3. Check latest IdEnrollment var maxIdEnrollment = _dbcontext.Enrollment.Max(e => e.IdEnrollment); var nextIdEnrollment = ++maxIdEnrollment; //4. Create new Enrollment for student var newEnrollemnt = new Enrollment(); newEnrollemnt.IdEnrollment = nextIdEnrollment; newEnrollemnt.Semester = 1; newEnrollemnt.IdStudy = idStudy; newEnrollemnt.StartDate = startDate; _dbcontext.Add(newEnrollemnt); _dbcontext.SaveChanges(); //5. Add student to Studyies var newStudent = new Student(); newStudent.IndexNumber = request.IndexNumber; newStudent.FirstName = request.FirstName; newStudent.LastName = request.LastName; newStudent.BirthDate = request.BirthDate; newStudent.IdEnrollment = nextIdEnrollment; _dbcontext.Add(newStudent); _dbcontext.SaveChanges(); var response = new EnrollStudentResponse(); response.IdEnrollment = nextIdEnrollment; response.Semester = semester; response.IdStudy = idStudy; response.StartDate = startDate; return(response); }
public Student UpdateStudent(Student student) { if (String.IsNullOrWhiteSpace(student.IndexNumber)) { throw new InvalidArgumentException("Nie podano numeru indexu studenta do zmiany"); } var db = new apbdContext(); db.Attach(student); if (student.FirstName != null && student.FirstName.Trim().Length > 0) { db.Entry(student).Property("FirstName").IsModified = true; } if (student.LastName != null && student.LastName.Trim().Length > 0) { db.Entry(student).Property("LastName").IsModified = true; } if (student.BirthDate != null) { db.Entry(student).Property("BirthDate").IsModified = true; } db.SaveChanges(); return(GetStudent(student.IndexNumber)); }
Student IDbService.CreateStudent([FromBody] EnrollStudentRequest request) { var idEnroll = _studentContext.Enrollment.Include(e => e.IdStudyNavigation) .Where(study => study.IdStudyNavigation.Name == request.Studies && study.Semester == 1) .Select(id => id.IdEnrolment).FirstOrDefault(); var student = new Student { IndexNumber = request.IndexNumber, FirstName = request.FirstName, LastName = request.LastName, BirthDate = request.BirthDate, IdEnrollment = idEnroll }; _studentContext.Student.Add(student); _studentContext.SaveChanges(); return(student); }
public Enrollment EnrollStudentToStudies(StudentEnrollment enrollments) { if (!IsValidEnrollments(enrollments)) { throw new InvalidArgumentException("Brakujące dane wejściowe"); } var db = new apbdContext(); var salt = PasswordService.CreateSalt(); var s = new Student { FirstName = enrollments.FirstName, LastName = enrollments.LastName, BirthDate = enrollments.BirthDate, IndexNumber = enrollments.IndexNumber, Salt = salt, Password = PasswordService.Create(enrollments.Password, salt) }; var st = db.Studies.Where(s => s.Name == enrollments.Studies).Single(); Enrollment en; en = db.Enrollment.Where(en => en.IdStudy == st.IdStudy && en.Semester == 1).Single(); if (en == null) { var newEnrollment = new Enrollment { Semester = 1, IdStudy = st.IdStudy, StartDate = new DateTime() }; db.Enrollment.Add(newEnrollment); db.SaveChanges(); en = newEnrollment; } s.IdEnrollmentNavigation = en; db.Student.Add(s); db.SaveChanges(); return(en); }
public void DeleteStudent(string index) { var db = new apbdContext(); var s = new Student { IndexNumber = index }; db.Attach(s); db.Remove(s); db.SaveChanges(); }
public Student UpdateStudent(StudentDto studentDto, string id) { var existingStudent = (Student)_dbcontext.Student.FirstOrDefault <Student>(); if (studentDto.FirstName != null && studentDto.FirstName.Equals("")) { existingStudent.FirstName = studentDto.FirstName; } if (studentDto.LastName != null && studentDto.LastName.Equals("")) { existingStudent.LastName = studentDto.LastName; } if (studentDto.BirthDate != null && studentDto.BirthDate.Equals("")) { existingStudent.BirthDate = studentDto.BirthDate; } if (studentDto.IdEnrollment.Equals("")) { existingStudent.IdEnrollment = studentDto.IdEnrollment; } _dbcontext.SaveChanges(); return(existingStudent); }