public List <EnrollmentDetailListDto> GetStudentsByCourse(Int32 programmingID, Int32 schoolID, Boolean active) { SqlConnection conn = null; SqlDataReader reader; String sql; SqlCommand command; SqlParameter prmprogrammingID = null; SqlParameter prmschoolID = null; SqlParameter prmactive = null; try { EnrollmentDetailListDto enrollmentDetail; List <EnrollmentDetailListDto> lstEnrollmentDetails; conn = new SqlConnection(Functions.GetConnectionString()); sql = "GetEnrollmentDetailByprogrammingIDByschoolID"; command = new SqlCommand(sql, conn); command.CommandType = CommandType.StoredProcedure; prmprogrammingID = new SqlParameter(); prmprogrammingID.ParameterName = "@programmingID"; prmprogrammingID.SqlDbType = SqlDbType.Int; prmprogrammingID.Value = programmingID; command.Parameters.Add(prmprogrammingID); prmschoolID = new SqlParameter(); prmschoolID.ParameterName = "@schoolID"; prmschoolID.SqlDbType = SqlDbType.Int; prmschoolID.Value = schoolID; command.Parameters.Add(prmschoolID); prmactive = new SqlParameter(); prmactive.ParameterName = "@active"; prmactive.SqlDbType = SqlDbType.Bit; prmactive.Value = active; command.Parameters.Add(prmactive); command.Connection.Open(); reader = command.ExecuteReader(); AmazonS3 s3 = new AmazonS3(); s3 = AmazonS3Factory.setAmazonS3(); lstEnrollmentDetails = new List <EnrollmentDetailListDto>(); while (reader.Read()) { enrollmentDetail = new EnrollmentDetailListDto(); enrollmentDetail.enrollmentDetailID = reader.GetInt32(reader.GetOrdinal("enrollmentDetailID")); enrollmentDetail.schoolID = reader.GetInt32(reader.GetOrdinal("schoolID")); enrollmentDetail.enrollmentID = reader.GetInt32(reader.GetOrdinal("enrollmentID")); enrollmentDetail.studentID = reader.GetInt32(reader.GetOrdinal("studentID")); enrollmentDetail.student_code = reader.GetString(reader.GetOrdinal("student_code")); enrollmentDetail.student_name = reader.GetString(reader.GetOrdinal("student_name")); enrollmentDetail.student_firstName = reader.GetString(reader.GetOrdinal("student_firstName")); enrollmentDetail.student_lastName = reader.GetString(reader.GetOrdinal("student_lastName")); enrollmentDetail.ImageKey = reader.GetString(reader.GetOrdinal("student_imageKey")); enrollmentDetail.EndPoint = s3.getStudentEndPoint(enrollmentDetail.schoolID, enrollmentDetail.ImageKey); lstEnrollmentDetails.Add(enrollmentDetail); } command.Connection.Close(); conn.Dispose(); return(lstEnrollmentDetails); } catch (Exception ex) { conn.Dispose(); throw ex; } }
public List <EnrollmentDetailListDto> GetStudentsByTutorID(Int32 schoolID, int tutorID, bool isCurrentPeriod, Boolean active, int skip, int pageSize) { SqlConnection conn = null; SqlDataReader reader; String strSqlUser; SqlCommand cmdUser; SqlParameter prmtutorID = null; SqlParameter prmschoolID = null; SqlParameter prmIsCurrentPeriod = null; SqlParameter prmactive = null; SqlParameter prmskip = null; SqlParameter prmpageSize = null; try { EnrollmentDetailListDto enrollmentDetail; List <EnrollmentDetailListDto> lstEnrollmentDetails; conn = new SqlConnection(Functions.GetConnectionString()); strSqlUser = "******"; cmdUser = new SqlCommand(strSqlUser, conn); cmdUser.CommandType = CommandType.StoredProcedure; prmtutorID = new SqlParameter(); prmtutorID.ParameterName = "@tutorID"; prmtutorID.SqlDbType = SqlDbType.Int; prmtutorID.Value = tutorID; cmdUser.Parameters.Add(prmtutorID); prmschoolID = new SqlParameter(); prmschoolID.ParameterName = "@schoolID"; prmschoolID.SqlDbType = SqlDbType.Int; prmschoolID.Value = schoolID; cmdUser.Parameters.Add(prmschoolID); prmactive = new SqlParameter(); prmactive.ParameterName = "@active"; prmactive.SqlDbType = SqlDbType.Bit; prmactive.Value = active; cmdUser.Parameters.Add(prmactive); prmIsCurrentPeriod = new SqlParameter(); prmIsCurrentPeriod.ParameterName = "@isCurrentPeriod"; prmIsCurrentPeriod.SqlDbType = SqlDbType.Bit; prmIsCurrentPeriod.Value = isCurrentPeriod; cmdUser.Parameters.Add(prmIsCurrentPeriod); prmskip = new SqlParameter(); prmskip.ParameterName = "@skip"; prmskip.SqlDbType = SqlDbType.Int; prmskip.Value = skip; cmdUser.Parameters.Add(prmskip); prmpageSize = new SqlParameter(); prmpageSize.ParameterName = "@pageSize"; prmpageSize.SqlDbType = SqlDbType.Int; prmpageSize.Value = pageSize; cmdUser.Parameters.Add(prmpageSize); cmdUser.Connection.Open(); reader = cmdUser.ExecuteReader(); lstEnrollmentDetails = new List <EnrollmentDetailListDto>(); AmazonS3 s3 = new AmazonS3(); s3 = AmazonS3Factory.setAmazonS3(); while (reader.Read()) { enrollmentDetail = new EnrollmentDetailListDto(); enrollmentDetail.studentID = reader.GetInt32(reader.GetOrdinal("studentID")); enrollmentDetail.enrollmentID = reader.GetInt32(reader.GetOrdinal("enrollmentID")); enrollmentDetail.student_code = reader.GetString(reader.GetOrdinal("student_code")); enrollmentDetail.student_name = reader.GetString(reader.GetOrdinal("student_name")); enrollmentDetail.student_firstName = reader.GetString(reader.GetOrdinal("student_firstName")); enrollmentDetail.student_lastName = reader.GetString(reader.GetOrdinal("student_lastName")); enrollmentDetail.ImageKey = reader.GetString(reader.GetOrdinal("student_imageKey")); enrollmentDetail.EndPoint = s3.getStudentEndPoint(schoolID, enrollmentDetail.ImageKey); lstEnrollmentDetails.Add(enrollmentDetail); } cmdUser.Connection.Close(); conn.Dispose(); return(lstEnrollmentDetails); } catch (Exception ex) { conn.Dispose(); throw ex; } }
public StudentListDto GetBystudentIDByactive(Int32 studentID, Boolean active) { SqlConnection conn = null; SqlDataReader reader; String sql; SqlCommand command; SqlParameter prmstudentID = null; SqlParameter prmactive = null; try { StudentListDto student; conn = new SqlConnection(Functions.GetConnectionString()); sql = "GetStudentBystudentIDByactive"; command = new SqlCommand(sql, conn); command.CommandType = CommandType.StoredProcedure; prmstudentID = new SqlParameter(); prmstudentID.ParameterName = "@studentID"; prmstudentID.SqlDbType = SqlDbType.Int; prmstudentID.Value = studentID; command.Parameters.Add(prmstudentID); prmactive = new SqlParameter(); prmactive.ParameterName = "@active"; prmactive.SqlDbType = SqlDbType.Bit; prmactive.Value = active; command.Parameters.Add(prmactive); command.Connection.Open(); reader = command.ExecuteReader(); student = new StudentListDto(); AmazonS3 s3 = new AmazonS3(); s3 = AmazonS3Factory.setAmazonS3(); while (reader.Read()) { student.code = reader.GetString(reader.GetOrdinal("code")); student.name = reader.GetString(reader.GetOrdinal("name")); student.otherName = reader.GetString(reader.GetOrdinal("otherName")); student.firstName = reader.GetString(reader.GetOrdinal("firstName")); student.lastName = reader.GetString(reader.GetOrdinal("lastName")); student.imageKey = reader.GetString(reader.GetOrdinal("imageKey")); student.dni = reader.GetString(reader.GetOrdinal("dni")); student.schoolID = reader.GetInt32(reader.GetOrdinal("schoolID")); student.phone = reader.GetString(reader.GetOrdinal("phone")); student.email = reader.GetString(reader.GetOrdinal("email")); student.alternativeMail = reader.GetString(reader.GetOrdinal("alternativeMail")); student.homeAddress = reader.GetString(reader.GetOrdinal("homeAddress")); student.career_name = reader.GetString(reader.GetOrdinal("career_name")); student.endPoint = s3.getStudentEndPoint(student.schoolID, student.imageKey); } command.Connection.Close(); conn.Dispose(); return(student); } catch (Exception ex) { conn.Dispose(); throw ex; } }