public Classes FindClass(int id)
{
// Creating connection to access DB
MySqlConnection Conn = School.AccessDatabase();
// Opening connection
Conn.Open();
// Creating a command for sending query
MySqlCommand cmd = Conn.CreateCommand();
// SQL query for retrieving a student's info
cmd.CommandText = "SELECT * FROM classes left outer join teachers on classes.teacherid = teachers.teacherid where classid = @key";
// Sanitizing the query to prevent SQL injection
cmd.Parameters.AddWithValue("@key", id);
cmd.Prepare();
// Storing result into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
// Creating a student object to store the data
Classes NewClass = new Classes();
while (ResultSet.Read())
{
NewClass.classId = Convert.ToString(ResultSet["classid"]);
NewClass.classCode = Convert.ToString(ResultSet["classcode"]);
NewClass.teacherId = Convert.ToInt32(ResultSet["teacherid"]);
NewClass.startdate = DateTime.Parse(Convert.ToString(ResultSet["startdate"]));
NewClass.finishdate = DateTime.Parse(Convert.ToString(ResultSet["finishdate"]));
NewClass.classname = Convert.ToString(ResultSet["classname"]);
NewClass.teacherName = Convert.ToString(ResultSet["teacherfname"]) + " " + Convert.ToString(ResultSet["teacherlname"]);
NewClass.employeeNumber = Convert.ToString(ResultSet["employeenumber"]);
}
return(NewClass);
}
public IEnumerable <Teacher> ListTeachers(string searchKey = null)
{
// Creating instance of the connection
MySqlConnection Conn = School.AccessDatabase();
// Opening connection between server and DB
Conn.Open();
// New command for query
MySqlCommand cmd = Conn.CreateCommand();
// SQL query for filtering
cmd.CommandText = "Select * from teachers where lower(teacherfname) like lower(@key) or" +
" lower(teacherlname) like lower(@key) or" +
" lower(CONCAT(teacherfname, ' ',teacherlname)) like lower(@key) or " +
"lower(employeenumber) like lower(@key);";
// Sanitizing the query to prevent SQL injection
cmd.Parameters.AddWithValue("@key", "%" + searchKey + "%");
cmd.Prepare();
// Storing the result of query execution into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
// empty list of type Teacher
List <Teacher> TeacherDetails = new List <Teacher> {
};
// Read until the result set is complete
while (ResultSet.Read())
{
int teacherID = Convert.ToInt32(ResultSet["teacherid"]);
string teacherEmpNumber = Convert.ToString(ResultSet["employeenumber"]);
string teacherFname = Convert.ToString(ResultSet["teacherfname"]);
string teacherLname = Convert.ToString(ResultSet["teacherlname"]);
DateTime hireDate = DateTime.Parse(Convert.ToString(ResultSet["hiredate"]));
string teacherSalary = Convert.ToString(ResultSet["salary"]);
Teacher NewTeacher = new Teacher
{
teacherId = teacherID,
teacherFname = teacherFname,
teacherLname = teacherLname,
employeeNumber = teacherEmpNumber,
hireDate = hireDate,
salary = teacherSalary
};
// Adding teacher object into a list
TeacherDetails.Add(NewTeacher);
}
// Close the connection
Conn.Close();
// Return the list of teacher objects
return(TeacherDetails);
}
public IEnumerable <StuXCla> ListClassesOfStudent(StuXCla studentClassFilter)
{
// Creating instance of the connection
MySqlConnection Conn = School.AccessDatabase();
// Opening connection between server and DB
Conn.Open();
// New command for query
MySqlCommand cmd = Conn.CreateCommand();
// SQL query for filtering
cmd.CommandText = "Select * from studentsxclasses where studentid = @studentkey or classid = @classkey;";
// Sanitizing the query to prevent SQL injection
cmd.Parameters.AddWithValue("@studentkey", studentClassFilter.student_id);
cmd.Parameters.AddWithValue("@classkey", studentClassFilter.class_id);
cmd.Prepare();
// Storing the result of query execution into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
// empty list of type Student
List <StuXCla> Details = new List <StuXCla> {
};
// Read until the result set is complete
while (ResultSet.Read())
{
StuXCla NewStuXClassInstance = new StuXCla
{
class_id = Convert.ToInt32(ResultSet["classid"]),
student_id = Convert.ToInt32(ResultSet["studentid"])
};
// Adding student object into a list
Details.Add(NewStuXClassInstance);
}
// Close the connection
Conn.Close();
// Return the list of objects
return(Details);
}
public IEnumerable <Student> ListStudents(string searchKey)
{
// Creating instance of the connection
MySqlConnection Conn = School.AccessDatabase();
// Opening connection between server and DB
Conn.Open();
// New command for query
MySqlCommand cmd = Conn.CreateCommand();
// Sanitising input parameters
int index = 1;
string strAppend = "";
String[] strArrayIDs;
string strNames = searchKey;
strArrayIDs = strNames.Split(',');
string paramName = "";
foreach (String item in strArrayIDs)
{
paramName = "@idParam" + index;
cmd.Parameters.AddWithValue(paramName, item); //Making individual parameters for every name
strAppend += paramName + ",";
index += 1;
}
strAppend = strAppend.ToString().Remove(strAppend.LastIndexOf(","), 1); //Remove the last comma
// SQL query for filtering, appended with the parameterized values
cmd.CommandText = "Select * from students where studentid IN (" + strAppend + ")";
cmd.Prepare();
// Storing the result of query execution into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
// empty list of type student
List <Student> StudentDetails = new List <Student> {
};
// Read until the result set is complete
while (ResultSet.Read())
{
int studentID = Convert.ToInt32(ResultSet["studentid"]);
string studentNumber = Convert.ToString(ResultSet["studentnumber"]);
string studentFname = Convert.ToString(ResultSet["studentfname"]);
string studentLname = Convert.ToString(ResultSet["studentlname"]);
DateTime enrolDate = DateTime.Parse(Convert.ToString(ResultSet["enroldate"]));
Student Newstudent = new Student
{
studentId = studentID,
studentFname = studentFname,
studentLname = studentLname,
studentNumber = studentNumber,
enrolDate = enrolDate
};
// Adding student object into a list
StudentDetails.Add(Newstudent);
}
// Close the connection
Conn.Close();
// Return the list of student objects
return(StudentDetails);
}