using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Bind();
}
}
int index = 1;//当前页索引
int size = 5;//每页大小(记录条数)
protected void Bind()
{
//创建连接对象
SqlConnection con = new SqlConnection("server=.;uid=sa;pwd=;database=northwind");
//创建数据适配器对象(命令字符串为存储过程pageing,连接对象为con)
SqlDataAdapter sda = new SqlDataAdapter("paging", con);
//指定SelectCommand类型为存储过程
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
//创建参数数组
SqlParameter[] parames = new SqlParameter[] {
new SqlParameter("@index",SqlDbType.Int),
new SqlParameter("@size",SqlDbType.Int),
new SqlParameter("@count",SqlDbType.Int)
};
//设置每个参数的值以及参数方向
parames[0].Value = index;
parames[1].Value = size;
parames[2].Direction = ParameterDirection.Output;
//为sda对象的SelectCommand添加参数
foreach (SqlParameter param in parames)
{
sda.SelectCommand.Parameters.Add(param);
}
//创建数据集对象ds
DataSet ds = new DataSet();
//执行命令,将执行结果填充到数据集对象ds
sda.Fill(ds);
//获取被查询表中记录总数
int count = Convert.ToInt32(sda.SelectCommand.Parameters["@count"].Value);
//设置总页数(总记录数除以每页大小)
if (count % size == 0)
{
lblTotal.Text = Convert.ToString(count / size);
}
else
{//如果除不断,总页数为商+1
lblTotal.Text = Convert.ToString(count / size + 1);
}
//设置当前页索引
lblCurrentPage.Text = index.ToString();
//将数据集ds绑定到GridView,DataList,Repeater
GridView1.DataSource = ds;
GridView1.DataBind();
DataList1.DataSource = ds;
DataList1.DataBind();
Repeater1.DataSource = ds;
Repeater1.DataBind();
//设置“首页”等连接的可用状态
SetLinkButtonState();
}
//定义方法SetLinkButtonState用来设置“首页”等连接的可用状态
protected void SetLinkButtonState()
{
if (lblTotal.Text == "1")
{//如果总页数只有一页,则全部设为不可用
firstPage.Enabled = false;
previousPage.Enabled = false;
nextPage.Enabled = false;
lastPage.Enabled = false;
}
else
{//否则,总页数不止一页
if (lblCurrentPage.Text == lblTotal.Text)
{//如果当前页是最后一页,则启用“首页”“上一页”,禁用“末页”“下一页”
firstPage.Enabled = true;
previousPage.Enabled = true;
nextPage.Enabled = false;
lastPage.Enabled = false;
}
else if (lblCurrentPage.Text == "1")
{//如果当前页是第一页,则禁用“首页”“上一页”,启用“末页”“下一页”
firstPage.Enabled = false;
previousPage.Enabled = false;
nextPage.Enabled = true;
lastPage.Enabled = true;
}
else
{//如果既不是第一页,又不是最后一页,则启用全部LinkButton
firstPage.Enabled = true;
previousPage.Enabled = true;
nextPage.Enabled = true;
lastPage.Enabled = true;
}
}
}
//首页
protected void firstPage_Click(object sender, EventArgs e)
{
index = 1;
Bind();
}
//上一页
protected void previousPage_Click(object sender, EventArgs e)
{
index = int.Parse(lblCurrentPage.Text) - 1;
Bind();
}
//下一页
protected void nextPage_Click(object sender, EventArgs e)
{
index = int.Parse(lblCurrentPage.Text) + 1;
Bind();
}
//末页
protected void lastPage_Click(object sender, EventArgs e)
{
index = int.Parse(lblTotal.Text);
Bind();
}
}
======================================================
分页存储过程
======================================================
create procedure paging
(
@index int,--当前页索引
@size int,--每页大小
@count int output--目标表中记录总数
)
as
--创建临时表temp,解决目标表(本例中是products)中主键值可能不连续问题
create table #temp
(
idnew int identity(1,1),
idold int
)
--从目标表中查询出主键值,插入到临时表中
insert into #temp select productid from products
--通过与临时表temp进行内连接,从目标表中查出所需记录
select productid,productname,unitprice from products
inner join #temp on productid=idold
where #temp.idnew>(@index-1)*@size and #temp.idnew<=@index*@size
--给输出参数@count赋值
set @count=(select count(*) from products)