存储过程DataGrid分页及注意点

sqlserver中的存储过程完整代码
/*****************************************************************
* 存储过程名: GetCustomersDataPage
* 过程描述: 通用大数据集分页
* 传入参数：
* 传出参数：
* 修改记录
* 姓名 日期 修改类型
* NickLee 2005－1－17 新建
*
*
*
*
*
******************************************************************/
-- 获取指定页的数据

CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT, --页面索引,从datagrid中获取
@PageSize INT, --页面显示数量，从datagrid中获取
@RecordCount INT OUT, --返回记录总数
@PageCount INT OUT, --返回分页后页数
@strGetFields nvarchar(1000), -- 需要查询的列
@tableName nvarchar(500) , --表名称
@ID nvarchar(100), --主键,（为表的主键）
@strWhere nvarchar(1000) =@#@#, -- 查询条件 (注意: 不要加 where)
@sortName nvarchar(50) =@# asc @# , --排序方式
@orderName nvarchar(100) --父级查询排序方式

AS
declare @countSelect nvarchar(2000)
--设置统计查询语句
if len(@strWhere) =0
--如果没有查询条件
begin
set @countSelect=N@#SELECT @CountRecord = COUNT(*) FROM @#+@tableName
end
else
--否则
begin
set @countSelect=N@#SELECT @CountRecord = COUNT(*) FROM @#+@tableName+@# where @#+@strWhere
end
--执行并返回总数
exec sp_executesql @countSelect,N@#@CountRecord int output@#,@RecordCount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)

SET NOCOUNT ON

DECLARE @SQLSTR NVARCHAR(3000)
--实际总共的页码小于当前页码 或者 最大页码
if @PageCount>=0
--如果分页后页数大于0
begin
if @PageCount<=@PageIndex and @PageCount>0 --如果实际总共的页数小于datagrid索引的页数
--or @PageCount=1
begin
--设置为最后一页
set @PageIndex=@PageCount-1
end
else if @PageCount<=@PageIndex and @PageCount=0
begin
set @PageIndex=0;
end
end

IF @PageIndex = 0 OR @PageCount <= 1 --如果为第一页
begin
if len(@strWhere) =0
begin
SET @SQLSTR =N@#SELECT TOP @#+STR( @PageSize )+@strGetFields+@# FROM @#+@tableName+@# ORDER BY @#+@orderName+@sortName
end
else
begin
SET @SQLSTR =N@#SELECT TOP @#+STR( @PageSize )+@strGetFields+@# FROM @#+@tableName+@# where @#+@strWhere+@# ORDER BY @#+@orderName+@sortName
end
end
ELSE IF @PageIndex = @PageCount - 1 --如果为最后一页
begin
if len(@strWhere) =0
begin
SET @SQLSTR =N@# SELECT @#+@strGetFields+@# FROM @#+@tableName+@# where @#+@ID+@# not in ( SELECT TOP @#+STR(/*@RecordCount - */@PageSize * @PageIndex )+@ID+@# FROM @#+@tableName+@#ORDER BY @#+@orderName+@sortName+@# ) ORDER BY @#+@orderName+@sortName
end
else
begin
SET @SQLSTR =N@# SELECT @#+@strGetFields+@# FROM @#+@tableName+@# where @#+@ID+@# not in ( SELECT TOP @#+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+@# FROM @#+@tableName+@# where @#+@strWhere+@#ORDER BY @#+@orderName+@sortName+@# ) and @#+@strWhere+@# ORDER BY @#+@orderName+@sortName
end
end
ELSE --否则执行
begin
if len(@strWhere) =0
begin
SET @SQLSTR =N@# SELECT TOP @#+STR( @PageSize )+@strGetFields+@# FROM @#+@tableName+@# where @#+@ID+@# not in ( SELECT TOP @#+STR( /*@RecordCount - */@PageSize * @PageIndex )+@ID+@# FROM @#+@tableName+@# ORDER BY @#+@orderName+@sortName+@# ) ORDER BY @#+@orderName+@sortName
end
else
begin
SET @SQLSTR =N@# SELECT TOP @#+STR( @PageSize )+@strGetFields+@# FROM @#+@tableName+@# where @#+@ID+@# not in (SELECT TOP @#+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+@# FROM @#+@tableName+@# where @#+@strWhere+@# ORDER BY @#+@orderName+@sortName+@# )and @#+@strWhere+@#ORDER BY @#+@orderName+@sortName
end
end
EXEC (@SQLSTR)
set nocount off
GO

在asp.net中调用方法
#region 调用函数
//绑定数据

private void DataGridDataBind()
{
DataSet ds = GetCustomersData(PageIndex,PageSize,ref recordCount,ref pageCount);

DataGrid1.VirtualItemCount = RecordCount;
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
// GridExpand(this.DataGrid1,2);
SetPagingState();
}

