web自定义控件示例，一个自动分页的datagrid例子

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Data.OleDb;

namespace ZFControls
... {
/**/ /// <summary>
/// DataGrid
/// 实现功能
/// </summary>
[
ToolboxData( " <{0}:DataGridrunat=server></{0}:DataGrid> " ),
ToolboxBitmap( typeof (ZFControls.DataGrid), " Grid.bmp " )

]
public class DataGrid:System.Web.UI.WebControls.DataGrid,IPostBackEventHandler
... {


public DataGrid()
... {
if ( this .Context == null )
... {
this .ShowFooter = true ; // 显示设计视图
}
}



private int CurrentPageNo
... {
get
... {

if (ViewState[ " PageNo " ] == null )
... {
ViewState[ " PageNo " ] = 1 ;
}
return ( int )ViewState[ " PageNo " ];
}
set
... {
ViewState[ " PageNo " ] = value;
}
}

/**/ /// <summary>
/// 是否在列表中显示记录数,默认为True
/// </summary>
private bool ShowRecordCount
... {
get
... {

if (ViewState[ " ShowRecordCount " ] == null )
... {
ViewState[ " ShowRecordCount " ] = true ;
}
return ( bool )ViewState[ " ShowRecordCount " ];
}
set
... {
ViewState[ " ShowRecordCount " ] = value;
}
}

/**/ /// <summary>
/// 是否使用默认的样式默认为true
/// </summary>
public bool bDefaultStyle
... {
get
... {

if (ViewState[ " DefaultStyle " ] == null )
... {
ViewState[ " DefaultStyle " ] = true ;
}
return ( bool )ViewState[ " DefaultStyle " ];
}
set
... {
ViewState[ " DefaultStyle " ] = value;
}
}

public string SortExpression
... {
get
... {
if (ViewState[ " SortExpression " ] == null )
... {
ViewState[ " SortExpression " ] = "" ;
}
return ( string )ViewState[ " SortExpression " ];
}
set
... {
ViewState[ " SortExpression " ] = value;
}
}

public bool bIsAccess
... {
get
... {
if (ViewState[ " IsAccess " ] == null )
... {
ViewState[ " IsAccess " ] = false ;
}
return ( bool )ViewState[ " IsAccess " ];
}
set
... {
ViewState[ " IsAccess " ] = value;
}
}

private int RecordCount
... {
get
... {
if (ViewState[ " RecordCount " ] == null )
... {
ViewState[ " RecordCount " ] = 0 ;
}
return ( int )ViewState[ " RecordCount " ];
}
set
... {
ViewState[ " RecordCount " ] = value;
}
}
public string IdentityKey
... {
get
... {
if (ViewState[ " IdentityKey " ] == null )
... {
ViewState[ " IdentityKey " ] = "" ;
}
return ( string )ViewState[ " IdentityKey " ];
}
set
... {
ViewState[ " IdentityKey " ] = value;
}
}
private int PageCount1
... {
get
... {
if ( this .Context == null ) return 0 ;

if ( this .RecordCount % this .PageSize == 0 )
return this .RecordCount / this .PageSize;
else
return Convert.ToInt32( this .RecordCount / this .PageSize) + 1 ;
}


}


private System.Web.UI.WebControls.DataGridItemobjFooterItem; // footerItem

public string strSQL
... {
get
... {
if (ViewState[ " strSQL " ] == null )
... {
ViewState[ " strSQL " ] = "" ;
}
return ( string )ViewState[ " strSQL " ];
}
set
... {
if (( string )value != this .strSQL)
... {
this .CurrentPageNo = 1 ; // 更改了strSQL,重置参数
this .RecordCount = 0 ;

}


ViewState[ " strSQL " ] = value;

}
}

public string ConnectionString
... {
get
... {
if (ViewState[ " ConnectionString " ] == null )
... {
ViewState[ " ConnectionString " ] = "" ;
}
return ( string )ViewState[ " ConnectionString " ];
}
set
... {
ViewState[ " ConnectionString " ] = value;
}
}

public string ImagePath
... {
get
... {
if (ViewState[ " ImagePath " ] == null )
... {
ViewState[ " ImagePath " ] = " ../images/ " ;
}
return ( string )ViewState[ " ImagePath " ];
}
set
... {
ViewState[ " ImagePath " ] = value;

}
}

private string OldSortExpression
... {
get
... {
if (ViewState[ " OldSortExpression " ] == null )
... {
ViewState[ " OldSortExpression " ] = "" ;
}
return ( string )ViewState[ " OldSortExpression " ];
}
set
... {
ViewState[ " OldSortExpression " ] = value;
}
}

public bool ShowNoRecordMsg
... {
get
... {
if (ViewState[ " ShowNoRecordMsg " ] == null )
... {
ViewState[ " ShowNoRecordMsg " ] = true ;
}
return ( bool )ViewState[ " ShowNoRecordMsg " ];
}
set
... {
ViewState[ " ShowNoRecordMsg " ] = value;
}
}








private string GetStyleString()
... {
string res = "" ;
foreach ( string sKey in this .Style.Keys)
... {
res += sKey + " : " + this .Style[sKey] + " ; " ;
}
return res;



}




protected override void OnSortCommand(DataGridSortCommandEventArgse)
... {


string sSort = e.SortExpression.Trim();
if ( this .SortExpression.Trim().Split( ' ' )[ 0 ] == e.SortExpression)
... {
sSort = this .SortExpression;
}

string direct = " ASC " ;
if (sSort.IndexOf( " " ) >- 1 )
... {
direct = sSort.Split( ' ' )[ 1 ];


if (direct.ToUpper() == " ASC " )
... {
direct = " DESC " ;


}
else
... {
direct = " ASC " ;

}


}


sSort = sSort.Split( ' ' )[ 0 ] + " " + direct;

if ( this .OldSortExpression != "" )
... {
DataGridColumnoOldCol = this .GetColumnBySortExpression( this .OldSortExpression);
oOldCol.HeaderText = oOldCol.HeaderText.Split( ' ' )[ 0 ];
}

DataGridColumncol = GetColumnBySortExpression(e.SortExpression);

if (direct == " ASC " )
... {
col.HeaderText = col.HeaderText.Split( ' ' )[ 0 ] + " <fontclass='gridarrow'face='webdings'>5</font> " ;
}
else
... {
col.HeaderText = col.HeaderText.Split( ' ' )[ 0 ] + " <fontclass='gridarrow'face='webdings'>6</font> " ;
}
this .OldSortExpression = e.SortExpression;
this .SortExpression = sSort;
this .DataBind();
}

private DataGridColumnGetColumnBySortExpression( string sort)
... {
for ( int i = 0 ;i < this .Columns.Count;i ++ )
... {
if ( this .Columns[i].SortExpression == sort.Split( ' ' )[ 0 ])
... {
return this .Columns[i];
}
}

return null ;
}






protected override void OnItemCreated(DataGridItemEventArgse)
... {



if (e.Item.ItemType == ListItemType.Footer)
... {

this .objFooterItem = e.Item; // 将它保存下来,目前还不能取得它的Visible属性,最后再处理


}
else if (e.Item.ItemType == ListItemType.Item
|| e.Item.ItemType == ListItemType.AlternatingItem)
... {
e.Item.Attributes.Add( " onmouseover " , " javascript:returnDataGridOnMouseOver(); " );
e.Item.Attributes.Add( " onmouseout " , " javascript:returnDataGridOnMouseOut(); " );

}

base .OnItemCreated(e);
}






private string GetPagerText()
... {

string res = @" <!--{3}-->
<tablealign='right'>
<TR>
<TD>
<span{0}{9}title='回到首页'onclick=""if(this.disabled)return;hidAction_{2}.value='1';btnPager_{2}.click();""style='CURSOR:hand;;border:solid1px#ffffff;padding-right:2px;'><fontface='webdings'>7</font>[首页]</span>
</TD>
<TD>
<span{0}{9}title='回到上一页'onclick=""if(this.disabled)return;hidAction_{2}.value='2';btnPager_{2}.click();""style='CURSOR:hand;border:solid1px#ffffff;padding-right:2px;'><fontface='webdings'>3</font>上页</span>
</TD>
<TD>
<span{1}{9}title='回到下一页'onclick=""if(this.disabled)return;hidAction_{2}.value='3';btnPager_{2}.click();""style='CURSOR:hand;border:solid1px#ffffff;padding-right:2px;'>下页<fontface='webdings'>4</font></span>
</TD>
<TD>
<span{1}{9}title='回到最后一页'onclick=""if(this.disabled)return;hidAction_{2}.value='4';btnPager_{2}.click();""style='CURSOR:hand;border:solid1px#ffffff;padding-right:2px;'>[末页]<fontface='webdings'>8</font></span>
</TD>
<TD>
<spanid='lblCurrentIndex'style='CURSOR:hand'>[{4}/{8}页]</span>
</TD><TD>
<spanid='tbl1'style='CURSOR:hand;height:20px;border:solid0px#e0e0e0;padding:2px;'></TD><TD>{10}</TD><TD>跳到</TD><TD></span><inputname='txtGoPage_{2}'value='{7}'type='text'id='txtGoPage'class='textbox1'style='width:20px;height:18px'/>
</TD><TD><INPUTclass='btnPager'onclick=""hidAction_{2}.value='5';btnPager_{2}.click();""type=buttonvalue='GO'></TD><TD>
<spanid='tbl2'style='CURSOR:hand;height:20px;border:solid0px#e0e0e0;padding:2px;'>每页显示</span></TD><TD><inputname='txtRowsPager_{2}'type='text'id='txtRowsPager'value='{5}'class='textbox1'style='width:20px;height:18px'/></TD><TD><INPUTclass='btnPager'onclick=""hidAction_{2}.value='6';btnPager_{2}.click();""type=buttonvalue=重置>
</TD><TD><inputname='hidAction_{2}'id='hidAction_{2}'type='hidden'/> " ;
if ( this .Context != null ) // 非设计视图
... {
res += @"
<inputtype='button'name='btnPager_{2}'id='btnPager_{2}'{6}value='Button'id='btnPager_{2}'style='DISPLAY:none'/> " ;
}
res += @" </TD><TD>
</TR></TABLE> " ;




// System.Web.HttpContext.Current.Response.Write(System.Web.HttpContext.Current.Server.HtmlEncode(res));
// System.Web.HttpContext.Current.Response.Flush();
string sP0 = this .CurrentPageNo > 1 ? "" : " disabled " ;
string sP1 = this .CurrentPageNo < this .PageCount1 ? "" : " disabled " ;
string sP2 = this .ID;
string sP3 = this .ImagePath;
string sP4 = " <fontcolor='red'> " + this .CurrentPageNo.ToString() + " </font> " ;
string sP5 = this .PageSize.ToString();
string sP6 = " onclick="javascript: " + this .Page.GetPostBackEventReference( this , " btnPager_ " + sP2) + " " " ;
string sP7 = this .CurrentPageNo.ToString();
// System.Web.HttpContext.Current.Response.Write(this.PageCount1+"**");

string sP8 = " <fontcolor='red'> " + this .PageCount1.ToString() + " </font> " ;
string sP9 = " onmouseover='javascript:{0}_PagerOnMouseOver(this);'onmouseout='javascript:{0}_PagerOnMouseOut(this);'onmousedown='javascript:{0}_PagerOnMouseDown(this);'onmouseup='javascript:{0}_PagerOnMouseUp(this);' " ;
string sP10 = "" ;
if ( this .ShowRecordCount) // 显示记录数
... {
sP10 = " [<fontcolor='red'> "
+ ((( this .CurrentPageNo - 1 ) * this .PageSize) + 1 ).ToString() + " </font>-<fontcolor='red'> "
+ ( this .CurrentPageNo * this .PageSize < this .RecordCount ? this .CurrentPageNo * this .PageSize: this .RecordCount).ToString() + " </font>/<fontcolor='red'> " + this .RecordCount.ToString() + " </font>条] " ;
}
sP9 = String.Format(sP9,sP2);
res = String.Format(res,sP0,sP1,sP2,sP3,sP4,sP5,sP6,sP7,sP8,sP9,sP10);


string res1 = @"
<SCRIPTLANGUAGE='javascript'>
<!--
function{0}_PagerOnMouseOver(obj)
{
if(obj.disabled)return;obj.runtimeStyle.cssText='border-right:solid1pxgray;border-bottom:solid1pxgray';
}
function{0}_PagerOnMouseOut(obj)
{
if(obj.disabled)return;obj.runtimeStyle.cssText='';
}
function{0}_PagerOnMouseDown(obj)
{
if(obj.disabled)return;
obj.runtimeStyle.cssText='border-bottom:solid1pxwhite;border-right:solid1pxwhite;border-top:solid1pxgray;border-left:solid1pxgray;';
}
function{0}_PagerOnMouseUp(obj)
{
if(obj.disabled)return;obj.runtimeStyle.cssText='border-top:solid1pxwhite;border-left:solid1pxwhite;border-bottom:solid1pxgray;border-right:solid1pxgray;';
}
//-->
</SCRIPT>
<!--********************************************--->
" ;
// res1=String.Format(res1,sP2);
res1 = res1.Replace( " {0} " ,sP2);
return res + res1;
}


protected override void OnPreRender(EventArgse)
... {
// base.OnPreRender(e);
// return;
if (bDefaultStyle) // 默认的样式
... {
this .BorderColor = (Color) new System.Drawing.ColorConverter().ConvertFromString( " #E3EDF5 " );
this .Attributes[ " Class " ] = " GridTable " ;
} // 处理FoooterItem
// 找到第一个Visible=True的列
if ( this .objFooterItem != null )
... {
int i = 0 ;
for (i = 0 ;i < this .Columns.Count;i ++ )
... {
if ( this .Columns[i].Visible)
... {
break ;
}
}


while ( this .objFooterItem.Cells.Count > i + 1 )
... {
objFooterItem.Cells.RemoveAt( 0 );
}
objFooterItem.Cells[i].ColumnSpan = this .Columns.Count - i;
if ( this .bDefaultStyle)
... {
objFooterItem.Cells[i].Attributes[ " class " ] = " t1 " ;
}
if ( this .Items.Count == 0 && this .ShowNoRecordMsg) // 没有记录
... {
this .objFooterItem.Cells[i].Text = @" <tablewidth='100%'cellspacing='0'cellpadding='0'><TR><TDheight='20px'style='color:gray'align='center'>信息:没有查询到任何记录！</td></tr>
</table> " ;
}
else
... {
this .objFooterItem.Cells[i].HorizontalAlign = HorizontalAlign.Right;
this .objFooterItem.Cells[i].Text = this .GetPagerText();
this .objFooterItem.Cells[i].Height = 22 ;
// e.Item.Cells[0].Style.Add("border-top","solid2px#336699");
}
}




if ( this .HeaderStyle.CssClass == "" )
... {
this .HeaderStyle.CssClass = " gridheader " ;
}
if ( this .ItemStyle.CssClass == "" )
... {
this .ItemStyle.CssClass = " t1 " ;
}
if ( this .AlternatingItemStyle.CssClass == "" )
... {
this .AlternatingItemStyle.CssClass = " t2 " ;
}

this .ShowFooter = true ;
base .OnPreRender(e);
}

















[Bindable( true ),
Category( " Appearance " ),
DefaultValue( "" )]
/**/ /// <summary>
/// 将此控件呈现给指定的输出参数。
/// </summary>
/// <paramname="output"> 要写出到的HTML编写器 </param>

protected override void Render(HtmlTextWriteroutput)
... {

if ( this .Context == null ) // 设计
... {
output.Write( " <divstyle='width:100%;border:solid1px#336699'> " );

output.Write( " <fontcolor='orange'>请注意:<BR>1、必须指定的参数：ConnectString,strSQL<BR>2、如果查询中只有一张表且有IdentityKey必须指定该Key</font> " );
}
base .Render(output);

if ( this .Context == null )
... {
output.Write( this .GetPagerText());
output.Write( " <DIV> " );
}


}

/**/ /// <summary>
/// 利用存储过程进行分页
/// </summary>
/// <paramname="strSQl"> sql </param>
/// <paramname="PrimaryKey"> 关键字段，一般为表的主健 </param>
/// <paramname="PageNo"> 当前页从1开始 </param>
/// <paramname="PageSize"> 页面大小 </param>
/// <paramname="SortExpression"> 排序表达式 </param>
/// <paramname="RecordCount"> 记录总数 </param>
/// <returns></returns>
public DataSetGetSqlResult( string strSQL, string PrimaryKey, int PageNo, int PageSize, string SortExpression, ref int RecordCount)
... {
SqlConnectionconn = null ;
SqlCommandcmd = null ;
SqlDataAdapterdapt = null ;
try
... {
conn = new SqlConnection( this .ConnectionString);

cmd = new SqlCommand( " GetPageResult " ,conn);
cmd.CommandTimeout = 60000 ;
cmd.CommandType = CommandType.StoredProcedure;
SqlParameterpSql = cmd.Parameters.Add( " @sql " ,SqlDbType.NVarChar, 4000 );
pSql.Value = strSQL;
SqlParameterpPKey = cmd.Parameters.Add( " @PKey " ,SqlDbType.VarChar, 50 );
pPKey.Value = PrimaryKey;
SqlParameterpPageNo = cmd.Parameters.Add( " @PageNo " ,SqlDbType.Int, 4 );
pPageNo.Value = PageNo;
SqlParameterpPageSize = cmd.Parameters.Add( " @PageSize " ,SqlDbType.Int, 4 );
pPageSize.Value = PageSize;
SqlParameterpSort = cmd.Parameters.Add( " @sort " ,SqlDbType.VarChar, 50 );
pSort.Value = SortExpression;
SqlParameterpRecordCount = cmd.Parameters.Add( " @RecordCount " ,SqlDbType.Int, 4 );
// pRecordCount.Value=SortExpression;
pRecordCount.Direction = ParameterDirection.Output;
dapt = new SqlDataAdapter(cmd);
conn.Open();
DataSetds = new DataSet();
dapt.Fill(ds, " Table1 " );
RecordCount = ( int )pRecordCount.Value;
return ds;

}
catch (Exceptione)
... {
throw (e);
// returnnull;
}
finally
... {
if (conn != null )
conn.Dispose();
if (cmd != null )
cmd.Dispose();
if (dapt != null )
dapt.Dispose();
}

}

/**/ /// <summary>
/// 利用存储过程进行分页
/// </summary>
/// <paramname="strSQl"> sql </param>
/// <paramname="PrimaryKey"> 关键字段，一般为表的主健 </param>
/// <paramname="PageNo"> 当前页从1开始 </param>
/// <paramname="PageSize"> 页面大小 </param>
/// <paramname="SortExpression"> 排序表达式 </param>
/// <paramname="RecordCount"> 记录总数 </param>
/// <returns></returns>
public DataSetGetAccessResult( string strSQL, string PrimaryKey, int PageNo, int PageSize, string SortExpression, ref int RecordCount)
... {


DataSetds = new DataSet();
if (System.Web.HttpContext.Current.Session[ this .Page.ToString()] == null || ( ! this .Page.IsPostBack))
... {

OleDbConnectionconn = null ;
OleDbDataAdapterdapt = null ;
try
... {


conn = new OleDbConnection( this .ConnectionString);
conn.Open();
dapt = new OleDbDataAdapter(strSQL,conn);
// DataSetds=newDataSet();
dapt.Fill(ds, " Table1 " );
System.Web.HttpContext.Current.Session[ this .Page.ToString()] = ds;
}
catch (Exceptione)
... {
throw (e);
// returnnull;
}
finally
... {
if (conn != null )
conn.Dispose();

if (dapt != null )
dapt.Dispose();
}
}
else
... {
ds = (DataSet)System.Web.HttpContext.Current.Session[ this .Page.ToString()];
}

DataViewdv = ds.Tables[ 0 ].DefaultView;
if (SortExpression != "" )
dv.Sort = SortExpression;
RecordCount = dv.Count;

DataTabledt = ds.Tables[ 0 ].Clone();
int iStart = (PageNo - 1 ) * PageSize + 1 ;
int iEnd = PageNo * PageSize;
// System.Web.HttpContext.Current.Response.Write(this.PageCount1);

if (iEnd > dv.Count)
iEnd = dv.Count;

if (iStart > 0 && iEnd >= iStart)
... {
for ( int i = iStart - 1 ;i < iEnd;i ++ )
... {
DataRowrow = dt.NewRow();
row.ItemArray = dv[i].Row.ItemArray;

dt.Rows.Add(row);
}
}
// ds=null;
dv = null ;
ds = new DataSet();
ds.Tables.Add(dt);
return ds;

}


private string GetRequestValue( string sKey)
... {
object o = this .Page.Request.Form[sKey];
if (o != null )
... {
return o.ToString().Trim();
}
return "" ;
}



public override void DataBind()
... {
if ( this .Context == null )
... {
base .DataBind();
return ;
}
if ( this .ConnectionString == "" )
... {
throw ( new Exception( " 没有指定ConnectionString " ));
}
if ( this .strSQL == "" )
... {
throw ( new Exception( " 没有指定strSQL " ));
}

int iCount = 0 ;
// AddBYzhaofeng2004-11-19
if ( this .CurrentPageNo > this .PageCount1)
... {
this .CurrentPageNo = this .PageCount1;
}
if ( this .CurrentPageNo == 0 )
this .CurrentPageNo = 1 ;
// AddEnd
DataSetds = null ;
if ( this .bIsAccess)
ds = this .GetAccessResult( this .strSQL, this .IdentityKey, this .CurrentPageNo, this .PageSize, this .SortExpression, ref iCount);
else

ds = this .GetSqlResult( this .strSQL, this .IdentityKey, this .CurrentPageNo, this .PageSize, this .SortExpression, ref iCount);

this .RecordCount = iCount;
this .DataSource = ds.Tables[ 0 ].DefaultView;
base .DataBind();
}

private void DoPager()
... {
string sAcionType = this .GetRequestValue( " hidAction_ " + this .ID);
switch (sAcionType)
... {
case " 1 " :
this .CurrentPageNo = 1 ;
break ;
case " 2 " :
if ( this .CurrentPageNo > 1 )
... {
this .CurrentPageNo = this .CurrentPageNo - 1 ;
}
else
return ;
break ;
case " 3 " :

if ( this .CurrentPageNo < this .PageCount1)
... {
this .CurrentPageNo = this .CurrentPageNo + 1 ;
}
else
return ;
break ;
case " 4 " :
if ( this .CurrentPageNo != this .PageCount1)
... {
this .CurrentPageNo = this .PageCount1;
}
else
return ;
break ;
case " 5 " : // Goto
string sCurPage = this .GetRequestValue( " txtGoPage_ " + this .ID);
if (CCConvert.IsInt32(sCurPage))
... {
int iCurrentPageNo = Convert.ToInt32(sCurPage);
if (iCurrentPageNo > 0 && iCurrentPageNo <= this .PageCount1)
... {
this .CurrentPageNo = iCurrentPageNo;
}
}
else
return ;
break ;
case " 6 " : // 重设显示页数
string sPageSize = this .GetRequestValue( " txtRowsPager_ " + this .ID);
if (CCConvert.IsInt32(sPageSize))
... {
int iPage = Convert.ToInt32(sPageSize);
if (iPage > 0 )
this .PageSize = iPage;
else
return ;
}
else
return ;
break ;
default :
return ;

}


this .DataBind();
}
IPostBackEventHandler成员 #region IPostBackEventHandler成员

public void RaisePostBackEvent( string eventArgument)
... {
if (eventArgument == " btnPager_ " + this .ID)
... {
this .DoPager();
}

}
protected override void OnInit(EventArgse)
... {


base .OnInit(e);
}



#endregion
}
}

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO





