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;
}
}