public static DegreeAuditData degreeAudit(String studentID, String majorID) { //SQL Statements for major reqs String getMajorInfoString = @"SELECT c.course_id,CONCAT(d.department_short_name,c.course_num)AS course_number,c.course_name,c.course_credits FROM major_requirements mr INNER JOIN course c ON c.course_id = mr.course_id INNER JOIN major m ON m.major_id = mr.major_id INNER JOIN department d ON d.department_id = m.department_id WHERE m.major_id = " + majorID; //SQL Statements for electives String getMajorElectivesString = @"SELECT c.course_id, CONCAT(d.department_short_name, c.course_num) AS 'course_num' ,c.course_name, c.course_credits FROM [HarborViewUniversity].[dbo].[major_elective] m JOIN course c ON c.course_id = m.course_id JOIN department d ON c.department_id = d.department_id WHERE m.major_id = " + majorID; //SQL to get classes already taken String getClassesTakenString = "SELECT DISTINCT s.course_id,sh.grade FROM student_semester_history sh INNER JOIN section s ON s.section_id = sh.section_id INNER JOIN course c ON c.course_id = s.course_id inner join department d on d.department_id = c.department_id INNER join major m on m.department_id = d.department_id where sh.student_id = " + studentID; //SQL to get out of major classes taken String getOutOfMajorClassesTaken = "SELECT [course_id], [course_number], [course_name], [prereqs], [course_credits], [grade] FROM out_of_major_reqs_view WHERE student_id = " + studentID + "AND course_id NOT IN(select course_id from major_requirements where major_id = " + majorID + ") AND course_id NOT IN(select course_id from major_elective where major_id = " + majorID + ")"; //SQL to get classes currently being taken String getClassesInProgressString = "SELECT s.course_id FROM enrollment e INNER JOIN section s ON s.section_id = e.section_id WHERE s.semster = 'spring' AND s.[year] = '2019' AND e.student_id = " + studentID; //SQL to get prereqs for major courses String getPrereqsString = "SELECT [course_id] ,[prereq_course_id] ,[prereq_course_name] FROM [HarborViewUniversity].[dbo].[prereq_view] WHERE [major_id] = " + majorID; //SQL to get requirements for minor String getMinorReqsString = "SELECT c.course_id,CONCAT(d.department_short_name,c.course_num)AS course_number,c.course_name,c.course_credits FROM minor_requirements mr INNER JOIN course c ON c.course_id = mr.course_id INNER JOIN minor m ON m.minor_id = mr.minor_id INNER JOIN department d ON d.department_id = m.department_id WHERE m.minor_id = (SELECT [minor_id] FROM [student_minor_list] WHERE [student_id] = " + studentID + ")"; //SQL to get prereqs for minor courses String getMinorPrereqsString = "SELECT[course_id],[prereq_course_id] ,[prereq_course_name] FROM[HarborViewUniversity].[dbo].[prereq_view_minors] WHERE[minor_id] = (SELECT[minor_id] FROM [student_minor_list] WHERE[student_id] = " + studentID + ")"; //Connection String String cString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnect"].ConnectionString; //List of major reqs List <DegreeAuditMajorReqs> majorReqs = new List <DegreeAuditMajorReqs>(); //List of courses taken Hashtable coursesTaken = new Hashtable(); //Prerequisites for major courses DataTable majorPrereqTable = new DataTable(); //Prerequisites for minor courses DataTable minorPrereqTable = new DataTable(); //List of courses currently being taken List <string> inProgress = new List <string>(); //Lists for major electives List <DegreeAuditElectives> majorElectives = new List <DegreeAuditElectives>(); //List of out of major classes taken List <DegreeAuditOutOfMajorReqs> outOfMajorReqs = new List <DegreeAuditOutOfMajorReqs>(); //List of Minor Requirements List <DegreeAuditMajorReqs> minorReqs = new List <DegreeAuditMajorReqs>(); //Create connect using (SqlConnection connection = new SqlConnection(cString)) { //Major Requirements //Get list of major requirements SqlCommand command = new SqlCommand(getMajorInfoString, connection); connection.Open(); using (var reader = command.ExecuteReader()) { while (reader.Read()) { majorReqs.Add(new DegreeAuditMajorReqs(reader.GetInt32(0).ToString(), reader.GetString(1), reader.GetString(2), reader.GetByte(3))); } } //Get classes taken by student SqlCommand command2 = new SqlCommand(getClassesTakenString, connection); using (var reader = command2.ExecuteReader()) { while (reader.Read()) { coursesTaken.Add(reader.GetInt32(0).ToString(), reader.GetString(1)); } } //Get prereqs for major reqs SqlCommand cmd = new SqlCommand(getPrereqsString, connection); SqlDataAdapter da = new SqlDataAdapter(cmd); // this will query your database and return the result to your datatable da.Fill(majorPrereqTable); da.Dispose(); //Major Electives //get list of major electives SqlCommand command3 = new SqlCommand(getMajorElectivesString, connection); using (var reader = command3.ExecuteReader()) { while (reader.Read()) { majorElectives.Add(new DegreeAuditElectives(reader.GetInt32(0).ToString(), reader.GetString(1), reader.GetString(2), reader.GetByte(3))); } } //Get classes currently being taken SqlCommand command4 = new SqlCommand(getClassesInProgressString, connection); using (var reader = command4.ExecuteReader()) { while (reader.Read()) { inProgress.Add(reader.GetInt32(0).ToString()); } } //Out of Major Requirements SqlCommand command5 = new SqlCommand(getOutOfMajorClassesTaken, connection); using (var reader = command5.ExecuteReader()) { while (reader.Read()) { //[course_id], [course_number], [course_name], [prereqs], [course_credits], [grade] string courseId; string courseNumber; string courseName; string prereqs; int courseCredits; string grade; try { courseId = reader.GetInt32(0).ToString(); } catch { courseId = ""; } try { courseNumber = reader.GetString(1); } catch { courseNumber = ""; } try { courseName = reader.GetString(2); } catch { courseName = ""; } try { prereqs = reader.GetString(3); } catch { prereqs = ""; } try { courseCredits = reader.GetByte(4); } catch { courseCredits = 0; } try { grade = reader.GetString(5); } catch { grade = ""; } outOfMajorReqs.Add(new DegreeAuditOutOfMajorReqs(courseId, courseNumber, courseName, prereqs, courseCredits, grade)); } } //Minor Requirements //Get list of minor requirements SqlCommand command6 = new SqlCommand(getMinorReqsString, connection); using (var reader = command6.ExecuteReader()) { while (reader.Read()) { minorReqs.Add(new DegreeAuditMajorReqs(reader.GetInt32(0).ToString(), reader.GetString(1), reader.GetString(2), reader.GetByte(3))); } } //Get prereqs for minor reqs SqlCommand cmd2 = new SqlCommand(getMinorPrereqsString, connection); SqlDataAdapter da2 = new SqlDataAdapter(cmd); // this will query your database and return the result to your datatable da2.Fill(minorPrereqTable); da2.Dispose(); connection.Close(); } //Major reqs data processing foreach (var mr in majorReqs) { //if course is in progress, mark as in progress if (inProgress.Contains(mr.courseID)) { mr.courseStatus = "☐"; } //if taken course is passed, mark as complete if (coursesTaken.Contains(mr.courseID)) { if (GradeList.isPassing((string)coursesTaken[mr.courseID])) { mr.courseStatus = "☑"; } } //foreach buildingid //listofroomsforbuildingx.add( datarow dr prereqTable.Select(where buildingid = currentbuilingid)) foreach (DataRow dr in majorPrereqTable.Select("course_id = '" + mr.courseID + "'")) { if (coursesTaken.Contains(dr[1] + "")) { mr.prereqsTaken += " " + dr[2] + ","; mr.grade = (string)coursesTaken[mr.courseID]; } else { mr.prereqsToTake += " " + dr[2] + ","; } } if (coursesTaken.Contains(mr.courseID + "")) { mr.grade = (string)coursesTaken[mr.courseID]; } } //major electives data processing foreach (var el in majorElectives) { //if course is in progress, mark as in progreess if (inProgress.Contains(el.courseID)) { el.courseStatus = "☐"; } //if taken course is passed, mark as complete if (coursesTaken.Contains(el.courseID)) { if (GradeList.isPassing((string)coursesTaken[el.courseID])) { el.courseStatus = "☑"; el.grade = (string)coursesTaken[el.courseID]; } } //sort and add prereqs foreach (DataRow dr in majorPrereqTable.Select("course_id = '" + el.courseID + "'")) { if (coursesTaken.Contains(dr[1] + "")) { el.prereqsTaken += " " + dr[2] + ","; } else { el.prereqsToTake += " " + dr[2] + ","; } } if (coursesTaken.Contains(el.courseID)) { el.grade = (string)coursesTaken[el.courseID]; } } //Out of Major Requirements data processing foreach (var oom in outOfMajorReqs) { if (oom.grade.Equals("")) { oom.courseStatus = "☐"; } else { if (GradeList.isPassing(oom.grade)) { oom.courseStatus = "☑"; } else { oom.courseStatus = "☒"; } } } foreach (var min in minorReqs) { //if course is in progress, mark as in progreess if (inProgress.Contains(min.courseID)) { min.courseStatus = "☐"; } //if taken course is passed, mark as complete if (coursesTaken.Contains(min.courseID)) { if (GradeList.isPassing((string)coursesTaken[min.courseID])) { min.courseStatus = "☑"; min.grade = (string)coursesTaken[min.courseID]; } } //sort and add prereqs foreach (DataRow dr in minorPrereqTable.Select("course_id = '" + min.courseID + "'")) { if (coursesTaken.Contains(dr[1] + "")) { min.prereqsTaken += " " + dr[2] + ","; } else { min.prereqsToTake += " " + dr[2] + ","; } } if (coursesTaken.Contains(min.courseID)) { min.grade = (string)coursesTaken[min.courseID]; } } return(new DegreeAuditData(majorReqs, majorElectives, outOfMajorReqs, minorReqs)); }