private void button1_Click_1(object sender, EventArgs e) { Excel.Workbook MyBook = null; Excel.Application MyApp = null; Excel.Worksheet MySheet = null; MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(@"C:\Users\Francis\Desktop\IBPExcelApp\IBPExcelApplication\bin\Debug\IBP MEMBERS06182014-only.xlsx"); MySheet = (Excel.Worksheet)MyBook.Sheets[1]; int lastRow = 1164; var list = new List<Lawyer>(); long success = 0; long fail = 0; int init = int.Parse(txtstart.Text); int end = int.Parse(txtend.Text); new Thread(() => { for (int i = init; i <= end; i++) { System.Array MyValues = (System.Array)MySheet.get_Range("A" + i.ToString(), "R" + i.ToString()).Cells.Value; var data = new Lawyer(); data.RollNumber = MyValues.GetValue(1, 1).ToString() == "-" ? "" : MyValues.GetValue(1, 1).ToString(); data.Chapter = MyValues.GetValue(1, 2).ToString() == "-" ? "" : MyValues.GetValue(1, 2).ToString(); data.Name = MyValues.GetValue(1, 3).ToString() == "-" ? "" : MyValues.GetValue(1, 3).ToString(); data.Address = MyValues.GetValue(1, 4).ToString() == "-" ? "" : MyValues.GetValue(1, 4).ToString(); data.HomePhone = MyValues.GetValue(1, 5).ToString() == "-" ? "" : MyValues.GetValue(1, 5).ToString(); data.WorkAddress = MyValues.GetValue(1, 6).ToString() == "-" ? "" : MyValues.GetValue(1, 6).ToString(); data.WorkPhone = MyValues.GetValue(1, 7).ToString() == "-" ? "" : MyValues.GetValue(1, 7).ToString(); data.LawSchool = MyValues.GetValue(1, 8).ToString() == "-" ? "" : MyValues.GetValue(1, 8).ToString(); data.Occupation = MyValues.GetValue(1, 9).ToString() == "-" ? "" : MyValues.GetValue(1, 9).ToString(); data.Specialization = MyValues.GetValue(1, 10).ToString() == "-" ? "" : MyValues.GetValue(1, 10).ToString(); data.Status = MyValues.GetValue(1, 11).ToString() == "-" ? "" : MyValues.GetValue(1, 11).ToString(); data.BirthDate = (DateTime)MyValues.GetValue(1, 12); data.BirthPlace = MyValues.GetValue(1, 13).ToString() == "-" ? "" : MyValues.GetValue(1, 13).ToString(); data.Sex = MyValues.GetValue(1, 14).ToString() == "-" ? "" : MyValues.GetValue(1, 14).ToString(); data.Email = MyValues.GetValue(1, 16).ToString() == "-" ? "" : MyValues.GetValue(1, 16).ToString(); data.LifeTimeRoll = MyValues.GetValue(1, 17) == null ? "" : MyValues.GetValue(1, 17).ToString(); data.YearAdmitted = MyValues.GetValue(1, 18).ToString() == "-" ? "" : MyValues.GetValue(1, 18).ToString(); var record = EncodeLawyer(data); ProcessLawyer.Add(new ProcessData { Data = data, RollNumber = data.RollNumber, Success = record.Success, Index = i }); listView1.Invoke((Action)(() => { ListViewItem lvitem = new ListViewItem(i.ToString()); lvitem.SubItems.Add(record.RollNumber); lvitem.SubItems.Add(record.Type); lvitem.SubItems.Add(record.Message); lvitem.BackColor = record.Success ? Color.LimeGreen : Color.DarkRed; listView1.Items.Add(lvitem); })); success = record.Success ? success + 1 : success; fail = !record.Success ? fail + 1 : fail; label1.Invoke((Action)(() => { label1.Text = string.Format("Current Index is: {0}", (i - init)); })); label2.Invoke((Action)(() => { label2.Text = string.Format("Success Data: {0}", success); })); label3.Invoke((Action)(() => { label3.Text = string.Format("Fail Data: {0}", fail); })); } }).Start(); }
private dynamic EncodeLawyer(Lawyer Data) { dynamic response = new ExpandoObject(); response.RollNumber = Data.RollNumber; response.Success = false; response.Message = ""; try { using (TransactionScope tx = new TransactionScope()) { var dbs = new basecampprodEntities(); var lawyerinfo = new UserData(); bool IsNew = false; var basicinfo = (from item in dbs.users where item.LoginID.Equals(Data.RollNumber) select item).FirstOrDefault(); response.Type = basicinfo == null ? Type.Insert : Type.Update; response.Message = basicinfo == null ? string.Format("succesfully {0} the record", Type.Insert) : string.Format("succesfully {0} the record", Type.Update); #region User Type IsNew = basicinfo == null ? true : false; basicinfo = basicinfo == null ? new user() : basicinfo; string[] fullname = Data.Name.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); string[] name = fullname[1].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries); string firstname = ""; for (int ii = 0; ii <= name.Length - 2; ii++) { firstname = firstname + " " + name[ii]; } basicinfo.LastName = fullname[0]; basicinfo.FirstName = firstname; basicinfo.MiddleName = name[name.Length - 1]; basicinfo.LoginID = Data.RollNumber; basicinfo.MembershipType = Data.LifeTimeRoll == "" ? "Annual" : "LifeTime"; basicinfo.IBPNumber = Data.LifeTimeRoll; basicinfo.AddToBar = int.Parse(Data.YearAdmitted); basicinfo.IsLiving = true; basicinfo.isLawyer = true; if (IsNew) { dbs.users.Add(basicinfo); } dbs.SaveChanges(); IsNew = false; #endregion #region UserInfo Type var OtherInfo = (from item in dbs.userinfoes where item.UserID == basicinfo.UserID select item).FirstOrDefault(); IsNew = OtherInfo == null ? true : false; OtherInfo = OtherInfo == null ? new userinfo() : OtherInfo; var ChapterDetail = (from item in dbs.chapters where item.Name.ToUpper() == Data.Chapter.ToUpper() select item).FirstOrDefault(); if (ChapterDetail != null) { OtherInfo.UserID = basicinfo.UserID; OtherInfo.ChapterID = ChapterDetail.ChapterID; OtherInfo.Birthday = Data.BirthDate; OtherInfo.Address = Data.HomePhone; OtherInfo.Sex = Data.Sex.Substring(0, 1); OtherInfo.CivilStatus = Data.Status; OtherInfo.CreatedDate = DateTime.UtcNow; if (IsNew) { dbs.userinfoes.Add(OtherInfo); } dbs.SaveChanges(); IsNew = false; } else { throw new Exception(string.Format("{0} Chapter is not Existing in Database.", Data.Chapter)); } #endregion #region PhoneInfo Type var phones = (from item in dbs.userinfophones where item.UserID == basicinfo.UserID select item).ToList(); foreach (var item in phones) { dbs.userinfophones.Remove(item); } string[] homephones = Data.HomePhone.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); string[] offphones = Data.WorkPhone.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); foreach (string phone in homephones) { var HomePhone = new userinfophone(); HomePhone.UserID = basicinfo.UserID; HomePhone.Type = "Home"; HomePhone.SecurityType = "Private"; HomePhone.Number = string.Format("{0}/{1}Home", phone, basicinfo.UserID); dbs.userinfophones.Add(HomePhone); } foreach (string phone in offphones) { var EmployePhone = new userinfophone(); EmployePhone.UserID = basicinfo.UserID; EmployePhone.Type = "Office"; EmployePhone.SecurityType = "Private"; EmployePhone.Number = string.Format("{0}/{1}Office", phone, basicinfo.UserID); dbs.userinfophones.Add(EmployePhone); } dbs.SaveChanges(); #endregion #region EmailInfo Type if (Data.Email != "") { var emails = (from item in dbs.userinfoemails where item.UserID == basicinfo.UserID select item).ToList(); foreach (var item in emails) { dbs.userinfoemails.Remove(item); } var email = new userinfoemail(); email.UserID = basicinfo.UserID; email.Type = "personal"; email.IsPrimary = false; email.EmailAddress = Data.Email; email.SecurityType = "Private"; dbs.userinfoemails.Add(email); dbs.SaveChanges(); } #endregion #region College Type var CollegeSchool = (from item in dbs.colleges where item.Name.ToUpper() == Data.LawSchool.ToUpper() select item).FirstOrDefault(); IsNew = CollegeSchool == null ? true : false; CollegeSchool = CollegeSchool == null ? new college() : CollegeSchool; CollegeSchool.Name = Data.LawSchool; if (IsNew) { dbs.colleges.Add(CollegeSchool); } dbs.SaveChanges(); var CollegeSchools = (from item in dbs.userinfocolleges where item.UserID == basicinfo.UserID select item).ToList(); foreach (var item in CollegeSchools) { dbs.userinfocolleges.Remove(item); } var collegeInfo = new userinfocollege(); collegeInfo.UserID = basicinfo.UserID; collegeInfo.ID = CollegeSchool.ID; collegeInfo.Year = new DateTime(); dbs.userinfocolleges.Add(collegeInfo); dbs.SaveChanges(); #endregion #region Employer Type var EmployerInfo = new employer(); EmployerInfo.Address = Data.WorkAddress; EmployerInfo.Name = ""; EmployerInfo.Description = Data.Occupation; dbs.employers.Add(EmployerInfo); dbs.SaveChanges(); var empInfo = new userinfoemployer(); empInfo.UserID = basicinfo.UserID; empInfo.ID = EmployerInfo.ID; empInfo.StartYear = new DateTime(); empInfo.StillEmployed = true; dbs.userinfoemployers.Add(empInfo); dbs.SaveChanges(); #endregion #region Specialization if (Data.Specialization != "") { var specialization = (from item in dbs.professions where item.Name.ToUpper() == Data.Specialization.ToUpper() select item).FirstOrDefault(); IsNew = specialization == null ? true : false; specialization = specialization == null ? new profession() : specialization; specialization.Name = Data.Specialization; if (IsNew) { dbs.professions.Add(specialization); } basicinfo.professions.Add(specialization); dbs.SaveChanges(); } #endregion response.Success = true; tx.Complete(); return response; } } catch (Exception e) { response.Type = Type.Unknown; string message = ""; if (e.InnerException != null) { if (e.InnerException.InnerException != null) { message = e.InnerException.InnerException.Message; } else { message = e.InnerException.Message; } } else { message = e.Message; } response.Message = message; return response; } }