public List <StudentMarkCustomDTO> GetStudentmarks()
{
List <StudentMarkCustomDTO> oStudentMarkList = new List <StudentMarkCustomDTO>();
try
{
sb.Clear();
sb.Append("SELECT sub.SubjectName,stm.StudentId,stm.Marks FROM dbo.Subject AS sub RIGHT JOIN dbo.StudentMark AS stm ON sub.SubjectId = stm.SubjectId");
sb.Append("");
using (CloudConnection connection = new CloudConnection(DMSSWE.Common.ConnectionString))
{
connection.CommandText = sb.ToString();
connection.Parameters.Clear();
using (IDataReader dr = connection.ExecuteReader())
{
while (dr.Read())
{
StudentMarkCustomDTO oStudentMark = new StudentMarkCustomDTO();
oStudentMark.StudentId = dr["StudentId"].ToString();
oStudentMark.SubjectName = dr["SubjectName"].ToString();
oStudentMark.Marks = Convert.ToInt32(dr["Marks"]);
oStudentMarkList.Add(oStudentMark);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(oStudentMarkList);
}
public List <StudentMarkCustomDTO> filterData(string studentName)
{
List <StudentMarkCustomDTO> oStudentMarkList = new List <StudentMarkCustomDTO>();
try
{
sb.Clear();
sb.Append("SELECT stm.SubjectId, stm.Marks, sub.SubjectName, g.Description FROM [Subject] sub RIGHT JOIN [StudentMark] stm ON sub.SubjectId = stm.SubjectId LEFT JOIN [Grade] g ON stm.SubjectId = g.SubjectId WHERE stm.StudentId = ?StudentId AND (stm.Marks BETWEEN g.LowerLimit AND g.UpperLimit)");
using (CloudConnection connection = new CloudConnection(DMSSWE.Common.ConnectionString))
{
connection.CommandText = sb.ToString();
connection.Parameters.Clear();
connection.Parameters.Add(new Parameter {
Name = "StudentId", Value = studentName
});
using (IDataReader dr = connection.ExecuteReader())
{
if (dr != null)
{
while (dr.Read())
{
StudentMarkCustomDTO oStudentMark = new StudentMarkCustomDTO();
oStudentMark.SubjectId = dr["SubjectId"].ToString();
oStudentMark.SubjectName = dr["SubjectName"].ToString();
oStudentMark.Marks = Convert.ToInt32(dr["Marks"]);
oStudentMark.Grade = dr["Description"].ToString();
oStudentMarkList.Add(oStudentMark);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(oStudentMarkList);
}
