private string SearchStudentTransConditionReturn(SearchTransRecord SearchStructure) { string ConditionReturn = ""; if (SearchStructure.txtstudentName != null) { ConditionReturn += " AND StudentDatabase.StudentName like (@StudentName) "; } if (SearchStructure.txttransYear != null) { ConditionReturn += " AND TurnYear=@TurnYear"; } if (SearchStructure.txttransActivity != null) { ConditionReturn += " AND Events=@Events"; } if (SearchStructure.txtmeeting != null) { ConditionReturn += " AND ParticipateMeeting=@ParticipateMeeting"; } if (SearchStructure.txtschoolVisit != null) { ConditionReturn += " AND SchoolVisit=@SchoolVisit"; } 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 CaseTransitionService.Unit =" + UserFile[2] + " "; } return ConditionReturn; }
public string[] SearchStudentTransCount(SearchTransRecord SearchStructure) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); string ConditionReturn = this.SearchStudentTransConditionReturn(SearchStructure); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT COUNT(*) AS QCOUNT FROM CaseTransitionService " + "INNER JOIN StudentDatabase ON CaseTransitionService.StudentID=StudentDatabase.StudentID " + "WHERE CaseTransitionService.isDeleted=0 " + ConditionReturn; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%"; cmd.Parameters.Add("@TurnYear", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txttransYear); cmd.Parameters.Add("@Events", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txttransActivity); cmd.Parameters.Add("@ParticipateMeeting", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtmeeting); cmd.Parameters.Add("@SchoolVisit", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtschoolVisit); returnValue[0] = cmd.ExecuteScalar().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] SearchStudentTransDataBaseCount(SearchTransRecord SearchStructure) { CaseDataBase SDB = new CaseDataBase(); if (int.Parse(SDB._StaffhaveRoles[3]) == 1) { return SDB.SearchStudentTransCount(SearchStructure); } else { return new string[2] { _noRole, _errorMsg }; } }
public List<SearchTransRecordResult> SearchStudentTrans(int indexpage, SearchTransRecord SearchStructure) { List<SearchTransRecordResult> returnValue = new List<SearchTransRecordResult>(); DataBase Base = new DataBase(); string ConditionReturn = this.SearchStudentTransConditionReturn(SearchStructure); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CaseTransitionService.ID DESC) " + "AS RowNum, .CaseTransitionService.* ,StudentDatabase.StudentName " + "FROM CaseTransitionService " + "INNER JOIN StudentDatabase ON CaseTransitionService.StudentID=StudentDatabase.StudentID " + "WHERE CaseTransitionService.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("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%"; cmd.Parameters.Add("@TurnYear", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txttransYear); cmd.Parameters.Add("@Events", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txttransActivity); cmd.Parameters.Add("@ParticipateMeeting", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtmeeting); cmd.Parameters.Add("@SchoolVisit", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtschoolVisit); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { SearchTransRecordResult addValue = new SearchTransRecordResult(); addValue.ID = dr["ID"].ToString(); addValue.txtstudentName = dr["StudentName"].ToString(); addValue.txtstudentAge = dr["Age"].ToString(); addValue.txtstudentMonth = dr["AgeMonth"].ToString(); addValue.txttransStage = dr["TurnStage"].ToString(); addValue.txttransYear = dr["TurnYear"].ToString(); returnValue.Add(addValue); } Sqlconn.Close(); } catch (Exception e) { SearchTransRecordResult addValue = new SearchTransRecordResult(); addValue.checkNo = "-1"; addValue.errorMsg = e.Message; returnValue.Add(addValue); } } return returnValue; }
public List<SearchTransRecordResult> SearchStudentTransDataBase(int index, SearchTransRecord SearchStructure) { CaseDataBase SDB = new CaseDataBase(); return SDB.SearchStudentTrans(index, SearchStructure); }