怎样实现sql数据库中表的分页功能,用存储过程。asp.net 谢谢回答!

发布网友 发布时间：2022-05-02 05:15

我来回答

共2个回答

懂视网 时间：2022-05-02 09:36

因为要写的是简单SQL语句实现分页所以SQL自然就不会很难啦！

1.IN NOT IN写法 效率低

--IN 和 NOT IN,效率较低
--这条语句的意思是查询五条数据不在前十条里的数据
SELECT TOP 5 * FROM TableName WHERE ID NOT IN(SELECT TOP 10 ID FROM TableName )

2.通过升序和降序排列

--通过排序进行分页
--首先查询十五条数据中的前五条 获取11到15的数据，因为是倒序，所以最后进行升序排列
SELECT * FROM(
 SELECT TOP 5 * FROM(
 SELECT TOP 15 * FROM TableName ORDER BY ID ASC) 
  AS TEMP1 ORDER BY ID DESC)
 AS TEMP2 ORDER BY ID ASC

3.使用SQL Server Row Num()函数

--首先Row_Number()函数要和 over 配套使用
--首先通过Row_Number()函数查询前十条数据 然后全部并且大于0条的数据
--简单来讲top10 相当于一页显示多少条数据 而0相当于当前页数
select * from (select top 10 ROW_NUMBER() over(order by id) as temp,* from sqlpagetest )as temp where xxx>0

实现分页

Dbhelper

 public DataTable PageBysql(int pageSize, int pageIndex)
 {
  string sql = string.Format("select * from (select top {0} ROW_NUMBER() over(order by id) as xxx,* from sqlpagetest )as temp where xxx>{1}", pageSize, pageIndex);
  DataTable tb = new DataTable();
  SqlDataAdapter dp = new SqlDataAdapter(sql, conn);
  dp.Fill(tb);
  return tb;
 }

前台代码

 <form id="form1" runat="server">
 <div>
  <table>
  <tr>
   <td>ID</td>
   <td>姓名</td>
  </tr>
  <asp:Repeater ID="Repeater1" runat="server">
   <ItemTemplate>
   <tr>
    <td><%#Eval("id") %></td>
    <td><%#Eval("name") %></td>
   </tr>
   </ItemTemplate>
  </asp:Repeater>
  </table>
  <%--显示pageSize--%>
  <asp:HiddenField ID="HiddenField1" runat="server" />
   <%--显示pageIndex--%>
  <asp:HiddenField ID="HiddenField2" runat="server" />
  <asp:LinkButton ID="LinkButton1" OnClick="LinkButton1_Click" runat="server">上一页</asp:LinkButton>
  <asp:LinkButton ID="LinkButton2" OnClick="LinkButton2_Click" runat="server">下一页</asp:LinkButton>
 </div>
 </form>

后台代码

public int maxpage;
 public int nowpage;
 protected void Page_Load(object sender, EventArgs e)
 {

  if (!IsPostBack)
  {
  maxpage = 10;
  nowpage = 0;
  page(maxpage, nowpage);
  HiddenField1.Value = maxpage.ToString();
  HiddenField2.Value = nowpage.ToString();
  }
 }
 public void page(int maxpage, int nowpage)
 {
  Repeater1.DataSource = new dbhelper().PageBysql(maxpage, nowpage);
  Repeater1.DataBind();
 }
 //上一页
 protected void LinkButton1_Click(object sender, EventArgs e)
 {
  if (int.Parse(HiddenField2.Value) > 0)
  {
  maxpage =int.Parse(HiddenField1.Value)-10;
  nowpage = int.Parse(HiddenField2.Value)-10;
  page(maxpage, nowpage);
  HiddenField1.Value = maxpage.ToString();
  HiddenField2.Value = nowpage.ToString();
  }
  else
  {

  }
 }
 //下一页
 protected void LinkButton2_Click(object sender, EventArgs e)
 {
  
  maxpage +=(10+ int.Parse(HiddenField1.Value));
  nowpage += (10+int.Parse(HiddenField2.Value));
  page(maxpage, nowpage);
  HiddenField1.Value = maxpage.ToString();
  HiddenField2.Value = nowpage.ToString();
  
 }

积土而为山，积水而为海。 ——荀子

ASP.NET简单SQL分页的实现

标签：排序   效率   asp   大于   ble   click   fill   xxx   obj   

热心网友 时间：2022-05-02 06:44