public List <EnrollCourseViewModel> GetAllStudent()
{
SqlConnection connection = new SqlConnection(connectionString);
string quary = "Select S.Id, S.Name, S.Email, S.DepartmenteId, S.RegistrationNo, D.Name AS DepartmentName From Student AS S INNER JOIN Department AS D ON S.DepartmenteId = D.Id";
SqlCommand command = new SqlCommand(quary, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <EnrollCourseViewModel> StudentList = new List <EnrollCourseViewModel>();
while (reader.Read())
{
EnrollCourseViewModel students = new EnrollCourseViewModel();
students.StudentId = Convert.ToInt32(reader["Id"].ToString());
students.Name = reader["Name"].ToString();
students.Email = reader["Email"].ToString();
students.DepartmentId = Convert.ToInt32(reader["DepartmenteId"].ToString());
students.RegistrationNo = reader["RegistrationNo"].ToString();
students.DepartmentName = reader["DepartmentName"].ToString();
StudentList.Add(students);
}
reader.Close();
connection.Close();
return(StudentList);
}
public ActionResult SaveCourseEnroll([Bind(Include = "OfferedCourseID")] EnrollCourseViewModel model)
{
var name = Session["username"];
if (name != null)
{
var id = model.OfferedCourseID;
var course = _db.OfferedCourses.Where(x => x.OfferedCourseID == id).FirstOrDefault();
var learner = _db.Users.Where(x => x.Username == name.ToString()).FirstOrDefault();
//var ax = course.CoursesEnrolled.Contains(_db.LearnerEnrollments.Where(s => s.EnrolledLearner.Username == name.ToString()).FirstOrDefault());
var ax = _db.LearnerEnrollments.Where(s => s.EnrolledLearner.Username == name.ToString() && s.EnrolledCourseID == id).FirstOrDefault();
if (ax == null)
{
course.LearnerCount = course.LearnerCount + 1;
_db.LearnerEnrollments.Add(new LearnerEnroll
{
EnrollDate = DateTime.Now,
EnrolledCourse = course,
EnrolledLearner = learner,
EnrolledLearnerID = learner.Username,
CompletionDate = course.FinishDate,
EnrolledCourseID = id,
});
_db.SaveChanges();
return(RedirectToAction("Index"));
}
return(RedirectToAction("Index"));
}
return(RedirectToAction("Index", "Default"));
}
public bool UnassignAllCourse()
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "Select * from EnrollStudent where Status='" + 1 + "'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <EnrollCourseViewModel> courseList = new List <EnrollCourseViewModel>();
while (reader.Read())
{
EnrollCourseViewModel courses = new EnrollCourseViewModel();
courses.Id = Convert.ToInt32(reader["Id"].ToString());
courses.StudentId = Convert.ToInt32(reader["StudentId"].ToString());
courses.CourseId = Convert.ToInt32(reader["CourseId"].ToString());
courses.Date = Convert.ToDateTime(reader["EnrollmentDate"].ToString());
courses.Status = Convert.ToInt32(reader["Status"].ToString());
courseList.Add(courses);
}
reader.Close();
connection.Close();
if (courseList.Count == 0)
{
return(false);
}
else
{
foreach (var course in courseList)
{
string query1 = "UPDATE EnrollStudent SET Status='" + 0 + " '";
SqlCommand command1 = new SqlCommand(query1, connection);
connection.Open();
int rowAffected = command1.ExecuteNonQuery();
connection.Close();
}
return(true);
}
}
