public bool OverRideCourse(int advisorID, StudentCourse sc) { dlStudent = new DLStudent(); return dlStudent.OverRideCourse(advisorID, sc); }
public List<StudentCourse> GetAvailableCourses(List<StudentCourse> studentCourses) { SqlConnection connection = SqlHelper.CreateConnection(); List<StudentCourse> availableCourses = new List<StudentCourse>(); string RegisteredCoursesList = "-"; foreach (StudentCourse studentCourse in studentCourses) { RegisteredCoursesList = RegisteredCoursesList + "," + studentCourse.CourseID+"-"+ studentCourse.SemesterID +"-"+studentCourse.Status; } RegisteredCoursesList = RegisteredCoursesList.Replace("-,", ""); if (RegisteredCoursesList.Length == 1) { RegisteredCoursesList = ""; } try { SqlParameter pCourseList = new SqlParameter("@CourseIDs", SqlDbType.NVarChar); SqlParameter pSemester = new SqlParameter("@SemesterID", SqlDbType.Int); string spName = "GetAvailableCoursesForStudentCheckingAllPreReqChain"; //Replace 2 with current semester for (int sem = 1; sem <= 12; sem++) { pCourseList.Value = RegisteredCoursesList; //RegisteredCoursesList; pSemester.Value = sem; using (SqlDataReader dr = SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, pCourseList, pSemester)) { while (dr.Read()) { StudentCourse st = new StudentCourse(); st.CourseID = SqlHelper.ToInt32(dr["CourseID"]); st.Credits = SqlHelper.ToInt32(dr["Credits"]); st.SemesterCourseID = SqlHelper.ToInt32(dr["SemCourseID"]); st.IsActiveFL = SqlHelper.ToBool(dr["IsActiveFL"]); st.IsElectiveAFL = SqlHelper.ToBool(dr["IsElectiveAFL"]); st.IsElectiveBFL = SqlHelper.ToBool(dr["IsElectiveBFL"]); st.SemesterID = SqlHelper.ToInt32(dr["SemesterID"]); st.CourseName = SqlHelper.ToString(dr["CourseName"]); st.Status = "Not Yet"; st.CreatedBy = SqlHelper.ToInt32(dr["CreatedBy"]); st.LastUpdatedBy = SqlHelper.ToInt32(dr["LastUpdatedBy"]); st.CreationDate = SqlHelper.ToDateTime(dr["CreationDate"]); st.LastUpdatedDate = SqlHelper.ToDateTime(dr["LastUpdatedDate"]); availableCourses.Add(st); } } } } catch (SqlException sqlEx) { SqlHelper.CloseConnection(connection); throw new Exception("SearchStudent: " + sqlEx.ToString()); } catch (Exception Ex) { SqlHelper.CloseConnection(connection); throw new Exception("SearchStudent: " + Ex.ToString()); } return availableCourses; }
public List<StudentCourse> OverRideCourses(int studentID , int SemesterID) { SqlConnection connection = SqlHelper.CreateConnection(); StringBuilder sb = new StringBuilder(); List<StudentCourse> studentCourses = new List<StudentCourse>(); try { sb.Append("SELECT SC.ID AS SemesterCourseID,C.ID AS CourseID,C.Name AS CourseName,SC.SemesterID AS SemesterID ,C.Credits AS Credits, "); sb.Append("IsElectiveAFL,IsElectiveBFL FROM SemesterCourse SC INNER JOIN Course C ON SC.CourseID = C.ID WHERE C.ID NOT IN"); sb.Append("(SELECT CourseID FROM StudentCourse WHERE StudentID = "+studentID); sb.Append(") AND SemesterID ="+SemesterID); using (SqlDataReader dr = SqlHelper.ExecuteReader(connection, CommandType.Text, sb.ToString())) { while (dr.Read()) { StudentCourse sc = new StudentCourse(); sc.SemesterCourseID = SqlHelper.ToInt32(dr["SemesterCourseID"]); sc.CourseID = SqlHelper.ToInt32(dr["CourseID"]); sc.CourseName = SqlHelper.ToString(dr["CourseName"]); sc.Credits = SqlHelper.ToInt32(dr["Credits"]); sc.SemesterID = SqlHelper.ToInt32(dr["SemesterID"]); sc.IsElectiveAFL = SqlHelper.ToBool(dr["IsElectiveAFL"]); sc.IsElectiveBFL = SqlHelper.ToBool(dr["IsElectiveBFL"]); studentCourses.Add(sc); } } } catch (SqlException sqlEx) { SqlHelper.CloseConnection(connection); throw new Exception("GetStudentDetails: " + sqlEx.ToString()); } catch (Exception e) { SqlHelper.CloseConnection(connection); throw new Exception("GetStudentDetails: " + e.ToString()); } finally { SqlHelper.CloseConnection(connection); } return studentCourses; }
public bool OverRideCourse(int advisorID, StudentCourse sc) { SqlConnection connection = SqlHelper.CreateConnection(); StringBuilder sb = new StringBuilder(); try { //Creating SqlParameter objects to fields in student sb.Append("INSERT INTO StudentCourse(StudentID,CourseID,SemesterCourseID,CourseName,ElectiveID,SemesterID,"); sb.Append("Credits,[Status],IsActiveFL,CreationDate,LastUpdatedDate,CreatedBy,LastUpdatedBy) VALUES"); sb.Append("(" + sc.StudentID + "," + sc.CourseID + ","+sc.SemesterCourseID+",'" + sc.CourseName + "'"); sb.Append(","+sc.ElectiveID+"," +sc.SemesterID + "," + sc.Credits + ",'Pending','" + sc.IsActiveFL); sb.Append("','" + DateTime.UtcNow + "','" + DateTime.UtcNow + "'," + advisorID + "," + advisorID); sb.Append(")"); SqlHelper.ExecuteNonQuery(connection, CommandType.Text, sb.ToString()); } catch (SqlException sqlEx) { SqlHelper.CloseConnection(connection); throw new Exception("GetStudentDetails: " + sqlEx.ToString()); } catch (Exception e) { SqlHelper.CloseConnection(connection); throw new Exception("GetStudentDetails: " + e.ToString()); } finally { SqlHelper.CloseConnection(connection); } return true; }
public List<StudentCourse> GetTransferedCourses(int StudentID) { SqlConnection connection = SqlHelper.CreateConnection(); StringBuilder sb = new StringBuilder(); List<StudentCourse> studentCourses = new List<StudentCourse>(); try { //Creating SqlParameter objects to fields in student sb.Append("SELECT cour.ID as ID,sc.StudentID as StudentID,sc.SemesterCourseID as SemesterCourseID,cour.Name as CourseName,"); sb.Append("cour.ID as CourseID,cour.Credits as Credits,cour.IsElectiveAFL as IsElectiveAFL ,cour.IsElectiveBFL as IsElectiveBFL ,"); sb.Append("sc.SemesterID as SemesterID,sc.[Status] as Status,sc.IsActiveFL as IsActiveFL,sc.CreationDate as CreationDate,"); sb.Append("sc.LastUpdatedDate as LastUpdatedDate,sc.CreatedBy as CreatedBy,sc.LastUpdatedBy as LastUpdatedBy FROM StudentCourse sc "); sb.Append(" JOIN Course cour ON cour.Name = sc.CourseName WHERE StudentID = " + StudentID + " AND SemesterID =0 ;"); using (SqlDataReader dr = SqlHelper.ExecuteReader(connection, CommandType.Text, sb.ToString())) { while (dr.Read()) { StudentCourse sc = new StudentCourse(); sc.ID = SqlHelper.ToInt32(dr["ID"]); sc.StudentID = SqlHelper.ToInt32(dr["StudentID"]); sc.SemesterCourseID = SqlHelper.ToInt32(dr["SemesterCourseID"]); sc.CourseID = SqlHelper.ToInt32(dr["CourseID"]); sc.CourseName = SqlHelper.ToString(dr["CourseName"]); sc.Credits = SqlHelper.ToInt32(dr["Credits"]); sc.SemesterID = SqlHelper.ToInt32(dr["SemesterID"]); sc.Status = SqlHelper.ToString(dr["Status"]); sc.IsElectiveAFL = SqlHelper.ToBool(dr["IsElectiveAFL"]); sc.IsElectiveBFL = SqlHelper.ToBool(dr["IsElectiveBFL"]); sc.IsActiveFL = SqlHelper.ToBool(dr["IsActiveFL"]); sc.CreationDate = SqlHelper.ToDateTime(dr["CreationDate"]); sc.LastUpdatedDate = SqlHelper.ToDateTime(dr["LastUpdatedDate"]); sc.CreatedBy = SqlHelper.ToInt32(dr["CreatedBy"]); sc.LastUpdatedBy = SqlHelper.ToInt32(dr["LastUpdatedBy"]); studentCourses.Add(sc); } } } catch (SqlException sqlEx) { SqlHelper.CloseConnection(connection); throw new Exception("GetStudentDetails: " + sqlEx.ToString()); } catch (Exception e) { SqlHelper.CloseConnection(connection); throw new Exception("GetStudentDetails: " + e.ToString()); } finally { SqlHelper.CloseConnection(connection); } return studentCourses; }
public List<StudentCourse> GetStudentRegisteredCourses(int studentID) { StringBuilder sb = new StringBuilder(); StringBuilder sb1 = new StringBuilder(); SqlConnection connection = SqlHelper.CreateConnection(); try { sb.Append("SELECT cour.ID as ID,sc.StudentID as StudentID,sc.SemesterCourseID as SemesterCourseID,cour.Name as CourseName,cour.ID as CourseID,"); sb.Append("cour.Credits as Credits,cour.IsElectiveAFL as IsElectiveAFL ,cour.IsElectiveBFL as IsElectiveBFL ,sc.SemesterID as SemesterID,sc.[Status] as Status,"); sb.Append("sc.IsActiveFL as IsActiveFL,sc.CreationDate as CreationDate,sc.LastUpdatedDate as LastUpdatedDate,sc.CreatedBy as CreatedBy,"); sb.Append("sc.LastUpdatedBy as LastUpdatedBy FROM StudentCourse sc INNER JOIN SemesterCourse SemCour ON sc.SemesterCourseID=SemCour.ID "); sb.Append(" JOIN Course cour ON cour.ID = SemCour.CourseID WHERE StudentID = " + studentID + "; "); sb1.Append("SELECT sc.ID as ID,sc.StudentID as StudentID,sc.SemesterCourseID as SemesterCourseID ,CourseName,CourseID,Credits,"); sb1.Append("ElectiveID,SemesterID,[Status],IsActiveFL,CreationDate,sc.LastUpdatedDate as LastUpdatedDate,sc.CreatedBy as CreatedBy,"); sb1.Append("sc.LastUpdatedBy as LastUpdatedBy FROM StudentCourse sc WHERE (CourseID =0 or SemesterID=0) and StudentID = " + studentID + "; "); using(SqlDataReader dr = SqlHelper.ExecuteReader(connection,CommandType.Text,sb.ToString())) { while (dr.Read()) { StudentCourse sc = new StudentCourse(); sc.ID = SqlHelper.ToInt32(dr["ID"]); sc.StudentID = SqlHelper.ToInt32(dr["StudentID"]); sc.SemesterCourseID = SqlHelper.ToInt32(dr["SemesterCourseID"]); sc.CourseID = SqlHelper.ToInt32(dr["CourseID"]); sc.CourseName = SqlHelper.ToString(dr["CourseName"]); sc.Credits = SqlHelper.ToInt32(dr["Credits"]); sc.SemesterID = SqlHelper.ToInt32(dr["SemesterID"]); sc.Status = SqlHelper.ToString(dr["Status"]); sc.IsElectiveAFL = SqlHelper.ToBool(dr["IsElectiveAFL"]); sc.IsElectiveBFL = SqlHelper.ToBool(dr["IsElectiveBFL"]); sc.IsActiveFL = SqlHelper.ToBool(dr["IsActiveFL"]); sc.CreationDate = SqlHelper.ToDateTime(dr["CreationDate"]); sc.LastUpdatedDate = SqlHelper.ToDateTime(dr["LastUpdatedDate"]); sc.CreatedBy = SqlHelper.ToInt32(dr["CreatedBy"]); sc.LastUpdatedBy = SqlHelper.ToInt32(dr["LastUpdatedBy"]); studentCourses.Add(sc); } } using (SqlDataReader dr1 = SqlHelper.ExecuteReader(connection, CommandType.Text, sb1.ToString())) { while (dr1.Read()) { StudentCourse sc = new StudentCourse(); sc.ID = SqlHelper.ToInt32(dr1["ID"]); sc.StudentID = SqlHelper.ToInt32(dr1["StudentID"]); sc.SemesterCourseID = SqlHelper.ToInt32(dr1["SemesterCourseID"]); sc.CourseID = SqlHelper.ToInt32(dr1["CourseID"]); sc.CourseName = SqlHelper.ToString(dr1["CourseName"]); sc.Credits = SqlHelper.ToInt32(dr1["Credits"]); sc.SemesterID = SqlHelper.ToInt32(dr1["SemesterID"]); sc.Status = SqlHelper.ToString(dr1["Status"]); sc.ElectiveID = SqlHelper.ToInt32(dr1["ElectiveID"]); if (SqlHelper.ToInt32(dr1["ElectiveID"]) == 1) { sc.IsElectiveAFL = true; } else if (SqlHelper.ToInt32(dr1["ElectiveID"]) == 2) { sc.IsElectiveBFL = true; } else if (SqlHelper.ToInt32(dr1["ElectiveID"]) == 3) { sc.GenEd = true; } sc.IsActiveFL = SqlHelper.ToBool(dr1["IsActiveFL"]); sc.CreationDate = SqlHelper.ToDateTime(dr1["CreationDate"]); sc.LastUpdatedDate = SqlHelper.ToDateTime(dr1["LastUpdatedDate"]); sc.CreatedBy = SqlHelper.ToInt32(dr1["CreatedBy"]); sc.LastUpdatedBy = SqlHelper.ToInt32(dr1["LastUpdatedBy"]); studentCourses.Add(sc); } } } catch (SqlException sqlEx) { SqlHelper.CloseConnection(connection); throw new Exception("GetStudentSemesterCourses: " + sqlEx.ToString()); } catch (Exception ex) { SqlHelper.CloseConnection(connection); throw new Exception("GetStudentSemesterCourses: " + ex.ToString()); } finally { SqlHelper.CloseConnection(connection); } return studentCourses; }