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