public string PromoteStudents(PromoteStudentRequest request) { using (SqlConnection connection = new SqlConnection(ConnectionString)) { connection.Open(); using (var command = new SqlCommand("EXECUTE jd0 @studies_name , @semester", connection)) { command.Parameters.AddWithValue("studies_name", request.Studies); command.Parameters.AddWithValue("semester", request.Semester); try { using (var reader = command.ExecuteReader()) { if (!reader.Read()) { return("Failed: Unable to read."); } Enrollment enrollment = new Enrollment { IdEnrollment = (int)reader["IdEnrollment"], Semester = (int)reader["Semester"], IdStudy = (int)reader["IdStudy"], StartDate = (DateTime)reader["StartDate"] }; return(enrollment.ToString()); } }catch (SqlException e) { return("Failed: Check if procedure exists. " + e.Message); } } } }
public PromoteStudentResponse PromoteStudents(PromoteStudentRequest request) { PromoteStudentResponse response = null; using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=s18963;Integrated Security=True")) { using (SqlCommand com = new SqlCommand()) { com.Connection = con; con.Open(); com.CommandText = "PromoteStudent"; com.CommandType = System.Data.CommandType.StoredProcedure; com.Parameters.AddWithValue("Name", request.Name); com.Parameters.AddWithValue("Semester", request.Semester); var dr = com.ExecuteReader(); if (dr.Read()) { dr.Close(); request.Name = dr["Name"].ToString(); request.Semester = (int)dr["Semester"]; dr = com.ExecuteReader(); dr.Read(); response = new PromoteStudentResponse(); response.Name = dr["Name"].ToString(); response.Semester = (int)dr["Semester"]; dr.Close(); } } return(response); } }
public IActionResult PromoteStudents(PromoteStudentRequest request) { var studies = Db_context.Studies.Single(s => s.Name == request.Studies); if (studies == null) { return(NotFound("Studia " + request.Studies + " nie istnieja")); } var enrollment = Db_context.Enrollment.Where(e => e.Semester == request.Semester && e.IdStudy == studies.IdStudy).ToList(); if (enrollment.Count() == 0) { return(NotFound("Wpis na " + request.Semester + " nie istnieje")); } var newEnrollment = new Enrollment(); { newEnrollment.IdEnrollment = Db_context.Enrollment.Max(e => e.IdEnrollment) + 1; newEnrollment.Semester = enrollment.First().Semester + 1; newEnrollment.IdStudy = enrollment.First().IdStudy; newEnrollment.StartDate = DateTime.Now; Db_context.Enrollment.Add(newEnrollment); } Db_context.Student.Where(s => s.IdEnrollment == enrollment.First().IdEnrollment) .ToList() .ForEach(s => s.IdEnrollment = newEnrollment.IdEnrollment); Db_context.SaveChanges(); return(Ok("Promocja zakończona sukcesem!")); }
public EnrollStudentResponse PromoteStudents(PromoteStudentRequest request) { var semestr = new SqlParameter("@Semester", SqlDbType.Int); semestr.Value = request.Semester; var name = new SqlParameter("@Name", SqlDbType.NVarChar); name.Value = request.Studies; var idEnrollment = new SqlParameter("@IdEnrollment", SqlDbType.Int); idEnrollment.Direction = ParameterDirection.Output; var idStudies = new SqlParameter("@IdStudies", SqlDbType.Int); idStudies.Direction = ParameterDirection.Output; var startDate = new SqlParameter("@StartDate", SqlDbType.DateTime); startDate.Direction = ParameterDirection.Output; var x = context.Database.ExecuteSqlRaw("PromoteStudents @Name, @Semester, @IdEnrollment OUT, @IdStudies OUT, @StartDate OUT", parameters: new[] { name, semestr, idEnrollment, idStudies, startDate }); var response = new EnrollStudentResponse { IdEnrollment = (int)idEnrollment.Value, IdStudy = (int)idStudies.Value, Semester = request.Semester + 1, StartDate = (DateTime)startDate.Value }; return(response); }
public PromoteStudentResponse PromoteStudent(PromoteStudentRequest request) { var enroll = db.Studies.Join(db.Enrollment, s => s.IdStudy, en => en.IdStudy, (s, e) => new { e, s.Name, s.IdStudy }) .Where(t => t.Name == request.Studies && t.e.Semester == request.Semester).First(); if (enroll != null) { var tmp = db.Enrollment.Where(e => e.IdStudy == enroll.IdStudy && e.Semester == enroll.e.Semester); foreach (var t in tmp) { t.Semester = t.Semester + 1; } db.SaveChanges(); PromoteStudentResponse response = new PromoteStudentResponse { IdEnrollment = enroll.e.IdEnrollment, Semester = enroll.e.Semester, Study = enroll.Name, StartDate = enroll.e.StartDate }; return(response); } else { return(null); } }
public PromoteStudentResponse PromoteStudents(PromoteStudentRequest request) { var study = _context.Studies.FirstOrDefault(s => s.Name == request.Name); if (study == null) { throw new Exception("There is no such study"); } var idStudy = _context.Studies.FirstOrDefault(s => s.Name == request.Name).IdStudy; var enrollment = _context.Enrollment.FirstOrDefault(en => en.Semester == request.Semester && en.IdStudy == idStudy); if (enrollment == null) { throw new Exception("there are no students to promote"); } var res = _context.Enrollment.Where(enr => enr.Semester == request.Semester).ToList(); res.ForEach(e => { e.Semester = request.Semester + 1; }); var response = new PromoteStudentResponse() { Semester = request.Semester + 1, Name = request.Name }; _context.SaveChanges(); return(response); }
public Enrollment PromoteStudent(PromoteStudentRequest request) { var res = _context.Enrollment.Join(_context.Studies, enroll => enroll.IdStudy, stud => stud.IdStudy, (enroll, stud) => new { stud.Name, enroll.Semester }).FirstOrDefault(e => e.Name == request.Studies && e.Semester == request.Semester); _context.Database.ExecuteSqlRaw("EXEC PromoteStudents @Studies, @Semester", request.Studies, request.Semester); _context.SaveChanges(); var res2 = _context.Enrollment.Join(_context.Studies, enroll => enroll.IdStudy, stud => stud.IdStudy, (enroll, stud) => new { enroll.IdEnrollment, enroll.Semester, enroll.IdStudy, enroll.StartDate, stud.Name }).Where(e => e.Name == request.Studies && e.Semester == request.Semester + 1).ToList(); var enrollment = new Enrollment() { IdEnrollment = res2[0].IdEnrollment, Semester = res2[0].Semester, IdStudy = res2[0].IdStudy, StartDate = res2[0].StartDate }; return(enrollment); }
public IActionResult PromoteStudent(PromoteStudentRequest request) { PromoteStudentResponse response; 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 = "exec PromoteStudents @studies, @semester "; com.Parameters.AddWithValue("studies", request.Studies); com.Parameters.AddWithValue("semester", request.Semester); var dr = com.ExecuteReader(); response = new PromoteStudentResponse() { Name = request.Studies, Semester = request.Semester + 1 }; return(Ok(response)); }catch (SqlException exc) { return(BadRequest("nie dziala")); } } }
public List <Enrollment> promoteStudents(PromoteStudentRequest request) { List <Enrollment> list = new List <Enrollment>(); using (var connection = new SqlConnection(connString)) using (var command = new SqlCommand()) { command.Connection = connection; connection.Open(); var transaction = connection.BeginTransaction(); command.Transaction = transaction; command.CommandText = "exec PromoteStudents @StudiesName, @Semester"; command.Parameters.AddWithValue("StudiesName", request.Studies); command.Parameters.AddWithValue("Semester", request.Semester); command.ExecuteNonQuery(); command.CommandText = "Select * from Enrollment"; var dr = command.ExecuteReader(); while (dr.Read()) { Enrollment enrollment = new Enrollment(); enrollment.IdEnrollment = (string)dr["IdEnrollment"]; enrollment.Semester = (string)dr["Semester"]; enrollment.IdStudy = (string)dr["IdStudy"]; enrollment.StartDate = (string)dr["StartDate"]; list.Add(enrollment); } } return(list); }
public IActionResult PromoteStudent(PromoteStudentRequest promote) { Enrollment enrollment = new Enrollment(); using (SqlConnection con = new SqlConnection(ConString)) using (SqlCommand com = new SqlCommand()) { com.Connection = con; con.Open(); com.Parameters.AddWithValue("Studies", promote.Studies); com.Parameters.AddWithValue("Semester", promote.Semester); var wynik = UseProcedure("PromoteStudents", com); if (wynik[0][0].Equals("404")) { return(new NotFoundResult()); } enrollment.IdEnrollment = wynik[0][0]; enrollment.IdStudy = wynik[0][2]; enrollment.Semester = wynik[0][1]; enrollment.StartDate = wynik[0][3]; } ObjectResult objectResult = new ObjectResult(enrollment); objectResult.StatusCode = 201; return(objectResult); }
public EnrollStudentResponse PromoteStudent(PromoteStudentRequest request) { using (var connection = new SqlConnection(connectionString)) using (var command = new SqlCommand()) { command.Connection = connection; connection.Open(); command.CommandText = "exec PromoteStudents @stud,@sem"; command.Parameters.AddWithValue("stud", request.Studies); command.Parameters.AddWithValue("sem", request.Semester); var read = command.ExecuteReader(); if (read.Read()) { EnrollStudentResponse response = new EnrollStudentResponse { IdEnroll = (int)read["IdEnrollment"], Semester = (int)read["Semester"], Study = (int)read["IdStudy"], Date = DateTime.Parse(read["StartDate"].ToString()) }; return(response); } } throw new Exception("Exception"); }
public async Task <IActionResult> PromoteStudents(PromoteStudentRequest request) { var enrollment = _context.Enrollment.Join(_context.Studies, e => e.IdStudy, s => s.IdStudy, (e, s) => new { enrolIdStud = e.IdStudy, studIdStud = s.IdStudy, s.Name, e.Semester }).FirstOrDefault(a => a.enrolIdStud == a.studIdStud && a.Name == request.StudiesName && a.Semester == request.Semester); if (enrollment == null) { return(new BadRequestResult()); } try { _context.Database.ExecuteSqlRaw($"PromoteStudents {request.Semester},{request.StudiesName}"); } catch (Exception exc) { Console.WriteLine(exc); return(new BadRequestResult()); } return(new OkObjectResult(new PromoteStudentResponse { Studies = request.StudiesName, Semester = request.Semester })); }
public string PromoteStudent(PromoteStudentRequest req) { var db = new s18625Context(); try { var id = db.Studies.First(s => s.Name == req.Studies).IdStudy; var idE = db.Enrollment.Where(e => e.IdStudy == id) .Where(e => e.Semester == req.Semester); if (idE.Count() < 1) { return("error"); } foreach (var en in idE) { en.Semester += 1; } db.SaveChanges(); return("Ok"); } catch (SqlException exc) { return("error"); } }
public PromoteStudentRequest PromoteStudents(PromoteStudentRequest request) { using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=s18725;Integrated Security=True")) using (var com = new SqlCommand("Execute PromoteStudents @name, @semester;", con)) { Console.WriteLine("open"); con.Open(); var tran = con.BeginTransaction(); try { Console.WriteLine("try"); com.Parameters.AddWithValue("name", request.Studies); com.Parameters.AddWithValue("semester", request.Semester); tran.Commit(); com.ExecuteNonQuery(); } catch (SqlException exc) { tran.Rollback(); Console.WriteLine("err"); } } return(request); }
public IActionResult PromoteStudents(PromoteStudentRequest preq) { PromoteStudentResponse pres = new PromoteStudentResponse(); pres = _dbService.PromoteStudent(preq); return(Created("promote", pres)); }
public IActionResult PromoteStudents(PromoteStudentRequest request) { _service.PromoteStudents(request); var response = new PromoteStudentResponse(); return(Ok(response)); }
public IActionResult PromoteStudents(PromoteStudentRequest request) { PromoteStudentRequest res = _service.PromoteStudents(request); res.Semester = res.Semester + 1; return(Ok(res)); }
public async Task <IActionResult> Promote(PromoteStudentRequest request) { try { var enrollment = await _db.PromoteStudent(request); await _db.SaveAll(); return(Ok(enrollment)); } catch (DbServiceExceptionHandler e) { if (e.Type == ExceptionHandlerEnumType.NotFound) { return(NotFound(e.Message)); } else if (e.Type == ExceptionHandlerEnumType.NotUnique) { return(BadRequest(e.Message)); } } catch (Exception e) { return(BadRequest("Error: " + e.Message)); } return(Ok()); }
public EnrollStudentResponse PromoteStudents(PromoteStudentRequest request) { using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=s18830;Integrated Security=True")) using (var com = new SqlCommand()) { com.CommandType = CommandType.StoredProcedure; com.Connection = con; con.Open(); com.CommandText = "PromoteStudents"; com.Parameters.AddWithValue("@Name", SqlDbType.NVarChar).Value = request.Studies; com.Parameters.AddWithValue("@Semester", SqlDbType.Int).Value = request.Semester; com.Parameters.Add("@IdStudies", SqlDbType.Int).Direction = ParameterDirection.Output; com.Parameters.Add("@IdEnrollment", SqlDbType.Int).Direction = ParameterDirection.Output; com.Parameters.Add("@StartDate", SqlDbType.DateTime).Direction = ParameterDirection.Output; com.ExecuteNonQuery(); var response = new EnrollStudentResponse { IdEnrollment = (int)com.Parameters["@IdEnrollment"].Value, IdStudy = (int)com.Parameters["@IdStudies"].Value, Semester = request.Semester + 1, StartDate = (DateTime)com.Parameters["@StartDate"].Value }; return(response); } }
public IActionResult PromoteStudent(PromoteStudentRequest promote) { var db = new s16705Context(); var semestr = promote.Semester; var idStudy = db.Studies.Where(s => s.Name == promote.Studies).First().IdStudy; var tmp = promote.Semester + 1; //var s = db.Enrollment.Where(s => (s.IdStudy == idStudy) && (s.Semester == promote.Semester)); //db.Entry(e).Property(e => e.Semester).CurrentValue = promote.Semester+1; var query = "UPDATE Enrollment SET semester = " + tmp + " WHERE Semester = " + semestr + " AND IdStudy = " + idStudy; var pr = db.Enrollment.FromSqlRaw(query).ToList(); db.SaveChanges(); return(Ok()); //Old /* * var response = _dbService.PromoteStudents(promote.Semester, promote.Studies); * if (response.Info == "201 Semestr zmieniony") * { * return Created(response.Info, response); * * } * else * { * return BadRequest(response); * } */ }
public PromoteStudentResponse PromoteStudents(PromoteStudentRequest request) { PromoteStudentResponse response = new PromoteStudentResponse(); List <Student> stud = 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 e = new Enrollment { IdEnrollment = idEnrollment, Semester = request.Semester + 1, IdStudy = studies.IdStudy, StartDate = DateTime.Now }; _dbContext.Enrollment.Add(e); _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, }; stud.Add(s1); } response.students = stud; return(response); }
public PromoteStudentResponse PromoteStudents(PromoteStudentRequest request) { PromoteStudentResponse response = new PromoteStudentResponse(); using (SqlConnection connectionSql = new SqlConnection(ConString)) using (var sqlCommand = new SqlCommand("PromoteStudents", connectionSql)) { sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.Parameters.AddWithValue("@Studies", request.Studies); sqlCommand.Parameters.AddWithValue("@semester", request.Semester); connectionSql.Open(); sqlCommand.ExecuteNonQuery(); connectionSql.Close(); response.dtoResponse = "Done"; SqlCommand command = new SqlCommand(); command.Connection = connectionSql; connectionSql.Open(); int newSemester = request.Semester + 1; command.CommandText = "SELECT IdStudy FROM Studies where Name=@name"; command.Parameters.AddWithValue("name", request.Studies); var idStudies = command.ExecuteReader(); if (!idStudies.Read()) { response.dtoResponse = "Podanych studia nie istnieją"; return(response); } int idstudies = (int)idStudies["IdStudy"]; idStudies.Close(); Enrollment enrollment = new Enrollment(); command.CommandText = "SELECT idEnrollment,idStudy,Semester,StartDate FROM Enrollment WHERE idStudy=@idStudy AND Semester=@Semester"; command.Parameters.AddWithValue("idStudy", idstudies); command.Parameters.AddWithValue("Semester", newSemester); var newEnrollSemester = command.ExecuteReader(); if (newEnrollSemester.Read()) { enrollment.IdEnrollment = (int)newEnrollSemester["IdEnrollment"]; enrollment.IdStudy = (int)newEnrollSemester["IdStudy"]; enrollment.Semester = (int)newEnrollSemester["Semester"]; enrollment.StartDate = (String)newEnrollSemester["StartDate"]; } newEnrollSemester.Close(); response.enrollment = enrollment; return(response); } }
public async Task <ActionResult> PromoteStudentAsync(PromoteStudentRequest request) { var studies = await _context.Studies.FirstOrDefaultAsync(s => s.Name == request.Studies); var lastEnrollment = _context.Enrollments.AsNoTracking().OrderByDescending(e => e.IdEnrollment).First().IdEnrollment; var enrollment = _context.Enrollments.AsNoTracking().FirstOrDefault(e => e.IdStudy == studies.IdStudy && e.Semester == request.Semester); if (enrollment == null) { return(BadRequest()); } var nextEnrollment = await _context.Enrollments.FirstOrDefaultAsync(e => e.IdStudy == studies.IdStudy && e.Semester == enrollment.Semester + 1); if (nextEnrollment == null) { var newEnrolment = new Enrollment { IdEnrollment = lastEnrollment + 1, IdStudy = studies.IdStudy, Semester = enrollment.Semester + 1, StartDate = DateTime.Now }; nextEnrollment = newEnrolment; await _context.Enrollments.AddAsync(nextEnrollment); try { await _context.SaveChangesAsync(); } catch (DbUpdateException) { return(Conflict()); } } var students = await _context.Students.ToListAsync(); foreach (var s in students) { if (s.IdEnrollment == enrollment.IdEnrollment) { s.IdEnrollment = nextEnrollment.IdEnrollment; } _context.Entry(s).State = EntityState.Modified; try { await _context.SaveChangesAsync(); } catch (DbUpdateConcurrencyException) { return(Conflict()); } } return(Ok("Promoted")); }
public PromoteStudentResponse PromoteStudent(PromoteStudentRequest promotion) { using (var connection = new SqlConnection("Data Source=db-mssql;Initial Catalog=s18589;Integrated Security=true")) using (var command = new SqlCommand()) { connection.Open(); command.Connection = connection; var transaction = connection.BeginTransaction(); command.Transaction = transaction; Console.WriteLine(promotion.Studies); command.Parameters.AddWithValue("name", promotion.Studies); command.Parameters.AddWithValue("semester", promotion.Semester); command.CommandText = "select * from enrollment inner join studies on enrollment.idstudy = studies.idstudy where studies.name = @name and enrollment.semester = @semester"; var dr = command.ExecuteReader(); command.Parameters.Clear(); if (!dr.HasRows) { dr.Close(); transaction.Rollback(); throw new Exception("query failed"); } dr.Close(); command.Parameters.AddWithValue("name", promotion.Studies); command.Parameters.AddWithValue("semester", promotion.Semester); command.CommandText = "exec promotion @name,@semester"; command.ExecuteNonQuery(); command.CommandText = "select * from enrollment inner join studies on enrollment.idstudy = studies.idstudy where studies.name = @name and enrollment.semester = @semester+1"; dr = command.ExecuteReader(); if (!dr.HasRows) { dr.Close(); transaction.Rollback(); throw new Exception("no promoted students"); } dr.Read(); PromoteStudentResponse response = new PromoteStudentResponse { IdEnrollment = int.Parse(dr["IdEnrollment"].ToString()), Semester = int.Parse(dr["Semester"].ToString()), IdStudy = int.Parse(dr["IdStudy"].ToString()), StartDate = DateTime.Parse(dr["StartDate"].ToString()) }; dr.Close(); transaction.Commit(); return(response); } }
public Enrollment PromoteStudent(PromoteStudentRequest studentRequest) { Enrollment enrollment = null; using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var commands = new SqlCommand()) { commands.Connection = connection; commands.CommandText = $"Select * From Enrollment e, Studies s Where e.IdStudy = s.IdStudy AND e.Semester = @Semester AND s.Name = @Studies"; commands.Parameters.AddWithValue("Studies", studentRequest.Studies); commands.Parameters.AddWithValue("Semester", studentRequest.Semester); var dr = commands.ExecuteReader(); if (dr.Read()) { enrollment = new Enrollment() { IdEnrollment = Int32.Parse(dr["IdEnrollment"].ToString()), Semester = Int32.Parse(dr["Semester"].ToString()), IdStudy = Int32.Parse(dr["IdStudy"].ToString()), StartDate = dr["StartDate"].ToString() }; } dr.Close(); } if (enrollment == null) { return(null); } using (var commands = new SqlCommand()) { commands.Connection = connection; commands.CommandText = "PromoteStudent"; commands.CommandType = System.Data.CommandType.StoredProcedure; commands.Parameters.AddWithValue("Studies", studentRequest.Studies); commands.Parameters.AddWithValue("Semester", studentRequest.Semester); var dr = commands.ExecuteReader(); if (dr.Read()) { enrollment.IdEnrollment = Int32.Parse(dr["IdEnrollment"].ToString()); enrollment.Semester = Int32.Parse(dr["Semester"].ToString()); enrollment.IdStudy = Int32.Parse(dr["IdStudy"].ToString()); enrollment.StartDate = dr["StartDate"].ToString(); } } } return(enrollment); }
public IActionResult Promote(PromoteStudentRequest request) { if (!_enrollmentService.EnrollmentExists(request.Studies, request.Semester)) { return(NotFound("Student does not found")); } return(Ok(_enrollmentService.Promote(request))); }
public IActionResult PromoteStudents(PromoteStudentRequest promoteStudentRequest) { PromoteStudentResponse promoteStudentResponse = _db.PromoteStudents(promoteStudentRequest); if (promoteStudentResponse == null) { return(NotFound()); } return(this.StatusCode(201, promoteStudentResponse)); }
public IActionResult PromoteStudent(PromoteStudentRequest promote) { var result = _service.PromoteStudent(promote); if (result != null) { return(Ok(result)); } return(NotFound()); }
public IActionResult PromoteStudent(PromoteStudentRequest request) { if (this._dbService.PromoteStudent(request)) { return(Ok()); } ; return(BadRequest()); }
public IActionResult PromoteStudents(PromoteStudentRequest request) { Enrollment result = _dbService.PromoteStudents(request.Semester, request.Studies); if (result == null) { return(NotFound()); } return(Created("", result)); }