///// <summary>
//// Read SES cvs file and returns a list of studentData object
///// </summary>
public List <StudentData> ParseSESData(string CSVFile, string[] Headers)
{
//Validate csv headers files
ValidateFieldHeader(Headers, 0, "Stu no", true);
ValidateFieldHeader(Headers, 1, "Name", true);
ValidateFieldHeader(Headers, 2, "Course", true);
ValidateFieldHeader(Headers, 3, "SCJ status", true);
ValidateFieldHeader(Headers, 4, "contact email address", true);
ValidateFieldHeader(Headers, 5, "Uni email address", true);
ValidateFieldHeader(Headers, 6, "Programme code", true);
ValidateFieldHeader(Headers, 7, "Course code", true);
//get csvReader object
try
{
CsvReader csvReader = new CsvReader(new StreamReader(CSVFile), true);
string[] studentDataRecord;
int StuNumberIndex = csvReader.GetFieldIndex("Stu no");
int NameIndex = csvReader.GetFieldIndex("Name");
int CourseIndex = csvReader.GetFieldIndex("Course");
int SCJStatusIndex = csvReader.GetFieldIndex("SCJ status");
int ContactEmailAddressIndex = csvReader.GetFieldIndex("contact email address");
int UniEmailIndex = csvReader.GetFieldIndex("Uni email address");
int ProgrammeCodeIndex = csvReader.GetFieldIndex("Programme code");
int CourseCodeIndex = csvReader.GetFieldIndex("Course code");
List <StudentData> _studentDataList = new List <StudentData>();
while (csvReader.ReadNextRecord())
{
string studentData = csvReader[0];
studentDataRecord = studentData.Split('|');
StudentData _studentData = new StudentData();
_studentData.StudentName = Convert.ToString(studentDataRecord[1]);
_studentData.Initial = Convert.ToString(studentDataRecord[1].Substring(0, 1));
_studentData.StudentNumber = Convert.ToString(studentDataRecord[0]); //Derive student number from SPR Code
_studentData.UniEmail = Convert.ToString(studentDataRecord[5]);
_studentData.DegreeCode = Convert.ToString(studentDataRecord[6]);
_studentData.CourseCode = Convert.ToString(studentDataRecord[7]);
_studentData.Status = Convert.ToString(studentDataRecord[3]);
//add current studentData record to list
_studentDataList.Add(_studentData);
}
return(_studentDataList);
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
public void UpsertStudentModule(StudentData _studentData)
{
try
{
if (_connectionString != null)
{
using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
{
if (sqlConnection == null)
{
///"Sql Connection Failed")
}
else
{
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandTimeout.Equals(60000);
sqlCommand.CommandType = CommandType.Text;
string updateQuery = "Update [Student_Mailer].[dbo].[StudentModule] "
+ " SET " +
" [StudentID] = @StudentID " +
" ,[ModuleID] = @ModuleID " +
" ,[YearID] = @YearID " +
" WHERE [StudentID] = @StudentID AND [ModuleID] = @ModuleID ";
string insertQuery = " IF(@@ROWCOUNT = 0) BEGIN " +
" INSERT INTO [Student_Mailer].[dbo].[StudentModule] " +
" ([StudentID] " +
" ,[ModuleID] " +
" ,[YearID] )" +
" VALUES ( " +
" @StudentID " +
" ,@ModuleID " +
" ,@YearID )" +
"END ";
sqlCommand.CommandText = updateQuery + " " + insertQuery;
//Add paramters and assign values
if (_studentData.StudentNumber == null)
{
sqlCommand.Parameters.Add("@StudentID", SqlDbType.SmallInt).Value = 0;
}
else
{
sqlCommand.Parameters.Add("@StudentID", SqlDbType.SmallInt).Value = this.GetStudentID(_studentData.StudentNumber);
}
if (_studentData.ModuleCode == null)
{
sqlCommand.Parameters.Add("@ModuleID", SqlDbType.SmallInt).Value = 0;
}
else
{
sqlCommand.Parameters.Add("@ModuleID", SqlDbType.SmallInt).Value = this.GetModuleID(_studentData.ModuleCode);
}
if (_studentData.Year == null)
{
sqlCommand.Parameters.Add("@YearID", SqlDbType.SmallInt).Value = 0;
}
else
{
sqlCommand.Parameters.Add("@YearID", SqlDbType.SmallInt).Value = this.GetYearID(_studentData.Year);
}
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
}
}
catch (Exception ex)
{
//catch exception
throw new Exception(ex.ToString());
}
}
public void UpsertStudentData(StudentData _studentData)
{
try
{
if (_connectionString != null)
{
using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
{
if (sqlConnection == null)
{
///"Sql Connection Failed"
}
else
{
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandTimeout.Equals(60000);
sqlCommand.CommandType = CommandType.Text;
string updateQuery = "Update [Student_Mailer].[dbo].[StudentData] "
+ " SET " +
" [StudentNumber] = @StudentNumber " +
",[UniEmail] = @UniEmail " +
",[StudentName] = @StudentName " +
",[Initial] = @Initial " +
",[LevelID] = @LevelID " +
",[CourseID] = @CourseID " +
",[FacultyID] = @FacultyID " +
",[DegreeID] = @DegreeID " +
",[TutorID] = @TutorID " +
",[YearID] = @YearID " +
",[Status] = @Status " +
",[SPRCode] = @SPRCode " +
",[OtherEmail] = @OtherEmail " +
"WHERE [StudentNumber] = @StudentNumber AND [UniEmail] = @UniEmail ";
string insertQuery = " IF(@@ROWCOUNT = 0) BEGIN " +
" INSERT INTO [Student_Mailer].[dbo].[StudentData] " +
" ([StudentNumber] " +
" ,[UniEmail] " +
" ,[StudentName] " +
" ,[Initial] " +
" ,[LevelID] " +
" ,[CourseID] " +
" ,[FacultyID] " +
" ,[DegreeID] " +
" ,[TutorID] " +
" ,[YearID] " +
" ,[Status] " +
" ,[SPRCode] " +
" ,[OtherEmail]) " +
" VALUES ( " +
" @StudentNumber " +
" ,@UniEmail " +
" ,@StudentName " +
" ,@Initial " +
" ,@LevelID " +
" ,@CourseID " +
" ,@FacultyID " +
" ,@DegreeID " +
" ,@TutorID " +
" ,@YearID " +
" ,@Status " +
" ,@SPRCode " +
" ,@OtherEmail )" +
"END ";
sqlCommand.CommandText = updateQuery + " " + insertQuery;
//Add paramters and assign values
if (_studentData.StudentNumber == null)
{
sqlCommand.Parameters.Add("@StudentNumber", SqlDbType.NVarChar, 50).Value = "N/A";
}
else
{
sqlCommand.Parameters.Add("@StudentNumber", SqlDbType.NVarChar, 50).Value = _studentData.StudentNumber;
}
if (_studentData.UniEmail == null)
{
sqlCommand.Parameters.Add("@UniEmail", SqlDbType.NVarChar, 50).Value = "N/A";
}
else
{
sqlCommand.Parameters.Add("@UniEmail", SqlDbType.NVarChar, 50).Value = _studentData.UniEmail;
}
if (_studentData.StudentName == null)
{
sqlCommand.Parameters.Add("@StudentName", SqlDbType.NVarChar, 50).Value = "N/A";
}
else
{
sqlCommand.Parameters.Add("@StudentName", SqlDbType.NVarChar, 50).Value = _studentData.StudentName;
}
if (_studentData.Initial == null)
{
sqlCommand.Parameters.Add("@Initial", SqlDbType.NVarChar, 50).Value = "N/A";
}
else
{
sqlCommand.Parameters.Add("@Initial", SqlDbType.NVarChar, 50).Value = _studentData.Initial;
}
if (_studentData.Level == null)
{
sqlCommand.Parameters.Add("@LevelID", SqlDbType.SmallInt, 50).Value = 0;
}
else
{
sqlCommand.Parameters.Add("@LevelID", SqlDbType.SmallInt, 50).Value = this.GetLevelID(Convert.ToString(_studentData.Level));
}
if (_studentData.Faculty == null)
{
sqlCommand.Parameters.Add("@CourseID", SqlDbType.Int, 50).Value = 0;
}
else
{
sqlCommand.Parameters.Add("@CourseID", SqlDbType.Int, 50).Value = this.GetCourseID(_studentData.CourseCode);
}
if (_studentData.Faculty == null)
{
sqlCommand.Parameters.Add("@FacultyID", SqlDbType.Int).Value = 0;
}
else
{
sqlCommand.Parameters.Add("@FacultyID", SqlDbType.Int).Value = this.GetFacultyID(_studentData.Faculty);
}
if (_studentData.DegreeCode == null)
{
sqlCommand.Parameters.Add("@DegreeID", SqlDbType.Int).Value = 0;
}
else
{
sqlCommand.Parameters.Add("@DegreeID", SqlDbType.Int).Value = this.GetDegreeID(_studentData.DegreeCode);
}
if (_studentData.TutorCode == null)
{
sqlCommand.Parameters.Add("@TutorID", SqlDbType.Int).Value = 0;
}
else
{
sqlCommand.Parameters.Add("@TutorID", SqlDbType.Int).Value = this.GetTutorID(_studentData.TutorCode);
}
if (_studentData.Year == null)
{
sqlCommand.Parameters.Add("@YearID", SqlDbType.SmallInt).Value = 0;
}
else
{
sqlCommand.Parameters.Add("@YearID", SqlDbType.SmallInt).Value = this.GetYearID(_studentData.Year);
}
if (_studentData.Status == null)
{
sqlCommand.Parameters.Add("@Status", SqlDbType.NVarChar, 4).Value = "N/A";
}
else
{
sqlCommand.Parameters.Add("@Status", SqlDbType.NVarChar, 4).Value = _studentData.Status;
}
if (_studentData.SPRCode == null)
{
sqlCommand.Parameters.Add("@SPRCode", SqlDbType.NVarChar, 20).Value = "N/A";
}
else
{
sqlCommand.Parameters.Add("@SPRCode", SqlDbType.NVarChar, 20).Value = _studentData.SPRCode;
}
if (_studentData.ContactEmail == null)
{
sqlCommand.Parameters.Add("@OtherEmail", SqlDbType.NVarChar, 50).Value = "N/A";
}
else
{
sqlCommand.Parameters.Add("@OtherEmail", SqlDbType.NVarChar, 50).Value = _studentData.ContactEmail;
}
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
}
}
catch (Exception ex)
{
//catch exception
throw new InvalidDataException(ex.ToString());
}
}
///// <summary>
//// Read ClassList cvs file and returns a list of studentData object
///// </summary>
public List <StudentData> ParseClassListData(string CSVFile, string[] Headers)
{
//validate field names, throw exception if field name mismatched
ValidateFieldHeader(Headers, 0, "Year", true);
ValidateFieldHeader(Headers, 1, "Module code", true);
ValidateFieldHeader(Headers, 2, "Module name", true);
ValidateFieldHeader(Headers, 3, "Occurr", true);
ValidateFieldHeader(Headers, 4, "SPR code", true);
ValidateFieldHeader(Headers, 5, "Surname", true);
ValidateFieldHeader(Headers, 6, "First name", true);
ValidateFieldHeader(Headers, 7, "Initials", true);
ValidateFieldHeader(Headers, 8, "Degree code", true);
ValidateFieldHeader(Headers, 9, "Level", true);
ValidateFieldHeader(Headers, 10, "Programme code", true);
ValidateFieldHeader(Headers, 11, "Programme name", true);
ValidateFieldHeader(Headers, 12, "Status", true);
ValidateFieldHeader(Headers, 13, "Faculty", true);
ValidateFieldHeader(Headers, 14, "Tutor code", true);
ValidateFieldHeader(Headers, 15, "Tutor name", true);
ValidateFieldHeader(Headers, 16, "Univ e-mail", true);
try
{
//get csvReader object
CsvReader csvReader = this.ParseCSVReader(CSVFile);
string[] studentDataRecord;
List <StudentData> _studentDataList = new List <StudentData>();
while (csvReader.ReadNextRecord())
{
string studentData = csvReader[0];
studentDataRecord = studentData.Split('|');
StudentData _studentData = new StudentData();
if (studentDataRecord.Count() == 17)
{
_studentData.StudentNumber = Convert.ToString(studentDataRecord[4].Substring(0, studentDataRecord[4].Length - 2));
_studentData.UniEmail = Convert.ToString(studentDataRecord[16]);
_studentData.StudentName = Convert.ToString(studentDataRecord[5]) + " " + Convert.ToString(studentDataRecord[6]);
_studentData.Initial = Convert.ToString(studentDataRecord[7]);
_studentData.Year = Convert.ToString(studentDataRecord[0]);
_studentData.ModuleCode = Convert.ToString(studentDataRecord[1]);
_studentData.ModuleName = Convert.ToString(studentDataRecord[2]);
_studentData.Occur = Convert.ToString(studentDataRecord[3]);
_studentData.SPRCode = Convert.ToString(studentDataRecord[4]);
_studentData.DegreeCode = Convert.ToString(studentDataRecord[8]);
_studentData.Level = Convert.ToString(studentDataRecord[9]);
_studentData.CourseCode = Convert.ToString(studentDataRecord[10]);
_studentData.CourseName = Convert.ToString(studentDataRecord[11]);
_studentData.Status = Convert.ToString(studentDataRecord[12]);
_studentData.Faculty = Convert.ToString(studentDataRecord[13]);
_studentData.TutorCode = Convert.ToString(studentDataRecord[14]);
_studentData.TutorName = Convert.ToString(studentDataRecord[15]);
}
//add current studentData record to list
_studentDataList.Add(_studentData);
}
return(_studentDataList);
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}