private string SearchVolunteerConditionReturn(SearchVolunteer SearchStructure) { string ConditionReturn = ""; if (SearchStructure.txtvID != null) { ConditionReturn += " AND VOLID=@vID "; } if (SearchStructure.txtvName != null) { ConditionReturn += " AND VOLName like @vName "; } if (SearchStructure.txtvSex != null && SearchStructure.txtvSex != "0") { ConditionReturn += " AND Sex=(@vSex)"; } StaffDataBase sDB = new StaffDataBase(); List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0) { ConditionReturn += " AND Unit =" + UserFile[2] + " "; } return ConditionReturn; }
public string[] searchVolunteerDataCount(SearchVolunteer SearchStructure) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); string ConditionReturn = this.SearchVolunteerConditionReturn(SearchStructure); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT COUNT(*) FROM VolunteerData WHERE isDeleted =0 " + ConditionReturn ; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@vID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtvID); cmd.Parameters.Add("@vName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtvName) + "%"; cmd.Parameters.Add("@vSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtvSex); returnValue[0] = cmd.ExecuteScalar().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] searchVolunteerDataBaseCount(SearchVolunteer SearchStructure) { CaseDataBase SDB = new CaseDataBase(); if (int.Parse(SDB._StaffhaveRoles[3]) == 1) { return SDB.searchVolunteerDataCount(SearchStructure); } else { return new string[2] { _noRole, _errorMsg }; } }
public List<CreateVolunteer> searchVolunteerData(int indexpage, SearchVolunteer SearchStructure) { List<CreateVolunteer> returnValue = new List<CreateVolunteer>(); DataBase Base = new DataBase(); string ConditionReturn = this.SearchVolunteerConditionReturn(SearchStructure); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY VOLID DESC) " + "AS RowNum, * FROM VolunteerData WHERE isDeleted = 0 " + ConditionReturn + " ) " + "AS NewTable " + "WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage) "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage; cmd.Parameters.Add("@vID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtvID); cmd.Parameters.Add("@vName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtvName) + "%"; cmd.Parameters.Add("@vSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtvSex); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { CreateVolunteer addValue = new CreateVolunteer(); addValue.ID = dr["ID"].ToString(); addValue.volunteerId = dr["VOLID"].ToString(); addValue.volunteerName = dr["VOLName"].ToString(); addValue.volunteerPhone = dr["Phone"].ToString(); addValue.vEmail = dr["Email"].ToString(); returnValue.Add(addValue); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { CreateVolunteer addValue = new CreateVolunteer(); addValue.checkNo = "-1"; addValue.errorMsg = e.Message; returnValue.Add(addValue); } } return returnValue; }
public List<CreateVolunteer> searchVolunteerDataBase(int index,SearchVolunteer SearchStructure) { CaseDataBase SDB = new CaseDataBase(); return SDB.searchVolunteerData(index, SearchStructure); }