存储过程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;
   }

作者Blog:http://blog.csdn.net/mail_ricklee/

http://dev.csdn.net/article/62/62373.shtm

posted @ 2010-01-29 12:16  emanlee  阅读(340)  评论(0编辑  收藏  举报