public List<Teacher> GetAllTeachers()
{
List<Teacher> teachers = new List<Teacher>();
comm = new SqlCommand();
comm.CommandText = "SELECT * FROM Person";
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
Person pers = new Person();
pers.UserType = Convert.ToInt32(dr["userType"]);
if (pers.UserType == 1)
{
Teacher teacher = new Teacher();
teacher.Id = Convert.ToInt32(dr["pid"]);
teacher.Name = dr["name"].ToString();
teacher.Email = dr["email"].ToString();
teacher.Phone = dr["phone"].ToString();
teacher.Subject = dr["subject"].ToString();
teachers.Add(teacher);
}
}
comm.Connection.Close();
return teachers;
}
public int SubmitHomework(Homework hw)
{
try
{
cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO Homework(childId, assignmentId, date, diskName) VALUES(@childId, @assignmentId, @date, @diskName)";
cmd.Parameters.AddWithValue("childId", hw.Child.Id);
cmd.Parameters.AddWithValue("assignmentId", hw.Assignment.Id);
cmd.Parameters.AddWithValue("date", hw.Date);
cmd.Parameters.AddWithValue("diskName", hw.DiskName);
dbCon = new DbConnection();
cmd.Connection = dbCon.GetConnection();
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
result = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
cmd.Connection.Close();
}
return result;
}
public int CreateTutoringTime(TutoringTime tt)
{
try
{
cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO TutoringTime(date, time, teacherId) VALUES(@date, @time, @teacherId)";
cmd.Parameters.AddWithValue("date", tt.Date);
cmd.Parameters.AddWithValue("time", tt.Time);
cmd.Parameters.AddWithValue("teacherId", tt.Teacher.Id);
dbCon = new DbConnection();
cmd.Connection = dbCon.GetConnection();
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
result = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
cmd.Connection.Close();
}
return result;
}
public Child GetChild(Person p)
{
comm = new SqlCommand();
comm.CommandText = "SELECT * FROM Child WHERE pid=(@pid)";
comm.Parameters.AddWithValue("pid", p.Id);
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
if (dr.Read() && dr.HasRows)
{
Child child = new Child();
child.Email = p.Email;
child.Id = p.Id;
child.Name = p.Name;
child.Password = p.Password;
child.Phone = p.Phone;
child.UserName = p.UserName;
child.UserType = p.UserType;
child.Grade = dr["grade"].ToString();
return child;
}
else
{
comm.Connection.Close();
return null;
}
}
public int CreateAssignment(Assignment ass)
{
try
{
cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO Assignment(subject, title, exercise, date, deadline, teacherId) VALUES(@subject, @title, @exercise, @date, @deadline, @teacherId)";
cmd.Parameters.AddWithValue("subject", ass.Subject);
cmd.Parameters.AddWithValue("title", ass.Title);
cmd.Parameters.AddWithValue("exercise", ass.Exercise);
cmd.Parameters.AddWithValue("date", ass.Date);
cmd.Parameters.AddWithValue("deadline", ass.Deadline);
cmd.Parameters.AddWithValue("teacherId", ass.Teacher.Id);
dbCon = new DbConnection();
cmd.Connection = dbCon.GetConnection();
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
result = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
cmd.Connection.Close();
}
return result;
}
public Teacher GetTeacher(Person p)
{
comm = new SqlCommand();
comm.CommandText = "SELECT * FROM Teacher WHERE pid=(@pid)";
comm.Parameters.AddWithValue("pid", p.Id);
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
if (dr.Read() && dr.HasRows)
{
Teacher teacher = new Teacher();
teacher.Id = p.Id;
teacher.Name = p.Name;
teacher.Password = p.Password;
teacher.Phone = p.Phone;
teacher.UserName = p.UserName;
teacher.UserType = p.UserType;
teacher.Subject = Convert.ToString(dr["subject"]);
return teacher;
}
else
{
comm.Connection.Close();
return null;
}
}
public Object GetPerson(Person p)
{
comm = new SqlCommand();
comm.CommandText = "SELECT * FROM Person WHERE pid=(@personId)";
comm.Parameters.AddWithValue("personId", p.Id);
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
if (dr.Read() && dr.HasRows)
{
Person pers = new Person();
pers.UserType = Convert.ToInt32(dr["userType"]);
if (pers.UserType == 1)
{
Teacher teacher = new Teacher();
teacher.Id = Convert.ToInt32(dr["pid"]);
teacher.Name = dr["name"].ToString();
teacher.Email = dr["email"].ToString();
teacher.Phone = dr["phone"].ToString();
teacher.Subject = dr["subject"].ToString();
comm.Connection.Close();
return teacher;
}
else
{
Child child = new Child();
child.Id = Convert.ToInt32(dr["pid"]);
child.Name = dr["name"].ToString();
child.Email = dr["email"].ToString();
child.Phone = dr["phone"].ToString();
child.Grade = dr["grade"].ToString();
comm.Connection.Close();
return child;
}
}
else
{
comm.Connection.Close();
return null;
}
}
public int RegisterBooking(TutoringTime tt)
{
dbCon = new DbConnection();
using (dbCon.GetConnection())
{
dbCon.GetConnection().Open();
SqlTransaction sqlTransaction = dbCon.GetConnection().BeginTransaction(IsolationLevel.Serializable);
cmd = dbCon.GetConnection().CreateCommand();
cmd.Transaction = sqlTransaction;
try
{
if (GetTutoringTime(tt.Id).Child == null)
{
cmd.CommandText = "UPDATE TutoringTime SET childId=(@childId) WHERE ttid=(@tutoringTimeId)";
cmd.Parameters.AddWithValue("childId", tt.Child.Id);
cmd.Parameters.AddWithValue("tutoringTimeId", tt.Id);
cmd.CommandType = CommandType.Text;
result = cmd.ExecuteNonQuery();
if (result == 1)
{
sqlTransaction.Commit();
}
}
}
catch (Exception)
{
try
{
sqlTransaction.Rollback();
}
catch (Exception)
{
throw;
}
}
finally
{
dbCon.GetConnection().Close();
}
}
return result;
}
public ListForObjects GetAllHomeworksById(int assignmentId)
{
ListForObjects homeworkList = new ListForObjects();
try
{
comm = new SqlCommand();
comm.CommandText = "SELECT * FROM Homework WHERE assignmentId = " + assignmentId;
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
Homework h = new Homework();
h.Id = Convert.ToInt32(dr["hid"]);
Assignment a = new Assignment();
a.Id = Convert.ToInt32(dr["assignmentId"]);
h.Assignment = a;
Child c = new Child();
c.Id = Convert.ToInt32(dr["childId"]);
h.Child = c;
h.Date = Convert.ToDateTime(dr["date"]);
h.DiskName = Convert.ToString(dr["diskName"]);
homeworkList.AddObj(h);
}
}
catch (Exception)
{
throw;
}
finally
{
comm.Connection.Close();
}
return homeworkList;
}
public List<Homework> GetAllHomeworksByChildId(int childId)
{
List<Homework> hwList = new List<Homework>();
try
{
comm = new SqlCommand();
comm.CommandText = "SELECT * FROM Homework WHERE childId = " + childId;
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
Homework hw = new Homework();
hw.DiskName = dr["diskName"].ToString();
hw.Date = Convert.ToDateTime(dr["date"]);
Child child = new Child();
child.Id = Convert.ToInt32(dr["childId"]);
hw.Child = child;
hwList.Add(hw);
}
}
catch (Exception)
{
throw;
}
finally
{
comm.Connection.Close();
}
return hwList;
}
//Gets all the TutoringTime objects that has childId = null (which means that it
//is available to book)
public List<TutoringTime> GetAllAvailableTutoringTimes()
{
List<TutoringTime> ttTimes = new List<TutoringTime>();
try
{
comm = new SqlCommand();
comm.CommandText = "SELECT * FROM TutoringTime WHERE childId is null";
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
TutoringTime tt = new TutoringTime();
tt.Id = Convert.ToInt32(dr["tid"]);
tt.Time = Convert.ToString(dr["time"]);
tt.Date = Convert.ToDateTime(dr["date"]);
Teacher teacher = new Teacher();
teacher.Id = Convert.ToInt32(dr["teacherId"]);
tt.Teacher = teacher;
ttTimes.Add(tt);
}
}
catch (Exception)
{
throw;
}
finally
{
comm.Connection.Close();
}
return ttTimes;
}
public int RegisterBooking(TutoringTime tt)
{
SqlTransaction sqlTrans = null;
try
{
comm = new SqlCommand();
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
sqlTrans = comm.Connection.BeginTransaction(IsolationLevel.Serializable);
using (comm)
{
comm.CommandText = "UPDATE TutoringTime set childId=(@childId) WHERE tid =(@tutoringTimeId)";
comm.Parameters.AddWithValue("childId", tt.Child.Id);
comm.Parameters.AddWithValue("tutoringTimeId", tt.Id);
comm.CommandType = CommandType.Text;
comm.Transaction = sqlTrans;
result = comm.ExecuteNonQuery();
if (result == 1)
{
sqlTrans.Commit();
}
}
}
catch (Exception)
{
sqlTrans.Rollback();
throw;
}
finally
{
comm.Connection.Close();
}
return result;
}
public Homework GetHomeworkById(int id)
{
try
{
comm = new SqlCommand();
comm.CommandText = "SELECT * FROM Homework WHERE hid = " + id;
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
Homework h = new Homework();
h.Id = Convert.ToInt32(dr["hid"]);
Assignment a = new Assignment();
a.Id = Convert.ToInt32(dr["aid"]);
h.Assignment = a;
Child c = new Child();
c.Id = Convert.ToInt32(dr["childId"]);
h.Child = c;
h.Date = Convert.ToDateTime(dr["date"]);
h.DiskName = Convert.ToString(dr["diskName"]);
return h;
}
}
catch (Exception)
{
throw;
}
finally
{
comm.Connection.Close();
}
return null;
}
private List<TutoringTime> MiscWhere(string where, Boolean retrieveAssociation)
{
List<TutoringTime> tts = new List<TutoringTime>();
try
{
cmd = new SqlCommand();
cmd.CommandText = BuildQuery(where);
dbCon = new DbConnection();
cmd.Connection = dbCon.GetConnection();
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader();
TutoringTime tt = null;
while (dr.Read())
{
tt = BuildTutoringTime(dr);
if (retrieveAssociation)
{
PersonDb persDb = new PersonDb();
int pid = tt.Teacher.Id;
Person teacher = persDb.SingleWhere("pid = '" + pid + "'", false);
tt.Teacher = teacher;
}
tts.Add(tt);
}
dr.Close();
}
catch
{
throw;
}
return tts;
}
public int RemoveTutoringTime(TutoringTime tt)
{
string sqlDate = tt.Date.ToString("yyyy/MM/dd");
try
{
cmd = new SqlCommand();
cmd.CommandText = "DELETE FROM TutoringTime WHERE date =(@date) AND time = (@time) AND teacherId = (@teacherId)";
cmd.Parameters.AddWithValue("date", sqlDate);
cmd.Parameters.AddWithValue("time", tt.Time);
cmd.Parameters.AddWithValue("teacherId", tt.Teacher.Id);
dbCon = new DbConnection();
cmd.Connection = dbCon.GetConnection();
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
result = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
cmd.Connection.Close();
}
return result;
}
public List<TutoringTime> GetTtByDate(DateTime date)
{
List<TutoringTime> ttTimes = new List<TutoringTime>();
string testDate = date.ToString("yyyy/MM/dd");
try
{
comm = new SqlCommand();
comm.CommandText = "SELECT * FROM TutoringTime WHERE date = '" + testDate + "'";
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
TutoringTime tt = new TutoringTime();
tt.Id = Convert.ToInt32(dr["tid"]);
tt.Time = Convert.ToString(dr["time"]);
tt.Date = Convert.ToDateTime(dr["date"]);
Teacher teacher = new Teacher();
teacher.Id = Convert.ToInt32(dr["teacherId"]);
tt.Teacher = teacher;
ttTimes.Add(tt);
}
}
catch (Exception)
{
throw;
}
finally
{
comm.Connection.Close();
}
return ttTimes;
}
private List<Person> MiscWhere(string where, Boolean retrieveAssociation)
{
List<Person> persons = new List<Person>();
try
{
cmd = new SqlCommand();
cmd.CommandText = BuildQuery(where);
dbCon = new DbConnection();
cmd.Connection = dbCon.GetConnection();
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader();
Person hw = null;
while (dr.Read())
{
hw = BuildPerson(dr);
persons.Add(hw);
}
dr.Close();
}
catch
{
throw;
}
return persons;
}
public TutoringTime GetTtTimesByTime(DateTime date, string time, int teacherId)
{
try
{
comm = new SqlCommand();
string testDate = date.ToString("yyyy/MM/dd");
comm.CommandText = "SELECT * FROM TutoringTime WHERE date = '" + testDate + "'" + "AND time= '" + time + "'" + "AND teacherId= '" + teacherId + "'";
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
TutoringTime tt = new TutoringTime();
tt.Id = Convert.ToInt32(dr["tid"]);
tt.Time = Convert.ToString(dr["time"]);
tt.Date = Convert.ToDateTime(dr["date"]);
Teacher teacher = new Teacher();
teacher.Id = Convert.ToInt32(dr["teacherId"]);
tt.Teacher = teacher;
return tt;
}
}
catch (Exception)
{
throw;
}
finally
{
comm.Connection.Close();
}
return null;
}
public TutoringTime GetTtByTtId(int ttId)
{
SqlTransaction sqlTrans = null;
try
{
comm = new SqlCommand();
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
sqlTrans = comm.Connection.BeginTransaction(IsolationLevel.Serializable);
using (comm)
{
comm.CommandText = "SELECT * FROM TutoringTime WHERE tid = '" + ttId + "'";
comm.CommandType = CommandType.Text;
SqlDataReader dr = comm.ExecuteReader();
TutoringTime tt = new TutoringTime();
while (dr.Read())
{
tt.Id = Convert.ToInt32(dr["tid"]);
}
return tt;
}
}
catch (Exception)
{
throw;
}
finally
{
comm.Connection.Close();
}
}
private List<Assignment> MiscWhere(string where, Boolean retrieveAssociation)
{
List<Assignment> asgs = new List<Assignment>();
try
{
cmd = new SqlCommand();
cmd.CommandText = BuildQuery(where);
dbCon = new DbConnection();
cmd.Connection = dbCon.GetConnection();
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader();
Assignment asg = null;
while (dr.Read())
{
asg = BuildAssignment(dr);
if (retrieveAssociation)
{
PersonDb persDb = new PersonDb();
int pid = asg.Teacher.Id;
Person teacher = persDb.SingleWhere("pid = '" + pid + "'", false);
asg.Teacher = teacher;
}
asgs.Add(asg);
}
dr.Close();
}
catch
{
throw;
}
return asgs;
}
public int RemoveTutoringTime(int teacherId, DateTime date, string time)
{
string testDate = date.ToString("yyyy/MM/dd");
try
{
comm = new SqlCommand();
comm.CommandText = "DELETE FROM TutoringTime WHERE date = '" + testDate + "'" + "AND teacherId= '" + teacherId + "'" + "AND time= '" + time + "'";
dbCon = new DbConnection();
comm.Connection = dbCon.GetConnection();
comm.Connection.Open();
comm.CommandType = CommandType.Text;
result = comm.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
comm.Connection.Close();
}
return result;
}
private List<Homework> MiscWhere(string where, Boolean retrieveAssociation)
{
List<Homework> hws = new List<Homework>();
try
{
cmd = new SqlCommand();
cmd.CommandText = BuildQuery(where);
dbCon = new DbConnection();
cmd.Connection = dbCon.GetConnection();
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader();
Homework hw = null;
while (dr.Read())
{
hw = BuildHomework(dr);
if (retrieveAssociation)
{
PersonDb persDb = new PersonDb();
int childId = hw.Child.Id;
Person child = persDb.SingleWhere("pid = '" + childId + "'", false);
hw.Child = child;
AssignmentDb asgDb = new AssignmentDb();
int assignmentId = hw.Assignment.Id;
Assignment asg = asgDb.SingleWhere("aid = '" + assignmentId + "'", false);
hw.Assignment = asg;
}
hws.Add(hw);
}
dr.Close();
}
catch
{
throw;
}
return hws;
}
