数据库中的分页问题及代码

最新推荐文章于 2021-09-14 22:00:35 发布

qiyihan0911

最新推荐文章于 2021-09-14 22:00:35 发布

阅读量426

点赞数

本文链接：https://blog.csdn.net/qiyihan0911/article/details/8670182

版权

设计前台

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        #divResult
        {
            height: 197px;
        }
    </style>
</head>
<body style="height: 259px">
    <form id="form1" runat="server">
    <div>
    <div id="divResult" runat="server"> </div>

    <asp:Button ID="btnFirst" runat="server" οnclick="btnFirst_Click" Text="第一页"
        Width="57px" />
  
    <asp:Button ID="btnShangye" runat="server" Text="上一页"
        οnclick="btnShangye_Click" />
  
    <asp:Button ID="btnNext" runat="server" Text="下一页" οnclick="btnNext_Click" />
  
    <asp:Button

ID="btnFillay" runat="server" Text="最末页"
οnclick="btnFillay_Click" />
 

</div>
</form>
</body>
</html>

后台代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Text;

namespace _3._8UserManager
{
    public partial class fenye : System.Web.UI.Page
    {
        int pagesize = 3;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
       //ViewState虽然是声明在函数内部看似是局部变量，但是在ú类中的其他函
     //数中也可以直接使用
                ViewState["pageindex"] = 1;
                GetCount();
                LoadData();
            }
        }
        private void GetCount()
        {
            string strcon = @"Data Source=DELL-PC;Initial Catalog=News;Persist Security Info=True;User ID=sa;Password=yihan0203";
            SqlConnection conn = new SqlConnection(strcon);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT COUNT(*) FROM T_News";
            conn.Open();
            int totalcount = Convert.ToInt32(cmd.ExecuteScalar());
            if (totalcount % pagesize == 0)
            {
                ViewState["pagelastindex"] = totalcount / pagesize;
            }
            else
            {
                ViewState["pagelastindex"] = totalcount / pagesize + 1;
            }
        }
        private void LoadData()
        {
            string strcon = @"Data Source=DELL-PC;Initial Catalog=News;Persist Security Info=True;User ID=sa;Password=yihan0203";
            SqlConnection conn = new SqlConnection(strcon);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SELECT TOP(@pagesize) * FROM T_News WHERE Id NOT IN(SELECT TOP ((@pageindex-1)*@pagesize) Id FROM T_News ORDER BY Id) ORDER BY Id";

cmd.Parameters.AddWithValue("@pageindex", Convert.ToInt32(ViewState["pageindex"]));
cmd.Parameters.AddWithValue("@pagesize", pagesize);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adapter.Fill(dt);

            cmd.Dispose();
            conn.Dispose();
            StringBuilder sb1 = new StringBuilder();
            sb1.Append("<table border=2>");
            sb1.Append("<tr><td>标题</td><td>内容</td><td>创建时间</td></tr>");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sb1.Append("<tr>");
                sb1.Append("<td>" + dt.Rows[i]["NewsTitle"].ToString() + "</td>");
                sb1.Append("<td>" + dt.Rows[i]["NewsContent"].ToString() + "</td>");
                sb1.Append("<td>" + dt.Rows[i]["CreateTime"].ToString() + "</td>");
                sb1.Append("</tr>");
            }
            sb1.Append("</table>");

            divResult.InnerHtml = sb1.ToString();

        }
        //下一页
        protected void btnNext_Click(object sender, EventArgs e)
        {
            int pageindex = Convert.ToInt32(ViewState["pageindex"]);
            if(pageindex<Convert.ToInt32(ViewState["pagelastindex"]))
            {
              pageindex++;
              ViewState["pageindex"] = pageindex;
              LoadData();
            }
        }
        //上一页
        protected void btnShangye_Click(object sender, EventArgs e)
        {
            int pageindex = Convert.ToInt32(ViewState["pageindex"]);
            if(pageindex>1)
            {
                pageindex--;
                ViewState["pageindex"] = pageindex;
                LoadData();
            }
        }
        //最后一页
        protected void btnFillay_Click(object sender, EventArgs e)
        {
            ViewState["pageindex"] = ViewState["pagelastindex"];
            LoadData();
        }
        //第一页
        protected void btnFirst_Click(object sender, EventArgs e)
        {

            ViewState["pageindex"] =1;
            LoadData();
        }

}
}

--三\分页
use Test
select * FROM MyStudents
--分页，每页5条记录
--前提：将记录排序
select * from MyStudents
order by FId
DESC
--第一页
select top 5 * from MyStudents
order by FId
--第二页
select top 5 * from MyStudents
where FId not in
(select top 5 FId from MyStudents
order by FId )
order by FId
--第五页
select top 5 * from MyStudents
where FId not in
(select top (5*4) FId from MyStudents
order by FId )
order by FId
---以上这种方法是sql2000以及之前的版本用的方法
---在sql2005中就有了一个函数 row_Number

select * from MyStudents

delete from MyStudents where FId in(3,6,13,15,22)
--如果能给表添加一列,这列中放着记录的序号这样再分页就可以按这个序号来计算:
--假设查询第7页的数据(每页5条记录)
--5*6+1 ----31为第7页第1条数据的序号
--5*7-------35为第7页第最后1条数据的序号
---查询第十页的数据
--5*9+1
--5*10

--5*2+1
--5*3

select
*,
ROW_NUMBER()over(order by FId asc) as Rnumber
from MyStudents

--第七页

select * from
(
select *,
ROW_NUMBER()over(order by FId asc) as Rnumber
from MyStudents
)
as Tbl3
where Rnumber between (4*5+1) and 4*6
--------------开窗函数OVER(),与聚合函数一起使用时，可以给每条记录一个聚合的值。

select *,COUNT(*) over() as 总记录条数
from MyStudents