private void GetCourses(acschedule.Section SchoolSection)
{
try
{
courses.Clear();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = Program.dbConnection;
cmd.CommandText = "SELECT * FROM tbl_SectionCourses LEFT JOIN tbl_Course ON tbl_SectionCourses.course = tbl_Course.corsCode WHERE section = @section";
cmd.Parameters.AddWithValue("@section", SchoolSection.SectionName);
cmd.Connection.Open();
Program.dr = cmd.ExecuteReader();
while (Program.dr.Read())
{
courses.Add(new Course(Program.dr["corsCode"].ToString(), Program.dr["corsDescription"].ToString(), int.Parse(Program.dr["corsUnit"].ToString())));
}
Program.dr.Close();
cmd.Connection.Close();
}
}
catch (Exception ex)
{
Program.dbConnection.Close();
MessageBox.Show(ex.ToString());
}
}
// Methods for all-section report.
private void GetClasses(acschedule.Section SchoolSection)
{
try
{
classes.Clear();
// load section classes
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = Program.dbConnection;
cmd.CommandText = "SELECT * FROM tbl_SectionClasses LEFT JOIN tbl_Class ON tbl_SectionClasses.class = tbl_Class.clsId WHERE section = @sectionName";
cmd.Parameters.AddWithValue("@sectionName", SchoolSection.SectionName);
cmd.Connection.Open();
Program.dr = cmd.ExecuteReader();
while (Program.dr.Read())
{
classes.Add(new School_Class(int.Parse(Program.dr["clsId"].ToString()), new List <acschedule.Section>(), new Course(), new Instructor(), new Room(), DateTime.Parse(Program.dr["clsTime_Start"].ToString()), DateTime.Parse(Program.dr["clsTime_End"].ToString()), Program.dr["clsDay"].ToString(), Program.dr["clsType"].ToString()));
classes[classes.Count - 1].Course.Coursecode = Program.dr["clsCourse"].ToString();
classes[classes.Count - 1].Instructor.Identification = Program.dr["clsInstructor"].ToString();
classes[classes.Count - 1].Room.Name = Program.dr["clsRoom"].ToString();
}
Program.dr.Close();
// retrieve class 'COURSE' information
for (int i = 0; i < classes.Count; i++)
{
cmd.Parameters.Clear();
cmd.CommandText = "SELECT * FROM tbl_Course WHERE corsCode = @code";
cmd.Parameters.AddWithValue("@code", classes[i].Course.Coursecode);
Program.dr = cmd.ExecuteReader();
while (Program.dr.Read())
{
classes[i].Course.Coursecode = Program.dr["corsCode"].ToString();
classes[i].Course.CourseDescription = Program.dr["corsDescription"].ToString();
classes[i].Course.Units = int.Parse(Program.dr["corsUnit"].ToString());
}
Program.dr.Close();
}
// retrieve instructor information
for (int i = 0; i < classes.Count; i++)
{
cmd.Parameters.Clear();
cmd.CommandText = "SELECT * FROM tbl_Instructor LEFT JOIN tbl_User ON tbl_Instructor.instrUserName = tbl_User.userName LEFT JOIN tbl_Department ON tbl_Instructor.instrDepartment = tbl_Department.deptId WHERE instrId = @id";
cmd.Parameters.AddWithValue("@id", classes[i].Instructor.Identification);
Program.dr = cmd.ExecuteReader();
while (Program.dr.Read())
{
classes[i].Instructor.Identification = Program.dr["instrId"].ToString();
classes[i].Instructor.FirstName = Program.dr["firstName"].ToString();
classes[i].Instructor.LastName = Program.dr["lastName"].ToString();
classes[i].Instructor.Email_Address = Program.dr["emailAddr"].ToString();
classes[i].Instructor.UserName = Program.dr["instrUserName"].ToString();
classes[i].Instructor.Department_Id = Program.dr["instrDepartment"].ToString();
classes[i].Instructor.Department_Description = Program.dr["deptDescription"].ToString();
}
Program.dr.Close();
}
// retrieve classroom information
for (int i = 0; i < classes.Count; i++)
{
cmd.Parameters.Clear();
cmd.CommandText = "SELECT * FROM tbl_Room WHERE rmName = @name";
cmd.Parameters.AddWithValue("@name", classes[i].Room.Name);
Program.dr = cmd.ExecuteReader();
while (Program.dr.Read())
{
classes[i].Room.Name = Program.dr[0].ToString();
classes[i].Room.RoomType = Program.dr[1].ToString();
}
Program.dr.Close();
}
Program.dr.Dispose();
cmd.Connection.Close();
}
// retrieve all sections
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = Program.dbConnection;
cmd.Connection.Open();
for (int i = 0; i < classes.Count; i++)
{
cmd.CommandText = "SELECT * FROM tbl_SectionClasses LEFT JOIN tbl_Section ON tbl_SectionClasses.section = tbl_Section.sectName WHERE class = @classId";
cmd.Parameters.AddWithValue("@classId", classes[i].Id);
Program.dr = cmd.ExecuteReader();
while (Program.dr.Read())
{
classes[i].Sections.Add(new acschedule.Section(Program.dr["sectName"].ToString(), int.Parse(Program.dr["sectYrLevel"].ToString()), Program.dr["sectProgram"].ToString(), int.Parse(Program.dr["sectClassDensity"].ToString())));
}
Program.dr.Close();
cmd.Parameters.Clear();
}
cmd.Connection.Close();
}
}
catch (Exception ex)
{
Program.dbConnection.Close();
MessageBox.Show(ex.ToString());
}
}
