public List <Boards> GetDataList(ForPaging Paging, string Search) { List <Boards> DataList = new List <Boards>(); //Sql語法 if (!string.IsNullOrWhiteSpace(Search)) { SetMaxPaging(Paging, Search); DataList = GetAllDataList(Paging, Search); } else { SetMaxPaging(Paging); DataList = GetAllDataList(Paging); } return(DataList); }
//根據分頁以及搜尋來取得資料陣列的方法 public List <Guestbooks> GetDataList(ForPaging Paging, string Search) { //宣告要接受全部搜尋資料的物件 IQueryable <Guestbooks> SearchData; //判斷搜尋是否為空或Null,用於決定要呼叫取得搜尋資料 if (String.IsNullOrEmpty(Search)) { SearchData = GetAllDataList(Paging); } else { SearchData = GetAllDataList(Paging, Search); } //先排序再根據分頁來回傳所需部分的資料陣列 return(SearchData.OrderByDescending(p => p.Id) .Skip((Paging.NowPage - 1) * Paging.ItemNum).Take(Paging.ItemNum).ToList()); }
public List <Boards> GetAllDataList(ForPaging paging, string Search) { List <Boards> DataList = new List <Boards>(); string sql = $@" select * from (select row_number() over(order by Id) as sort,* from Guestbooks where Name like '%{Search}%' or Content like '%{Search}%' or Reply like '%{Search}%' ) m Where m.sort Between {(paging.NowPage - 1) * paging.ItemNum + 1} and {paging.NowPage * paging.ItemNum} "; try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Boards Data = new Boards(); Data.Id = Convert.ToInt32(dr["Id"]); Data.Name = dr["Name"].ToString(); Data.Content = dr["Content"].ToString(); Data.CreateTime = Convert.ToDateTime(dr["CreateTime"]); if (!dr["ReplyTime"].Equals(DBNull.Value)) { Data.Reply = dr["Reply"].ToString(); Data.ReplyTime = Convert.ToDateTime(dr["ReplyTime"]); } DataList.Add(Data); } } catch (Exception e) { throw new Exception(e.Message.ToString()); } finally { conn.Close(); } return(DataList); }