// public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request) // { // using (var connection = // new SqlConnection("Data Source=db-mssql;Initial Catalog=s18310;Integrated Security=True")) // using (var command = new SqlCommand()) // { // command.Connection = connection; // command.CommandText = "select * from Studies where Name=@Name"; // command.Parameters.AddWithValue("Name", request.Studies); // connection.Open(); // // var reader = command.ExecuteReader(); // if (!reader.Read()) // { // throw new Exception("No such studies: " + request.Studies); // } // // int idStudy = (int) reader["IdStudy"]; // reader.Close(); // if (GetStudent(request.IndexNumber) != null) // { // throw new Exception("Student " + request.IndexNumber + " already exists"); // } // // command.CommandText = "select * from Enrollment where IdStudy=@idStudy and Semester=@Semester"; // command.Parameters.AddWithValue("idStudy", idStudy); // command.Parameters.AddWithValue("Semester", 1); // reader = command.ExecuteReader(); // int enrollmentId = 0; // DateTime startDate = DateTime.Now; // if (!reader.Read()) // { // reader.Close(); // command.CommandText = "select max(IdEnrollment) as currentMax from Enrollment"; // reader = command.ExecuteReader(); // if (!reader.Read()) // { // enrollmentId = 1; // } // else // { // enrollmentId = 1 + (int) reader["currentMax"]; // } // // reader.Close(); // command.CommandText = "insert into Enrollment(IdEnrollment,Semester,IdStudy,StartDate)" + // " values(@newId,@Semester,@IdStudy,@StartDate)"; // command.Parameters.AddWithValue("newId", enrollmentId); // command.Parameters.AddWithValue("IdStudy", idStudy); // command.Parameters.AddWithValue("Semester", 1); // command.Parameters.AddWithValue("StartDate", startDate); // command.ExecuteNonQuery(); // } // else // { // startDate = (DateTime) reader["StartDate"]; // enrollmentId = (int) reader["IdEnrollment"]; // reader.Close(); // } // // Console.WriteLine(enrollmentId); // 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", enrollmentId); // command.ExecuteNonQuery(); // return new EnrollStudentResponse() // {IdEnrollment = enrollmentId, Semester = 1, IdStudy = idStudy, StartDate = startDate}; // } // } public EnrollStudentResponse EnrollStudent(EnrollStudentRequest request) { var database = new s18310Context(); var studies = database.Studies.First(stud => stud.Name.Equals(request.Studies)); if (studies.Equals(null)) { throw new Exception("No such studies: " + request.Studies); } var student = database.Student.First(stud => stud.IndexNumber == request.IndexNumber); if (!student.Equals(null)) { throw new Exception("Student " + request.IndexNumber + " already exists"); } var enrollment = database.Enrollment.First(enroll => enroll.IdStudy == studies.IdStudy && enroll.Semester == 1); var newStudent = new GeneratedModels.Student { IndexNumber = request.IndexNumber, FirstName = request.FirstName, LastName = request.LastName, BirthDate = request.BirthDate }; var response = new EnrollStudentResponse(); if (enrollment.Equals(null)) { var newEnrollment = new GeneratedModels.Enrollment { IdEnrollment = database.Enrollment.Max(enroll => enroll.IdEnrollment), Semester = 1, IdStudy = studies.IdStudy, StartDate = DateTime.Now, IdStudyNavigation = studies }; database.Enrollment.Add(newEnrollment); newStudent.IdEnrollment = newEnrollment.IdEnrollment; newStudent.IdEnrollmentNavigation = newEnrollment; response.IdEnrollment = newEnrollment.IdEnrollment; response.Semester = newEnrollment.Semester; response.IdStudy = newEnrollment.IdStudy; response.StartDate = newEnrollment.StartDate; } else { newStudent.IdEnrollment = enrollment.IdEnrollment; newStudent.IdEnrollmentNavigation = enrollment; response.IdEnrollment = enrollment.IdEnrollment; response.Semester = enrollment.Semester; response.IdStudy = enrollment.IdStudy; response.StartDate = enrollment.StartDate; } database.Add(newStudent); database.SaveChanges(); return(response); }
public void UpdateStudent(UpdateStudentRequest request) { var database = new s18310Context(); var student = database.Student.First(st => st.IndexNumber == request.IndexNumber); student.FirstName = request.FirstName != null ? request.FirstName : student.FirstName; student.LastName = request.LastName != null ? request.LastName : student.LastName; student.BirthDate = request.BirthDate.Equals(null) ? request.BirthDate : student.BirthDate; student.IdEnrollment = request.Enrollment.Equals(null) ? request.Enrollment : student.IdEnrollment; database.SaveChanges(); }
public void DeleteStudent(string id) { var database = new s18310Context(); var student = new GeneratedModels.Student { IndexNumber = id }; var roles = database.StudentRoles.Where(studentRoles => studentRoles.IndexNumber.Equals(id)).ToList(); foreach (var role in roles) { database.Remove(role); } database.SaveChanges(); database.Remove(student); }
// public EnrollStudentResponse PromoteStudents(PromoteStudentRequest request) // { // using (var connection = // new SqlConnection("Data Source=db-mssql;Initial Catalog=s18310;Integrated Security=True")) // using (var command = new SqlCommand()) // { // command.Connection = connection; // connection.Open(); // command.CommandText = "exec PromoteStudents @Studies,@Semester"; // command.Parameters.AddWithValue("Studies", request.Studies); // command.Parameters.AddWithValue("Semester", request.Semester); // var reader = command.ExecuteReader(); // if (reader.Read()) // { // EnrollStudentResponse response = new EnrollStudentResponse // { // IdEnrollment = (int) reader["IdEnrollment"], // Semester = (int) reader["Semester"], // IdStudy = (int) reader["IdStudy"], // StartDate = DateTime.Parse(reader["StartDate"].ToString()) // }; // return response; // } // } // // return null; // } public EnrollStudentResponse PromoteStudents(PromoteStudentRequest request) { var database = new s18310Context(); var enrollment = database.Enrollment.Include(enroll => enroll.IdStudyNavigation).FirstOrDefault(enroll => enroll.Semester == request.Semester && enroll.IdStudyNavigation.Name.Equals(request.Studies)); if (enrollment == null) { throw new Exception("No such enrollment to promote"); } var sem = new SqlParameter("@semester", request.Semester); var stud = new SqlParameter("@Studies", request.Studies); database.Database.ExecuteSqlRaw("exec PromoteStudents @Studies,@Semester", stud, sem); EnrollStudentResponse response = new EnrollStudentResponse { IdEnrollment = enrollment.IdEnrollment, Semester = enrollment.Semester + 1, IdStudy = enrollment.IdStudy }; return(response); }
// public IEnumerable<Student> GetStudents() // { // List<Student> students = new List<Student>(); // // using (var connection = // new SqlConnection("Data Source=db-mssql;Initial Catalog=s18310;Integrated Security=True")) // using (var command = new SqlCommand()) // { // command.Connection = connection; // command.CommandText = // "select * from Student inner join Enrollment on Student.IdEnrollment=Enrollment.IdEnrollment inner join Studies on Enrollment.IdStudy=Studies.IdStudy"; // connection.Open(); // var reader = command.ExecuteReader(); // while (reader.Read()) // { // var student = new Student(); // student.FirstName = reader["FirstName"].ToString(); // student.LastName = reader["LastName"].ToString(); // student.IndexNumber = reader["IndexNumber"].ToString(); // student.BirthDate = DateTime.Parse(reader["BirthDate"].ToString()); // student.Studies = reader["Name"].ToString(); // student.Semester = int.Parse(reader["Semester"].ToString()); // students.Add(student); // } // } // // return students; // } public IEnumerable <GeneratedModels.Student> GetStudents() { var database = new s18310Context(); return(database.Student.ToList()); }