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 xlWSInfo;
Excel.Worksheet xlWSEduWorkExp;
Excel.Worksheet xlWSWorkHisBank;
Excel.Worksheet xlWSParSib;
Excel.Worksheet xlWSSpouFam;
Excel.Range range, rangeEdu, rangeWHis, rangeParent, rangeSpouse;
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
xlWSInfo = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWSEduWorkExp = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
xlWSWorkHisBank = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(3);
xlWSParSib = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(4);
xlWSSpouFam = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(5);
range = xlWSInfo.UsedRange;
rangeEdu = xlWSEduWorkExp.UsedRange;
rangeWHis = xlWSWorkHisBank.UsedRange;
rangeParent = xlWSParSib.UsedRange;
rangeSpouse = xlWSSpouFam.UsedRange;
#endregion
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
if ((range.Cells[rCnt + 3, 3] as Excel.Range).Value != null)
{
#region Sheet to Array
// For Info
string[] Info = new string[range.Columns.Count - 1];
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
{
int i = cCnt;
if ((range.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value != null)
{
Info[i - 1] = (range.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value.ToString();
}
}
// For Education and Working Experience
string[] EduWEx = new string[rangeEdu.Columns.Count - 1];
for (cCnt = 1; cCnt <= rangeEdu.Columns.Count; cCnt++)
{
int i = cCnt;
if ((rangeEdu.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value != null)
{
EduWEx[i - 1] = (rangeEdu.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value.ToString();
}
}
// For Working History and Bank Information
string[] WorkHisBank = new string[rangeWHis.Columns.Count - 1];
for (cCnt = 1; cCnt <= rangeWHis.Columns.Count; cCnt++)
{
int i = cCnt;
if ((rangeWHis.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value != null)
{
WorkHisBank[i - 1] = (rangeWHis.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value.ToString();
}
}
// For Parent and Sibling
string[] ParSib = new string[rangeParent.Columns.Count - 1];
for (cCnt = 1; cCnt <= rangeParent.Columns.Count; cCnt++)
{
int i = cCnt;
if ((rangeParent.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value != null)
{
ParSib[i - 1] = (rangeParent.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value.ToString();
}
}
// For Spouse and Family
string[] SpoFam = new string[rangeSpouse.Columns.Count - 1];
for (cCnt = 1; cCnt <= rangeSpouse.Columns.Count; cCnt++)
{
int i = cCnt;
if ((rangeSpouse.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value != null)
{
SpoFam[i - 1] = (rangeSpouse.Cells[rCnt + 3, cCnt + 1] as Excel.Range).Value.ToString();
}
}
#endregion
if (Info.Count() != 0)
{
#region Sheet1
try
{
#region For Inserting into Employee Table
EmployeeVO employee = new EmployeeVO();
try
{
//Get Employed Date
if (Info[0] != null)
{
employee.EmployDate = DateTime.Parse(Info[0].ToString());
}
//Get Employee Name
if (Info[1] != null)
{
employee.EmpName = Info[1].ToString();
}
//Get Position Name and use it to retrieve Position ID
if (Info[2] != null)
{
string postName = Info[2].ToString();
int PositionID = new EmployeeDAO().SelectIDByPositionName(postName);
employee.PostID = PositionID;
//employee.PostID = 1;
}
//Get Department name and use it to retrieve Deptment ID
if (Info[3] != null)
{
string deptName = Info[3].ToString();
int DeptID = new EmployeeDAO().SelectIDByDeptName(deptName);
employee.DeptID = DeptID;
}
//Get fingerID
if (Info[4] != null)
{
string fingerID = Info[4].ToString();
employee.FingerID = Convert.ToInt32(fingerID);
}
//Get Date of Birth
if (Info[5] != null)
{
string dateOfBirth = Info[5].ToString();
DateTime DateOfBirth = DateTime.Parse(dateOfBirth);
employee.DOB = DateOfBirth;
}
//Get Employee NRC
if (Info[6] != null)
{
employee.NRCNo = Info[6].ToString();
}
//Get Employed Date
if (Info[7] != null)
{
string employedDate = Info[7].ToString();
DateTime EmployedDate = DateTime.Parse(employedDate);
employee.EmployDate = EmployedDate;
}
//Get Approval Date
if (Info[8] != null)
{
employee.ApprovalDate = DateTime.Parse(Info[8].ToString());
employee.IsPermanent = true;
}
else
{
employee.IsPermanent = false;
}
//Get Father Name
if (Info[9] != null)
{
employee.FatherName = Info[9].ToString();
}
//Get Race
if (Info[10] != null)
{
employee.Race = Info[10].ToString();
}
//Get Religion
if (Info[11] != null)
{
if (Info[10].ToString() == "ဗုဒ္ဓဘာသာ")
{
employee.Religion = 1;
}
else if (Info[10].ToString() == "မူဆလင်")
{
employee.Religion = 2;
}
else if (Info[10].ToString() == "ခရစ်ယာန်")
{
employee.Religion = 3;
}
else
{
employee.Religion = 4;
}
}
//Get Gender
if (Info[12] != null)
{
if (Info[12].ToString() == "1")
{
employee.Gender = true;
}
else if (Info[12].ToString() == "0")
{
employee.Gender = false;
}
}
//get Mariental Status
if (Info[13] != null)
{
if (Info[13].ToString() == "1")
{
employee.MaritalStatus = true;
}
else if (Info[13].ToString() == "0")
{
employee.MaritalStatus = false;
}
}
//get Passport Number
if (Info[14] != null)
{
employee.PassportNo = Info[14].ToString();
}
//Get Social Security Number
if (Info[15] != null)
{
employee.SSCode = Info[15].ToString();
}
//Get Driver License
if (Info[16] != null)
{
employee.DriverLicence = Info[16].ToString();
}
//Get in Case of Death Name of beneficiary
if (Info[17] != null)
{
employee.IncaseOfDeathNameOfBeneficiary = Info[17].ToString();
}
// Get Criminal
if (Info[18] != null)
{
if (Info[13].ToString() == "1")
{
employee.Criminal = true;
}
else if (Info[13].ToString() == "0")
{
employee.Criminal = false;
}
}
employee.ApplicantID = 1;
employee.PostID = 1;
employee.EmpPhoto = null;
employee.SalaryLvlID = 1;
//employee.Criminalrecord = null;
//employee.ReferencesName1 = null;
//employee.ReferencesName2 = null;
//employee.ReferencesPh1 = null;
//employee.ReferencesPh2 = null;
if (EduWEx[31] != null)
{
employee.ReferencesName1 = EduWEx[31];
}
if (EduWEx[33] != null)
{
employee.ReferencesPh1 = EduWEx[33];
}
employee.IsActive = true;
employee.IsDeleted = false;
employee.CreatedDate = DateTime.Today;
employee.LastModified = DateTime.Today;
employee.IsBLO = null;
employee.EmpPhoto = new byte[0];
//Insert into employee table
EmployeeDAO employeeDAO = new EmployeeDAO();
//This is need to uncomment
int lastInsertId = employeeDAO.Insert(employee);
EMPLOYEEID = lastInsertId;
}
catch (Exception ex)
{
MessageBox.Show("Error Occurred in inserting to Employee Table!");
return;
}
#endregion
}
catch (Exception ex)
{
MessageBox.Show("Empoyee Insert Error:" + ex.ToString());
}
try
{
#region for inserting to employee address table Both Current Address and Permanent Address
try
{
AddressVO address = new AddressVO();
//Get Home Number
if (Info[19] != null)
{
address.HomeNo = Info[19];
}
//Get Street Number
if (Info[20] != null)
{
address.Street = Info[20];
}
if (Info[21] != null)
{
address.Quarter = Info[21];
}
//Get TownshipID by Name
if (Info[22] != null)
{
string townshipName = Info[22].ToString();
int TownshipID = new TownshipInfoDAO().SelectIDByTownshipName(townshipName);
address.TownshipID = TownshipID;
}
if (Info[32] != null)
{
address.Phone = Info[32].ToString();
}
//Get State Division ID by Name
if (Info[24] != null)
{
string stateDivisionName = Info[24].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 Filled State Division!");
}
else
{
var Id = dt.Rows[0][0];
string ID = Id.ToString();
int stateDivisionID = Convert.ToInt32(ID);
address.StateDivisionID = stateDivisionID;
}
}
}
address.ApplicantID = 1;
address.IsPermanent = false;
//Get EmpID from FingerID
string figerID = Info[4].ToString();
int FigerID = Convert.ToInt32(figerID);
System.Data.DataTable EmpIDDT = new EmployeeDAO().GetEmpIDFromFingerID(FigerID);
foreach (DataRow row in EmpIDDT.Rows)
{
object value = row["ID"];
if (value == DBNull.Value)
{
MessageBox.Show("Can't Find EmpID from FingerID!");
}
else
{
int EmpID = Convert.ToInt32(row["ID"].ToString());
address.EmpID = EmpID;
}
}
AddressDAO addressDAO = new AddressDAO();
int lastInsertedId = addressDAO.Insert(address);
if (lastInsertedId > 0)
{
// MessageBox.Show("Inserting to Address table for not permanent is success");
}
}
catch (Exception ex)
{
MessageBox.Show("Error Occurred in Employee Info!");
return;
}
try
{
AddressVO vo = new AddressVO();
vo.EmpID = EMPLOYEEID;
vo.ApplicantID = 1;
vo.IsPermanent = true;
if (Info[26] != null)
{
vo.HomeNo = Info[26];
}
if (Info[27] != null)
{
vo.Street = Info[27];
}
if (Info[28] != null)
{
vo.Quarter = Info[28];
}
if (Info[29] != null)
{
string townshipName = Info[29];
int TownshipID = new TownshipInfoDAO().SelectIDByTownshipName(townshipName);
vo.TownshipID = TownshipID;
}
if (Info[32] != null)
{
vo.Phone = Info[32];
}
if (Info[31] != null)
{
string stateDivisionName = Info[31];
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 Filled State Division!");
}
else
{
var Id = dt.Rows[0][0];
string ID = Id.ToString();
int stateDivisionID = Convert.ToInt32(ID);
vo.StateDivisionID = stateDivisionID;
//MessageBox.Show(ID);
}
}
}
AddressDAO addressDAO = new AddressDAO();
int lastInsertedId = addressDAO.Insert(vo);
if (lastInsertedId > 0)
{
// MessageBox.Show("Inserting to Address table for permanent is success");
}
}
catch (Exception ex)
{
MessageBox.Show("Error Occurred in Employee Info!");
return;
}
#endregion
}
catch (Exception ex)
{
MessageBox.Show("Address Insert Error:" + ex.ToString());
}
#endregion
}
if (EduWEx.Count() != 0)
{
#region sheet2
#region for Inserting to Employee_Qualification
try
{
EmployeeQualificationVO quvo = new EmployeeQualificationVO();
//Database colums are EmpID, Degree, University, QYear
//Get string value from Excel
//EmpID should be declared as global variable
quvo.EmpID = EMPLOYEEID;
if (EduWEx[4] != null && EduWEx[5] != null && EduWEx[6] != null)
{
quvo.Degree = EduWEx[4]; quvo.University = EduWEx[5]; quvo.QYear = EduWEx[6];
EmployeeQualificationDAO dao = new EmployeeQualificationDAO();
dao.Insert(quvo);
}
if (EduWEx[7] != null && EduWEx[8] != null && EduWEx[9] != null)
{
quvo.Degree = EduWEx[7]; quvo.University = EduWEx[8]; quvo.QYear = EduWEx[9];
EmployeeQualificationDAO dao = new EmployeeQualificationDAO();
dao.Insert(quvo);
}
if (EduWEx[10] != null && EduWEx[11] != null && EduWEx[12] != null)
{
quvo.Degree = EduWEx[10]; quvo.University = EduWEx[11]; quvo.QYear = EduWEx[12];
EmployeeQualificationDAO dao = new EmployeeQualificationDAO();
dao.Insert(quvo);
}
if (EduWEx[13] != null && EduWEx[14] != null && EduWEx[15] != null)
{
quvo.Degree = EduWEx[13]; quvo.University = EduWEx[14]; quvo.QYear = EduWEx[15];
EmployeeQualificationDAO dao = new EmployeeQualificationDAO();
dao.Insert(quvo);
}
//MessageBox.Show("Inserting to Qualification Table is OK");
}
catch (Exception ex)
{
MessageBox.Show("Error Occurred in Inserting Data to Employee_Qualification!");
return;
}
#endregion
#region for Inserting Working Experience
EmployeeWorkingExperienceVO vo = new EmployeeWorkingExperienceVO();
vo.EmpID = EMPLOYEEID;
//17/18/19/20/21 - Array Index for Column
if (EduWEx[16] != null && EduWEx[17] != null && EduWEx[18] != null && EduWEx[19] != null && EduWEx[20] != null)
{
vo.Period = EduWEx[16]; vo.Company = EduWEx[17]; vo.Position = EduWEx[18]; vo.Address = EduWEx[19]; vo.Phone = EduWEx[20]; vo.RefName = EduWEx[31];
EmployeeWorkingExperienceDAO doa = new EmployeeWorkingExperienceDAO();
doa.Insert(vo);
}
if (EduWEx[21] != null && EduWEx[22] != null && EduWEx[23] != null && EduWEx[24] != null && EduWEx[25] != null)
{
vo.Period = EduWEx[21]; vo.Company = EduWEx[22]; vo.Position = EduWEx[23]; vo.Address = EduWEx[24]; vo.Phone = EduWEx[25]; vo.RefName = EduWEx[31];
EmployeeWorkingExperienceDAO doa = new EmployeeWorkingExperienceDAO();
doa.Insert(vo);
}
if (EduWEx[26] != null && EduWEx[27] != null && EduWEx[28] != null && EduWEx[29] != null && EduWEx[30] != null)
{
vo.Period = EduWEx[26]; vo.Company = EduWEx[27]; vo.Position = EduWEx[28]; vo.Address = EduWEx[29]; vo.Phone = EduWEx[30]; vo.RefName = EduWEx[31];
EmployeeWorkingExperienceDAO doa = new EmployeeWorkingExperienceDAO();
doa.Insert(vo);
}
//MessageBox.Show("Inserting to Working Experience is OK");
#endregion
#endregion
}
if (WorkHisBank.Count() != 0)
{
#region sheet3
#region for Inserting Bank Information
//Insert employee bank info
//EmpID,BankID,AccountNumber,AccountType
EmployeeBankInfoVO vo = new EmployeeBankInfoVO();
//Cell Start From 15,16,17->18,19,20
vo.EmpID = EMPLOYEEID;
if (WorkHisBank[14] != null && WorkHisBank[15] != null && WorkHisBank[16] != null)
{
string bankName = WorkHisBank[14];
BankVO bvo = new BankDAO().GetByBank(bankName);
vo.BankID = bvo.Id;//Get BankID from Bank table
vo.AccountNumber = WorkHisBank[15];
vo.AccountType = WorkHisBank[16];
EmployeeBankInfoDAO empdao = new EmployeeBankInfoDAO();
empdao.Insert(vo);
}
if (WorkHisBank[17] != null && WorkHisBank[18] != null && WorkHisBank[19] != null)
{
string bankName = WorkHisBank[17];
BankVO bvo = new BankDAO().GetByBank(bankName);
vo.BankID = bvo.Id;//Get BankID from Bank table
vo.AccountNumber = WorkHisBank[18];
vo.AccountType = WorkHisBank[19];
EmployeeBankInfoDAO empdao = new EmployeeBankInfoDAO();
empdao.Insert(vo);
// MessageBox.Show("Inserting in to Employee bank Info is OK");
}
#endregion
#endregion
}
if (ParSib.Count() != 0)
{
#region sheet4
#region for Inserting Both Relative Information and Children Information
EmployeeRelativeVO vo = new EmployeeRelativeVO();
vo.EmpID = EMPLOYEEID;
if (ParSib[4] != null)
{
vo.RelativeName = ParSib[4];
vo.Relation = "Partner";
}
if (ParSib[7] != null)
{
vo.NameOfCompany = ParSib[7];
}
if (ParSib[5] != null)
{
vo.Occupation = ParSib[5];
}
if (ParSib[8] != null)
{
vo.Address = ParSib[8];
}
if (ParSib[6] != null)
{
vo.Phone = ParSib[6];
}
EmployeeRelativeDAO redao = new EmployeeRelativeDAO();
redao.Insert(vo);
if (ParSib[10] != null && ParSib[11] != null)
{
EmployeeRelativeVO childrenvo1 = new EmployeeRelativeVO();
childrenvo1.EmpID = EMPLOYEEID;
childrenvo1.RelativeName = ParSib[10];
childrenvo1.Relation = "Children";
redao.Insert(childrenvo1);
string dateOfBirth = ParSib[11].ToString();
DateTime DateOfBirth = DateTime.Parse(dateOfBirth);
string DOB = DateOfBirth.ToString("yyyy-MM-dd");
redao.InsertWithNoObject("INSERT INTO Employee_Children (EmpID, ChildrenName, DOB) VALUES (" + EMPLOYEEID + ", N'" + ParSib[10] + "'," + DOB + ")");
}
if (ParSib[12] != null && ParSib[13] != null)
{
EmployeeRelativeVO childrenvo2 = new EmployeeRelativeVO();
childrenvo2.EmpID = EMPLOYEEID;
childrenvo2.RelativeName = ParSib[12];
childrenvo2.Relation = "Children";
redao.Insert(childrenvo2);
string dateOfBirth = ParSib[13].ToString();
DateTime DateOfBirth = DateTime.Parse(dateOfBirth);
string DOB = DateOfBirth.ToString("yyyy-MM-dd");
redao.InsertWithNoObject("INSERT INTO Employee_Children (EmpID, ChildrenName, DOB) VALUES (" + EMPLOYEEID + ", N'" + ParSib[12] + "'," + DOB + ")");
}
if (ParSib[14] != null && ParSib[15] != null)
{
EmployeeRelativeVO childrenvo3 = new EmployeeRelativeVO();
childrenvo3.EmpID = EMPLOYEEID;
childrenvo3.RelativeName = ParSib[14];
childrenvo3.Relation = "Children";
redao.Insert(childrenvo3);
string dateOfBirth = ParSib[15].ToString();
DateTime DateOfBirth = DateTime.Parse(dateOfBirth);
string DOB = DateOfBirth.ToString("yyyy-MM-dd");
redao.InsertWithNoObject("INSERT INTO Employee_Children (EmpID, ChildrenName, DOB) VALUES (" + EMPLOYEEID + ", N'" + ParSib[14] + "'," + DOB + ")");
}
if (ParSib[16] != null && ParSib[17] != null)
{
EmployeeRelativeVO childrenvo4 = new EmployeeRelativeVO();
childrenvo4.EmpID = EMPLOYEEID;
childrenvo4.RelativeName = ParSib[16];
childrenvo4.Relation = "Children";
redao.Insert(childrenvo4);
string dateOfBirth = ParSib[17].ToString();
DateTime DateOfBirth = DateTime.Parse(dateOfBirth);
string DOB = DateOfBirth.ToString("yyyy-MM-dd");
redao.InsertWithNoObject("INSERT INTO Employee_Children (EmpID, ChildrenName, DOB) VALUES (" + EMPLOYEEID + ", N'" + ParSib[16] + "'," + DOB + ")");
}
//MessageBox.Show("Inserting into Employee Relative and Children is OK");
#endregion
#endregion
}
}
}
}
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);
}