/* ****************************
名称:GetPageResult
功能:得到分页记录集
作者:cpp2017
编写时间:2002-08-17
**************************** */

CREATE PROCEDURE GetPageResult
@sql nvarchar ( 4000 ), -- SqlStatment
@PKey varchar ( 100 ), -- -PrimaryKeyName
@PageNo int , -- CurrentPageNo
@PageSize int , -- PageSize
@Sort varchar ( 50 ), -- SortField
@RecordCount int output -- RecordCount传出参数
AS
BEGIN


DECLARE @sqlStr NVARCHAR ( 4000 );
-- -得到记录总数Start
if @RecordCount = - 1 or @RecordCount is null
begin
SET @sqlStr = ' select@count=Count(1)from( ' + @sql + ' )asAA ' ;
EXECUTE sp_executesql @sqlStr ,N ' @countintout ' , @RecordCount out;
end
-- -得到记录总数End
-- 加上排序Start
IF @Sort IS not null and @sort <> ''
BEGIN

Set @sort = ' orderby ' + @sort ;
END
-- 加上排序End
IF ( @PageNo = 1 ) -- 第一页
SET @sqlStr = ' selecttop ' + cast ( @PageSize as varchar ( 5 )) + ' *FROM( ' + @sql + ' )ASAA ' + @sort
ELSE

