public StudentsEnrollment StudentsEnrollments(string index)
{
string EnrollmentQuery = "SELECT IndexNumber,FirstName, LastName, s.Name, e.Semester, e.StartDate" +
" FROM Student stu INNER JOIN Enrollment e on stu.IdEnrollment = e.IdEnrollment INNER JOIN" +
" Studies s on e.IdStudy = s.IdStudy WHERE IndexNumber = @index";
using (SqlConnection con = new SqlConnection(ConString))
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
com.CommandText = EnrollmentQuery;
com.Parameters.AddWithValue("index", index);
con.Open();
SqlDataReader dr = com.ExecuteReader();
if (!dr.HasRows())
{
return(null);
}
var stEnrollment = new StudentsEnrollment();
while (dr.Read())
{
stEnrollment.IndexNumber = dr["IndexNumber"].ToString();
stEnrollment.FirstName = dr["FirstName"].ToString();
stEnrollment.LastName = dr["LastName"].ToString();
stEnrollment.Name = dr["Name"].ToString();
stEnrollment.Semester = int.Parse(dr["Semester"].ToString());
stEnrollment.StartDate = DateTime.Parse(dr["StartDate"].ToString());
}
return(stEnrollment);
}
}
public ActionResult Create(StudentCourseViewModel SCVM, Student students, StudentsEnrollment enroll)
{
List <StudentsEnrollment> ste = new List <StudentsEnrollment>();
students.StudentName = SCVM.StudentName;
students.Address = SCVM.Address;
students.Email = SCVM.Email;
students.StudentId = SCVM.StudentId;
db.Students.AddObject(students);
db.SaveChanges();
//enroll.StudentId = SCVM.StudentId;
//enroll.CourseId = SCVM.CourseId;
////enroll.CourseName = SCVM.CourseName;
//db.StudentsEnrollments.AddObject(enroll);
// enroll.StudentName = SCVM.StudentName;
//db.StudentsEnrollments.AddObject(enroll);
//db.SaveChanges();
int studentid = students.StudentId;
foreach (var item in SCVM.AvailableCourses)
{
if (item.IsChecked == true)
{
ste.Add(new StudentsEnrollment()
{
StudentId = studentid, CourseId = item.CourseId
});
//db.StudentsEnrollments.AddObject(new StudentsEnrollment()
//{StudentId=studentid,CourseId=item.CourseId,CourseName =item.CourseName });
//ste.Add(new StudentCourse() { StudentId = studentid, CourseId = item.CourseId });
//db.StudentsEnrollments.AddObject(new StudentsEnrollment() { StudentId = studentid, CourseId = item.CourseId });
}
}
foreach (var item in ste)
{
db.StudentsEnrollments.AddObject(item);
}
db.SaveChanges();
return(RedirectToAction("Index"));
//return View();
}
public ActionResult Delete(int id, FormCollection collection)
{
try
{
using (UniversitySystemContextDb db = new UniversitySystemContextDb())
{
//Student enroll = db.Students.Where(e => e.StudentId == id).FirstOrDefault();
StudentsEnrollment enroll = db.StudentsEnrollments.SingleOrDefault(e => e.EnrollmentId == id);
db.StudentsEnrollments.DeleteObject(enroll);
db.SaveChanges();
}
return(RedirectToAction("Index"));
}
catch
{
return(View());
}
}
public ActionResult Edit(int id, StudentCourseViewModel SCVM, Student students, StudentsEnrollment enroll)
{
List <StudentsEnrollment> ste = new List <StudentsEnrollment>();
students.StudentName = SCVM.StudentName;
students.Address = SCVM.Address;
students.Email = SCVM.Email;
students.StudentId = SCVM.StudentId;
db.Students.Attach(students); //check attach wrk or not
db.ObjectStateManager.ChangeObjectState(students, EntityState.Modified);
db.SaveChanges();
int studentid = students.StudentId;
foreach (var item in SCVM.AvailableCourses)
{
if (item.IsChecked == true)
{
ste.Add(new StudentsEnrollment()
{
StudentId = studentid, CourseId = item.CourseId
});
}
}
var dbtable = db.StudentsEnrollments.Where(a => a.StudentId == studentid).ToList();
var result = dbtable.Except(ste).ToList();
foreach (var item in result)
{
db.StudentsEnrollments.DeleteObject(item);
db.SaveChanges();
}
var getcourseid = db.StudentsEnrollments.Where(a => a.StudentId == studentid).ToList();
foreach (var item in ste)
{
if (!getcourseid.Contains(item))
{
;
}
{
db.StudentsEnrollments.AddObject(item);
db.SaveChanges();
}
}
return(RedirectToAction("Index"));
}