コード例 #1
0
        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);
                    }
                }
            }
        }
コード例 #2
0
ファイル: StudentDbService.cs プロジェクト: brvchd/tut7
        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);
            }
        }
コード例 #3
0
ファイル: EnrollmentsController.cs プロジェクト: s16535/cw2
        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!"));
        }
コード例 #4
0
ファイル: StudentDbService.cs プロジェクト: s18923/Cw10
        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);
        }
コード例 #5
0
ファイル: SqlEntityDbService.cs プロジェクト: s18486/Cw5
        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);
            }
        }
コード例 #6
0
        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);
        }
コード例 #7
0
ファイル: SqlStudentDbServive.cs プロジェクト: s18891/cw10
        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);
        }
コード例 #8
0
        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"));
                    }
                }
        }
コード例 #9
0
        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);
        }
コード例 #10
0
        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);
        }
コード例 #11
0
ファイル: SqlServerDbService.cs プロジェクト: s18534/Cw3
        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");
        }
コード例 #12
0
        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
            }));
        }
コード例 #13
0
        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");
            }
        }
コード例 #14
0
        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);
        }
コード例 #15
0
        public IActionResult PromoteStudents(PromoteStudentRequest preq)
        {
            PromoteStudentResponse pres = new PromoteStudentResponse();

            pres = _dbService.PromoteStudent(preq);
            return(Created("promote", pres));
        }
コード例 #16
0
        public IActionResult PromoteStudents(PromoteStudentRequest request)
        {
            _service.PromoteStudents(request);
            var response = new PromoteStudentResponse();

            return(Ok(response));
        }
コード例 #17
0
ファイル: EnrollmentsController.cs プロジェクト: s18725/Cw7
        public IActionResult PromoteStudents(PromoteStudentRequest request)
        {
            PromoteStudentRequest res = _service.PromoteStudents(request);

            res.Semester = res.Semester + 1;
            return(Ok(res));
        }
コード例 #18
0
        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());
        }
コード例 #19
0
        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);
                }
        }
コード例 #20
0
        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);
             * }
             */
        }
コード例 #21
0
ファイル: EfStudentDbService.cs プロジェクト: s18687/cw10
        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);
        }
コード例 #22
0
        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);
                }
        }
コード例 #23
0
ファイル: EnrollmentsController.cs プロジェクト: s7452/Ex10
        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"));
        }
コード例 #24
0
        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);
                }
        }
コード例 #25
0
ファイル: SQLServerDbService.cs プロジェクト: s19434/cw5
        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);
        }
コード例 #26
0
        public IActionResult Promote(PromoteStudentRequest request)
        {
            if (!_enrollmentService.EnrollmentExists(request.Studies, request.Semester))
            {
                return(NotFound("Student does not found"));
            }

            return(Ok(_enrollmentService.Promote(request)));
        }
コード例 #27
0
        public IActionResult PromoteStudents(PromoteStudentRequest promoteStudentRequest)
        {
            PromoteStudentResponse promoteStudentResponse = _db.PromoteStudents(promoteStudentRequest);

            if (promoteStudentResponse == null)
            {
                return(NotFound());
            }
            return(this.StatusCode(201, promoteStudentResponse));
        }
コード例 #28
0
        public IActionResult PromoteStudent(PromoteStudentRequest promote)
        {
            var result = _service.PromoteStudent(promote);

            if (result != null)
            {
                return(Ok(result));
            }
            return(NotFound());
        }
コード例 #29
0
ファイル: EnrollmentsController.cs プロジェクト: Blizu/APBD-5
        public IActionResult PromoteStudent(PromoteStudentRequest request)
        {
            if (this._dbService.PromoteStudent(request))
            {
                return(Ok());
            }
            ;

            return(BadRequest());
        }
コード例 #30
0
        public IActionResult PromoteStudents(PromoteStudentRequest request)
        {
            Enrollment result = _dbService.PromoteStudents(request.Semester, request.Studies);

            if (result == null)
            {
                return(NotFound());
            }
            return(Created("", result));
        }