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);
}