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