private List <TownDAO> LoadFromExcel(string path) { List <TownDAO> excelTemplates = new List <TownDAO>(); using (var package = new ExcelPackage(new FileInfo(path))) { var worksheet = package.Workbook.Worksheets[8]; for (int i = worksheet.Dimension.Start.Row + 1; i <= worksheet.Dimension.End.Row; i++) { string provinceCode = worksheet.Cells[i, 4].Value?.ToString(); string districtCode = worksheet.Cells[i, 3].Value?.ToString(); string townCode = worksheet.Cells[i, 1].Value?.ToString(); string townName = worksheet.Cells[i, 2].Value?.ToString(); TownDAO excelTemplate = new TownDAO() { Id = CreateGuid("Town" + provinceCode + districtCode + townCode), DistrictId = CreateGuid("District" + provinceCode + districtCode), Code = townCode, Name = townName }; excelTemplates.Add(excelTemplate); } } return(excelTemplates); }
public async Task <bool> Create(Town town) { TownDAO TownDAO = new TownDAO { Id = town.Id, Code = town.Code, Name = town.Name, DistrictId = town.DistrictId, }; context.Town.Add(TownDAO); await context.SaveChangesAsync(); return(true); }
public Towns() { towns = new TownDAO(); }
private void readDataFormExcel() { Excel.Application xlApp; Excel.Workbook xlWorkBook; // For "Employee Info data","Education & Working Experience","Work History & Bank Account","Parents & Sibling","Spouse & Family" WorkSheets Excel.Worksheet xlWSTown; //sheet 6 Excel.Worksheet xlWSTownship; //sheet 7 Excel.Range rangeTown, rangeTownship; int rCnt = 0; int cCnt = 0; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(txtFileName.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); #region get sheet and define range xlWSTown = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(6); xlWSTownship = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(7); rangeTown = xlWSTown.UsedRange; rangeTownship = xlWSTownship.UsedRange; #endregion #region For Town for (rCnt = 1; rCnt <= rangeTown.Rows.Count; rCnt++) { if ((rangeTown.Cells[rCnt + 3, 3] as Excel.Range).Value != null) { #region Sheet to Array string[] Town = new string[rangeTown.Columns.Count]; //In Town[], cell number 0 and 4. for (cCnt = 0; cCnt <= rangeTown.Columns.Count; cCnt++) { int i = cCnt + 1; if ((rangeTown.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value != null) { Town[i - 1] = (rangeTown.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value.ToString(); } } #endregion if (Town.Count() != 0) { int TownID = 0; #region sheet 6 try { #region for inserting Town information if (Town[0] != null && Town[4] != null) { TownVO vo = new TownVO(); string stateDivisionName = Town[0].ToString(); string townName = Town[4].ToString(); System.Data.DataTable dt = new StateDivisionDAO().SelectByDivisionName(stateDivisionName); foreach (DataRow row in dt.Rows) { object value = row["ID"]; if (value == DBNull.Value) { MessageBox.Show("Can't Find State Division!"); } else { //Geting DivisionID, not inserting town one time var Id = dt.Rows[0][0]; string ID = Id.ToString(); int stateDivisionID = Convert.ToInt32(ID); vo.StateDivisionID = stateDivisionID; vo.Town = townName; vo.DateAdded = DateTime.Now; vo.LastModified = DateTime.Now; vo.IsDeleted = false; //check town if it is duplicate TownID = new TownDAO().SelectIDByTownName(townName); if (TownID == 0) { TownDAO dao = new TownDAO(); dao.Insert(vo); insertedTownCount++; //MessageBox.Show("Inserting Town Information is Success"); } else { //MessageBox.Show("This Town Data has already in Database"); } } } } #endregion } catch (Exception ex) { MessageBox.Show("Insert Error:" + ex.ToString()); } #endregion } } } #endregion #region for Township for (rCnt = 1; rCnt <= rangeTownship.Rows.Count; rCnt++) { if ((rangeTownship.Cells[rCnt + 3, 3] as Excel.Range).Value != null) { #region Sheet to Array string[] Township = new string[rangeTownship.Columns.Count]; for (cCnt = 0; cCnt <= rangeTownship.Columns.Count; cCnt++) { int i = cCnt + 1; if ((rangeTownship.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value != null) { Township[i - 1] = (rangeTownship.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value.ToString(); } } #endregion if (Township.Count() != 0) { #region sheet 7 try { #region for inserting Township information if (Township[2] != null & Township[5] != null) { int TownshipID = 0; TownshipInfoVO vo = new TownshipInfoVO(); string townName = Township[2]; string townshipName = Township[5]; int TownID = new TownDAO().SelectIDByTownName(townName); vo.TownID = TownID; vo.Township = townshipName; vo.DateAdded = DateTime.Now; vo.LastModified = DateTime.Now; vo.IsDeleted = false; TownshipID = new TownshipInfoDAO().SelectIDByTownshipName(townshipName); if (TownshipID == 0) { TownshipInfoDAO dao = new TownshipInfoDAO(); dao.Insert(vo); //MessageBox.Show("Inserting Township Information is Success"); insertedTownshipCount++; } else { //MessageBox.Show("This Township Data is already in Database."); } } #endregion } catch (Exception ex) { MessageBox.Show("Insert Error:" + ex.ToString()); } #endregion } } } #endregion MessageBox.Show("Importing Town and Township Information is finished."); Debug.WriteLine(insertedTownCount); Debug.WriteLine(insertedTownshipCount); }