public ActionResult UnassignCourse(Course course)
{
var courses = unassignCourseManager.UnassignCourse(course);
ViewBag.Courses = courses;
return View();
}
public int Save(Course course)
{
connection.ConnectionString = connectionString;
string query = "INSERT INTO Course (Code,Name,Credit,Description,DepartmentId,SemesterId,TeacherId) VALUES(@Code,@Name,@Credit,@Description,@DepartmentId,@SemesterId,@TeacherId)";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Clear();
command.Parameters.Add("Code", SqlDbType.VarChar);
command.Parameters["Code"].Value = course.Code;
command.Parameters.Add("Name", SqlDbType.VarChar);
command.Parameters["Name"].Value = course.Name;
command.Parameters.Add("Credit", SqlDbType.Decimal);
command.Parameters["Credit"].Value =course.Credit;
command.Parameters.Add("Description", SqlDbType.VarChar);
command.Parameters["Description"].Value = course.Description;
command.Parameters.Add("DepartmentId", SqlDbType.Int);
command.Parameters["DepartmentId"].Value = course.DepartmentId;
command.Parameters.Add("SemesterId", SqlDbType.Int);
command.Parameters["SemesterId"].Value = course.SemesterId;
command.Parameters.Add("TeacherId", SqlDbType.Int);
command.Parameters["TeacherId"].Value = -1;
connection.Open();
int rowAffected = command.ExecuteNonQuery();
connection.Close();
return rowAffected;
}
public bool IsEmailUnique(Teacher teacher)
{
connection.ConnectionString = connectionString;
string query = "SELECT * FROM Teacher WHERE Email=@Email";
Course course=new Course();
SqlCommand command = new SqlCommand();
command.CommandText = query;
command.Connection = connection;
command.Parameters.Add("Email", SqlDbType.VarChar);
command.Parameters["Email"].Value = teacher.Email;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
bool isEmailUnique = false;
if (reader.HasRows)
{
isEmailUnique = true;
}
connection.Close();
return isEmailUnique;
}
public bool IsCourseExists(Course course)
{
connection.ConnectionString = connectionString;
string query = "SELECT * FROM Course WHERE (Code=@Code OR Name = @Name )";
SqlCommand command = new SqlCommand();
command.CommandText = query;
command.Connection = connection;
command.Parameters.Add("Code", SqlDbType.VarChar);
command.Parameters["Code"].Value = course.Code;
command.Parameters.Add("Name", SqlDbType.VarChar);
command.Parameters["Name"].Value = course.Name;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
bool isCourseExist = false;
if (reader.HasRows)
{
isCourseExist = true;
}
connection.Close();
return isCourseExist;
}
public ActionResult SaveCourse(Course course)
{
List<Department> departments = departmentManager.GetAllDepartments();
ViewBag.Departments = departments;
List<Semester> semesters = semesterManager.GetAllSemester();
ViewBag.Semesters = semesters;
ViewBag.Message = courseManager.Save(course);
ModelState.Clear();
return View();
}
public string UnassignCourse(Course course)
{
if (unassignCourseGateway.UnassignCourses(course) > 0)
{
return "Unassign courses successfully.";
}
else
{
return "Unassign courses fail.";
}
}
public int UnassignCourses(Course course)
{
connection.ConnectionString = connectionString;
string query = "Update Course Set TeacherId=NULL";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
int rowAffected = command.ExecuteNonQuery();
connection.Close();
return rowAffected;
}
public string Save(Course course)
{
if (courseGateway.IsCourseExists(course))
{
return "Course Code or Name already exist";
}
else
{
if (courseGateway.Save(course) > 0)
{
return "Saved";
}
else
{
return "Save failed";
}
}
}
public List<Course> GetAllCourseNameByRegNo(string regNo)
{
connection.ConnectionString = connectionString;
string query = "SELECT Course.Id, Course.Name AS CourseName FROM Course INNER JOIN EnrollCourse ON Course.Id=EnrollCourse.CourseId INNER JOIN Student ON EnrollCourse.RegNo= Student.RegNo WHERE Student.RegNo= '" + regNo + "'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List<Course> courselist = new List<Course>();
while (reader.Read())
{
Course course = new Course();
course.Id = Convert.ToInt32(reader["Id"].ToString());
course.Name = reader["CourseName"].ToString();
courselist.Add(course);
}
reader.Close();
connection.Close();
return courselist;
}
public Course GetCourseInfoById(int courseId)
{
connection.ConnectionString = connectionString;
string query = "SELECT * FROM Course WHERE id=@courseId";
SqlCommand command = new SqlCommand();
command.CommandText = query;
command.Connection = connection;
command.Parameters.Add("courseId", SqlDbType.Int);
command.Parameters["courseId"].Value = courseId;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
//List<Teacher> teacherList = new List<Teacher>();
Course course = new Course();
while (reader.Read())
{
course.Credit = (decimal)reader["Credit"];
course.Name = reader["Name"].ToString();
}
reader.Close();
connection.Close();
return course;
}
//change by sayed
public List<Course> GetAllCoursebyDeptId(int departmentId)
{
connection.ConnectionString = connectionString;
string query = "SELECT * FROM Course WHERE (DepartmentId=@DepartmentId)";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("DepartmentId", SqlDbType.Int);
command.Parameters["DepartmentId"].Value = departmentId;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List<Course> courselist = new List<Course>();
while (reader.Read())
{
Course course = new Course();
course.Id =(int) reader["id"];
course.Code = reader["Code"].ToString();
course.Name = reader["Name"].ToString();
course.Credit = (decimal) reader["Credit"];
courselist.Add(course);
}
reader.Close();
connection.Close();
return courselist;
}
