public List <Guesbooks> GetDataList(ForPaging Paging, string Search) { List <Guesbooks> DataList = new List <Guesbooks>(); if (!string.IsNullOrWhiteSpace(Search)) { SetMaxPaging(Paging, Search); DataList = GetAllDataList(Paging, Search); } else { SetMaxPaging(Paging); DataList = GetAllDataList(Paging); } return(DataList); }
public List <Guesbooks> GetAllDataList(ForPaging paging, string Search) { List <Guesbooks> DataList = new List <Guesbooks>(); 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()) { Guesbooks Data = new Guesbooks(); 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); }
public List <Guesbooks> GetAllDataList(ForPaging paging) { List <Guesbooks> DataList = new List <Guesbooks>(); string sql = $@"select * from (select row_number() OVER(order By Id)As sort,* From Guestbooks) 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()) { Guesbooks Data = new Guesbooks(); 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); }