public Dictionary <int, List <StudentClassEnrolment> > GetForStudents(List <int> iStudentIDs)
{
Dictionary <int, List <StudentClassEnrolment> > returnMe = new Dictionary <int, List <StudentClassEnrolment> >();
using (SqlConnection connection = new SqlConnection(SQLConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
foreach (int studentID in iStudentIDs)
{
sqlCommand.CommandText = "SELECT " +
" iStudentID, "+
" iClassID, "+
" dInDate, "+
" dOutDate, "+
" LookupValues.cName as enStatus "+
"FROM Enrollment " +
"LEFT OUTER JOIN LookupValues ON Enrollment.iLV_CompletionStatusID=LookupValues.iLookupValuesID " +
"WHERE iStudentID=@STUDID";
sqlCommand.Parameters.Clear();
sqlCommand.Parameters.AddWithValue("STUDID", studentID);
sqlCommand.Connection.Open();
SqlDataReader dataReader = sqlCommand.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
StudentClassEnrolment parsedEnrolment = dataReaderToObject(dataReader);
if (parsedEnrolment != null)
{
if (!returnMe.ContainsKey(studentID))
{
returnMe.Add(studentID, new List <StudentClassEnrolment>());
}
returnMe[studentID].Add(parsedEnrolment);
}
}
}
sqlCommand.Connection.Close();
}
}
}
return(returnMe);
}
public StudentClassEnrolmentRepository(string ConnectionString)
{
this._studentRepo = new StudentRepository(ConnectionString);
this._schoolClassRepo = new SchoolClassRepository(ConnectionString);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = sql;
sqlCommand.Connection.Open();
SqlDataReader dataReader = sqlCommand.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
StudentClassEnrolment sce = dataReaderToEnrolment(dataReader);
if (sce != null)
{
if ((sce.Student != null) && (sce.Class != null))
{
// add entry by student Id
if (!_enrolmentsByStudentID.ContainsKey(sce.Student.iStudentID))
{
_enrolmentsByStudentID.Add(sce.Student.iStudentID, new List <StudentClassEnrolment>());
}
_enrolmentsByStudentID[sce.Student.iStudentID].Add(sce);
// add entry by class id
if (!_enrolmentsByClassID.ContainsKey(sce.Class.iClassID))
{
_enrolmentsByClassID.Add(sce.Class.iClassID, new List <StudentClassEnrolment>());
}
_enrolmentsByClassID[sce.Class.iClassID].Add(sce);
// add to general list
_allEnrolments.Add(sce);
}
}
}
}
sqlCommand.Connection.Close();
}
}
}
