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); }