public IActionResult EnrollStudent(StudentEnrollmentReq request)
        {
            var response = _dbService.EnrollStudent(request);

            if (response.Error != null)
            {
                return(BadRequest(response.Error));
            }
            return(Ok(response));
        }
        public StudentEnrollmentRes EnrollStudent(StudentEnrollmentReq request)
        {
            using SqlConnection connection = new SqlConnection(ConnectionString);
            using SqlCommand command       = new SqlCommand
                  {
                      Connection  = connection,
                      CommandText = "SELECT * FROM Studies WHERE Name=@Name"
                  };
            command.Parameters.AddWithValue("Name", request.Studies);

            connection.Open();

            var transaction = connection.BeginTransaction();

            command.Transaction = transaction;
            int idEnrollment;

            try
            {
                var dr = command.ExecuteReader();
                // Check if the Study exists
                if (!dr.Read())
                {
                    dr.Close();
                    transaction.Rollback();
                    return(new StudentEnrollmentRes
                    {
                        Error = "Study does not exist"
                    });
                }
                int idStudy = (int)dr["iDstudy"];

                command.CommandText = "Select * from Student where IndexNumber=@IndexNumber";
                command.Parameters.AddWithValue("IndexNumber", request.IndexNumber);
                dr.Close();
                dr = command.ExecuteReader();
                // Check student with given index exists
                if (dr.Read())
                {
                    dr.Close();
                    transaction.Rollback();
                    return(new StudentEnrollmentRes
                    {
                        Error = "Student with given index exists"
                    });
                }

                command.CommandText = "SELECT * FROM Enrollment WHERE Semester=1 AND IdStudy=@IdStudy";
                command.Parameters.AddWithValue("IdStudy", idStudy);
                dr.Close();
                dr = command.ExecuteReader();

                if (dr.Read())
                {
                    idEnrollment = (int)dr["IdEnrollment"];
                    dr.Close();
                }
                // If there is no Enrollment with a given Study add new Enrollment
                else
                {
                    command.CommandText = "SELECT MAX(idEnrollment) as IdEnrollment FROM Enrollment";
                    dr.Close();
                    dr = command.ExecuteReader();
                    var nextIdEnrollment = 1;
                    if (dr.Read())
                    {
                        nextIdEnrollment = (int)dr["IdEnrollment"] + 1;
                    }
                    dr.Close();
                    var startDate = DateTime.Now;
                    command.CommandText = "INSERT INTO Enrollment(idEnrollment, idStudy, semester, startDate) " +
                                          "values(@NextIdEnrollment, @IdStudy, 1, @StartDate)";
                    command.Parameters.AddWithValue("NextIdEnrollment", nextIdEnrollment);
                    command.Parameters.AddWithValue("StartDate", startDate);
                    command.ExecuteNonQuery();
                    idEnrollment = nextIdEnrollment;
                }

                command.CommandText = "INSERT INTO Student(IndexNumber, firstname, lastname, birthdate, idenrollment) " +
                                      "values(@IndexNumber, @FirstName, @LastName, @BirthDate, @IdEnrollment)";
                command.Parameters.AddWithValue("FirstName", request.FirstName);
                command.Parameters.AddWithValue("LastName", request.LastName);
                command.Parameters.AddWithValue("BirthDate", request.BirthDate);
                command.Parameters.AddWithValue("IdEnrollment", idEnrollment);
                command.ExecuteNonQuery();

                transaction.Commit();
            }
            catch (SqlException)
            {
                transaction.Rollback();
                return(new StudentEnrollmentRes
                {
                    Error = "SQLException"
                });
            }
            return(new StudentEnrollmentRes
            {
                IdEnrollment = idEnrollment,
                Semester = 1
            });
        }