public IEnumerable <Teacher> ListTeachers(string SearchKey = null)
{
//Create an instance of a connection
MySqlConnection Conn = School.ForDatabaseAccess();
//Open the connection between the web server and database
Conn.Open();
//Establish a new command (query) for our database
MySqlCommand cmd = Conn.CreateCommand();
//SQL QUERY
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)";
cmd.Parameters.AddWithValue("@key", "%" + SearchKey + "%");
cmd.Prepare();
//Gather Result Set of Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Teachers
List <Teacher> listTeacher = new List <Teacher> {
};
//Loop Through Each Row the Result Set
while (ResultSet.Read())
{
//Access Column information by the DB column name as an index
int teacherid = (int)ResultSet["teacherid"];
string teacherfname = ResultSet["teacherfname"].ToString();
string teacherlname = ResultSet["teacherlname"].ToString();
string employeenumber = ResultSet["employeenumber"].ToString();
DateTime?hiredate = Convert.ToDateTime(ResultSet["hiredate"].ToString());
decimal? salary = Convert.ToDecimal(ResultSet["salary"].ToString());
Teacher NewTeacher = new Teacher();
NewTeacher.teacherid = teacherid;
NewTeacher.teacherfname = teacherfname;
NewTeacher.teacherlname = teacherlname;
NewTeacher.employeenumber = employeenumber;
NewTeacher.hiredate = hiredate;
NewTeacher.salary = salary;
//Add the Teachers Name to the List
listTeacher.Add(NewTeacher);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of teachers names
return(listTeacher);
}