private DataSet GetCustomersData(int pageIndex,int pageSize,ref int recordCount,ref int pageCount)
{
dataFill.ConString=System.Configuration.ConfigurationSettings.AppSettings["SqlConnectionString"];
dataFill.sqlClientDataSet("GetCustomersDataPage");
System.Data.SqlClient.SqlDataAdapter comm=dataFill.mySqlAdapter;

comm.SelectCommand.Parameters.Add(new SqlParameter("@PageIndex",SqlDbType.Int));
comm.SelectCommand.Parameters[0].Value = pageIndex;
comm.SelectCommand.Parameters.Add(new SqlParameter("@PageSize",SqlDbType.Int));
comm.SelectCommand.Parameters[1].Value = pageSize;
comm.SelectCommand.Parameters.Add(new SqlParameter("@RecordCount",SqlDbType.Int));
comm.SelectCommand.Parameters[2].Direction = ParameterDirection.Output;
comm.SelectCommand.Parameters.Add(new SqlParameter("@PageCount",SqlDbType.Int));
comm.SelectCommand.Parameters[3].Direction = ParameterDirection.Output;

comm.SelectCommand.Parameters.Add(new SqlParameter("@strGetFields",SqlDbType.NVarChar));
comm.SelectCommand.Parameters[4].Value ="tOrder.orderTime as @#下订单时间@#,tOrder.facName as @#工厂@#,tOrder.facOrderNum as @#工厂订单号@#,tOrder.quantity as @#定单数@#,tOrder.realQuantity as @#实际出货数@#,tOrder.reqTime as @#要求出货时间@#,tOrder.repTime as @#出货时间@#,tMaterial.matName as @#材料@#,tMaterial.colName as @#颜色@#,tOrder.leaveQuantity as @#未出货数@#,tOrder.orderStatic as @#全部出货@#,tOrder.orderDetail as @#备注@# ";
/*tOrder.comName as @#公司@#,tOrder.comOrderNum as @#公司订单号@#,*/
comm.SelectCommand.Parameters.Add(new SqlParameter("@tableName",SqlDbType.NVarChar));
comm.SelectCommand.Parameters[5].Value =" tOrder left join tStock on tOrder.stoID=tStock.stoID left join tMaterial on tStock.matID=tMaterial.matID ";
comm.SelectCommand.Parameters.Add(new SqlParameter("@ID",SqlDbType.NVarChar));
comm.SelectCommand.Parameters[6].Value =" tOrder.orderID ";
comm.SelectCommand.Parameters.Add(new SqlParameter("@orderName",SqlDbType.NVarChar));
comm.SelectCommand.Parameters[7].Value =" tMaterial.matName ";
comm.SelectCommand.Parameters.Add(new SqlParameter("@strWhere",SqlDbType.NVarChar));
comm.SelectCommand.Parameters[8].Value =" facName=@#"+en1.decyrpt(this.Request.QueryString["facName"].ToString())+"@# and facOrderNum=@#"+en1.decyrpt(this.Request.QueryString["facNum"].ToString())+"@# ";
// comm.Parameters.Add(new SqlParameter("@sortName",SqlDbType.NVarChar));
// comm.Parameters[8].Value =" desc ";

comm.Fill(dataFill.myDateSet);

recordCount = (int)comm.SelectCommand.Parameters[2].Value;
pageCount = (int)comm.SelectCommand.Parameters[3].Value;

if(pageIndex>=pageCount&&pageCount>0)
{
PageIndex=pageCount-1;
}
else if(pageIndex>=pageCount&&pageCount==0)
{
PageIndex=0;
}
//
return dataFill.myDateSet;

}

/// <summary>
/// 控制导航按钮或数字的状态
/// </summary>
public void SetPagingState()
{
if( PageCount <= 1 )//( RecordCount <= PageSize )//小于等于一页
{
this.Menu1.Items[0].Enabled = false;
this.Menu1.Items[1].Enabled = false;
this.Menu1.Items[2].Enabled = false;
this.Menu1.Items[3].Enabled = false;
}
else //有多页
{
if( PageIndex == 0 )//当前为第一页
{
this.Menu1.Items[0].Enabled = false;
this.Menu1.Items[1].Enabled = false;
this.Menu1.Items[2].Enabled = true;
this.Menu1.Items[3].Enabled = true;
}

else if( PageIndex == PageCount - 1 )//当前为最后页
{
this.Menu1.Items[0].Enabled = true;
this.Menu1.Items[1].Enabled = true;
this.Menu1.Items[2].Enabled = false;
this.Menu1.Items[3].Enabled = false;
}
else //中间页
{
this.Menu1.Items[0].Enabled = true;
this.Menu1.Items[1].Enabled = true;
this.Menu1.Items[2].Enabled = true;
this.Menu1.Items[3].Enabled = true;
}
}
if(RecordCount == 0)
{
lab_PageCount.Text="第0页 共0页 每页"+PageSize.ToString()+"条 共"+RecordCount.ToString()+"条";
}
else
{
lab_PageCount.Text="第"+(PageIndex + 1).ToString()+"页 共"+PageCount.ToString()+"页 每页"+PageSize.ToString()+"条 共"+RecordCount.ToString()+"条";
}
}

#endregion

重点在数据对datagrid绑定前进行判定
if(pageIndex>=pageCount&&pageCount>0)
{
PageIndex=pageCount-1;
}
else if(pageIndex>=pageCount&&pageCount==0)
{
PageIndex=0;
}

原文转自：http://www.ltesting.net