private string getStudentSSN(DataTable currentSheet, DataTableReader reader, StudentCacheDTO cacheItem, List <string> recordErrors) { string Student_SSN; if (reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.StudentSSN))) { // generate SSN var random = new Random(); Student_SSN = $"{random.Next(1, 999)}-{random.Next(1, 99)}-{random.Next(1, 9999)}"; //chceck if in cache while (currentSheet.Select($"[{FileHeaderDefinitions.StudentSSN}] = '{Student_SSN}'").Any()) { Student_SSN = $"{random.Next(1, 999)}-{random.Next(1, 99)}-{random.Next(1, 9999)}"; } // check that same Name,DOB and grade do not exist in the same school //Grade condition var gradeFilter = cacheItem.Grade != null ? "Grade = " + cacheItem.Grade : "Grade Is Null"; var duplicateStudentQuery = $"FirstName = '{cacheItem.FirstName}' AND LastName = '{cacheItem.LastName}' AND DOB = '{cacheItem.DOB}' AND {gradeFilter} AND SchoolCode = '{ cacheItem.SchoolCode }'"; var hasMultipleEntires = currentSheet.Select(duplicateStudentQuery).Count() > 1; if (hasMultipleEntires) { recordErrors.Add($"Another student has the same record details in this school"); } } else { Student_SSN = reader.GetString(reader.GetOrdinal(FileHeaderDefinitions.StudentSSN)); } return(Student_SSN); }
private StudentCacheDTO GetStudentCacheDTO(DataTableReader reader) { var cacheItem = new StudentCacheDTO(); cacheItem.FirstName = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.FirstName)) ? null : reader.GetString(reader.GetOrdinal(FileHeaderDefinitions.FirstName)); cacheItem.LastName = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.LastName)) ? null : reader.GetString(reader.GetOrdinal(FileHeaderDefinitions.LastName)); var dateTime = reader.GetDateTime(reader.GetOrdinal(FileHeaderDefinitions.DOB)); cacheItem.DOB = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.DOB)) ? (DateTime?)null : dateTime; cacheItem.SchoolCode = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.SchoolCode)) ? null : reader.GetString(reader.GetOrdinal(FileHeaderDefinitions.SchoolCode)); cacheItem.SchoolName = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.SchoolName)) ? null : reader.GetString(reader.GetOrdinal(FileHeaderDefinitions.SchoolName)); cacheItem.Grade = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.Grade)) ? (double?)null : reader.GetDouble(reader.GetOrdinal(FileHeaderDefinitions.Grade)); cacheItem.StudentID = reader.IsDBNull(reader.GetOrdinal(FileHeaderDefinitions.StudentID)) ? null : Convert.ToString(reader.GetValue(reader.GetOrdinal(FileHeaderDefinitions.StudentID))); return(cacheItem); }