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