当前位置: 王朝网络 >> c# >> [原创]存储过程DataGrid分页及注意点

[原创]存储过程DataGrid分页及注意点

王朝c#·作者佚名 2006-01-09

窄屏简体版字體: 小|中|大|超大

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) --父级查询排序方式

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

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

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

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

EXEC (@SQLSTR)

set nocount off

在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;