public static List <Teacher> GetTeachers()
{
List <Teacher> teachers = null;
using (OleDbConnection conn = ConnectionDatabase.GetConnection())
{
conn.Open();
string sql = "SELECT teacher.ID, Nimi, Isikukood, Telefon, Aadress, group.NimiRuhm FROM[group] INNER JOIN teacher ON group.ID = teacher.Ruhm";
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataReader reader = cmd.ExecuteReader();
teachers = new List <Teacher>();
using (reader)
{
while (reader.Read())
{
Teacher teacher = new Teacher();
Ruhm ruhm = new Ruhm();
teacher.ID = (int)reader[0];
teacher.Nimi = reader[1].ToString();
teacher.Isikukood = reader[2].ToString();
teacher.Telefon = reader[3].ToString();
teacher.Aadress = reader[4].ToString();
ruhm.NimiRuhm = reader[5].ToString();
teacher.Ruhm = ruhm;
teachers.Add(teacher);
}
}
}
return(teachers);
}
public static List <Student> GetStudents()
{
List <Student> students = null;
using (OleDbConnection conn = ConnectionDatabase.GetConnection())
{
conn.Open();
string sql = "SELECT student.ID, Nimi, Isikukood, Kool, Klass, Telefon, Aadress, group.NimiRuhm FROM[group] INNER JOIN student ON group.ID = student.Ruhm";
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataReader reader = cmd.ExecuteReader();
students = new List <Student>();
using (reader)
{
while (reader.Read())
{
Student student = new Student();
Ruhm ruhm = new Ruhm();
student.ID = (int)reader[0];
student.Nimi = reader[1].ToString();
student.Isikukood = reader[2].ToString();
student.Kool = reader[3].ToString();
student.Klass = (int)reader[4];
student.Telefon = reader[5].ToString();
student.Aadress = reader[6].ToString();
ruhm.NimiRuhm = reader[7].ToString();
student.Ruhm = ruhm;
students.Add(student);
}
}
}
return(students);
}
public static int InsertNewRuhm(Ruhm ruhm)
{
using (OleDbConnection conn = ConnectionDatabase.GetConnection())
{
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "INSERT INTO [group] (NimiRuhm)" + "VALUES (@nimiruhm)";
cmd.Parameters.AddWithValue("@nimiruhm", ruhm.NimiRuhm);
cmd.Connection = conn;
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch
{
return(0);
}
}
return(1);
}
public static int UpdateRuhm(Ruhm ruhm, int id)
{
using (OleDbConnection conn = ConnectionDatabase.GetConnection())
{
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE [group] SET NimiRuhm = @nimi WHERE ID = @id";
cmd.Parameters.AddWithValue("@nimi", ruhm.NimiRuhm);
cmd.Parameters.AddWithValue("@id", id);
cmd.Connection = conn;
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch
{
return(0);
}
}
return(1);
}
public static List <Ruhm> GetRuhms()
{
List <Ruhm> ruhms = null;
using (OleDbConnection conn = ConnectionDatabase.GetConnection())
{
conn.Open();
string sql = "SELECT * FROM [group]";
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataReader reader = cmd.ExecuteReader();
ruhms = new List <Ruhm>();
using (reader)
{
while (reader.Read())
{
Ruhm ruhm = new Ruhm();
ruhm.ID = (int)reader[0];
ruhm.NimiRuhm = reader[1].ToString();
ruhms.Add(ruhm);
}
}
}
return(ruhms);
}