利用SQL或存储过程实现GridView分页

来源：互联网发布：时期数据和时点数据编辑：程序博客网时间：2024/05/22 10:39

web控件代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MyBookShop.Models;
using MyBookShop.BLL;
namespace 条件分页
{
public partial class _Default : System.Web.UI.Page
{

        private int PageSize = 1;//每页显示1条
        private int PageIndex   //第几页--属性
        {
            get { return (int)ViewState["PageIndex"]; }
            set { ViewState["PageIndex"] = value; }
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindData(1);
                //Admin admin1 = new Admin();
                //admin1.Name = "dd";
                //admin1.Phone = "123";
                //Admin admin2 = new Admin();
                //admin2.Name = "aa";
                //admin2.Phone = "1234";
                //List<Admin> list = new List<Admin>();
                //list.Add(admin1);
                //list.Add(admin2);
                //GridView1.DataSource = list;
                //GridView1.DataBind();

            }
        }

        private void BindData(int pageIndex)
        {
            List<Admin> list = AdminManager.GetAdminPages(PageSize, pageIndex);
            GridView1.DataSource = list;
            GridView1.DataBind();
            PageIndex = pageIndex;
            Label1.Text = PageIndex.ToString();//显示当前页
            Label2.Text = AdminManager.GetAdminPageNum(PageSize).ToString();//显示总共页数
        }
        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
        /// <summary>
        /// 首页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void LinkButton1_Click(object sender, EventArgs e)
        {
            BindData(1);//第一页
        }
        /// <summary>
        /// 上一页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void LinkButton2_Click(object sender, EventArgs e)
        {
            if (PageIndex > 1)//判断页码是否大于1，如果大于1，那么就有上一页
            {
                BindData(PageIndex - 1);

            }
            else
            {
                BindData(PageIndex);//如果没有就显示当前页面
            }
        }
        /// <summary>
        /// 下一页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void LinkButton3_Click(object sender, EventArgs e)
        {
            if (PageIndex < AdminManager.GetAdminPageNum(PageSize))//判断页码是否小于所有页，如果小于1，那么就有下一页
            {
                BindData(PageIndex +1);

            }
            else
            {
                BindData(PageIndex);//如果没有就显示当前页面
            }
        }

    }
}

数据库存储过程代码

create proc proc_AdminPages
@PageIndex int,
@PageSize int
as
declare @begion int,@end int
set @begion=@PageSize*(@pageIndex-1)+1
set @end=@PageSize*@PageIndex

select * from
(select ROW_NUMBER() over(order by id) as RowIndex,*from Admin) as a
where RowIndex between @begion and @end

效果