BEGIN
declare @sMaxCount varchar ( 10 )
declare @sMinCount varchar ( 10 )
set @sMaxCount = cast ( @PageSize * @PageNo as varchar ( 5 ))
set @sMinCount = Convert ( nvarchar ( 10 ),( @PageNo - 1 ) * @PageSize )


if @PKey != '' or @PKey is null -- 如果有主键,注此key必须是identitykey
begin
SET @sqlStr = ' selecttop ' + @sMaxCount + ' ' + @PKey + ' into#tempfrom( ' + @sql + ' )asAA ' + @sort + ' ; '
Set @sqlStr = @sqlStr + ' deletefrom#tempwhere ' + @PKey + ' in(selecttop ' + @sMinCount + ' ' + @PKey + ' from#temp); '
SET @sqlStr = @sqlStr + ' selectA.*from( ' + @sql + ' )ASAINNERJOIN#tempasBONA. ' + @PKey + ' =B. ' + @Pkey + ' ;droptable#temp '
end
else
Begin
SET @sqlStr = ' selecttop ' + @sMaxCount + ' *into#tempfrom( ' + @sql + ' )asAA ' + @sort + ' ; '
SET @sqlStr = @sqlStr + ' exec( '' altertable#tempaddPrimaryKeyintidentity(1,1); '' );deletefrom#tempwherePrimaryKeyin(selecttop ' + @sMinCount + ' PrimaryKeyFrom#temp) '

SET @sqlStr = @sqlStr + ' ;select*from#temp;droptable#temp '
end
END

EXECUTE ( @sqlStr )
print @sqlstr
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO