private string SearchClassNameCondition(SearchClassName SearchStructure) { string ConditionReturn = ""; if (SearchStructure.txtstaffID != null) { ConditionReturn += " AND TeacherID=(@txtstaffID) "; } if (SearchStructure.txtstaffName != null) { ConditionReturn += " AND TeacherName like (@txtstaffName) "; } if (SearchStructure.txtClassID != null) { ConditionReturn += " AND ClassID=(@txtClassID) "; } if (SearchStructure.txtClassName != null) { ConditionReturn += " AND ClassName like (@txtClassName) "; } StaffDataBase sDB = new StaffDataBase(); List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); caseBTFunction(); if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0) { ConditionReturn += " AND a.Unit =" + UserFile[2] + " "; } return ConditionReturn; }
public List<SearchClassName> SearchClassNameDataBaseCase(int indexpage, SearchClassName SearchStructure) { List<SearchClassName> returnValue = new List<SearchClassName>(); DataBase Base = new DataBase(); string ConditionReturn = this.SearchClassNameCondition(SearchStructure); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = " SELECT * from (select ROW_NUMBER() OVER (ORDER BY isnull( a.ClassID,'') DESC) as RowNum , * "; sql += " FROM classname a "; sql += " left join (select staffid as bid , StaffName as TeacherName from staffDatabase ) b on a.Teacherid = b.bid "; sql += " WHERE 1=1 " + ConditionReturn + ") AS NewTable "; sql += " where RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage) "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage; cmd.Parameters.Add("@txtstaffID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstaffID); cmd.Parameters.Add("@txtstaffName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstaffName) + "%"; cmd.Parameters.Add("@txtClassID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtClassID); cmd.Parameters.Add("@txtClassName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtClassName) + "%"; SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { SearchClassName addValue = new SearchClassName(); addValue.txtClassID = dr["ClassID"].ToString(); addValue.txtClassName = dr["ClassIDName"].ToString(); addValue.txtstaffID = dr["Teacherid"].ToString(); addValue.txtstaffName = dr["teachername"].ToString(); //addValue.RowNum = dr["rownum"].ToString(); //addValue.ID = dr["ID"].ToString(); //addValue.ClassType = dr["ClassType"].ToString(); //addValue.InspectDate = dr["InspectDate"].ToString(); //addValue.StudentName = dr["StudentName"].ToString(); //addValue.TeacherName = dr["TeacherName"].ToString(); //addValue.AcademicYear = dr["AcademicYear"].ToString(); //addValue.ClassIDName = dr["ClassIDName"].ToString(); //if (dr["ClassType"].ToString() == 1) //{ // addValue.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString()); //} returnValue.Add(addValue); } Sqlconn.Close(); } catch (Exception e) { string a = e.Message.ToString(); //SearchClassName addValue = new SearchClassName(); //addValue.checkNo = "-1"; //addValue.errorMsg = e.Message.ToString(); //returnValue.Add(addValue); } } return returnValue; }
public List<SearchClassName> SearchClassNameDataBaseCase(int index, SearchClassName SearchStaffCondition) { AdministrationDataBase aDB = new AdministrationDataBase(); return aDB.SearchClassNameDataBaseCase(index, SearchStaffCondition); }
public string[] SearchClassNameCountCase(SearchClassName SearchStructure) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); string ConditionReturn = this.SearchClassNameCondition(SearchStructure); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT COUNT(*) AS QCOUNT FROM ClassName a left join (select staffid as bid , StaffName as TeacherName from staffDatabase ) b on a.Teacherid = b.bid WHERE 1=1 " + ConditionReturn; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@txtstaffID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstaffID); cmd.Parameters.Add("@txtstaffName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstaffName) + "%"; cmd.Parameters.Add("@txtClassID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtClassID); cmd.Parameters.Add("@txtClassName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtClassName) + "%"; returnValue[0] = cmd.ExecuteScalar().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] SearchClassNameCountCase(SearchClassName SearchStaffCondition) { AdministrationDataBase aDB = new AdministrationDataBase(); return aDB.SearchClassNameCountCase(SearchStaffCondition); }