private static CSVColumns SetCSVColumns(string[] firstRow)
        {
            CSVColumns ColumnIndex = new CSVColumns();
            int        i           = 0;

            foreach (string s in firstRow)
            {
                switch (s)
                {
                case ("enr_id"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.enr_id = i;
                    break;

                case ("created"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.created = i;
                    break;

                case ("student_id"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.student_id = i;
                    break;

                case ("student_first_name"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.student_first_name = i;
                    break;

                case ("student_last_name"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.student_last_name = i;
                    break;

                case ("student_date_of_birth"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.student_date_of_birth = i;
                    break;

                case ("account_id"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.account_id = i;
                    break;

                case ("account_new_status"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.account_new_status = i;
                    break;

                case ("semester"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.semesterName = i;
                    ColumnIndex.semesterYear = i;
                    //semesterArray = s.Split(' ');
                    //if (Int32.TryParse(semesterArray[1], out numValue) convert.ToInt32(semesterArray[1]))
                    //ColumnIndex.semesterName = semesterArray[0];
                    break;

                case ("class_location"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.class_location = i;
                    break;

                case ("class_teacher"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.class_teacher = i;
                    break;

                case ("class_day_of_week"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.class_day_of_week = i;
                    break;

                case ("class_time"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.class_time = i;
                    break;

                case ("contact_first_name"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.contact_first_name = i;
                    break;

                case ("contact_last_name"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.contact_last_name = i;
                    break;

                case ("contact_email"):
                    Debug.WriteLine("Found:" + s);
                    ColumnIndex.contact_email = i;
                    break;
                }
                i++;
            }
            return(ColumnIndex);
        }
        public static void UploadFile(string csv_file_path, BackgroundWorker worker)
        {
            var lines = File.ReadAllLines(csv_file_path).Select(x => x.Split(','));

            //Considering each line contains same no. of elements
            string[]   firstRow   = lines.First();
            CSVColumns ColumIndex = SetCSVColumns(firstRow);
            //int lineLength = lines.First().Count();

            var CSVList = lines.Skip(1)
                          .Select(r => r)
                          .Take(10);

            //Initialiaze all the lists
            List <AccountHelper> AccountsHelper = new List <AccountHelper>();
            List <ClassHelper>   Classes        = new List <ClassHelper>();
            List <ContactHelper> ContactsHelper = new List <ContactHelper>();
            List <Email>         Emails         = new List <Email>();
            List <Enrollment>    Enrollments    = new List <Enrollment>();
            List <Location>      Locations      = new List <Location>();
            List <Semester>      Semesters      = new List <Semester>();
            List <SemesterYear>  SemesterYears  = new List <SemesterYear>();
            List <SemesterName>  SemesterNames  = new List <SemesterName>();
            List <StudentHelper> StudentsHelper = new List <StudentHelper>();
            List <Teacher>       Teachers       = new List <Teacher>();

            //Initialize all the helpers
            Email         email;
            ContactHelper contactHelper;
            Contact       contact;
            Student       student;
            StudentHelper studentHelper;
            Account       account;
            AccountHelper accountHelper;
            Enrollment    enrollment;
            Location      location;
            Semester      semester;
            SemesterName  semesterName;
            SemesterYear  semesterYear;
            ClassHelper   classHelper;
            Class         classDBHelper;
            Teacher       teacher;

            int      semesterYearHelper;
            string   semesterNameHelper;
            string   semesterHelper;
            string   semesterIdHelper;
            int      semesterSpaceIndex;
            DateTime _dateTime;
            int      totalRecords = CSVList.Count() * 2;
            double   i            = 0;

            //Iterate through the list
            foreach (var r in CSVList)
            {
                //Add to SemesterName and SemesterYear and Semester
                //Clean the semester data
                semesterHelper     = r[ColumIndex.semesterName].ToString().Replace(@"""", "");
                semesterSpaceIndex = semesterHelper.IndexOf(" ");
                if (semesterHelper.IndexOf(" ", semesterSpaceIndex + 1) != -1)
                {
                    semesterHelper = semesterHelper.Substring(0, semesterHelper.IndexOf(" ", semesterSpaceIndex + 1)).Trim();
                }
                //Debug.WriteLine("RowValue: " + semesterHelper);
                if (Int32.TryParse(semesterHelper.Split(' ')[1], out semesterYearHelper))
                {
                    semesterNameHelper = semesterHelper.Split(' ')[0];
                    semesterIdHelper   = semesterHelper.Replace(' ', '_');
                }
                else
                {
                    semesterNameHelper = semesterHelper.Split(' ')[0];
                    semesterIdHelper   = semesterHelper.Split(' ')[0];
                }
                //Add to SemesterName
                semesterName = new SemesterName();
                semesterName.SemesterNameId = semesterNameHelper;
                SemesterNames.Add(semesterName);
                //Add to SemesterYear
                semesterYear = new SemesterYear();
                semesterYear.SemesterYearId = semesterYearHelper;
                SemesterYears.Add(semesterYear);
                //Add to Semester
                semester            = new Semester();
                semester.SemesterId = semesterIdHelper;
                Semesters.Add(semester);

                //Add to email List
                email        = new Email();
                email.Email1 = r[ColumIndex.contact_email];
                Emails.Add(email);

                //Add to Contact List
                contactHelper           = new ContactHelper();
                contactHelper.FirstName = r[ColumIndex.contact_first_name];
                contactHelper.LastName  = r[ColumIndex.contact_last_name];
                contactHelper.Email     = email.Email1;
                ContactsHelper.Add(contactHelper);

                //Add to Student
                studentHelper           = new StudentHelper();
                studentHelper.StudentId = Convert.ToInt32(r[ColumIndex.student_id]);
                studentHelper.FirstName = r[ColumIndex.student_first_name];
                studentHelper.LastName  = r[ColumIndex.student_last_name];
                if (DateTime.TryParse(r[ColumIndex.student_date_of_birth], out _dateTime))
                {
                    studentHelper.BirthDate = _dateTime;
                }
                studentHelper.ContactFirstName = contactHelper.FirstName;
                studentHelper.ContactLastName  = contactHelper.LastName;
                studentHelper.ContactEmail     = email.Email1;
                StudentsHelper.Add(studentHelper);

                //Add to Accounts
                accountHelper           = new AccountHelper();
                accountHelper.AccountId = Convert.ToInt32(r[ColumIndex.account_id]);
                if (r[ColumIndex.account_new_status].ToString().Replace(@"""", "") == "Yes")
                {
                    accountHelper.CreatedSemesterId = semester.SemesterId;
                }
                accountHelper.ContactEmail     = email.Email1;
                accountHelper.ContactFirstName = contactHelper.FirstName;
                accountHelper.ContactLastName  = contactHelper.LastName;
                AccountsHelper.Add(accountHelper);

                //Add to Enrollment
                enrollment = new Enrollment();
                enrollment.EnrollmentId = Convert.ToInt32(r[ColumIndex.enr_id]);
                if (DateTime.TryParse(r[ColumIndex.created].ToString().Replace(@"""", ""), out _dateTime))
                {
                    enrollment.CreateDate = _dateTime;
                }
                enrollment.StudentId  = studentHelper.StudentId;
                enrollment.SemesterId = semester.SemesterId;
                enrollment.AccountId  = accountHelper.AccountId;
                Enrollments.Add(enrollment);

                //Add to Location
                location      = new Location();
                location.Name = r[ColumIndex.class_location].Replace(@"""", "").Trim();
                Locations.Add(location);

                //Add to Teachers
                teacher      = new Teacher();
                teacher.Name = r[ColumIndex.class_teacher].Replace(@"""", "").Trim();;
                Teachers.Add(teacher);

                //Add to Class
                classHelper              = new ClassHelper();
                classHelper.Time         = r[ColumIndex.class_time].ToString();
                classHelper.DayOfWeek    = r[ColumIndex.class_day_of_week];
                classHelper.LocationName = location.Name;
                classHelper.TeacherName  = teacher.Name;
                classHelper.SemesterId   = semesterIdHelper;
                Classes.Add(classHelper);

                /*  semesterYear = new SemesterYear();
                 * semesterYear.SemesterYearId = year;
                 * YearList.Add(semesterYear);
                 * //year = r[ColumIndex.semesterYear].ToString().Split(' ')[1]
                 * Debug.WriteLine("SemesterName: " + semesterName);
                 * Debug.WriteLine("SemesterYear: " + year.ToString());*/

                worker.ReportProgress(Convert.ToInt32(Math.Round(((100 / totalRecords) * i))));
                System.Threading.Thread.Sleep(10);
                i++;
            }
            //Remove the duplicates
            var SemesterYearsDistinct  = SemesterYears.Distinct(new SemesterYearComparer());
            var SemesterNamesDistinct  = SemesterNames.Distinct(new SemesterNameComparer());
            var SemestersDistinct      = Semesters.Distinct(new SemesterComparer());
            var EmailsDistinct         = Emails.Distinct(new EmailComparer());
            var ContactsHelperDistinct = ContactsHelper.Distinct(new ContactHelperComparer());
            var StudentsHelperDistinct = StudentsHelper.Distinct(new StudentHelperComparer());
            var AccountsHelperDistinct = AccountsHelper.Distinct(new AccountHelperComparer());
            var EnrollmentsDistinct    = Enrollments.Distinct(new EnrollmentComparer());
            var LocationsDistinct      = Locations.Distinct(new LocationComparer());
            var ClassesDistinct        = Classes.Distinct(new ClassHelperComparer());
            var TeachersDistinct       = Teachers.Distinct(new TeacherComparer());

            totalRecords = SemesterYearsDistinct.Count() + SemesterNamesDistinct.Count() + SemestersDistinct.Count() +
                           EmailsDistinct.Count() + ContactsHelperDistinct.Count() + StudentsHelperDistinct.Count() +
                           AccountsHelperDistinct.Count() + EnrollmentsDistinct.Count() + LocationsDistinct.Count() +
                           ClassesDistinct.Count() + TeachersDistinct.Count();
            i = 0;
            using (LisManagerADO db = new LisManagerADO())
            {
                //Process SemesterYear
                Debug.WriteLine("Number of items in the list: " + SemesterYearsDistinct.Count().ToString());
                foreach (SemesterYear e in SemesterYearsDistinct)
                {
                    Debug.WriteLine("itemValue: " + e.SemesterYearId);
                    db.SemesterYears.AddIfNotExists(e, x => x.SemesterYearId == e.SemesterYearId);
                    //db.SemesterYears.Add(e);
                }
                db.SaveChanges();
                i = i + SemesterYearsDistinct.Count();
                worker.ReportProgress(Convert.ToInt32(Math.Round(i / totalRecords)));

                //Process SemesterName
                Debug.WriteLine("Number of items in the list: " + SemesterNamesDistinct.Count().ToString());
                foreach (SemesterName e in SemesterNamesDistinct)
                {
                    Debug.WriteLine("itemValue: " + e.SemesterNameId);
                    db.SemesterNames.AddIfNotExists(e, x => x.SemesterNameId == e.SemesterNameId);
                    //db.SemesterNames.Add(e);
                }
                db.SaveChanges();

                //Process Semesters
                Debug.WriteLine("Number of items in the list: " + SemestersDistinct.Count().ToString());
                foreach (Semester e in SemestersDistinct)
                {
                    Debug.WriteLine("itemValue: " + e.SemesterId);
                    db.Semesters.AddIfNotExists(e, x => x.SemesterId == e.SemesterId);
                    //db.Semesters.Add(e);
                }
                db.SaveChanges();

                //Process Locations
                Debug.WriteLine("Number of items in the list: " + LocationsDistinct.Count().ToString());
                foreach (Location e in LocationsDistinct)
                {
                    Debug.WriteLine(String.Format("Classes itemValue: {0} {1}", e.LocationId, e.Name));
                    db.Locations.AddIfNotExists(e, x => x.Name == e.Name);
                    // db.Locations.Add(e);
                }
                db.SaveChanges();

                //Process Teachers
                Debug.WriteLine("Number of items in the list: " + TeachersDistinct.Count().ToString());
                foreach (Teacher e in TeachersDistinct)
                {
                    Debug.WriteLine(String.Format("Teacher itemValue: {0} {1}", e.TeacherId, e.Name));
                    db.Teachers.AddIfNotExists(e, x => x.Name == e.Name);
                    // db.Teachers.Add(e);
                }
                db.SaveChanges();

                //Process Classes
                Debug.WriteLine("Number of items in the list: " + ClassesDistinct.Count().ToString());
                foreach (ClassHelper e in ClassesDistinct)
                {
                    classDBHelper            = new Class();
                    classDBHelper.DayOfWeek  = e.DayOfWeek;
                    classDBHelper.Time       = e.Time;
                    classDBHelper.SemesterId = e.SemesterId;
                    classDBHelper.LocationId = Convert.ToInt32((from l in db.Locations where l.Name == e.LocationName select l.LocationId).First());
                    classDBHelper.TeacherId  = Convert.ToInt32((from t in db.Teachers where t.Name.ToString() == e.TeacherName.ToString() select t.TeacherId).First());
                    Debug.WriteLine(String.Format("Classes itemValue: {0} {1} {2} {3} {4} {5}", classDBHelper.ClassId, classDBHelper.LocationId, classDBHelper.SemesterId, classDBHelper.Time, classDBHelper.DayOfWeek, classDBHelper.TeacherId));
                    db.Classes.AddIfNotExists(classDBHelper, x => classDBHelper.ClassId == classDBHelper.ClassId);
                }
                db.SaveChanges();

                //Process Email
                Debug.WriteLine("Number of items in the list: " + EmailsDistinct.Count().ToString());
                foreach (Email e in EmailsDistinct)
                {
                    Debug.WriteLine(String.Format("email itemValue: {0} {1}", e.EmailId, e.Email1));
                    db.Emails.AddIfNotExists(e, x => x.Email1 == e.Email1);
                }
                db.SaveChanges();

                //Process Contact
                Debug.WriteLine("Number of items in the list: " + ContactsHelperDistinct.Count().ToString());
                foreach (ContactHelper c in ContactsHelperDistinct)
                {
                    contact           = new Contact();
                    contact.FirstName = c.FirstName;
                    contact.LastName  = c.LastName;
                    Debug.WriteLine(String.Format("Contact itemValue: {0} {1} {2} {3}", contact.ContactId, contact.EmailId, contact.FirstName, contact.LastName));
                    db.Contacts.AddIfNotExists(contact, x => x.FirstName == contact.FirstName && x.LastName == contact.LastName && contact.EmailId == contact.EmailId);
                    //db.SemesterYears.Add(sm);
                }
                db.SaveChanges();

                //Process Account
                Debug.WriteLine("Number of items in the list: " + AccountsHelperDistinct.Count().ToString());
                foreach (AccountHelper a in AccountsHelperDistinct)
                {
                    a.ContactEmailId          = Convert.ToInt32((from e in db.Emails where e.Email1.ToString() == a.ContactEmail.ToString() select e.EmailId).First());
                    account                   = new Account();
                    account.AccountId         = a.AccountId;
                    account.CreatedSemesterId = a.CreatedSemesterId;
                    account.ContactId         = Convert.ToInt32((from c in db.Contacts where c.FirstName == a.ContactFirstName && c.LastName == a.ContactLastName && c.EmailId == a.ContactEmailId select c.ContactId).First());
                    Debug.WriteLine(String.Format("Account itemValue: {0} {1} {2}", account.AccountId, account.ContactId, account.CreatedSemesterId));
                    db.Accounts.AddIfNotExists(account, x => x.AccountId == account.AccountId);
                }
                db.SaveChanges();

                //Process Student
                Debug.WriteLine("Number of items in the list: " + StudentsHelperDistinct.Count().ToString());
                foreach (StudentHelper e in StudentsHelperDistinct)
                {
                    e.ContactEmailId  = Convert.ToInt32((from em in db.Emails where em.Email1.ToString() == e.ContactEmail.ToString() select em.EmailId).First());
                    student           = new Student();
                    student.StudentId = e.StudentId;
                    student.FirstName = e.FirstName;
                    student.LastName  = e.LastName;
                    student.BirthDate = e.BirthDate;
                    student.ContactId = Convert.ToInt32((from c in db.Contacts where c.FirstName == e.ContactFirstName && c.LastName == e.ContactLastName && c.EmailId == e.ContactEmailId select c.ContactId).First());
                    Debug.WriteLine(String.Format("Student itemValue: {0} {1} {2} {3} {4}", student.StudentId, student.FirstName, student.LastName, student.ContactId, student.BirthDate));
                    //db.Students.Add(s1);
                    db.Students.AddIfNotExists(student, x => x.StudentId == student.StudentId);
                }
                db.SaveChanges();

                //Process Enrollment
                Debug.WriteLine("Number of items in the list: " + EnrollmentsDistinct.Count().ToString());
                foreach (Enrollment e in EnrollmentsDistinct)
                {
                    Debug.WriteLine(String.Format("Enrollment itemValue: {0} {1} {2} {3} {4}", e.EnrollmentId, e.StudentId, e.SemesterId, e.AccountId, e.CreateDate));
                    db.Enrollments.AddIfNotExists(e, x => x.EnrollmentId == e.EnrollmentId);
                    //db.SemesterYears.Add(sm);
                }
                db.SaveChanges();
            }
        }