//This controller is used to retrieve classes taught by a single teacher
/// <summary>
/// Returns a list of courses taught by a teacher
/// </summary>
/// <param name="id"></param>
/// <returns>A list of classes taught by a teacher</returns>
public IEnumerable <Class> FindTeacherClass(int id)
{
//Establishes a new connection to the database using the AccessDatabase method in the SchoolDbContext class
MySqlConnection Conn = School.AccessDatabase();
//Opens connection to the database
Conn.Open();
//Creates a new MySql command that displays all columns from the class table
MySqlCommand cmd = Conn.CreateCommand();
//Query only requests classname from classes object since that is the only info needed in the teacher table for now
cmd.CommandText = "SELECT classname FROM `classes` WHERE `classes`.teacherid = " + id;
//Collects query result and stores them in an object we can access
MySqlDataReader ResultSet = cmd.ExecuteReader();
List <Class> Classes = new List <Class> {
};
//Loop to extract results and assign them to a class object
while (ResultSet.Read())
{
//Instantiate new Class object to assign classname to
Class NewClass = new Class();
//Extract classname from result set
string ClassName = (string)ResultSet["classname"];
//Assign result to classname property in a class object
NewClass.ClassName = ClassName;
//Add the new class object to a running list of objects
Classes.Add(NewClass);
}
Conn.Close();
return(Classes);
}
//IEnumerable method is used to get the list of Teachers from the teachers table
public IEnumerable <Teacher> ListTeachers(string SearchKey, string Number, string Date)
{
//Accessdatabse is the method of ScoolDbContext class,so use the method of the class we need
// to create an object of connection method.
//So here Conn is the object of a connection MySqlConnection to access the method AccessDatabase()
MySqlConnection Conn = School.AccessDatabase();
//Open the connection between the webserver and the 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%') or salary like '%"+Number + "%'";
cmd.CommandText = "Select * from teachers where lower(teacherfname) like lower('%" + SearchKey + "%') or lower(teacherlname) like lower('%" + SearchKey + "%') or lower(concat(teacherfname,' ',teacherlname)) like lower('" + SearchKey + "') or salary like '" + Number + "' or hiredate like '" + Date + "'";
//which parameter is to insert in a particular query.So here we are inserting searchkey
//cmd.Parameters.AddWithValue("key", "%" + SearchKey + "%");
//cmd.Prepare();
//Gather the result set of query into a variable
MySqlDataReader Resultset = cmd.ExecuteReader();
//Create ab Empty list of teacher names
List <Teacher> Teachers = new List <Teacher> {
};
//Loop for each row would be accessed by the resultset
while (Resultset.Read())
//Read method will read series of rows
{
//Access Colum infromation be the Db column name as an index
int TeacherId = (int)Resultset["teacherid"];
string TeacherFname = (string)Resultset["teacherfname"];
string TeacherLName = (string)Resultset["teacherlname"];
string EmployeeNumber = (string)Resultset["employeenumber"];
DateTime HireDate = (DateTime)Resultset["hiredate"];
decimal Salary = (decimal)Resultset["salary"];
//To make an object of an teacher class
Teacher newTeacher = new Teacher();
newTeacher.TeacherId = TeacherId;
newTeacher.TeacherFname = TeacherFname;
newTeacher.TeacherLname = TeacherLName;
newTeacher.EmployeeNumber = EmployeeNumber;
newTeacher.HireDate = HireDate;
newTeacher.Salary = Salary;
//Add the TechserName to the list
Teachers.Add(newTeacher);
}
//For closing the connection between Mysql dataabse and web server
Conn.Close();
//Return the final list of Techer Names
return(Teachers);
}
public IEnumerable <Teacher> ListTeachers(string SearchKey = null)
{
MySqlConnection Conn = School.AccessDatabase(); //Connects to Database
Conn.Open(); // Opens connection to the database
MySqlCommand cmd = Conn.CreateCommand(); // Makes a var name cmd to allow use to make the queries.
//cmd.CommandText = "Select * from Teachers"; // Place the following query into the cmd.CommandText
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();
MySqlDataReader ResultSet = cmd.ExecuteReader(); // Makes a var ResultSet to read and execute the command above.
List <Teacher> Teachers = new List <Teacher> {
}; // Empty list used to store the Teacher Objects.
// While loop to read over and grab all of information store in the Teacher table.
while (ResultSet.Read())
{
Teacher NewTeacher = new Teacher();
NewTeacher.TeacherId = Convert.ToInt32(ResultSet["teacherid"]);
NewTeacher.TeacherFirstName = ResultSet["teacherfname"].ToString();
NewTeacher.TeacherLastName = ResultSet["teacherlname"].ToString();
Teachers.Add(NewTeacher);
}
Conn.Close(); // Close the DB connection.
return(Teachers); // Returns List
}
public Teacher ShowTeacher(int?id)
{
Teacher NewTeacher = new Teacher();
//Create an instance of a connection
MySqlConnection Conn = School.AccessDatabase();
//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 teacherid = " + id;
//Gather Result Set of Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
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();
NewTeacher.TeacherId = TeacherId;
NewTeacher.TeacherFname = TeacherFname;
NewTeacher.TeacherLname = TeacherLname;
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Open the connection between the web server and database
Conn.Open();
//Create another instance of a connection
MySqlCommand classcmd = Conn.CreateCommand();
classcmd.CommandText = "Select * from classes where teacherid = " + id;
MySqlDataReader ClassResultSet = classcmd.ExecuteReader();
while (ClassResultSet.Read())
{
//Access Column information by the DB column name as an index
string TeacherClass = ClassResultSet["classname"].ToString();
NewTeacher.TeacherClass = TeacherClass;
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of author namess
return(NewTeacher);
}
public IEnumerable <Class> ClassInfo(string SearchKey = null)
{
//Creating connection with database
MySqlConnection Conn = School.AccessDatabase();
//Acivate connection between the web server and database
Conn.Open();
//Establish a new command for our database
MySqlCommand cmd = Conn.CreateCommand();
//SQL QUERY to access columns from classes table
cmd.CommandText = "Select * from Classes where lower(classname) like lower(@key) or lower(classcode) like lower(@key)";
cmd.Parameters.AddWithValue("@key", "%" + SearchKey + "%");
cmd.Prepare();
//Incorporating SQL Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Class names
List <Class> Class = new List <Class> {
};
//Loop Through Each Row the Result Set
while (ResultSet.Read())
{
//Get column information by the column name from classes table
int Classid = (int)ResultSet["classid"];
string Classname = ResultSet["classname"].ToString();
string Classcode = ResultSet["classcode"].ToString();
DateTime StartDate;
DateTime.TryParse(ResultSet["StartDate"].ToString(), out StartDate);
DateTime FinishDate;
DateTime.TryParse(ResultSet["FinishDate"].ToString(), out FinishDate);
int Teacherid = Convert.ToInt32(ResultSet["teacherid"]);
Class NewClass = new Class();
NewClass.ClassId = Classid;
NewClass.Classname = Classname;
NewClass.Classcode = Classcode;
NewClass.Startdate = StartDate;
NewClass.Finishdate = FinishDate;
NewClass.Teacherid = Teacherid;
//Adding class name to the List
Class.Add(NewClass);
}
//Ending connection between the MySQL Database and the WebServer
Conn.Close();
//Returning the final list of class names
return(Class);
}
public IEnumerable <Teacher> ListTeachers(string SearchKey = null)
{
//Create an instance of a connection
MySqlConnection Conn = school.AccessDatabase();
//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> Teachers = 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 = Convert.ToInt32(ResultSet["teacherid"]);
string TeacherFname = ResultSet["teacherfname"].ToString();
string TeacherLname = ResultSet["teacherlname"].ToString();
string employeenumber = ResultSet["employeenumber"].ToString();;
// This technique will work,
//DateTime TeacherJoinDate = (DateTime)ResultSet["teacherjoindate"];
// This technique is safer!
DateTime TeacherJoinDate;
DateTime.TryParse(ResultSet["hiredate"].ToString(), out TeacherJoinDate);
Teacher AddTeacher = new Teacher();
AddTeacher.TeacherId = TeacherId;
AddTeacher.TeacherFname = TeacherFname;
AddTeacher.TeacherLname = TeacherLname;
AddTeacher.employeenumber = employeenumber;
//Add the Teacher Name to the List
Teachers.Add(AddTeacher);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of teacher names
return(Teachers);
}
//IEnumerable method is used to get the list of Teachers from the teachers table
public IEnumerable <Class> ListClasses()
{
//Accessdatabse is the method of ScoolDbContext class,so use the method of the class we need
// to create an object of connection method.
//So here Conn is the object of a connection MySqlConnection to access the method AccessDatabase()
MySqlConnection Conn = School.AccessDatabase();
//Open the connection between the webserver and the database
Conn.Open();
//Establish a new command (query) for our database
MySqlCommand cmd = Conn.CreateCommand();
//Sql Query
cmd.CommandText = "Select * from classes";
//Gather the result set of query into a variable
MySqlDataReader Resultset = cmd.ExecuteReader();
//Create ab Empty list of teacher names
List <Class> Classes = new List <Class> {
};
//Loop for each row would be accessed by the resultset
while (Resultset.Read())
//Read method will read series of rows
{
//Access Colum infromation be the Db column name as an index
int ClassId = (int)Resultset["classid"];
string ClassCode = (string)Resultset["classcode"];
int TeacherId = Convert.ToInt32(Resultset["teacherid"]);
DateTime StartDate = (DateTime)Resultset["startdate"];
DateTime FinishDate = (DateTime)Resultset["finishdate"];
string ClassName = (string)Resultset["classname"];
//To make an object of an Class class
Class newClass = new Class();
newClass.ClassId = ClassId;
newClass.ClassCode = ClassCode;
newClass.TeacherId = TeacherId;
newClass.StartDate = StartDate;
newClass.FinishDate = FinishDate;
newClass.ClassName = ClassName;
//Add the TechserName to the list
Classes.Add(newClass);
}
//For closing the connection between Mysql dataabse and web server
Conn.Close();
//Return the final list of Techer Names
return(Classes);
}
public IEnumerable <Teacher> TeacherInfo(string SearchKey = null)
{
//Creating connection with database
MySqlConnection Conn = School.AccessDatabase();
//Acivate connection between the web server and database
Conn.Open();
//Establish a new command for our database
MySqlCommand cmd = Conn.CreateCommand();
//SQL QUERY to access columns from teacher table
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();
//Incorporating SQL Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Teacher Names
List <Teacher> Teachers = new List <Teacher> {
};
//Loop Through Each Row the Result Set
while (ResultSet.Read())
{
//Get column information by the column name from teacher table
int TeacherId = (int)ResultSet["teacherid"];
string TeacherFname = ResultSet["teacherfname"].ToString();
string TeacherLname = ResultSet["teacherlname"].ToString();
string EmployeeNumber = ResultSet["employeenumber"].ToString();
DateTime HireDate;
DateTime.TryParse(ResultSet["HireDate"].ToString(), out HireDate);
string Salary = 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;
//Adding Teacher Name to the List
Teachers.Add(NewTeacher);
}
//Ending connection between the MySQL Database and the WebServer
Conn.Close();
//Returning the final list of teacher names
return(Teachers);
}
public IEnumerable <Student> ListStudents(string SearchKey = null)
{
//Create an instance of a connection
MySqlConnection Conn = school.AccessDatabase();
//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 Students where lower(studentfname) like lower(@key) or lower(studentlname) like lower(@key) or lower(concat(studentfname,' ', studentlname)) 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 Students
List <Student> Students = new List <Student> {
};
//Loop Through Each Row the Result Set
while (ResultSet.Read())
{
//Access Column information by the DB column name as an index
uint StudentId = (uint)ResultSet["studentid"];
string StudentFname = ResultSet["studentfname"].ToString();
string StudentLname = ResultSet["studentlname"].ToString();
DateTime EnrolDate = DateTime.Parse(ResultSet["enroldate"].ToString());
string StudentNumber = ResultSet["studentnumber"].ToString();
Student NewStudent = new Student();
NewStudent.StudentId = StudentId;
NewStudent.StudentFname = StudentFname;
NewStudent.StudentLname = StudentLname;
NewStudent.EnrolDate = EnrolDate;
NewStudent.StudentNumber = StudentNumber;
//Add the Student Name to the List
Students.Add(NewStudent);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of student names
return(Students);
}
public IEnumerable <Teacher> ListTeachers(string searchKey = null)
{
//Instance of a connection using MySQL object.
MySqlConnection Connection = School.AccessDatabase();
//Establishes connection between web server and the database
Connection.Open();
//Create a new command of SQL.
MySqlCommand cmd = Connection.CreateCommand();
//Required SQL query to find teachers with the particular searchKey
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)";
// Telling the system to search for teacher names that contain the "SearchKey" in any part of it's string
cmd.Parameters.AddWithValue("@key", "%" + searchKey + "%");
//Storing the result in Resultset
MySqlDataReader ResultSet = cmd.ExecuteReader();
//List that stores all the teacher data.
List <Teacher> TeachersNames = new List <Teacher> {
};
while (ResultSet.Read())
{
//Storing each individual value in it's own variable
int TeacherId = (int)ResultSet["teacherid"];
string TeacherfName = (string)ResultSet["teacherfname"];
string TeacherlName = (string)ResultSet["teacherlname"];
DateTime Hiredate = (DateTime)ResultSet["hiredate"];
string TeacherHireDate = Hiredate.ToLongDateString();
double TeacherSalary = Convert.ToDouble(ResultSet["salary"]);
// Creating a NewTeacher object of type Teacher to store the information of one teacher at a time.
Teacher NewTeacher = new Teacher();
//Adding the variables to the NewTeacher object we created
NewTeacher.TeacherId = TeacherId;
NewTeacher.TeacherFname = TeacherfName;
NewTeacher.TeacherLname = TeacherlName;
NewTeacher.TeacherHireDate = TeacherHireDate;
NewTeacher.TeacherSalary = TeacherSalary;
// Storing all this information together in the TeachersNames List
TeachersNames.Add(NewTeacher);
}
//Closing the connection between the database and server
Connection.Close();
//Returning the stored List
return(TeachersNames);
}
public IEnumerable <Student> ListStudents(string searchKey)
{
//Instance of a connection using MySQL object.
MySqlConnection Connection = School.AccessDatabase();
//Establishes connection between web server and the database
Connection.Open();
//Create a new command of SQL.
MySqlCommand cmd = Connection.CreateCommand();
//Required SQL query to find students with the particular searchKey
cmd.CommandText = "Select * from students where lower(studentfname) like lower(@key) or lower(studentlname) like lower(@key) or lower(concat(studentfname, ' ', studentlname)) like lower(@key)";
// Telling the system to search for student names that contain the "SearchKey" in any part of it's string
cmd.Parameters.AddWithValue("@key", "%" + searchKey + "%");
//Storing the result in Resultset
MySqlDataReader ResultSet = cmd.ExecuteReader();
//List that stores all the student data.
List <Student> StudentNames = new List <Student> {
};
while (ResultSet.Read())
{
//Storing each individual value in it's own variable
int StudentId = Convert.ToInt32(ResultSet["studentid"]);
string StudentfName = (string)ResultSet["studentfname"];
string studentlName = (string)ResultSet["studentlname"];
string studentNumber = (string)ResultSet["studentnumber"];
DateTime studentEnrolDate = (DateTime)ResultSet["enroldate"];
string date = studentEnrolDate.ToLongDateString();
// Creating a NewStudent object of type Teacher to store the information of one teacher at a time.
Student NewStudent = new Student();
//Adding the variables to the NewStudent object we created
NewStudent.StudentId = StudentId;
NewStudent.StudentFname = StudentfName;
NewStudent.StudentLname = studentlName;
NewStudent.StudentNumber = studentNumber;
NewStudent.StudentEnrolDate = date;
// Storing all this information together in the StudentNames List
StudentNames.Add(NewStudent);
}
//Closing the connection between the database and server
Connection.Close();
//Returning the stored List
return(StudentNames);
}
public IEnumerable <Teacher> ListTeachers(string SearchKey = null) // since its a list of students, we have to use IEinumerable
{ //Links and creates a connection to mySql database
MySqlConnection Connection = School.AccessDatabase();
//Connection linked and opens between the database and the web server
Connection.Open();
//creates a new command to run the query from the database
MySqlCommand Command = Connection.CreateCommand();
// allows to write a query and send it to the database to retrive the information from teachers table
// query modified inorder to prevent direct input injection attacks
Command.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 hiredate like @key or salary like @key";
Command.Parameters.AddWithValue("@key", "%" + SearchKey + "%");
Command.Prepare();
//COnverts the query and stores it in a variable
MySqlDataReader ResultSet = Command.ExecuteReader();
// creates an empty array to store the listo of teachers
List <Teacher> Teachers = new List <Teacher> {
};
// using while loop o itirate the list information fromt the teachers table
while (ResultSet.Read())
{
int TeacherId = Convert.ToInt32(ResultSet["teacherid"]);
string TeacherFname = (string)ResultSet["teacherfname"];
string TeacherLname = (string)ResultSet["teacherlname"];
string EmployeeNumber = (string)ResultSet["employeenumber"];
DateTime HireDate = (DateTime)ResultSet["hiredate"];
decimal Salary = (decimal)ResultSet["salary"];
//creating a new variable and lining it to the models controller
Teacher NewTeacher = new Teacher();
NewTeacher.TeacherId = TeacherId;
NewTeacher.TeacherFname = TeacherFname;
NewTeacher.TeacherLname = TeacherLname;
NewTeacher.EmployeeNumber = EmployeeNumber;
NewTeacher.HireDate = HireDate;
NewTeacher.Salary = Salary;
//adding the variables to the empty list.
Teachers.Add(NewTeacher);
}
//Closing the connection once the information is retrieved from the database
Connection.Close();
// outputs the lists of teachers to the web browser
return(Teachers);
}
public IEnumerable <Teacher> ListTeachers(string SearchKey = null)
{
//Create an instance of a connection
MySqlConnection Conn = School.AccessDatabase();
//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 hiredate like (@key) or salary like (@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> teachers = 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 = (string)ResultSet["teacherfname"];
string TeacherLname = (string)ResultSet["teacherlname"];
string EmployeeNumber = (string)ResultSet["employeenumber"];
DateTime HireDate = (DateTime)ResultSet["hiredate"];
decimal Salary = (decimal)ResultSet["salary"];
Teacher teacher = new Teacher();
teacher.TeacherId = TeacherId;
teacher.TeacherFname = TeacherFname;
teacher.TeacherLname = TeacherLname;
teacher.EmployeeNumber = EmployeeNumber;
teacher.HireDate = HireDate;
teacher.Salary = Salary;
//Add the Teacher to the List
teachers.Add(teacher);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of teachers names
return(teachers);
}
public IEnumerable <Teacher> ListTeachers(string SearchKey = null)
{
//Create an instance of a connection
MySqlConnection Connection = School.AccessDatabase();
//Open the connection between the web server and database
Connection.Open();
//Establish a new command (query) for our database
MySqlCommand cmd = Connection.CreateCommand();
//SQL QUERY
cmd.CommandText = "Select * from Teachers where UPPER(teacherfname) like UPPER(@key) or UPPER(teacherlname)" +
"like UPPER(@key) or UPPER(CONCAT(teacherfname, ' ', teacherlname)) like UPPER(@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 Authors
List <Teacher> Teachers = 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 = Convert.ToInt32(ResultSet["Teacherid"]);
string TeacherFname = ResultSet["teacherfname"].ToString();
string TeacherLname = ResultSet["teacherlname"].ToString();
Decimal TeacherSalary = (Decimal)ResultSet["salary"];
string TeacherEmployeeNum = ResultSet["employeenumber"].ToString();
DateTime TeacherHireDate = (DateTime)ResultSet["hiredate"];
Teacher NewTeacher = new Teacher();
NewTeacher.TeacherId = TeacherId;
NewTeacher.TeacherFname = TeacherFname;
NewTeacher.TeacherLname = TeacherLname;
NewTeacher.TeacherEmployeeNum = TeacherEmployeeNum;
NewTeacher.TeacherHireDate = TeacherHireDate;
NewTeacher.TeacherSalary = TeacherSalary;
//Add the Teacher Name to the List
Teachers.Add(NewTeacher);
}
//Close the connection between the MySQL Database and the WebServer
Connection.Close();
//Return the final list of author names
return(Teachers);
}
public IEnumerable <Teacher> ListTeachers(string SearchKey = null)
{
//debug comments for searching the teacher table
Debug.WriteLine("I am looking for ");
Debug.WriteLine(SearchKey);
//connects to database and establishes connection between server & database
MySqlConnection Conn = Teacher.AccessDatabase();
Conn.Open();
MySqlCommand cmd = Conn.CreateCommand();
//select query from teachers table with search function
cmd.CommandText = "SELECT teachers.teacherid, teachers.teacherfname, teachers.teacherlname, teachers.employeenumber, teachers.hiredate, teachers.salary FROM teachers WHERE teachers.teacherfname LIKE @key OR teachers.teacherlname LIKE @key OR (concat(teacherfname, ' ', teacherlname)) LIKE @key";
//sql parameters to avoid injections
cmd.Parameters.AddWithValue("@key", "%" + SearchKey + "%");
cmd.Prepare();
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Creating a new list for classes
List <Teacher> Teachers = new List <Teacher> {
};
while (ResultSet.Read())
{
int TeacherId = (int)ResultSet["teacherid"];
string TeacherFname = (string)ResultSet["teacherfname"];
string TeacherLname = (string)ResultSet["teacherlname"];
string EmployeeNum = (string)ResultSet["employeenumber"];
DateTime HireDate = (DateTime)ResultSet["hiredate"];
decimal Salary = (decimal)ResultSet["salary"];
Teacher newTeacher = new Teacher();
newTeacher.TeacherId = TeacherId;
newTeacher.TeacherFname = TeacherFname;
newTeacher.TeacherLname = TeacherLname;
newTeacher.EmployeeNum = EmployeeNum;
newTeacher.HireDate = HireDate;
newTeacher.Salary = Salary;
//Adds a new teacher to the list
Teachers.Add(newTeacher);
}
//Closes connection to MySql database
Conn.Close();
//Returns list of all teachers
return(Teachers);
}
public IEnumerable <Class> ListClasses()
{
//Create an instance of a connection
MySqlConnection Conn = School.AccessDatabase();
//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
string query = "Select * from classes";
cmd.CommandText = query;
//Gather Result Set of Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Classes
List <Class> Classes = new List <Class> {
};
//Loop Through Each Row the Result Set
while (ResultSet.Read())
{
//Access Column information by the DB column name as an index
int ClassId = Convert.ToInt32(ResultSet["classid"]);
string ClassName = ResultSet["classname"].ToString();
string ClassCode = ResultSet["classcode"].ToString();
DateTime ClassStartdate = (DateTime)ResultSet["startdate"];
DateTime ClassFinishdate = (DateTime)ResultSet["finishdate"];
Class NewClass = new Class();
NewClass.ClassId = ClassId;
NewClass.ClassName = ClassName;
NewClass.ClassCode = ClassCode;
NewClass.ClassStartdate = ClassStartdate;
NewClass.ClassFinishdate = ClassFinishdate;
//Add the Class Name to the List
Classes.Add(NewClass);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of class names
return(Classes);
}
public IEnumerable <Teacher> ListTeachers(string SearchKey = null)
{
// Instance of Connection
MySqlConnection Conn = School.AccessDatabase();
// Open connection between web server and database
Conn.Open();
// Create new query for 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)";
// Parameters to protect Query from SQL Injection Attacks
cmd.Parameters.AddWithValue("@key", "%" + SearchKey + "%");
cmd.Prepare();
// Variable that stores the results from the SQL Query
MySqlDataReader ResultSet = cmd.ExecuteReader();
// Empty list of Teachers
List <Teacher> Teachers = new List <Teacher> {
};
// Loop through Result Set rows and add to teachers list
while (ResultSet.Read())
{
// New Teacher Object
Teacher NewTeacher = new Teacher();
// Access the Columns in the Teachers table
NewTeacher.TeacherId = Convert.ToInt32(ResultSet["teacherid"]);
NewTeacher.TeacherFname = ResultSet["teacherfname"].ToString();
NewTeacher.TeacherLname = ResultSet["teacherlname"].ToString();
NewTeacher.EmployeeNumber = ResultSet["employeenumber"].ToString();
NewTeacher.HireDate = Convert.ToDateTime(ResultSet["hiredate"]);
NewTeacher.Salary = Convert.ToDecimal(ResultSet["salary"]);
// Add the NewTeacher to empty list
Teachers.Add(NewTeacher);
}
// Close connection
Conn.Close();
// Return populated list
return(Teachers);
}
public IEnumerable <Teacher> ListTeachers(string SearchKey = null)
{
//Create an instance of a connection
MySqlConnection Conn = School.AccessDatabase();
//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)";
//Preventing SQL Injection Attack
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> Teachers = new List <Teacher> {
};
//Loop Through Each Row the Result Set
while (ResultSet.Read())
{
Teacher NewTeacher = new Teacher();
//Access Column information by the DB column name as an index
NewTeacher.TeacherId = Convert.ToInt32(ResultSet["teacherid"]);
NewTeacher.TeacherFname = ResultSet["teacherfname"].ToString();
NewTeacher.TeacherLname = ResultSet["teacherlname"].ToString();
NewTeacher.EmployeeNumber = ResultSet["employeenumber"].ToString();
NewTeacher.HireDate = Convert.ToDateTime(ResultSet["hiredate"]);
NewTeacher.Salary = Convert.ToDouble(ResultSet["salary"]);
//Add the Teacher Name to the List
Teachers.Add(NewTeacher);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of Teachers
return(Teachers);
}
public IEnumerable <Teacher> ListTeacher(string SearchKey = null)
{
//Creates an instance of a connection
MySqlConnection Conn = School.AccessDatabase();
//Opens connection between web server and database
Conn.Open();
//Establish a new command for 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)) or (salary like @key) or (hiredate like @key)";
cmd.Parameters.AddWithValue("@key", "%" + SearchKey + "%");
//Gather Query result into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Creates an empty list of Teacher
List <Teacher> Teachers = new List <Teacher> {
};
//Loop Through Each Row of the Result Set
while (ResultSet.Read())
{
//Access Column info by the DB column name as an index
int TeacherId = (int)ResultSet["teacherid"];
string TeacherFname = (String)ResultSet["teacherfname"];
string TeacherLname = (String)ResultSet["teacherlname"];
string EmployeeNumber = (String)ResultSet["employeenumber"];
DateTime HireDate = (DateTime)ResultSet["hiredate"];
decimal Salary = (decimal)ResultSet["salary"];
Teacher NewTeacher = new Teacher();
NewTeacher.TeacherId = TeacherId;
NewTeacher.TeacherFname = TeacherFname;
NewTeacher.TeacherLname = TeacherLname;
NewTeacher.EmployeeNumber = EmployeeNumber;
NewTeacher.HireDate = HireDate;
NewTeacher.Salary = Salary;
//Add the teacher name to the list
Teachers.Add(NewTeacher);
}
//Closes connection between the MySQL Database and the WebServer
Conn.Close();
//Return final list of teacher names
return(Teachers);
}
public IEnumerable <Teacher> ListTeachers(string SearchKey = null)
{
//We need to connect to datadase
MySqlConnection Conn = school.AccessDatabase();
//This method opens connection between the web server and the database
Conn.Open();
//The following method established a new command (query) for the school 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 + "%");
//Gather Result Set of Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Teachers
List <Teacher> Teachers = new List <Teacher> {
};
//Loop through an empty list of teacher names
while (ResultSet.Read())
{
//Access column data by the databse column name as an index
int TeacherId = (int)ResultSet["teacherid"];
string TeacherFname = (string)ResultSet["teacherfname"];
string TeacherLname = (string)ResultSet["teacherlname"];
string TeacherEnumber = (string)ResultSet["employeenumber"];
DateTime TeacherHdate = (DateTime)ResultSet["hiredate"];
decimal TeacherSalary = (decimal)ResultSet["salary"];
Teacher NewTeacher = new Teacher();
//Setting the properties of a new Teacher object as an extentsiation of the Teacher class
//to match the properties we have retrieved from the database.
NewTeacher.TeacherId = TeacherId;
NewTeacher.TeacherFname = TeacherFname;
NewTeacher.TeacherLname = TeacherLname;
NewTeacher.TeacherEnumber = TeacherEnumber;
NewTeacher.TeacherHdate = TeacherHdate;
NewTeacher.TeacherSalary = TeacherSalary;
//Add a teacher name to the list
Teachers.Add(NewTeacher);
}
//This method closes the connection between the MySQL Database and the Webserver
Conn.Close();
//Return the final list of teacher names
return(Teachers);
}
public IEnumerable <Teacher> ListTeachers()
{
//Create an instance of a connection
MySqlConnection Conn = School.AccessDatabase();
//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";
//Gather Result Set of Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Teachers
List <Teacher> Teachers = 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 = (string)ResultSet["teacherfname"];
string TeacherLname = (string)ResultSet["teacherlname"];
string EmployeeNumber = (string)ResultSet["employeenumber"];
DateTime HireDate = (DateTime)ResultSet["hiredate"];
decimal Salary = (decimal)ResultSet["salary"];
Teacher NewTeacher = new Teacher();
NewTeacher.TeacherId = TeacherId;
NewTeacher.TeacherFname = TeacherFname;
NewTeacher.TeacherLname = TeacherLname;
NewTeacher.EmployeeNumber = EmployeeNumber;
NewTeacher.HireDate = HireDate;
NewTeacher.Salary = Salary;
//Add the Teacher Name to the List
Teachers.Add(NewTeacher);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of teacher names
return(Teachers);
}
public IEnumerable <Student> ListStudents()
{
//Create an instance of a connection
MySqlConnection Conn = School.AccessDatabase();
//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
string query = "Select * from students";
cmd.CommandText = query;
//Gather Result Set of Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Students
List <Student> Students = new List <Student> {
};
//Loop Through Each Row the Result Set
while (ResultSet.Read())
{
//Access Column information by the DB column name as an index
int StudentId = Convert.ToInt32(ResultSet["studentid"]);
string StudentFName = ResultSet["studentfname"].ToString();
string StudentLName = ResultSet["studentlname"].ToString();
string StudentStudentnumber = ResultSet["studentnumber"].ToString();
DateTime StudentEnroldate = (DateTime)ResultSet["enroldate"];
Student NewStudent = new Student();
NewStudent.StudentId = StudentId;
NewStudent.StudentFname = StudentFName;
NewStudent.StudentLname = StudentLName;
NewStudent.StudentStudentnumber = StudentStudentnumber;
NewStudent.StudentEnroldate = StudentEnroldate;
//Add the Student Name to the List
Students.Add(NewStudent);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of students names
return(Students);
}
public IEnumerable <Teacher> ListTeachers(string search = null)
{
// creat an instance of a connection
MySqlConnection conn = school.AccessDatabase();
{
//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", "%" + search + "%");
cmd.Prepare();
//Gather Result Set of Query into a variable
MySqlDataReader ResultList = cmd.ExecuteReader();
//creat an empty list teacher names;
List <Teacher> TeacherInformation = new List <Teacher> {
};
//Loop Through Each Row the Result Set;
while (ResultList.Read())
{ //Access column information by the DB column name as an index;
int TeacherId = (int)ResultList["teacherid"];
string TeacherFName = (string)ResultList["teacherfname"];
string TeacherLname = (string)ResultList["teacherlname"];
string EmployeeNumber = (string)ResultList["employeenumber"];
string HireDate = ResultList["hiredate"].ToString();
decimal Salary = (decimal)ResultList["salary"];
Teacher teacher = new Teacher();
teacher.teacherid = TeacherId;
teacher.teacherfname = TeacherFName;
teacher.teacherlname = TeacherLname;
teacher.employeenumber = EmployeeNumber;
teacher.hiredate = HireDate;
teacher.salary = Salary;
//Add Teacher to the list
TeacherInformation.Add(teacher);
}
;
//Close the connection between the MySQL Database and the WebServer
conn.Close();
return(TeacherInformation);
}
}
public IEnumerable <Course> ListCourses(string SearchKey = null)
{
//Create an instance of a connection
MySqlConnection Connection = School.AccessDatabase();
//Open the connection between the web server and database
Connection.Open();
//Establish a new command (query) for our database
MySqlCommand cmd = Connection.CreateCommand();
//SQL QUERY
cmd.CommandText = "Select * from classes where UPPER(classname) like UPPER(@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 Authors
List <Course> Courses = new List <Course> {
};
//Loop Through Each Row the Result Set
while (ResultSet.Read())
{
//Access Column information by the DB column name as an index
int CourseId = Convert.ToInt32(ResultSet["classid"]);
string CourseName = ResultSet["classname"].ToString();
string CourseCode = ResultSet["classcode"].ToString();
DateTime CourseStartDate = (DateTime)ResultSet["startdate"];
DateTime CourseFinishDate = (DateTime)ResultSet["finishdate"];
Course NewCourse = new Course();
NewCourse.CourseId = CourseId;
NewCourse.CourseName = CourseName;
NewCourse.CourseCode = CourseCode;
NewCourse.CourseStartDate = CourseStartDate;
NewCourse.CourseFinishDate = CourseFinishDate;
//Add the class Name to the List
Courses.Add(NewCourse);
}
//Close the connection between the MySQL Database and the WebServer
Connection.Close();
//Return the final list of classes names
return(Courses);
}
public IEnumerable <Class> ListClasses(string SearchKey = null)
{
//Creates an instance of a connection
MySqlConnection Conn = School.AccessDatabase();
//Opens connection between the web server and database
Conn.Open();
//Establish a new command for database
MySqlCommand cmd = Conn.CreateCommand();
//SQL Query
cmd.CommandText = "Select * from Classes where (lower(classcode) like lower(@key)) or (lower(classname) like lower(@key))";
cmd.Parameters.AddWithValue("@key", "%" + SearchKey + "%");
//Gather Query result into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Students
List <Class> Classes = new List <Class> {
};
//Loop Through Each Row of the Result Set
while (ResultSet.Read())
{
//Access Column info by the DB column name as an index
int ClassId = (int)ResultSet["classid"];
string ClassCode = (String)ResultSet["classcode"];
string ClassName = (String)ResultSet["classname"];
DateTime StartDate = (DateTime)ResultSet["startdate"];
DateTime FinishDate = (DateTime)ResultSet["finishdate"];
Class NewClass = new Class();
NewClass.ClassId = ClassId;
NewClass.ClassCode = ClassCode;
NewClass.ClassName = ClassName;
NewClass.StartDate = StartDate;
NewClass.FinishDate = FinishDate;
//Add the student name to the list
Classes.Add(NewClass);
}
//Closes connection between the MySQL Database and the WebServer
Conn.Close();
//Return final list of student names
return(Classes);
}
public IEnumerable <Class> ListClasses(string SearchKey = null)
{
// Instance of Connection
MySqlConnection Conn = School.AccessDatabase();
// Open connection between web server and database
Conn.Open();
// Create new query for database
MySqlCommand cmd = Conn.CreateCommand();
// SQL Query
cmd.CommandText = "SELECT * FROM classes WHERE LOWER(classname) LIKE LOWER(@key)" +
"OR LOWER(classcode) LIKE LOWER(@key)";
// Parameters to protect Query from SQL Injection Attacks
cmd.Parameters.AddWithValue("@key", "%" + SearchKey + "%");
cmd.Prepare();
// Variable thats stores the results from the SQL Querys
MySqlDataReader ResultSet = cmd.ExecuteReader();
// Empty list of Classes
List <Class> Classes = new List <Class> {
};
// Loop through Result set rows and add to classes lists
while (ResultSet.Read())
{
// New Class Object
Class NewClass = new Class();
// Access the Columns in the Classes tab;es
NewClass.ClassId = Convert.ToInt32(ResultSet["classid"]);
NewClass.ClassCode = ResultSet["classcode"].ToString();
NewClass.ClassName = ResultSet["classname"].ToString();
NewClass.StartDate = Convert.ToDateTime(ResultSet["startdate"]);
NewClass.FinishDate = Convert.ToDateTime(ResultSet["finishdate"]);
// Add the NewClass to empty Classes list
Classes.Add(NewClass);
}
// Close Connection
Conn.Close();
// Return populated list
return(Classes);
}
public IEnumerable <Class> ListClasses()
{
//Links and creates a connection to mySql database
MySqlConnection Connection = School.AccessDatabase();
//Connection linked and opens between the database and the web server
Connection.Open();
//creates a new command to run the query from the database
MySqlCommand Command = Connection.CreateCommand();
// allows to write a query and send it to the database to retrive the information from Classes table
Command.CommandText = "Select * from Classes";
//Converts the query and stores it in a variable
MySqlDataReader ResultSet = Command.ExecuteReader();
// creates an empty array to store the list of Classes
List <Class> Classes = new List <Class> {
};
// using while loop to itirate the list information fromt the students table
while (ResultSet.Read())
{
int ClassId = Convert.ToInt32(ResultSet["classid"]);
string ClassCode = (string)ResultSet["classcode"];
int TeacherId = Convert.ToInt32(ResultSet["teacherid"]);
DateTime StartDate = (DateTime)ResultSet["startdate"];
DateTime FinishDate = (DateTime)ResultSet["finishdate"];
string ClassName = (string)ResultSet["classname"];
//creating a new variable and lining it to the models controller
Class NewClass = new Class();
NewClass.ClassId = ClassId;
NewClass.ClassCode = ClassCode;
NewClass.TeacherId = TeacherId;
NewClass.StartDate = StartDate;
NewClass.FinishDate = FinishDate;
NewClass.ClassName = ClassName;
//adding the variables to the empty list.
Classes.Add(NewClass);
}
//Closing the connection once the information is retrieved from the database
Connection.Close();
// outputs the lists of students to the web browser
return(Classes);
}
public IEnumerable <Student> ListStudents(string SearchKey = null)
{
// Instance of Connection
MySqlConnection Conn = School.AccessDatabase();
// Open connection
Conn.Open();
// Create new Query for database
MySqlCommand cmd = Conn.CreateCommand();
// SQL Query
cmd.CommandText = "SELECT * FROM students WHERE LOWER(studentfname) LIKE LOWER(@key) " +
"OR LOWER(studentlname) LIKE LOWER(@key) " +
"OR LOWER(CONCAT(studentfname, ' ', studentlname)) LIKE LOWER(@key)";
// Parameters to protect Query from SQL Injection Attacks
cmd.Parameters.AddWithValue("key", "%" + SearchKey + "%");
cmd.Prepare();
// Store results from Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
// Empty List of Students
List <Student> Students = new List <Student> {
};
// Loop through Result Set rows
while (ResultSet.Read())
{
// New Student Object
Student NewStudent = new Student();
// Access the Columns in the Students table
NewStudent.StudentId = Convert.ToInt32(ResultSet["studentid"]);
NewStudent.StudentFname = ResultSet["studentfname"].ToString();
NewStudent.StudentLname = ResultSet["studentlname"].ToString();
NewStudent.StudentNumber = ResultSet["studentnumber"].ToString();
NewStudent.EnrollDate = Convert.ToDateTime(ResultSet["enroldate"]);
// Add students to empty list
Students.Add(NewStudent);
}
// Close connection
Conn.Close();
return(Students);
}
public IEnumerable <Student> ListStudents(string SearchKey = null)
{
//Creates an instance of a connection
MySqlConnection Conn = School.AccessDatabase();
//Opens connection between the web server and database
Conn.Open();
//Establish a new command for database
MySqlCommand cmd = Conn.CreateCommand();
//SQL Query
cmd.CommandText = "Select * from students where (lower(studentfname) like lower(@key)) OR (lower(studentlname) like lower(@key)) or (lower(concat(studentfname,' ', studentlname)) like lower(@key)) or (studentnumber like @key) or (enroldate like @key)";
cmd.Parameters.AddWithValue("@key", "%" + SearchKey + "%");
//Gather Query result into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Students
List <Student> Students = new List <Student> {
};
//Loop Through Each Row of the Result Set
while (ResultSet.Read())
{
//Access Column info by the DB column name as an index
int StudentId = Convert.ToInt32(ResultSet["studentid"]);
string StudentFname = (String)ResultSet["studentfname"];
string StudentLname = (String)ResultSet["studentlname"];
string StudentNumber = (String)ResultSet["studentnumber"];
DateTime EnrolDate = (DateTime)ResultSet["enroldate"];
Student NewStudent = new Student();
NewStudent.StudentId = StudentId;
NewStudent.StudentFname = StudentFname;
NewStudent.StudentLname = StudentLname;
NewStudent.StudentNumber = StudentNumber;
NewStudent.EnrolDate = EnrolDate.Date;
//Add the student name to the list
Students.Add(NewStudent);
}
//Closes connection between the MySQL Database and the WebServer
Conn.Close();
//Return final list of student names
return(Students);
}
public IEnumerable <Course> ListCourses(string SearchKey = null)
{
//connects to database and establishes connection between server & database
MySqlConnection Conn = Course.AccessDatabase();
Conn.Open();
MySqlCommand cmd = Conn.CreateCommand();
//select query with a join with the teachers table
cmd.CommandText = "SELECT classid, classname, classcode, startdate, finishdate FROM classes WHERE classname LIKE @key OR classcode LIKE @key";
//sql parameters to avoid injections
cmd.Parameters.AddWithValue("@key", "%" + SearchKey + "%");
cmd.Prepare();
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Creating a new list for classes
List <Course> Courses = new List <Course> {
};
while (ResultSet.Read())
{
int CourseId = (int)ResultSet["classid"];
string CourseName = (string)ResultSet["classname"];
string CourseCode = (string)ResultSet["classcode"];
DateTime StartDate = (DateTime)ResultSet["startdate"];
DateTime FinishDate = (DateTime)ResultSet["finishdate"];
Course newCourse = new Course();
newCourse.CourseId = CourseId;
newCourse.CourseName = CourseName;
newCourse.CourseCode = CourseCode;
newCourse.StartDate = StartDate;
newCourse.FinishDate = FinishDate;
//Adds a new class to the list
Courses.Add(newCourse);
}
//Closes connection to MySql database
Conn.Close();
//Returns list of all Classes
return(Courses);
}
