預存程序DataGrid分頁及注意點

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