//顯示學生姓名(有點多餘) public List<SearchStudentResult> ShowStudent(int SID) { List<SearchStudentResult> returnvalue = new List<SearchStudentResult>(); SearchStudentResult temp = new SearchStudentResult(); DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); Sqlconn.Open(); string sql = "select StudentName from StudentDatabase where ID=@StudentID "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = SID; SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { //temp.txtstudentSex = int.Parse(dr["StudentSex"].ToString()); temp.txtstudentName = dr["StudentName"].ToString(); //temp.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString()); returnvalue.Add(temp); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { } } return returnvalue; }
public List<SearchStudentResult> SearchStudent(int indexpage, SearchStudent SearchStructure,int type) { List<SearchStudentResult> returnValue = new List<SearchStudentResult>(); DataBase Base = new DataBase(); string ConditionReturn = this.SearchStudentConditionReturn(SearchStructure, type); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY StudentDatabase.StudentID DESC) " + "AS RowNum, StudentDatabase.* " + "FROM StudentDatabase 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("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStructure.txtstudentID); cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(SearchStructure.txtstudentName) + "%"; cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtstudentSex); cmd.Parameters.Add("@CaseStatu", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtcaseStatu); cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdaystart); cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtbirthdayend); cmd.Parameters.Add("@sGuaranteeDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtjoindaystart); cmd.Parameters.Add("@sGuaranteeDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtjoindayend); cmd.Parameters.Add("@sCompletedDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDatestart); cmd.Parameters.Add("@sCompletedDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStructure.txtendReasonDateend); cmd.Parameters.Add("@CompletedType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtendReasonType); cmd.Parameters.Add("@NomembershipType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStructure.txtnomembershipType); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { SearchStudentResult addValue = new SearchStudentResult(); addValue.ID = int.Parse(dr["ID"].ToString()); addValue.txtstudentID = dr["StudentID"].ToString(); addValue.txtstudentName = dr["StudentName"].ToString(); addValue.txtstudentStatu = dr["CaseStatu"].ToString(); addValue.txtLegalRepresentativeRelation = dr["ContactRelation2"].ToString(); addValue.txtLegalRepresentative = dr["ContactName2"].ToString(); addValue.txtLegalRepresentativePhone = dr["ContactPhone2"].ToString(); addValue.txtLegalRepresentativeTel = dr["ContactTel_home2"].ToString(); addValue.txtstudentSex = int.Parse(dr["StudentSex"].ToString()); addValue.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString()); returnValue.Add(addValue); } Sqlconn.Close(); } catch (Exception e) { SearchStudentResult addValue = new SearchStudentResult(); addValue.checkNo = "-1"; addValue.errorMsg = e.Message.ToString(); returnValue.Add(addValue); } } return returnValue; }
public List<SearchStudentResult> SearchTeachISP(int indexpage, SearchStudentISP searchISPData) { List<SearchStudentResult> returnValue = new List<SearchStudentResult>(); string DateBase = "1900-01-01"; DataBase Base = new DataBase(); int Count = 0; string LimitsID = ""; if (searchISPData.txtstudentID != null) { LimitsID = " AND StudentDatabase.StudentID=(@StudentID) "; } string LimitsName = ""; if (searchISPData.txtstudentName != null) { LimitsName = " AND StudentDatabase.StudentName like (@StudentName) "; } string LimitsSex = ""; int sSex = 0; if (searchISPData.txtstudentSex != null && searchISPData.txtstudentSex != "0") { LimitsSex = " AND StudentDatabase.StudentSex=(@StudentSex) "; sSex = int.Parse(searchISPData.txtstudentSex); } string LimitsBirthday = ""; DateTime sBirthdaystart = new DateTime(1900, 01, 01); DateTime sBirthdayend = new DateTime(1900, 01, 01); if (searchISPData.txtbirthdaystart != null && searchISPData.txtbirthdayend != null && searchISPData.txtbirthdaystart != DateBase && searchISPData.txtbirthdayend != DateBase) { LimitsBirthday = " AND StudentDatabase.StudentBirthday BETWEEN (@sBirthdayStart) AND (@sBirthdayEnd) "; sBirthdaystart = DateTime.Parse(searchISPData.txtbirthdaystart); sBirthdayend = DateTime.Parse(searchISPData.txtbirthdayend); } string LimitsTeachName = ""; if (searchISPData.txtteachername != null) { LimitsTeachName = " AND CaseStatu like (@CaseStatu) "; } using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CaseISPstate.ID DESC) " + "AS RowNum, CaseISPstate.*, StudentDatabase.StudentName, StudentDatabase.StudentSex, StudentDatabase.StudentBirthday, StudentDatabase.ContactName2, StudentDatabase.ContactPhone2, StudentDatabase.ContactTel_home2 " + "FROM CaseISPstate INNER JOIN StudentDatabase ON CaseISPstate.StudentID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " + LimitsID + LimitsName + LimitsSex + LimitsBirthday + LimitsTeachName + " WHERE CaseISPstate.isDeleted=0 AND StudentDatabase.isDeleted=0 ) " + "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("@StudentID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(searchISPData.txtstudentID); cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = searchISPData.txtstudentName + "%"; cmd.Parameters.Add("@StudentSex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(searchISPData.txtstudentSex); cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = sBirthdaystart; cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = sBirthdayend; SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { SearchStudentResult addValue = new SearchStudentResult(); addValue.ID = Int64.Parse(dr["ID"].ToString()); addValue.txtstudentID = dr["StudentID"].ToString(); addValue.txtstudentName = dr["StudentName"].ToString(); addValue.txtLegalRepresentative = dr["ContactName2"].ToString(); addValue.txtLegalRepresentativePhone = dr["ContactPhone2"].ToString(); addValue.txtLegalRepresentativeTel = dr["ContactTel_home2"].ToString(); addValue.txtstudentSex = int.Parse(dr["StudentSex"].ToString()); addValue.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString()); returnValue.Add(addValue); } Sqlconn.Close(); } catch (Exception e) { string item = e.Message.ToString(); //returnValue = -1; } } return returnValue; }
public List<SearchStudentResult> getAllStudentDataList(int item) { List<SearchStudentResult> returnValue = new List<SearchStudentResult>(); DataBase Base = new DataBase(); string ConditionReturn = ""; if (item != 0) { ConditionReturn = " AND CaseStatu=(@CaseStatu) "; } StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT * FROM StudentDatabase WHERE isDeleted=0 AND Unit=@Unit" + ConditionReturn; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@CaseStatu", SqlDbType.TinyInt).Value = item; cmd.Parameters.Add("@Unit", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[2].ToString()); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { SearchStudentResult addValue = new SearchStudentResult(); addValue.ID = int.Parse(dr["ID"].ToString()); addValue.txtstudentID = dr["StudentID"].ToString(); addValue.txtstudentName = dr["StudentName"].ToString(); addValue.txtstudentStatu = dr["CaseStatu"].ToString(); /*addValue.txtLegalRepresentative = dr["ContactName2"].ToString(); addValue.txtLegalRepresentativePhone = dr["ContactPhone2"].ToString(); addValue.txtLegalRepresentativeTel = dr["ContactTel_home2"].ToString(); addValue.txtstudentSex = int.Parse(dr["StudentSex"].ToString()); addValue.txtstudentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString());*/ returnValue.Add(addValue); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { SearchStudentResult addValue = new SearchStudentResult(); addValue.checkNo = "-1"; addValue.errorMsg = e.Message; returnValue.Add(addValue); } } return returnValue; }