/// <summary> /// Get all Teachers /// </summary> /// <returns></returns> public async static Task <List <Teacher> > GetAllAsync() { OleDbConnection conn; OleDbCommand command; DbDataReader reader; List <Teacher> teachers = new List <Teacher>(); conn = new OleDbConnection(AppConfiguration.connectionString); conn.Open(); command = new OleDbCommand("SELECT Teachers.ID, Users.ID, Users.FirstName, Users.SecondName, Users.BirthDate, Users.Password FROM Teachers INNER JOIN Users ON Teachers.USER_ID=Users.ID", conn); reader = await command.ExecuteReaderAsync(); while (reader.Read()) { BasicUserProperties properties; properties = new BasicUserProperties(); properties.FirstName = reader.GetString(2); properties.SecondName = reader.GetString(3); properties.BirthDate = reader.GetDateTime(4); properties.Password = reader.GetString(5); teachers.Add(new Teacher(properties, reader.GetGuid(1), reader.GetGuid(0))); } reader.Close(); conn.Close(); return(teachers); }
/// <summary> /// Retrives the student associated with this class. /// </summary> /// <returns></returns> public async Task <List <Student> > GetStudentsAsync() { OleDbConnection conn; OleDbCommand command; List <Student> students = new List <Student>(); DbDataReader reader; Guid id = this.ID; conn = new OleDbConnection(AppConfiguration.connectionString); conn.Open(); command = new OleDbCommand("SELECT Students.*, Users.FirstName, Users.SecondName, Users.BirthDate, Users.Password FROM (( Students INNER JOIN Users ON Students.USER_ID=Users.ID) INNER JOIN Classes ON Students.CLASS_ID=Classes.ID) WHERE Classes.ID = @ID", conn); command.Parameters.AddWithValue("ID", id); reader = await command.ExecuteReaderAsync(); while (reader.Read()) { BasicUserProperties properties; properties = new BasicUserProperties(); properties.FirstName = reader.GetString(3); properties.SecondName = reader.GetString(4); properties.BirthDate = reader.GetDateTime(5); students.Add(new Student(properties, this, reader.GetGuid(2), reader.GetGuid(0))); } reader.Close(); conn.Close(); return(students); }
/// <summary> /// Retrives the teacher associated with this class. /// </summary> /// <returns></returns> public async Task <List <Teacher> > GetTeachersAsync() { OleDbConnection conn; OleDbCommand command; List <Teacher> teacher = new List <Teacher>(); DbDataReader reader; Guid id = this.ID; conn = new OleDbConnection(AppConfiguration.connectionString); conn.Open(); command = new OleDbCommand("SELECT Teachers.*, Users.FirstName, Users.SecondName, Users.BirthDate, Users.Password FROM (((Teachers INNER JOIN Users ON Teacher.USER_ID=Teachers.ID) INNER JOIN TeachesClass ON TeachesClass.TEACHER_ID=Teachers.ID) INNER JOIN Classes ON TeachesClass.CLASS_ID=Classes.ID) WHERE Classes.ID=@ID'", conn); command.Parameters.AddWithValue("ID", id); reader = await command.ExecuteReaderAsync(); while (reader.Read()) { BasicUserProperties properties; properties = new BasicUserProperties(reader.GetString(3), reader.GetString(4), reader.GetDateTime(5), reader.GetString(6)); teacher.Add(new Teacher(properties, reader.GetGuid(1), reader.GetGuid(0))); } reader.Close(); conn.Close(); return(teacher); }
/// <summary> /// Get all Students /// </summary> /// <returns></returns> public async static Task <List <Student> > GetAllAsync() { OleDbConnection conn; OleDbCommand command; DbDataReader reader; BasicUserProperties properties; Class sClass; List <Student> students = new List <Student>(); conn = new OleDbConnection(AppConfiguration.connectionString); conn.Open(); command = new OleDbCommand("SELECT Students.ID, Classes.ID, Users.ID, Users.FirstName, Users.SecondName, Users.BirthDate, Classes.SchoolYear, Classes.Course FROM (Students INNER JOIN Users ON Students.USER_ID=Users.ID)INNER JOIN Classes ON Students.CLASS_ID=Classes.ID", conn); reader = await command.ExecuteReaderAsync(); while (reader.Read()) { properties = new BasicUserProperties(); properties.FirstName = reader.GetString(3); properties.SecondName = reader.GetString(4); properties.BirthDate = reader.GetDateTime(5); sClass = new Class(reader.GetString(6), reader.GetString(7), reader.GetGuid(1)); students.Add(new Student(properties, sClass, reader.GetGuid(2), reader.GetGuid(0))); } reader.Close(); conn.Close(); return(students); }
/// <summary> /// Can be used to get a teacher of this topic. /// </summary> /// <returns></returns> public async Task <Teacher> GetTeacherAsync() { OleDbConnection conn; OleDbCommand command; DbDataReader reader; Guid id = this.ID; conn = new OleDbConnection(AppConfiguration.connectionString); conn.Open(); command = new OleDbCommand("SELECT Teachers.*, Users.FirstName, Users.SecondName, Users.BirthDate, Users.Password FROM ((Teachers INNER JOIN Topics ON Topics.TEACHER_ID=Teachers.ID) INNER JOIN Users ON Teachers.USER_ID=Users.ID) WHERE Topics.ID=@ID'", conn); command.Parameters.AddWithValue("ID", ID); reader = await command.ExecuteReaderAsync(); if (reader.Read()) { BasicUserProperties properties; Teacher teacher; properties = new BasicUserProperties(); properties.FirstName = reader.GetString(2); properties.SecondName = reader.GetString(3); properties.Password = reader.GetString(4); teacher = new Teacher(properties, reader.GetGuid(1), reader.GetGuid(0)); reader.Close(); conn.Close(); return(teacher); } else { throw new Exception("Query result not correct"); } }
/// <summary> /// Retrives the student associated with this evaluation. /// </summary> /// <returns></returns> public async Task <Student> GetStudentAsync() { OleDbConnection conn; OleDbCommand command; Guid id = this.ID; conn = new OleDbConnection(AppConfiguration.connectionString); conn.Open(); command = new OleDbCommand("SELECT Students.*, Users.FirstName, Users.SecondName, Users.BirthDate, Users.Password FROM ((Students INNER JOIN Users ON Students.USER_ID=Users.ID) INNER JOIN Evaluations ON Evaluations.STUDENT_ID=Students.ID) WHERE Evaluations.ID = @ID", conn); command.Parameters.AddWithValue("ID", id); DbDataReader reader = await command.ExecuteReaderAsync(); if (reader.Read()) { BasicUserProperties properties; Student student; properties = new BasicUserProperties(); properties.FirstName = reader.GetString(3); properties.SecondName = reader.GetString(4); properties.BirthDate = reader.GetDateTime(5); student = new Student(properties, reader.GetGuid(1), reader.GetGuid(2), reader.GetGuid(0)); reader.Close(); conn.Close(); return(student); } else { throw new Exception("Query result not correct"); } }
/// <summary> /// Constructs new teacher from properties. (existing record) /// </summary> /// <param name="firstName"></param> /// <param name="secondName"></param> /// <param name="birthDate"></param> /// <param name="classID"></param> public Teacher(BasicUserProperties basicProperties, Guid userID, Guid ID) : base( FromArgs(new KeyValuePair <string, object>("FirstName", basicProperties.FirstName) , new KeyValuePair <string, object>("SecondName", basicProperties.SecondName) , new KeyValuePair <string, object>("Password", basicProperties.Password) , new KeyValuePair <string, object>("Teachers.ID", ID) , new KeyValuePair <string, object>("BirthDate", basicProperties.BirthDate) , new KeyValuePair <string, object>("Users.ID", userID)) ) { }
/// <summary> /// Constructs new student from properties. (non-existing record) /// </summary> /// <param name="firstName"></param> /// <param name="secondName"></param> /// <param name="birthDate"></param> /// <param name="classID"></param> public Student(BasicUserProperties basicProperties) : base( FromArgs(new KeyValuePair <string, object>("FirstName", basicProperties.FirstName) , new KeyValuePair <string, object>("SecondName", basicProperties.SecondName) , new KeyValuePair <string, object>("BirthDate", basicProperties.BirthDate) , new KeyValuePair <string, object>("Students.ID", new Guid()) , new KeyValuePair <string, object>("Users.ID", new Guid()) , new KeyValuePair <string, object>("Password", null)) ) { }
/// <summary> /// Create an user by passing properties. /// </summary> /// <param name="basicProperties">Required user properties.</param> public User(BasicUserProperties basicProperties) { this.Properties = new Dictionary <string, object>( FromArgs( new KeyValuePair <string, object>("Users.ID", new Guid()) , new KeyValuePair <string, object>("FirstName", basicProperties.FirstName) , new KeyValuePair <string, object>("SecondName", basicProperties.SecondName) , new KeyValuePair <string, object>("Password", basicProperties.Password) , new KeyValuePair <string, object>("BirthDate", basicProperties.BirthDate)) ); }
/// <summary> /// Constructs new administrator from properties. (non-existing record) /// </summary> /// <param name="firstName"></param> /// <param name="secondName"></param> /// <param name="birthDate"></param> /// <param name="classID"></param> public Administrator(BasicUserProperties basicProperties) : base( FromArgs(new KeyValuePair <string, object>("FirstName", basicProperties.FirstName) , new KeyValuePair <string, object>("SecondName", basicProperties.SecondName) , new KeyValuePair <string, object>("Password", basicProperties.Password) , new KeyValuePair <string, object>("Administrators.ID", new Guid()) , new KeyValuePair <string, object>("BirthDate", basicProperties.BirthDate) , new KeyValuePair <string, object>("Users.ID", new Guid())) ) { }
/// <summary> /// Constructs new student from properties. (existing record) /// </summary> /// <param name="firstName"></param> /// <param name="secondName"></param> /// <param name="birthDate"></param> /// <param name="classID"></param> public Student(BasicUserProperties basicProperties, Class sClass, Guid userID, Guid ID) : base( FromArgs(new KeyValuePair <string, object>("FirstName", basicProperties.FirstName) , new KeyValuePair <string, object>("SecondName", basicProperties.SecondName) , new KeyValuePair <string, object>("BirthDate", basicProperties.BirthDate) , new KeyValuePair <string, object>("Students.ID", ID) , new KeyValuePair <string, object>("Users.ID", userID) , new KeyValuePair <string, object>("Password", null)) ) { _class = sClass; }
/// <summary> /// Retrieves the student corresponding to the passed ID. /// </summary> /// <param name="classID"></param> /// <returns></returns> public static Student FromID(Guid studentID) { using (OleDbConnection conn = new OleDbConnection(AppConfiguration.connectionString)) using (OleDbCommand command = new OleDbCommand()) { conn.Open(); command.Connection = conn; command.CommandText = "SELECT * FROM (Students INNER JOIN Users ON Students.USER_ID=Users.ID)INNER JOIN Classes ON Students.CLASS_ID=Classes.ID WHERE ID=@StudentID"; command.Parameters.AddWithValue("StudentID", studentID); using (DbDataReader reader = command.ExecuteReader()) { reader.Read(); BasicUserProperties properties = new BasicUserProperties(); properties.FirstName = reader.GetString(5); properties.SecondName = reader.GetString(6); properties.BirthDate = reader.GetDateTime(7); return(new Student(properties, reader.GetGuid(1), reader.GetGuid(2), reader.GetGuid(0))); } } }
/// <summary> /// Retrieves the teacher corresponding to the passed ID. /// </summary> /// <param name="classID"></param> /// <returns></returns> public static Teacher FromID(Guid teacherID) { using (OleDbConnection conn = new OleDbConnection(AppConfiguration.connectionString)) using (OleDbCommand command = new OleDbCommand()) { conn.Open(); command.Connection = conn; command.CommandText = "SELECT * FROM Teachers INNER JOIN Users ON Teachers.USER_ID=Users.ID WHERE ID=@TeacherID"; command.Parameters.AddWithValue("TeacherID", teacherID); using (DbDataReader reader = command.ExecuteReader()) { reader.Read(); BasicUserProperties properties = new BasicUserProperties(); properties.FirstName = reader.GetString(3); properties.SecondName = reader.GetString(4); properties.BirthDate = reader.GetDateTime(5); properties.Password = reader.GetString(6); return(new Teacher(properties, reader.GetGuid(1), reader.GetGuid(0))); } } }
/// <summary> /// Constructs new student from properties. (existing record) /// </summary> /// <param name="firstName"></param> /// <param name="secondName"></param> /// <param name="birthDate"></param> /// <param name="classID"></param> public Student(BasicUserProperties basicProperties, Guid classID, Guid userID, Guid ID) : this(basicProperties, Class.FromID(classID), userID, ID) { }