public string[] createExternalTeacherData(CreateExternal StaffData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); Sqlconn.Open(); string sql = "INSERT INTO ExternalTeacherDatabase (Unit, TeachName, TeachIdentity, AddressZip1, AddressCity1, AddressOther1, AddressZip2, AddressCity2, AddressOther2, Phone, Phone2, Email, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate ) " + "VALUES (@Unit, @TeachName, @TeachIdentity, @AddressZip1, @AddressCity1, @AddressOther1, @AddressZip2, @AddressCity2, @AddressOther2, @Phone, @Phone2, @Email, @CreateFileBy, getDate(), @UpFileBy, getDate())"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2]); cmd.Parameters.Add("@TeachName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.staffName); cmd.Parameters.Add("@TeachIdentity", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.staffTWID); cmd.Parameters.Add("@AddressZip1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.censusAddressZip); cmd.Parameters.Add("@AddressCity1", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffData.censusCity); cmd.Parameters.Add("@AddressOther1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.censusAddress); cmd.Parameters.Add("@AddressZip2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.addressZip); cmd.Parameters.Add("@AddressCity2", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffData.addressCity); cmd.Parameters.Add("@AddressOther2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.address); cmd.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.Phone); cmd.Parameters.Add("@Phone2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.Phone2); cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.staffemail); cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); returnValue[0] = cmd.ExecuteNonQuery().ToString(); if (returnValue[0] != "0") { sql = "select IDENT_CURRENT('ExternalTeacherDatabase') AS cID"; cmd = new SqlCommand(sql, Sqlconn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { returnValue[1] = dr["cID"].ToString(); } dr.Close(); } Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] setExternalTeacherDataBase(CreateExternal StaffData) { SalaryManagement smDB = new SalaryManagement(); if (int.Parse(smDB._StaffhaveRoles[1]) == 1) { return smDB.setExternalTeacherDataBase(StaffData); } else { return new string[2] { _noRole, _errorMsg }; } }
public CreateExternal getExternalTeacherDataBase(string sID) { SalaryManagement smDB = new SalaryManagement(); CreateExternal returnValue = new CreateExternal(); if (int.Parse(smDB._StaffhaveRoles[3]) == 1) { returnValue = smDB.getExternalTeacherDataBase(sID); } else { returnValue.checkNo = _noRole; returnValue.errorMsg = _errorMsg; } return returnValue; }
public string[] setExternalTeacherDataBase(CreateExternal StaffData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); Sqlconn.Open(); string sql = "UPDATE ExternalTeacherDatabase SET TeachName = @TeachName, TeachIdentity = @TeachIdentity, AddressZip1 = @AddressZip1, " + "AddressCity1 = @AddressCity1, AddressOther1 = @AddressOther1, AddressZip2 = @AddressZip2, AddressCity2 = @AddressCity2, " + "AddressOther2 = @AddressOther2, Phone = @Phone, Phone2 = @Phone2, Email = @Email, UpFileBy=@UpFileBy, UpFileDate=(getDate()) " + "WHERE ID=@ID AND isDeleted=0 "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(StaffData.ID); cmd.Parameters.Add("@TeachName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.staffName); cmd.Parameters.Add("@TeachIdentity", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.staffTWID); cmd.Parameters.Add("@AddressZip1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.censusAddressZip); cmd.Parameters.Add("@AddressCity1", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffData.censusCity); cmd.Parameters.Add("@AddressOther1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.censusAddress); cmd.Parameters.Add("@AddressZip2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.addressZip); cmd.Parameters.Add("@AddressCity2", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffData.addressCity); cmd.Parameters.Add("@AddressOther2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.address); cmd.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.Phone); cmd.Parameters.Add("@Phone2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.Phone2); cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffData.staffemail); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); returnValue[0] = cmd.ExecuteNonQuery().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public CreateExternal getExternalTeacherDataBase(string ID) { CreateExternal returnValue = new CreateExternal(); returnValue.WorkData = new List<ExternalWorkData>(); DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT * FROM ExternalTeacherDatabase WHERE isDeleted=0 AND ID=(@ID)"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Int64.Parse(ID); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { returnValue.ID = dr["ID"].ToString(); returnValue.staffName = dr["TeachName"].ToString(); returnValue.staffTWID = dr["TeachIdentity"].ToString(); returnValue.censusAddress = dr["AddressOther1"].ToString(); returnValue.censusAddressZip = dr["AddressZip1"].ToString(); returnValue.censusCity = dr["AddressCity1"].ToString(); returnValue.address = dr["AddressOther2"].ToString(); returnValue.addressCity = dr["AddressCity2"].ToString(); returnValue.addressZip = dr["AddressZip2"].ToString(); returnValue.Phone = dr["Phone"].ToString(); returnValue.Phone2 = dr["Phone2"].ToString(); returnValue.staffemail = dr["Email"].ToString(); returnValue.WorkData = this.GetExternalTeacherWorkData(returnValue.ID); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { returnValue.checkNo = "-1"; returnValue.errorMsg = e.Message.ToString(); } } return returnValue; }