存储过程分页的全套代码
aspx页面的代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
namespace AspDotNet07_AjaxCRUD.View
{
public partial class WebForm1 : System.Web.UI.Page
{
public static int pageIndex=1;
public static int pageSize=10;
public static int pageCount;
public static int pageRowcount;
public static int tag = 0;
protected void Page_Load(object sender, EventArgs e)
{
tag++;
if (tag == 1)
{
//InitPage();
}
}
public void InitPage()
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=db_EMS;Integrated Security=True");
SqlDataAdapter da = new SqlDataAdapter("kk", con);
//设置 命令类型 为存储过程
da.SelectCommand.CommandType = CommandType.StoredProcedure;
//设置 参数
da.SelectCommand.Parameters.AddWithValue("@pageIndex", pageIndex);//当前页码
da.SelectCommand.Parameters.AddWithValue("@pageSize", pageSize);//页容量
da.SelectCommand.Parameters.Add(new SqlParameter("@pageRowCount", SqlDbType.Int));//总行数
//在存储过程中 输出参数
da.SelectCommand.Parameters.Add(new SqlParameter("@pageCount", SqlDbType.Int));//总页数
//将后面两个参数 设置为 输出类型
da.SelectCommand.Parameters[2].Direction = ParameterDirection.Output;
da.SelectCommand.Parameters[3].Direction = ParameterDirection.Output;
//执行 并将查询到的 结果 赋给 数据表对象
da.Fill(dt);
//获得 存储过程 返回的 输出参数
pageCount = Convert.ToInt32(da.SelectCommand.Parameters[3].Value);
pageRowcount = Convert.ToInt32(da.SelectCommand.Parameters[2].Value);
GridView1.DataSource = dt.DefaultView;
GridView1.DataBind();
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string s="aawwaafffffkkkkkb";
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(s);
cf(sb);
pageIndex++;
if (pageIndex > pageCount)
{
pageIndex--;
Response.Write("<script>alert('redy last page')</script>");
return;
}
InitPage();
}
private void cf(System.Text.StringBuilder sb)
{
Console.WriteLine(sb[0]);
}
protected void Button2_Click(object sender, EventArgs e)
{
pageIndex--;
if (pageIndex <1)
{
pageIndex++;
Response.Write("<script>alert('redy first page')</script>");
return;
}
InitPage();
}
protected void Button3_Click(object sender, EventArgs e)
{
Response.Redirect("WebForm2.aspx?id=33");
}
}
}
存储过程的代码
create proc imd
@pageIndex int,
@pageSize int,
@pageRowCount int output,
@pageCount int output
as
begin
select @pageRowCount=COUNT(id)*1.0 from dbo.t
select @pageCount=CEILING(@pageRowCount*1.0/@pageSize)
select * from
(
select *,ROW_NUMBER() over(order by id) as rownum from dbo.t
)as temp where temp.rownum between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize
end;
declare @cr int
declare @pr int
exec imd 2,10,@cr output,@pr output