public IList <SurveyStudent> getStudentsBySurveyId(int SurveyId) { StringBuilder sql = new StringBuilder(); List <SurveyStudent> students = null; sql.AppendFormat( @"Select s.StudentNo,s.LastName,s.FirstName,se.StudentEnrollmentID From SurveyStudents as ss inner join Students as s on s.StudentNo=ss.StudentID inner join StudentEnrollment se on se.StudentEnrollmentID=ss.EnrolmentID where ss.SurveyID=@surveyId"); SqlCommand cmd = new SqlCommand(sql.ToString()); cmd.Parameters.Add(new SqlParameter("@surveyId", SurveyId)); using (IDataReader reader = this.ExecuteReader(cmd)) { students = new List <SurveyStudent>(); while (reader.Read()) { int colIndex = -1; SurveyStudent ss = new SurveyStudent() { StudentNo = reader.GetInt32(++colIndex), LastName = reader.GetString(++colIndex), FirstName = reader.GetString(++colIndex), EnrollmentId = reader.GetInt32(++colIndex) }; students.Add(ss); } } return(students); }
public IList <SurveyStudent> getFilteredStudents(int surveyId, int statusId, int programNo, int termId, int groupId, string session) { StringBuilder sql = new StringBuilder(); List <SurveyStudent> students = null; // sql.AppendFormat( // @"Select s.StudentNo as StudentID,se.StudentEnrollmentID, s.LastName,s.FirstName,t.TermBeginDate, ss.StudentStatus, p.ProgramCode // from Students as s // inner join StudentEnrollment as se on s.StudentNo =se.StudentNo // inner join StudentStatus as ss on ss.StudentStatusID =s.StudentStatusID // inner join Program as p on p.ProgramNo = s.ProgramNo // inner join Term as t on t.TermID = se.EnrollmentFirstTermID // inner join StudentGroup as sg on s.StudentGroupID=sg.GroupID // inner join Session on Session.Session = s.Session // where ss.StudentStatusID=@studentStatusId and p.ProgramNo=@programNo and t.TermID=@termId and sg.GroupID=@groupId and Session.Session=@session // AND CONCAT(s.StudentNo, '-',se.StudentEnrollmentID) NOT IN ( // SELECT CONCAT(StudentID, '-', EnrolmentID) // FROM SurveyStudents where SurveyID=@surveyId)"); sql.AppendFormat( @"Select s.StudentNo as StudentID,se.StudentEnrollmentID, s.LastName,s.FirstName,t.TermBeginDate, ss.StudentStatus, p.ProgramCode from Students as s inner join StudentEnrollment as se on s.StudentNo =se.StudentNo inner join StudentStatus as ss on ss.StudentStatusID =s.StudentStatusID inner join Program as p on p.ProgramNo = s.ProgramNo inner join Term as t on t.TermID = se.EnrollmentFirstTermID inner join StudentGroup as sg on s.StudentGroupID=sg.GroupID inner join Session on Session.Session = s.Session where ss.StudentStatusID=@studentStatusId and p.ProgramNo=@programNo and t.TermID=@termId and sg.GroupID=@groupId and Session.Session=@session"); SqlCommand cmd = new SqlCommand(sql.ToString()); cmd.Parameters.Add(new SqlParameter("@studentStatusId", statusId)); cmd.Parameters.Add(new SqlParameter("@programNo", programNo)); cmd.Parameters.Add(new SqlParameter("@termId", termId)); cmd.Parameters.Add(new SqlParameter("@groupId", groupId)); cmd.Parameters.Add(new SqlParameter("@session", session)); //cmd.Parameters.Add(new SqlParameter("@surveyId", surveyId)); using (IDataReader reader = this.ExecuteReader(cmd)) { students = new List <SurveyStudent>(); while (reader.Read()) { int colIndex = -1; SurveyStudent ss = new SurveyStudent() { StudentNo = reader.GetInt32(++colIndex), EnrollmentId = reader.GetInt32(++colIndex), LastName = reader.GetString(++colIndex), FirstName = reader.GetString(++colIndex), TermStartDate = reader.GetDateTime(++colIndex), StudentStatus = reader.GetString(++colIndex), ProgramCode = reader.GetString(++colIndex) }; students.Add(ss); } } return(students); }