//find all undergraduate students, graduate students, faculty, and chairpersons
//creates an instance of each class
public void createOwlMemberList()
{
using (OleDbConnection connection = new OleDbConnection(strConnection))
{
try
{
connection.Open();
using (OleDbCommand command1 = new OleDbCommand("SELECT OWLMEMBER.fldID, OWLMEMBER.fldName, OWLMEMBER.fldBirthDate, "
+ "STUDENT.fldMajor, STUDENT.fldGPA "
+ "FROM (OWLMEMBER INNER JOIN STUDENT ON OWLMEMBER.fldID = STUDENT.fldID);", connection))
{
OleDbDataReader reader = command1.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime bday = reader.GetDateTime(2);
string major = reader.GetString(3);
decimal gpa = reader.GetDecimal(4);
Student student = new Student(id, name, bday, major, gpa);
//GlobalData.owlList.addToList(student);
}
}
}
using (OleDbCommand command2 = new OleDbCommand("SELECT OWLMEMBER.fldID, OWLMEMBER.fldName, OWLMEMBER.fldBirthDate, "
+ "STUDENT.fldMajor, STUDENT.fldGPA, "
+ "UNDERGRADUATESTUDENT.fldTuition, UNDERGRADUATESTUDENT.fldYear, UNDERGRADUATESTUDENT.fldCredits "
+ "FROM (OWLMEMBER INNER JOIN STUDENT ON OWLMEMBER.fldID = STUDENT.fldID) INNER JOIN UNDERGRADUATESTUDENT ON STUDENT.fldID = UNDERGRADUATESTUDENT.fldID;", connection))
{
OleDbDataReader reader = command2.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime bday = reader.GetDateTime(2);
string major = reader.GetString(3);
decimal gpa = reader.GetDecimal(4);
decimal tuition = reader.GetDecimal(5);
string year = reader.GetString(6);
int credits = reader.GetInt32(7);
UndergradStudent UG = new UndergradStudent(id, name, bday, major, gpa, tuition, year, credits);
//GlobalData.owlList.addToList(UG);
}
}
}
using (OleDbCommand command3 = new OleDbCommand("SELECT OWLMEMBER.fldID, OWLMEMBER.fldName,OWLMEMBER.fldBirthdate, "
+ "STUDENT.fldMajor, STUDENT.fldGPA, "
+ "GRADUATESTUDENT.fldDegreeProgram, GRADUATESTUDENT.fldStipend "
+ "FROM (OWLMEMBER INNER JOIN STUDENT ON OWLMEMBER.fldID = STUDENT.fldID) INNER JOIN GRADUATESTUDENT ON STUDENT.fldID = GRADUATESTUDENT.fldID;", connection))
{
OleDbDataReader reader = command3.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime bday = reader.GetDateTime(2);
string major = reader.GetString(3);
decimal gpa = reader.GetDecimal(4);
string program = reader.GetString(5);
decimal stipend = reader.GetDecimal(6);
GradStudent GS = new GradStudent(id, name, bday, major, gpa, program, stipend);
// GlobalData.owlList.addToList(GS);
}
}
}
using (OleDbCommand command4 = new OleDbCommand("SELECT OWLMEMBER.fldID, OWLMEMBER.fldName, OWLMEMBER.fldBirthDate, "
+ "FACULTY.fldDepartment, FACULTY.fldRank "
+ "FROM OWLMEMBER INNER JOIN FACULTY ON OWLMEMBER.fldID = FACULTY.fldId;", connection))
{
OleDbDataReader reader = command4.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime bday = reader.GetDateTime(2);
string dept = Convert.ToString(reader.GetTextReader(3));
string rank = Convert.ToString(reader.GetTextReader(4));
Faculty member = new Faculty(id, name, bday, dept, rank);
//GlobalData.owlList.addToList(member);
}
}
}
using (OleDbCommand command5 = new OleDbCommand("SELECT OWLMEMBER.fldID, OWLMEMBER.fldName,OWLMEMBER.fldBirthdate, "
+ "FACULTY.fldDepartment, FACULTY.fldRank, CHAIRPERSON.fldStipend "
+ "FROM (OWLMEMBER INNER JOIN FACULTY ON OWLMEMBER.fldID = FACULTY.fldId) INNER JOIN CHAIRPERSON ON FACULTY.fldID = CHAIRPERSON.fldID;", connection))
{
OleDbDataReader reader = command5.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime bday = reader.GetDateTime(2);
string dept = reader.GetString(3);
string rank = reader.GetString(4);
decimal stipend = reader.GetDecimal(5);
Chairperson chair = new Chairperson(id, name, bday, dept, rank, stipend);
//GlobalData.owlList.addToList(chair);
}
}
}
connection.Close();
}
catch (OleDbException ex)
{
Console.Write("Error: " + ex.Message);
connection.Close();
}
}
}
