public CResult<bool> AddPeople(WebPeopleBasicInfo webPeople) { var people = new PeopleBasicInfo() { WorkerNum = webPeople.WorkerNum, PeopleName = webPeople.PeopleName, DateOfBirth = webPeople.DateOfBirth, Gender = webPeople.Gender, Age = webPeople.Age, IDCardNum = webPeople.IDCardNum, IsMarried = webPeople.IsMarried, PoliticalStatus = webPeople.PoliticalStatus, Nation = webPeople.Nation, HouseholdRegisterAddress = webPeople.HouseholdRegisterAddress, HouseholdRegisterType = webPeople.HouseholdRegisterType, HouseholdRegisterPostCode = webPeople.HouseholdRegisterPostCode, CurrentAddress = webPeople.CurrentAddress, PhoneNum = webPeople.PhoneNum, CurrentAddressPostCode = webPeople.CurrentAddressPostCode, EducationGrade = webPeople.EducationGrade, GraduationSchool = webPeople.GraduationSchool, Profession = webPeople.Profession, EmergencyContactPerson = webPeople.EmergencyContactPerson, EmergencyContactPersonPhone = webPeople.EmergencyContactPersonPhone, EmergencyContactPersonRelation = webPeople.EmergencyContactPersonRelation }; var result = new PeopleDAL().InsertPeople(people); if (result) { return new CResult<bool>(true); } else { return new CResult<bool>(false, ErrorCode.SaveDataFailed); } }
public PeopleBasicInfo GetPeopleBasicInfoByID(int peopleID) { using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) { string sqlText = string.Format(@"select PeopleID, PeopleName,Gender,DateOfBirth,Age,IDCardNum,IsMarried,PoliticalStatus, Nation,HouseholdRegisterAddress,HouseholdRegisterType,HouseholdRegisterPostCode,CurrentAddress,PhoneNum,CurrentAddressPostCode, EducationGrade,GraduationSchool,Profession,EmergencyContactPerson,EmergencyContactPersonPhone,EmergencyContactPersonRelation ,WorkerNum from {0} where PeopleID=@peopleID", _peopleTableName); var paras = new OleDbParameter[1]; paras[0] = new OleDbParameter("peopleID", peopleID); var reader = new OLESqlHelper().GetReader(sqlText, connection, paras); if (reader.Read()) { int i = 0; var people = new PeopleBasicInfo(); people.PeopleID = reader.GetInt32(i++); people.PeopleName = reader.GetString(i++); people.Gender = reader.GetString(i++); if (!reader.IsDBNull(i)) { people.DateOfBirth = reader.GetDateTime(i++); } else { i++; } people.Age = reader.GetInt32(i++); people.IDCardNum = reader.GetString(i++); people.IsMarried = reader.GetBoolean(i++); people.PoliticalStatus = reader.GetString(i++); people.Nation = reader.GetString(i++); people.HouseholdRegisterAddress = reader.GetString(i++); people.HouseholdRegisterType = reader.GetString(i++); people.HouseholdRegisterPostCode = reader.GetString(i++); people.CurrentAddress = reader.GetString(i++); people.PhoneNum = reader.GetString(i++); people.CurrentAddressPostCode = reader.GetString(i++); people.EducationGrade = reader.GetString(i++); people.GraduationSchool = reader.GetString(i++); people.Profession = reader.GetString(i++); people.EmergencyContactPerson = reader.GetString(i++); people.EmergencyContactPersonPhone = reader.GetString(i++); people.EmergencyContactPersonRelation = reader.GetString(i++); people.WorkerNum = reader.GetString(i++); return people; } else { return null; } } }
public List<PeopleBasicInfo> GetPeopleList(out int totalCount, string searchKey = "", int pageIndex = 1, int pageSize = 10) { searchKey = searchKey.Trim(); string sqlText = string.Format("select count(*) from {0} where (PeopleName like '%{1}%' or WorkerNum like '%{1}%')", _peopleTableName, searchKey); totalCount = new OLESqlHelper().GetCount(sqlText); var exceptCount = (pageIndex - 1) * pageSize; if (exceptCount > 0) { sqlText = string.Format("select top {0} * from {1} where (PeopleName like '%{3}%' or WorkerNum like '%{3}%') and PeopleID > (select max(PeopleID) from (select top {2} PeopleID from {1} where PeopleName like '%{3}%' or WorkerNum like '%{3}%' order by PeopleID)) order by PeopleID", pageSize, _peopleTableName, exceptCount, searchKey); } else { sqlText = string.Format("select top {0} * from {1} where PeopleName like '%{2}%' or WorkerNum like '%{2}%' order by PeopleID", pageSize, _peopleTableName, searchKey); } var table = new OLESqlHelper().GetTable(sqlText); var peopleList = new List<PeopleBasicInfo>(); foreach (DataRow row in table.Rows) { var people = new PeopleBasicInfo() { PeopleID = (int)row["PeopleID"], Gender = row["Gender"].ToString(), PeopleName = row["PeopleName"].ToString(), DateOfBirth = DateTime.Parse(row["DateOfBirth"].ToString()), Department = row["Department"].ToString(), PhoneNum = row["PhoneNum"].ToString(), Title = row["Title"].ToString(), WorkerNum = row["WorkerNum"].ToString(), TitleState = row["TitleState"].ToString(), }; peopleList.Add(people); } return peopleList; }
public bool UpdatePeopleWorkInfo(PeopleBasicInfo people) { string sqlText = string.Format(@"update {0} set JoinCompanyDate=@JoinCompanyDate, LengthOfService=@LengthOfService, Department=@Department, Title=@Title, TitleState=@TitleState, LeftCompanyDate=@LeftCompanyDate, LeftCompanyReason=@LeftCompanyReason, SpecialSkill=@SpecialSkill, WageCardNumber=@WageCardNumber, WageCardAddress=@WageCardAddress, IsCallInArchives=@IsCallInArchives, ArchivesNum=@ArchivesNum, ArchiveCallInDate=@ArchiveCallInDate, ArchiveCallOutDate=@ArchiveCallOutDate where PeopleID=@PeopleID", _peopleTableName); var paras = new OleDbParameter[15]; paras[0] = new OleDbParameter("JoinCompanyDate", people.JoinCompanyDate); paras[0].OleDbType = OleDbType.DBDate; paras[1] = new OleDbParameter("LengthOfService", people.LengthOfService); paras[1].OleDbType = OleDbType.Single; paras[2] = new OleDbParameter("Department", people.Department); paras[3] = new OleDbParameter("Title", people.Title); paras[4] = new OleDbParameter("TitleState", people.TitleState); paras[5] = new OleDbParameter("LeftCompanyDate", people.LeftCompanyDate); paras[5].OleDbType = OleDbType.DBDate; paras[6] = new OleDbParameter("LeftCompanyReason", people.LeftCompanyReason); paras[7] = new OleDbParameter("SpecialSkill", people.SpecialSkill); paras[8] = new OleDbParameter("WageCardNumber", people.WageCardNumber); paras[9] = new OleDbParameter("WageCardAddress", people.WageCardAddress); paras[10] = new OleDbParameter("IsCallInArchives", people.IsCallInArchives); paras[11] = new OleDbParameter("ArchivesNum", people.ArchivesNum); paras[12] = new OleDbParameter("ArchiveCallInDate", people.ArchiveCallInDate); paras[12].OleDbType = OleDbType.DBDate; paras[13] = new OleDbParameter("ArchiveCallOutDate", people.ArchiveCallOutDate); paras[13].OleDbType = OleDbType.DBDate; paras[14] = new OleDbParameter("PeopleID", people.PeopleID); // string sqlText = string.Format(@"update {0} set // LengthOfService=@LengthOfService // where PeopleID=@PeopleID", _ContractTableName); // var paras = new OleDbParameter[2]; // paras[0] = new OleDbParameter("LengthOfService", contract.LengthOfService); // //paras[5] = new OleDbParameter("LeftCompanyDate", contract.LeftCompanyDate.Value); // //paras[6] = new OleDbParameter("LeftCompanyReason", contract.LeftCompanyReason); // paras[1] = new OleDbParameter("PeopleID", contract.PeopleID); var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras); return result; }
public bool UpdatePeople(PeopleBasicInfo people) { string sqlText = string.Format(@"update {0} set WorkerNum=@WorkerNum, PeopleName=@peopleName, Gender=@gender, DateOfBirth=@dateOfBirth, Age=@Age, IDCardNum=@IDCardNum, IsMarried=@IsMarried, PoliticalStatus=@PoliticalStatus, Nation=@Nation, HouseholdRegisterAddress=@HouseholdRegisterAddress, HouseholdRegisterType=@HouseholdRegisterType, HouseholdRegisterPostCode=@HouseholdRegisterPostCode, CurrentAddress=@CurrentAddress, PhoneNum=@PhoneNum, CurrentAddressPostCode=@CurrentAddressPostCode, EducationGrade=@EducationGrade, GraduationSchool=@GraduationSchool, Profession=@Profession, EmergencyContactPerson=@EmergencyContactPerson, EmergencyContactPersonPhone=@EmergencyContactPersonPhone, EmergencyContactPersonRelation=@EmergencyContactPersonRelation where PeopleID=@peopleID", _peopleTableName); var paras = new OleDbParameter[22]; paras[0] = new OleDbParameter("WorkerNum", people.WorkerNum); paras[1] = new OleDbParameter("peopleName", people.PeopleName); paras[2] = new OleDbParameter("gender", people.Gender); paras[3] = new OleDbParameter("dateOfBirth", people.DateOfBirth.Value); paras[4] = new OleDbParameter("Age", people.Age); paras[5] = new OleDbParameter("IDCardNum", people.IDCardNum); paras[6] = new OleDbParameter("IsMarried", people.IsMarried); paras[7] = new OleDbParameter("PoliticalStatus", people.PoliticalStatus); paras[8] = new OleDbParameter("Nation", people.Nation); paras[9] = new OleDbParameter("HouseholdRegisterAddress", people.HouseholdRegisterAddress); paras[10] = new OleDbParameter("HouseholdRegisterType", people.HouseholdRegisterType); paras[11] = new OleDbParameter("HouseholdRegisterPostCode", people.HouseholdRegisterPostCode); paras[12] = new OleDbParameter("CurrentAddress", people.CurrentAddress); paras[13] = new OleDbParameter("PhoneNum", people.PhoneNum); paras[14] = new OleDbParameter("CurrentAddressPostCode", people.CurrentAddressPostCode); paras[15] = new OleDbParameter("EducationGrade", people.EducationGrade); paras[16] = new OleDbParameter("GraduationSchool", people.GraduationSchool); paras[17] = new OleDbParameter("Profession", people.Profession); paras[18] = new OleDbParameter("EmergencyContactPerson", people.EmergencyContactPerson); paras[19] = new OleDbParameter("EmergencyContactPersonPhone", people.EmergencyContactPersonPhone); paras[20] = new OleDbParameter("EmergencyContactPersonRelation", people.EmergencyContactPersonRelation); paras[21] = new OleDbParameter("peopleID", people.PeopleID); var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras); return result; }
public PeopleBasicInfo GetPeopleWorkInfoByID(int peopleID) { using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) { string sqlText = string.Format(@"select PeopleID,JoinCompanyDate,LengthOfService,Department,Title,TitleState, LeftCompanyDate,LeftCompanyReason,SpecialSkill,WageCardNumber,WageCardAddress, IsCallInArchives,ArchivesNum,ArchiveCallInDate,ArchiveCallOutDate from {0} where PeopleID=@peopleID", _peopleTableName); var paras = new OleDbParameter[1]; paras[0] = new OleDbParameter("peopleID", peopleID); var reader = new OLESqlHelper().GetReader(sqlText, connection, paras); if (reader.Read()) { int i = 0; var people = new PeopleBasicInfo(); people.PeopleID = reader.GetInt32(i++); if (!reader.IsDBNull(i)) { people.JoinCompanyDate = reader.GetDateTime(i++); } else { i++; } people.LengthOfService = reader.GetFloat(i++); people.Department = reader.IsDBNull(i) ? "" : reader.GetString(i++); people.Title = reader.IsDBNull(i) ? "" : reader.GetString(i++); people.TitleState = reader.IsDBNull(i) ? "" : reader.GetString(i++); people.LeftCompanyDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++); people.LeftCompanyReason = reader.IsDBNull(i) ? "" : reader.GetString(i++); people.SpecialSkill = reader.IsDBNull(i) ? "" : reader.GetString(i++); people.WageCardNumber = reader.IsDBNull(i) ? "" : reader.GetString(i++); people.WageCardAddress = reader.IsDBNull(i) ? "" : reader.GetString(i++); people.IsCallInArchives = reader.IsDBNull(i) ? false : reader.GetBoolean(i++); people.ArchivesNum = reader.IsDBNull(i) ? "" : reader.GetString(i++); people.ArchiveCallInDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++); people.ArchiveCallOutDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++); return people; } else { return null; } } }
public CResult<bool> UpdatePeopleWorkInfo(WebPeopleWorkInfo webPeopleWorkInfo) { var people = new PeopleBasicInfo() { PeopleID = webPeopleWorkInfo.PeopleID, JoinCompanyDate = webPeopleWorkInfo.JoinCompanyDate, LengthOfService = webPeopleWorkInfo.LengthOfService, Department = webPeopleWorkInfo.Department, Title = webPeopleWorkInfo.Title, TitleState = webPeopleWorkInfo.TitleState, LeftCompanyDate = webPeopleWorkInfo.LeftCompanyDate, LeftCompanyReason = webPeopleWorkInfo.LeftCompanyReason, SpecialSkill = webPeopleWorkInfo.SpecialSkill, WageCardNumber = webPeopleWorkInfo.WageCardNumber, WageCardAddress = webPeopleWorkInfo.WageCardAddress, IsCallInArchives = webPeopleWorkInfo.IsCallInArchives, ArchivesNum = webPeopleWorkInfo.ArchivesNum, ArchiveCallInDate = webPeopleWorkInfo.ArchiveCallInDate, ArchiveCallOutDate = webPeopleWorkInfo.ArchiveCallOutDate, }; var result = new PeopleDAL().UpdatePeopleWorkInfo(people); if (result) { return new CResult<bool>(true); } else { return new CResult<bool>(false, ErrorCode.SaveDataFailed); } }