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