public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { using (SqlConnection connection = new SqlConnection(ConnectionString)) { connection.Open(); SqlCommand command = new SqlCommand("PromoteStudents", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new SqlParameter("@Semester", request.Semester)); command.Parameters.Add(new SqlParameter("@Studies", request.Studies)); SqlDataReader reader = command.ExecuteReader(); if (reader.Read()) { var response = new PromoteStudentsResponse() { IdEnrollment = int.Parse(reader["IdEnrollment"].ToString()), Semester = int.Parse(reader["Semester"].ToString()), Study = reader["Name"].ToString(), StartDate = DateTime.Parse(reader["StartDate"].ToString()) }; reader.Close(); return(response); } reader.Close(); } return(null); }
public IActionResult PromoteStudent(PromoteStudentsRequest request) { var enrollment = _dbService.GetLatestEnrollment(request.Studies, request.Semester); if (enrollment == null) { return(NotFound("Enrollment does not exist")); } Enrollment newEnrollment; try { newEnrollment = _dbService.PromoteStudents(enrollment.Course, enrollment.Semester); } catch (Exception e) { return(BadRequest(e.Message)); } var response = new PromoteStudentsResponse { IdEnrollment = newEnrollment.IdEnrollment, Course = request.Studies, Semester = newEnrollment.Semester, }; return(Created("", response)); }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { PromoteStudentsResponse response = null; using (SqlConnection con = new SqlConnection(ConString)) using (SqlCommand com = new SqlCommand()) { com.Connection = con; com.CommandType = System.Data.CommandType.StoredProcedure; com.CommandText = "PromoteStudents"; com.Parameters.AddWithValue("@Studies", request.Studies); com.Parameters.AddWithValue("@Semester", request.Semester); com.Parameters.Add("@IdEnrollment", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output; com.Parameters.Add("@IdStudies", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output; com.Parameters.Add("@StartDate", System.Data.SqlDbType.Date).Direction = System.Data.ParameterDirection.Output; con.Open(); com.ExecuteNonQuery(); response = new PromoteStudentsResponse(); response.IdEnrollment = (int)com.Parameters["@IdEnrollment"].Value; response.Semester = request.Semester + 1; response.IdStudy = (int)com.Parameters["@IdStudies"].Value; response.StartDate = (DateTime)com.Parameters["@StartDate"].Value; } return(response); }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { var study = _context.Studies.FirstOrDefault(s => s.Name == request.Name); if (study == null) { throw new StudiesNotFound(); } var idStudy = _context.Studies.FirstOrDefault(s => s.Name == request.Name).IdStudy; var enrollment = _context.Enrollment.Where(en => en.Semester == request.Semester && en.IdStudy == idStudy) .FirstOrDefault(); if (enrollment == null) { throw new NoStudentsToPromote(); } var enrollments = _context.Enrollment.Where(enr => enr.Semester == request.Semester) .ToList(); enrollments.ForEach(e => e.Semester = request.Semester + 1); var response = new PromoteStudentsResponse() { Semester = request.Semester + 1, Name = request.Name }; _context.SaveChanges(); return(response); }
public PromoteStudentsResponse PromoteStudents(PromoteStudentRequest request) { PromoteStudentsResponse response = new PromoteStudentsResponse(); List <Student> listOfSt = new List <Student>(); var studies = _dbContext.Studies .Where(s => s.Name.Equals(request.Studies)) .Single(); var OldEnrollment = _dbContext.Enrollment .Where(e => e.IdStudy == studies.IdStudy && e.Semester == request.Semester) .Single(); var enrollment = _dbContext.Enrollment .Where(e => e.IdStudy == studies.IdStudy && e.Semester == request.Semester + 1) .SingleOrDefault(); int idEnrollment; if (enrollment == null) { idEnrollment = _dbContext.Enrollment.Count() + 1; var enr = new Enrollment { IdEnrollment = idEnrollment, Semester = request.Semester + 1, IdStudy = studies.IdStudy, StartDate = DateTime.Now }; _dbContext.Enrollment.Add(enr); _dbContext.SaveChanges(); } else { idEnrollment = enrollment.IdEnrollment; } var students = _dbContext.Student .Where(s => s.IdEnrollment == OldEnrollment.IdEnrollment) .ToList(); foreach (Student s in students) { s.IdEnrollment = idEnrollment; _dbContext.SaveChanges(); var s1 = new Student { IndexNumber = s.IndexNumber, FirstName = s.FirstName, LastName = s.LastName, BirthDate = s.BirthDate, IdEnrollment = idEnrollment, }; listOfSt.Add(s1); } response.Students = listOfSt; return(response); }
public PromoteStudentsResponse PromoteStudent(PromoteStudentsRequest request) { var response = new PromoteStudentsResponse(); using (var con = new SqlConnection(ConString)) { using (var com = new SqlCommand()) { com.Connection = con; con.Open(); var transaction = con.BeginTransaction(); com.Transaction = transaction; //1. Sprawdzam czy w tabeli enrollment istnieje wpis o podanej wartości Studies i Semester, W przeciwnym razie zwracam kod 404 Not Found com.CommandText = "SELECT * FROM Enrollment" + " INNER JOIN Studies" + " ON Studies.IdStudy = Enrollment.IdStudy" + " WHERE Enrollment.Semester = @semester" + " AND Studies.Name = @studies"; com.Parameters.AddWithValue("semester", request.Semester); com.Parameters.AddWithValue("studies", request.Studies); var dr = com.ExecuteReader(); if (!dr.Read()) { dr.Close(); response.Status = 404; response.Message = "Nie istnieje wpis Studies i Semester o podanej wartości"; return(response); } dr.Close(); // Jeżeli wszystko poszło dobrze uruchamiam procedurę składową com.CommandText = "promoteStudents"; com.CommandType = CommandType.StoredProcedure; dr = com.ExecuteReader(); if (dr.Read()) { var enrollment = new Enrollment(); enrollment.IdEnrollment = (int)dr["IdEnrollment"]; enrollment.Semester = (int)dr["Semester"]; enrollment.IdStudy = (int)dr["IdStudy"]; enrollment.StartDate = (DateTime)dr["StartDate"]; response.enrollment = enrollment; response.Status = 201; //response.Message = "Ok"; dr.Close(); } } } return(response); }
public IActionResult PromoteStudent(PromoteStudentsRequest promotion) { // _service.PromoteStudent(promotion); PromoteStudentsResponse resp = new PromoteStudentsResponse(); return(CreatedAtRoute(new RouteValues(), _service.PromoteStudent(promotion))); }
public Response PromoteStudents(PromoteStudentsRequest request) { using (var con = new SqlConnection(ConString)) using (var com = new SqlCommand()) { com.Connection = con; con.Open(); var tran = con.BeginTransaction(); com.Transaction = tran; try { com.CommandText = @"select * from enrollment join studies on enrollment.idStudy = studies.idStudy where name = @name and semester = @semester"; com.Parameters.AddWithValue("name", request.Studies); com.Parameters.AddWithValue("semester", request.Semester); var dr = com.ExecuteReader(); if (!dr.Read()) { dr.Close(); tran.Rollback(); return(new Response("404", "Wpis dla " + request.Studies + " semestr " + request.Semester + " nie istnieje", null)); } dr.Close(); com.Parameters.Clear(); com.CommandType = CommandType.StoredProcedure; com.CommandText = "dbo.PromoteStudents"; //kod tworzący procedurę PromoteStudents poniżej com.Parameters.Add(new SqlParameter("@Studies", request.Studies)); com.Parameters.Add(new SqlParameter("@Semester", request.Semester)); com.ExecuteNonQuery(); tran.Commit(); var enrollment = new PromoteStudentsResponse(); enrollment.Studies = request.Studies; enrollment.Semester = (int)request.Semester + 1; return(new Response("201", "Promocja studentow ukonczona", enrollment)); } catch (SqlException exc) { tran.Rollback(); } } return(new Response("400", null, null)); }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=s16985;Integrated Security=True")) using (var com = new SqlCommand()) { var response = new PromoteStudentsResponse(); Object idEnrollment; if (con.State == ConnectionState.Closed) { con.Open(); } var trans = con.BeginTransaction(); com.Connection = con; com.Transaction = trans; try { com.Parameters.Clear(); com.CommandType = CommandType.StoredProcedure; com.CommandText = "PromoteStudents"; com.Parameters.AddWithValue("Studies", request.Studies); com.Parameters.AddWithValue("Semester", request.Semester); var returnValue = com.Parameters.Add("@ReturnValue", SqlDbType.Int); returnValue.Direction = ParameterDirection.ReturnValue; com.ExecuteNonQuery(); if ((int)returnValue.Value == 0) { response.Message = "Podany semestr nie istnieje!"; return(response); } else { idEnrollment = returnValue.Value; } } catch (SqlException e) { trans.Rollback(); response.Message = e.Message; return(response); } trans.Commit(); response.IdEnrollment = (int)idEnrollment; response.Message = "Ok"; return(response); } }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { var response = new PromoteStudentsResponse(); if (!_context .Enrollment .Where(e => e.IdStudyNavigation.Name == request.Studies && e.Semester == request.Semester) .Any()) { response.Message = "NO_SUCH_ENROLLMENT"; return(response); } var oldEnrollment = _context .Enrollment .Where(e => e.IdStudyNavigation.Name == request.Studies && e.Semester == request.Semester) .Include(e => e.Student) .Single(); Enrollment newEnrollment = null; if (_context .Enrollment .Where(e => e.IdStudyNavigation.Name == request.Studies && e.Semester == request.Semester + 1) .Any()) { newEnrollment = _context .Enrollment .Where(e => e.IdStudyNavigation.Name == request.Studies && e.Semester == request.Semester + 1) .Single(); } if (newEnrollment == null) { newEnrollment = new Enrollment { Semester = request.Semester + 1, IdStudyNavigation = _context.Studies.Where(e => e.Name == request.Studies).Single(), StartDate = new DateTime(), }; } foreach (Student student in oldEnrollment.Student) { student.IdEnrollmentNavigation = newEnrollment; } _context.SaveChanges(); response.IdEnrollment = newEnrollment.IdEnrollment; response.Message = "OK"; return(response); }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { var response = new PromoteStudentsResponse(); response.setStatus(400, "Error"); var _studies = _context.Studies.Where(s => s.Name == request.Studies).FirstOrDefault(); if (_studies == null) { response.setStatus(404, "ERROR: studies do not exist"); return(response); } ; var _enrollment = _context.Enrollment .Where(a => a.IdStudy == _studies.IdStudy && a.Semester == request.Semester) .OrderByDescending(s => s.StartDate).FirstOrDefault(); if (_enrollment == null) { response.setStatus(404, "ERROR: semester and studies do not exist in database"); return(response); } ; Console.WriteLine("here:" + _enrollment.IdEnrollment); var _enrollment_next = _context.Enrollment.Where(p => p.Semester == _enrollment.Semester + 1).FirstOrDefault(); if (_enrollment_next == null) { _enrollment_next = new Enrollment() { IdEnrollment = _context.Enrollment.Max(p => p.IdEnrollment) + 1, Semester = _enrollment.Semester + 1, IdStudy = _enrollment.IdStudy, StartDate = DateTime.Now.Date }; _context.Add(_enrollment_next); _context.SaveChanges(); } var _students = _context.Student.Where(s => s.IdEnrollment == _enrollment.IdEnrollment); foreach (var s in _students) { s.IdEnrollment = _enrollment_next.IdEnrollment; } _context.SaveChanges(); response.Semester = _enrollment_next.Semester; response.StartDate = _enrollment_next.StartDate.ToString(); response.StudiesName = _studies.Name; response.setStatus(201, "Students are promoted on the next semester"); return(response); }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { var response = new PromoteStudentsResponse(); response.setStatus(400, "Unknown Error"); // domyślnie - błąd var _studies = _context.Studies.Where(s => s.Name == request.Studies).FirstOrDefault(); if (_studies == null) { response.setStatus(404, "ERROR: nie znaleziono studiów"); return(response); } ; var _enrollment = _context.Enrollment .Where(a => a.IdStudy == _studies.IdStudy && a.Semester == request.Semester) .OrderByDescending(s => s.StartDate).FirstOrDefault(); if (_enrollment == null) { response.setStatus(404, "ERROR: nie znaleziono semestru i studiów"); return(response); } ; Console.WriteLine("tutaj:" + _enrollment.IdEnrollment); var _enrollment_next = _context.Enrollment.Where(p => p.Semester == _enrollment.Semester + 1).FirstOrDefault(); if (_enrollment_next == null) { _enrollment_next = new Enrollment() { IdEnrollment = _context.Enrollment.Max(p => p.IdEnrollment) + 1, Semester = _enrollment.Semester + 1, IdStudy = _enrollment.IdStudy, StartDate = DateTime.Now.Date }; _context.Add(_enrollment_next); _context.SaveChanges(); } var _students = _context.Student.Where(s => s.IdEnrollment == _enrollment.IdEnrollment); foreach (var s in _students) { s.IdEnrollment = _enrollment_next.IdEnrollment; } _context.SaveChanges(); response.Semester = _enrollment_next.Semester; response.StartDate = _enrollment_next.StartDate.ToString(); response.StudiesName = _studies.Name; response.setStatus(201, "Studenci zostali promowani na następny semestr"); return(response); }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { var db = new s18985Context(); var response = new PromoteStudentsResponse(); var t = db.Studies.Where(x => x.Name == request.Studies).Select(x => x.IdStudy).First(); var e = db.Enrollment.Where(x => x.IdStudy == t && x.Semester == request.Semester).FirstOrDefault(); if (e == null) { return(null); } var studs = db.Student.Where(x => x.IdEnrollment == e.IdEnrollment).ToList(); int nextSemester = request.Semester + 1; e = db.Enrollment.Where(x => x.IdStudy == t && x.Semester == nextSemester).FirstOrDefault(); int f; if (e == null) { var max = (db.Enrollment.Max(x => x.IdEnrollment)) + 1; var enroll = new Enrollment { IdEnrollment = max, Semester = nextSemester, IdStudy = t, StartDate = DateTime.Now }; db.Enrollment.Add(enroll); f = max; } else { f = e.IdEnrollment; } foreach (var s in studs) { s.IdEnrollment = f; db.Attach(s); db.Entry(s).Property("IdEnrollment").IsModified = true; db.SaveChanges(); } response.IdEnrollment = f; response.Semester = nextSemester; response.Studies = request.Studies; response.StartDate = DateTime.Now; return(response); }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { var response = new PromoteStudentsResponse(); response.setStatus(400, "Unknown Error"); // domyślnie - błąd using (var connection = new SqlConnection(CONN_STR)) { connection.Open(); var command = connection.CreateCommand(); var transaction = connection.BeginTransaction(); command.Connection = connection; command.Transaction = transaction; command.CommandText = "select e.IdEnrollment from dbo.Enrollment e inner join dbo.Studies s on e.IdStudy = s.IdStudy where s.Name = @studyName and e.Semester = @semesterNumber"; command.Parameters.AddWithValue("studyName", request.Studies); command.Parameters.AddWithValue("semesterNumber", request.Semester); var reader = command.ExecuteReader(); if (!reader.Read()) { reader.Close(); response.setStatus(404, "ERROR: nie znaleziono semestru i/lub studiów"); transaction.Rollback(); } else { reader.Close(); using (SqlConnection conn = new SqlConnection(CONN_STR)) { conn.Open(); SqlCommand cmd = new SqlCommand("Promote", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@studies", request.Studies)); cmd.Parameters.Add(new SqlParameter("@semester", request.Semester)); cmd.ExecuteReader(); conn.Close(); } response.Semester = request.Semester + 1; response.StartDate = DateTime.Now.ToString("dd.MM.yyyy"); response.StudiesName = request.Studies; transaction.Commit(); response.setStatus(201, "Studenci zostali promowani na następny semestr"); } } return(response); }
public IActionResult PromoteStudents(PromoteStudentsRequest request) { PromoteStudentsResponse resp = _service.PromoteStudents(request); if (resp == null) { return(BadRequest("404 Not Found")); } else { return(Ok(resp)); } }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { var response = new PromoteStudentsResponse(); DateTime date = DateTime.Today; int success; const string ConString = "Data Source=db-mssql;Initial Catalog=s19391;Integrated Security=True"; using (var con = new SqlConnection(ConString)) using (var com = new SqlCommand()) { com.Connection = con; con.Open(); var tran = con.BeginTransaction(); com.CommandText = "execute PromoteStudents @study, @semester"; com.Parameters.AddWithValue("study", request.Studies); com.Parameters.AddWithValue("semester", request.Semester); com.Transaction = tran; success = com.ExecuteNonQuery(); var nextSemestr = request.Semester + 1; com.CommandText = "select IdEnrollment from enrollment where semester = @nextSemester and idstudy = (select idstudy from studies where name = @study)"; com.Parameters.AddWithValue("nextSemester", nextSemestr); com.Transaction = tran; var dr = com.ExecuteScalar(); response.IdEnrollment = (int)dr; com.CommandText = "select StartDate from enrollment where semester = @nextSemester and idstudy = (select idstudy from studies where name = @study)"; com.Transaction = tran; dr = com.ExecuteScalar(); response.StartDate = (DateTime)dr; tran.Commit(); response.Studies = request.Studies; response.Semester = request.Semester + 1; if (success > 0) { return(response); } else { return(null); } } }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { PromoteStudentsResponse response; DateTime now = DateTime.Now; var checkStudies = _context.Studies .Where(s => s.Name == request.Studies) .FirstOrDefault(); if (checkStudies == null) { throw new ArgumentException("Podane studia nie istnieja"); } var studies = _context.Enrollment .Join(_context.Studies, enr => enr.IdStudy, stu => stu.IdStudy, (enr, stu) => new { enr, stu }) .Where(s => s.stu.Name == request.Studies && s.enr.Semester == request.Semester) .FirstOrDefault(); if (studies == default) { throw new ArgumentException("Wpis z podanym semestrem nie istnieje w bazie"); } var result = _context.Enrollment .FromSqlRaw("EXEC [dbo].[PromoteStudents] {0}, {1}", request.Studies, request.Semester) .AsEnumerable() .FirstOrDefault(); if (result != null) { response = new PromoteStudentsResponse() { IdEnrollment = result.IdEnrollment, IdStudy = result.IdStudy, Semester = result.Semester, StartDate = result.StartDate }; return(response); } return(null); }
//zastosowanie SQLConnection oraz SQLCommand /* * public Enrollment PromoteStudents(int semester, string studiesName) * { * var enrollment = new Enrollment(); * using (var connection = new SqlConnection("Data Source=10.1.1.36; Initial Catalog=s8346;Integrated Security=True")) * { * using (var command = new SqlCommand()) * { * command.Connection = connection; * connection.Open(); * * command.CommandText = "SELECT semester FROM Enrollment inner join Studies on Enrollment.IdStudy=Studies.idStudy where Enrollment.Semester = @semester AND Studies.name = (SELECT name from Studies where Name = @name)"; * command.Parameters.AddWithValue("name", studiesName); * command.Parameters.AddWithValue("semester", semester); * * var SqlDataReader = command.ExecuteReader(); * * if (!SqlDataReader.Read()) * { * //return NotFound(); * } * SqlDataReader.Close(); * command.Parameters.Clear(); * * command.CommandText = "PromoteStudents"; * command.CommandType = System.Data.CommandType.StoredProcedure; * command.Parameters.AddWithValue("Studies", studiesName); * command.Parameters.AddWithValue("Semester", semester); * * SqlDataReader = command.ExecuteReader(); * if (SqlDataReader.Read()) * { * enrollment.IdEnrollment = int.Parse(SqlDataReader["IdEnrollment"].ToString()); * enrollment.IdStudy = int.Parse(SqlDataReader["IdStudy"].ToString()); * enrollment.Semester = int.Parse(SqlDataReader["Semester"].ToString()); * enrollment.StartDate = DateTime.Parse(SqlDataReader["StartDate"].ToString()); * } * } * } * return enrollment; * } * }*/ //zastosowanie EF public PromoteStudentsResponse PromoteStudents(int semester, string studies) { using (var transaction = _dbContext.Database.BeginTransaction()) { int idStudy = _dbContext.Studies .Where(st => st.Name == studies) .Select(st => st.IdStudy).SingleOrDefault(); Enrollment enrollment = _dbContext.Enrollment.FirstOrDefault(e => e.IdStudy == idStudy && e.Semester == semester); if (enrollment == null) { return(null); } int oldIdEnrollment = enrollment.IdEnrollment; enrollment = _dbContext.Enrollment.FirstOrDefault(e => e.IdStudy == idStudy && e.Semester == semester + 1); if (enrollment == null) { int maxId = _dbContext.Enrollment.Max(e => e.IdEnrollment); enrollment = new Enrollment(); enrollment.IdEnrollment = maxId + 1; enrollment.Semester = semester + 1; enrollment.IdStudy = idStudy; enrollment.StartDate = DateTime.Now; _dbContext.Enrollment.Add(enrollment); _dbContext.SaveChanges(); } var students = _dbContext.Student.Where(s => s.IdEnrollment == oldIdEnrollment).ToList(); foreach (Student student in students) { student.IdEnrollment = enrollment.IdEnrollment; } _dbContext.SaveChanges(); transaction.Commit(); var response = new PromoteStudentsResponse() { Study = studies, NewIdStudy = enrollment.IdStudy, NewSemester = enrollment.Semester }; return(response); } }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { var psr = new PromoteStudentsResponse(); using (var con = new SqlConnection(Program.GetConnectionString())) using (var com = new SqlCommand()) { com.Connection = con; con.Open(); var tran = con.BeginTransaction(); try { com.CommandText = "EXEC PROMOTESTUDENTS @STUDIES = @studies, @SEMESTER = @semester"; com.Parameters.AddWithValue("studies", request.Studies); com.Parameters.AddWithValue("semester", request.Semester); com.Transaction = tran; var dr = com.ExecuteReader(); if (!dr.Read()) { dr.Close(); tran.Rollback(); throw new Exception("Procedura nie powiodła się"); } else { psr.IdEnrollment = (int)dr["IdEnrollment"]; psr.IdStudy = (int)dr["IdStudy"]; psr.Semester = (int)dr["Semester"]; psr.StartDate = (DateTime)dr["StartDate"]; } dr.Close(); tran.Commit(); } catch (SqlException exc) { tran.Rollback(); throw new Exception("Operacja nie przebiegła pomyślnie"); } return(psr); } }
public IActionResult PromoteStudent(PromoteStudentsRequest req) { var arePromoted = _service.PromoteStudents(req); if (!arePromoted) { return(StatusCode(500)); } var response = new PromoteStudentsResponse() { Semester = req.Semester + 1, Studies = req.Studies }; return(Ok(response)); }
public PromoteStudentsResponse PromoteStudents(PromoteStudentRequest req) { Enrollment respEnrollment = new Enrollment(); var resp = new PromoteStudentsResponse(); SqlCommand com = new SqlCommand(); using (SqlConnection con = new SqlConnection(SqlConn)) { con.Open(); var transaction = con.BeginTransaction(); com.Connection = con; com.Transaction = transaction; com.CommandText = "select e.IdEnrollment from dbo.Enrollment e inner join dbo.Studies s on e.IdStudy = s.IdStudy where s.Name = @studyName and e.Semester = @semesterNumber"; com.Parameters.AddWithValue("@semester", req.Semester); com.Parameters.AddWithValue("@studies", req.Studies); var dr = com.ExecuteReader(); if (!dr.Read()) { dr.Close(); return(null); } else { dr.Close(); using (SqlConnection conn = new SqlConnection(SqlConn)) { conn.Open(); SqlCommand cmd = new SqlCommand("Promote", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@studies", req.Studies)); cmd.Parameters.Add(new SqlParameter("@semester", req.Semester)); cmd.ExecuteReader(); conn.Close(); } resp.Semester = req.Semester + 1; resp.StartDate = DateTime.Now.ToString("dd.MM.yyyy"); resp.StudiesName = req.Studies; transaction.Commit(); } } return(resp); }
public PromoteStudentsResponse PromoteStudents(PromoteStudentRequest request) { String test = "0 "; var result = new PromoteStudentsResponse(); //check if Enrollment exists using (var client = new SqlConnection(CONNECTION_STRING)) using (var command = new SqlCommand("PromoteStudents", client) { CommandType = System.Data.CommandType.StoredProcedure }) { test += "1 "; client.Open(); var tran = client.BeginTransaction(); command.Transaction = tran; command.Parameters.AddWithValue("Semester", request.Semester); command.Parameters.AddWithValue("StudiesName", request.Studies); var reader = command.ExecuteReader(); try { test += "2 "; //create response if (reader.Read()) { result.IdEnrollment = int.Parse(reader["IdEnrollment"].ToString()); result.IdStudy = int.Parse(reader["IdStudy"].ToString()); result.Semester = int.Parse(reader["Semester"].ToString()); result.StartDate = DateTime.Parse(reader["StartDate"].ToString()); } test += "3 "; tran.Commit(); client.Close(); }catch (Exception e) { test += "Roll " + e; reader.Close(); tran.Rollback(); return(null); } } test += "end "; return(result); }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { if (request.Studies == null || request.Semester == 0) { throw new ArgumentNullException("Incorrect request"); } PromoteStudentsResponse response = null; Enrollment respEnrollment = new Enrollment(); using (SqlConnection conn = new SqlConnection(SqlConn)) { conn.Open(); SqlCommand cmd = new SqlCommand("PromoteStudents", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@studies", request.Studies)); cmd.Parameters.Add(new SqlParameter("@semester", request.Semester)); using (SqlDataReader dr = cmd.ExecuteReader()) { if (!dr.Read()) { cmd.Dispose(); throw new ArgumentException("Nothing to be read by SqlDataReader"); } response = new PromoteStudentsResponse(); response.Enrollment = respEnrollment; respEnrollment.IdEnrollment = (int)dr["IdEnrollment"]; respEnrollment.Semester = (int)dr["Semester"]; respEnrollment.IdStudy = (int)dr["IdStudy"]; respEnrollment.StartDate = dr["StartDate"].ToString(); } cmd.Dispose(); } return(response); }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { PromoteStudentsResponse response; using (SqlConnection con = new SqlConnection(ConString)) using (SqlCommand com = new SqlCommand()) { com.Connection = con; con.Open(); var tran = con.BeginTransaction(); com.Transaction = tran; try { com.CommandText = "SELECT IdStudy FROM Studies WHERE name=@name"; com.Parameters.AddWithValue("name", request.Studies); var dr = com.ExecuteReader(); if (!dr.Read()) { dr.Close(); tran.Rollback(); throw new ArgumentException("Podane studia nie istnieja"); } dr.Close(); com.CommandText = "SELECT IdEnrollment FROM Enrollment, Studies " + "WHERE Enrollment.IdStudy = Studies.IdStudy " + "AND Studies.Name = @name " + "AND Enrollment.Semester = @semester "; com.Parameters.AddWithValue("semester", request.Semester); com.Transaction = tran; dr = com.ExecuteReader(); if (!dr.Read()) { dr.Close(); tran.Rollback(); throw new ArgumentException("Wpis z podanym semestrem nie istnieje w bazie"); } dr.Close(); com.Parameters.Clear(); com.CommandText = "EXEC [dbo].[PromoteStudents] @studies, @semester"; com.Parameters.AddWithValue("studies", request.Studies); com.Parameters.AddWithValue("semester", request.Semester); com.ExecuteNonQuery(); com.CommandText = "SELECT * FROM enrollment " + "WHERE idStudy = (SELECT idStudy FROM Studies WHERE name = @studiesName) " + "AND semester = @newSemester "; com.Parameters.AddWithValue("studiesName", request.Studies); com.Parameters.AddWithValue("newSemester", request.Semester + 1); dr = com.ExecuteReader(); if (dr.Read()) { response = new PromoteStudentsResponse() { IdEnrollment = (int)dr["IdEnrollment"], IdStudy = (int)dr["IdStudy"], Semester = (int)dr["Semester"], StartDate = DateTime.Now }; dr.Close(); tran.Commit(); return(response); } else { throw new ArgumentException("Problem przy tworzeniu odpowiedzi"); } } catch (SqlException exc) { tran.Rollback(); throw new ArgumentException(exc.Message); } } }
public PromoteStudentsResponse PromoteStudent(PromoteStudentsRequest promotion) { var pr = new PromoteStudentsRequest(); pr.Studies = promotion.Studies; pr.Semester = promotion.Semester; using (var client = new SqlConnection(ConString)) using (var com = new SqlCommand()) { com.Connection = client; client.Open(); var tran = client.BeginTransaction(); com.Transaction = tran; PromoteStudentsResponse resp = new PromoteStudentsResponse(); com.CommandText = "EXEC PromoteStudents '" + pr.Studies + "', " + pr.Semester + " WITH RESULT SETS(( IdEnrollment INT,IDStudy INT,Semester INT,StartDate DateTime)) "; /*used procedure: * CREATE PROCEDURE PromoteStudents @Studies NVARCHAR(100), @Semester INT * AS * BEGIN * DECLARE @IdStudies INT = (SELECT IdStudy FROM Studies WHERE NAme=@Studies); * IF @IdStudies IS NULL * BEGIN * RETURN; * * END * * DECLARE @StartDate DATETIME = (SELECT MAX(startdate) FROM enrollment where IdStudy=@IdStudies and Semester =@Semester) * * DECLARE @IdEnrollment INT = (select IdEnrollment from enrollment where IdStudy=@IdStudies and Semester =@Semester and startdate = @StartDate) * * IF @IdEnrollment IS NULL * BEGIN * RETURN; * * END * * DECLARE @NextSemester INT = (select IdEnrollment from enrollment where IdStudy=@IdStudies and Semester =@semester+1) * IF @NextSemester IS NULL * BEGIN * DECLARE @Max INT = (SELECT MAX(IdEnrollment) FROM enrollment) * DECLARE @NextId INT = @Max +1 * insert into enrollment values(@NextId, @semester+1, @IdStudies, CURRENT_TIMESTAMP) * UPDATE student SET IdEnrollment=@NextId WHERE IdEnrollment=@IdEnrollment * SELECT * FROM enrollment WHERE IdEnrollment=@NextId; * END * ELSE * * UPDATE student SET IdEnrollment=@NextSemester WHERE IdEnrollment=@IdEnrollment * SELECT * FROM enrollment WHERE IdEnrollment=@NextSemester; * RETURN; * END; * */ var dr = com.ExecuteReader(); while (dr.Read()) { resp.IdEnrollment = (int)dr["IdEnrollment"]; resp.idStudy = (int)dr["IDStudy"]; resp.Semester = (int)dr["Semester"]; resp.StartDate = (DateTime)dr["StartDate"]; } dr.Close(); tran.Commit(); return(resp); } }
public PromoteStudentsResponse PromoteStudents(PromoteStudentsRequest request) { using (var connection = new SqlConnection(sqlConnectionString)) using (var command = new SqlCommand()) { var response = new PromoteStudentsResponse(); Object idEnrollment; if (connection.State == ConnectionState.Closed) { connection.Open(); } var transaction = connection.BeginTransaction(); command.Connection = connection; command.Transaction = transaction; try { // 1. Sprawdzamy czy istnieje odpowiedni wpis w tabeli Enrollment // UWAGA!!! Funkcjonalność przeniesiona do procedury składowanej //command.CommandText = "" + // "SELECT IdEnrollment FROM Enrollment e" + // " JOIN Studies s ON e.IdStudy = s.IdStudy" + // " WHERE s.Name=@Name AND e.Semester=@Semester"; //command.Parameters.AddWithValue("Name", request.Studies); //command.Parameters.AddWithValue("Semester", request.Semester); //idEnrollment = command.ExecuteScalar(); //if(idEnrollment == null) //{ // response.Message = "There is no such studies in database"; // return response; //} // 2. Wywołanie procedury składowanej command.Parameters.Clear(); command.CommandType = CommandType.StoredProcedure; command.CommandText = "PromoteStudents"; command.Parameters.AddWithValue("Studies", request.Studies); command.Parameters.AddWithValue("Semester", request.Semester); var returnValue = command.Parameters.Add("@ReturnValue", SqlDbType.Int); returnValue.Direction = ParameterDirection.ReturnValue; command.ExecuteNonQuery(); if ((int)returnValue.Value == 0) { response.Message = "There is no such semester in database"; return(response); } else { idEnrollment = returnValue.Value; } } catch (SqlException e) { transaction.Rollback(); response.Message = e.Message; return(response); } transaction.Commit(); response.IdEnrollment = (int)idEnrollment; response.Message = "Ok"; return(response); } }
[Route("api/enrollments/promotions")] //zmień na student public IActionResult PromoteStudents(PromoteStudentsRequest request) { var db = new _2019SBDContext(); // var res = db.Student.ToList(); var res = db.Studies .Where((d) => d.Name == request.Studies).FirstOrDefault(); if (res == null) { return(BadRequest()); } var res2 = db.Enrollment .Where((d) => d.IdStudy == res.IdStudy && d.Semester == request.Semester).FirstOrDefault(); if (res == null) { return(BadRequest()); } var res3 = db.Enrollment .Where((d) => d.IdStudy == res.IdStudy && d.Semester == request.Semester + 1).FirstOrDefault(); PromoteStudentsResponse response = new PromoteStudentsResponse(); if (res3 == null) { //dodaj enrollment var res4 = db.Enrollment .OrderByDescending(d => d.IdEnrollment) //rosnąco czy malejąco? .FirstOrDefault(); int maxIdEnrollment = res4.IdEnrollment; response.StartDate = DateTime.Today; var e = new Enrollment() { IdEnrollment = maxIdEnrollment + 1, StartDate = response.StartDate, Semester = request.Semester + 1, IdStudy = res.IdStudy }; db.Enrollment.Add(e); response.IdEnrollment = maxIdEnrollment + 1; } else { response.IdEnrollment = res3.IdEnrollment; } // db.SaveChanges(); //teraz trzeba wszystkich studentów zrolować var res5 = db.Student .Where((d) => d.IdEnrollment == res2.IdEnrollment).ToList(); foreach (Student s in res5) { s.IdEnrollment = response.IdEnrollment; } db.SaveChanges(); return(Ok(response)); }