public StudentRepository(string ConnectionString)
{
this._schoolRepo = new SchoolRepository(ConnectionString);
this._contactRepo = new ContactRepository(ConnectionString);
this._absenceRepo = new AbsenceStatisticsRepository(ConnectionString);
this._mailingAddressRepo = new MailingAddressRepository(ConnectionString);
_allStudents = new Dictionary <int, Student>();
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())
{
Student s = dataReaderToStudent(dataReader);
if (!_allStudents.ContainsKey(s.iStudentID))
{
_allStudents.Add(s.iStudentID, s);
}
}
}
sqlCommand.Connection.Close();
}
}
}
public ContactRepository(string ConnectionString)
{
_mailingAddressRepo = new MailingAddressRepository(ConnectionString);
// Cache all contacts for all active staff
// Get all active student IDs
// Get all contact IDs for those student IDs
// Load all of those contacts, putting them in collections by student ID
// Get contact relations for all contacts
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
// Get all active student IDs
List <int> _activeStudentIDs = new List <int>();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = SQL_ActiveStudentIDs;
sqlCommand.Connection.Open();
SqlDataReader dataReader = sqlCommand.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
int studentID = Parsers.ParseInt(dataReader["iStudentID"].ToString().Trim());
if (studentID > 0)
{
if (!_activeStudentIDs.Contains(studentID))
{
_activeStudentIDs.Add(studentID);
}
}
}
}
sqlCommand.Connection.Close();
}
// Get all contact IDs for those student IDs
List <int> _activeContactIDs = new List <int>();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = SQL_ActiveContacts + " WHERE iStudentID IN (" + _activeStudentIDs.ToCommaSeparatedString() + ") ORDER BY iContactID";;
sqlCommand.Connection.Open();
SqlDataReader dataReader = sqlCommand.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
int contactID = Parsers.ParseInt(dataReader["iContactID"].ToString().Trim());
if (contactID > 0)
{
if (!_activeContactIDs.Contains(contactID))
{
_activeContactIDs.Add(contactID);
}
}
}
}
sqlCommand.Connection.Close();
}
// Load all of those contacts, putting them in collections by student ID
_allContacts = new Dictionary <int, Contact>();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = SQL_Contacts + " WHERE iContactID IN (" + _activeContactIDs.ToCommaSeparatedString() + ") ORDER BY iContactID";
sqlCommand.Connection.Open();
SqlDataReader dataReader = sqlCommand.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
int contactID = Parsers.ParseInt(dataReader["iContactID"].ToString().Trim());
if (contactID > 0)
{
Contact c = dataReaderToContact(dataReader);
if (c != null)
{
if (!_allContacts.ContainsKey(contactID))
{
_allContacts.Add(contactID, c);
}
}
}
}
}
sqlCommand.Connection.Close();
}
// Get contact relations for all contacts
_contactRelationsByStudentID = new Dictionary <int, List <StudentContact> >();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = SQL_ContactRelations + " WHERE iStudentID IN (" + _activeStudentIDs.ToCommaSeparatedString() + ") ORDER BY iStudentID";;;
sqlCommand.Connection.Open();
SqlDataReader dataReader = sqlCommand.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
int studentID = Parsers.ParseInt(dataReader["iStudentID"].ToString().Trim());
if (studentID > 0)
{
StudentContact sc = dataReaderToStudentContact(dataReader);
if (sc != null)
{
if (!_contactRelationsByStudentID.ContainsKey(studentID))
{
_contactRelationsByStudentID.Add(studentID, new List <StudentContact>());
}
_contactRelationsByStudentID[studentID].Add(sc);
}
}
}
}
sqlCommand.Connection.Close();
}
}
}
