/// <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)
{
}
