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