利用存储过程分页

最新推荐文章于 2020-11-19 10:17:22 发布

成长的小码农

最新推荐文章于 2020-11-19 10:17:22 发布

阅读量424

点赞数

分类专栏： Sql Server ado.net

本文链接：https://blog.csdn.net/zhs954838550/article/details/8670465

版权

ado.net 同时被 2 个专栏收录

15 篇文章 0 订阅

订阅专栏

Sql Server

2 篇文章 0 订阅

订阅专栏

数据库存储过程分页代码

create proc pro_NewsIndex
@pageSize int,
@pageIndex int,
@count int out
as
select top (@pageSize) * from (select *,ROW_NUMBER() over(order by Id) as rowIndex
from T_News) as tb1
where tb1.rowIndex between(@pageSize*(@pageIndex-1)+1) and @pageIndex * @pageSize
select @count=COUNT(*) from T_News
go

前台代码

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" Text="Button" οnclick="Button1_Click" />
        请输入记录条数:
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    </div>

    <div id="divResult" runat="server">

    </div>
    <table style="width: 40%;">
        <tr>
            <td>
                <asp:Button ID="btnFirst" runat="server" Text="第一页" οnclick="btnFirst_Click" />
            </td>
            <td>
                <asp:Button ID="btnPre" runat="server" Text="上一页" οnclick="btnPre_Click" />
            </td>
            <td>
                <asp:Button ID="btnNext" runat="server" Text="下一页" οnclick="btnNext_Click" />
            </td>
             <td>
                 <asp:Button ID="btnLast" runat="server" Text="最后一页" οnclick="btnLast_Click" />
            </td>
        </tr>

    </table>
    </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.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Text;

namespace 新闻系统3
{
    public partial class fenye : System.Web.UI.Page
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

        int pagesize=3;
        int paramcount1=0;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ViewState["pageindex"] = 1;
                DataLoad();
            }
        }

        private void DataLoad()
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "pro_NewsIndex";
                    cmd.Parameters.Add(new SqlParameter("@pageSize", pagesize));
                    cmd.Parameters.Add(new SqlParameter("@pageIndex",Convert.ToInt32(ViewState["pageindex"])));

                    SqlParameter paramcount = new SqlParameter("@count", SqlDbType.Int);
                    paramcount.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(paramcount);

                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();

                        adapter.Fill(dt);

                        //获取总页数
                        paramcount1 = Convert.ToInt32(paramcount.Value);
                        if (paramcount1 / pagesize == 0)
                        {
                            ViewState["pagelastindex"] = paramcount1 / pagesize;
                        }
                        else
                        {
                            ViewState["pagelastindex"] = paramcount1 / pagesize + 1;
                        }

                        StringBuilder sb1 = new StringBuilder();
                        sb1.Append("<table>");
                        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"] + "</td>");
                            sb1.Append("<td>" + dt.Rows[i]["NewsContent"] + "</td>");
                            sb1.Append("<td>" + dt.Rows[i]["CreateTime"] + "</td>");
                            sb1.Append("</tr>");
                        }
                        sb1.Append("</table>");
                        divResult.InnerHtml = sb1.ToString();
                    }
                }
            }
        }
        //第一页
        protected void btnFirst_Click(object sender, EventArgs e)
        {
            ViewState["pageindex"] = 1;
            DataLoad();
        }
        //上一页
        protected void btnPre_Click(object sender, EventArgs e)
        {

            int pageindex = Convert.ToInt32(ViewState["pageindex"]);
            if (pageindex > 1)
            {
                pageindex--;
                ViewState["pageindex"] = pageindex;
                DataLoad();
            }
            else
            {
                Response.Write("这已经是第一页了!");
            }
        }
        //下一页
        protected void btnNext_Click(object sender, EventArgs e)
        {
            int pageindex = Convert.ToInt32(ViewState["pageindex"]);
            if (pageindex < Convert.ToInt32(ViewState["pagelastindex"]))
            {
                pageindex++;
                ViewState["pageindex"] = pageindex;
                DataLoad();
            }
            else
            {
                Response.Write("已经是最后一页了！");
            }

        }
        //最后一页
        protected void btnLast_Click(object sender, EventArgs e)
        {
          ViewState["pageindex"] = Convert.ToInt32(ViewState["pagelastindex"]);
          DataLoad();
        }

成长的小码农

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
利用存储过程分页

数据库存储过程分页代码create proc pro_NewsIndex@pageSize int,@pageIndex int,@count int outasselect top (@pageSize) * from (select *,ROW_NUMBER() over(order by Id) as rowIndexfrom T_News) as tb1wh
复制链接

扫一扫

专栏目录