public List <Teacher> GetAllTeachersByAdminDepartment(Department department)
{
MySqlConnection dbConnection = DbConnection.GetConnection();
using (dbConnection)
{
dbConnection.Open();
var selectQuery =
"select * FROM dept_employs_teachers WHERE dept_name=@dept_name";
List <Teacher> teachers = new List <Teacher>();
using (MySqlCommand cmd = new MySqlCommand(selectQuery, dbConnection))
{
cmd.Parameters.AddWithValue("@dept_name", department.Name);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
int teacherIDOrdinal = reader.GetOrdinal("teacher_uid");
while (reader.Read())
{
var teacherID = reader[teacherIDOrdinal] == DBNull.Value
? default(string)
: reader.GetString(teacherIDOrdinal);
TeacherDAL teacherGetter = new TeacherDAL();
Teacher teacher = teacherGetter.GetTeacherByTeacherID(teacherID);
teachers.Add(teacher);
}
return(teachers);
}
}
}
}
public List <Department> GetAllDepartments()
{
MySqlConnection dbConnection = DbConnection.GetConnection();
using (dbConnection)
{
dbConnection.Open();
var selectQuery =
"select * FROM departments";
List <Department> departments = new List <Department>();
using (MySqlCommand cmd = new MySqlCommand(selectQuery, dbConnection))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
int departmentNameOrdinal = reader.GetOrdinal("name");
int chairOrdinal = reader.GetOrdinal("chair_uid");
while (reader.Read())
{
var departmentName = reader[departmentNameOrdinal] == DBNull.Value
? default(string)
: reader.GetString(departmentNameOrdinal);
var chairUID = reader[chairOrdinal] == DBNull.Value
? default(string)
: reader.GetString(chairOrdinal);
TeacherDAL teacherGetter = new TeacherDAL();
Teacher chair = teacherGetter.GetTeacherByTeacherID(chairUID);
Department dept = new Department(chair, departmentName);
departments.Add(dept);
}
return(departments);
}
}
}
}
/// <summary>
/// Gets the department by the user id.
/// </summary>
/// <param name="userID">The user id to check.</param>
/// <returns>
/// A department object for for the given userID
/// </returns>
/// <preconditions>
/// User ID cannot be null
/// </preconditions>
public Department GetDepartmentByUserID(string userID)
{
if (userID == null)
{
throw new Exception("User ID cannot be null");
}
MySqlConnection dbConnection = DbConnection.GetConnection();
using (dbConnection)
{
dbConnection.Open();
var selectQuery =
"select departments.* FROM department_admins, departments WHERE departments.name = department_admins.department_name AND department_admins.admin_uid = @user_uid";
using (MySqlCommand cmd = new MySqlCommand(selectQuery, dbConnection))
{
cmd.Parameters.AddWithValue("@user_uid", userID);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
int departmentNameOrdinal = reader.GetOrdinal("name");
int chairOrdinal = reader.GetOrdinal("chair_uid");
while (reader.Read())
{
var departmentName = reader[departmentNameOrdinal] == DBNull.Value
? default(string)
: reader.GetString(departmentNameOrdinal);
var chairUID = reader[chairOrdinal] == DBNull.Value
? default(string)
: reader.GetString(chairOrdinal);
TeacherDAL teacherGetter = new TeacherDAL();
Teacher chair = teacherGetter.GetTeacherByTeacherID(chairUID);
Department dept = new Department(chair, departmentName);
return(dept);
}
}
}
}
return(null);
}