public List <Guestbook> GetAllDataList(ForPaging paging, string Search)
{
List <Guestbook> DataList = new List <Guestbook>();
//sql語法
string sql = $@"select m.*,d.Name,d.IsAdmin From(select row_number() over(order by Id)As sort,* FROM Guestbooks where Content like '%{Search}%' or Reply like '%{Search}%') m inner join Member d on m.Account = d.Account Where m.sort BETWEEN { (paging.NowPage - 1) * paging.ItemNum + 1} AND { paging.NowPage * paging.ItemNum}";
//) m 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();
//執行Sql指令
SqlCommand cmd = new SqlCommand(sql, conn);
//取得Sql資料
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Guestbook Data = new Guestbook();
Data.Id = Convert.ToInt32(dr["Id"]);
Data.Account = dr["Account"].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 <Guestbook> GetAllDataList(ForPaging paging)
{
List <Guestbook> DataList = new List <Guestbook>();
//sql 語法
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();
//執行Sql指令
SqlCommand cmd = new SqlCommand(sql, conn);
//取得Sql資料
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Guestbook Data = new Guestbook();
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);
}
//function 取得陣列資料方法
public List <Guestbook> GetDataList(ForPaging Paging, string Search)
{
List <Guestbook> DataList = new List <Guestbook>();
//sql語法
//string sql = string.Empty;
if (!string.IsNullOrWhiteSpace(Search))
{
SetMaxPaging(Paging, Search);
DataList = GetAllDataList(Paging, Search);
//
//SetMaxPaging();
//sql = $@"select * from Guestbooks where Name Like '%{Search}%' OR Content Like '%{Search}%' OR Reply Like '%{Search}%'";
}
else
{
//sql = $@"Select * FROM Guestbooks;";
SetMaxPaging(Paging);
DataList = GetAllDataList(Paging);
}
//string sql = $@"Select * FROM Guestbooks;";
//try
//{
// //開啟資料庫連線
// conn.Open();
// //執行Sql指令
// SqlCommand cmd = new SqlCommand(sql,conn);
// //取得Sql資料
// SqlDataReader dr = cmd.ExecuteReader();
// while (dr.Read())
// {
// Guestbook Data = new Guestbook();
// 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);
}
