通用分页控件(DataGrid,DataList,Repeater都可以用它来分页)

<iframe align="top" marginwidth="0" marginheight="0" src="http://www.zealware.com/46860.html" frameborder="0" width="468" scrolling="no" height="60"></iframe>

1.建立用户控件Pager.ascx

1.1 html

scriptlanguage="javascript">
functioncallButtonEvent()
{
varkeycode=window.event.keyCode;
if(keycode==13)
{
if(check()==true)
{
event.cancelBubble=true;
event.returnValue=false;
document.getElementById('btnGo.ClientID%>').click();
}
}
}
functioncheck()
{
varcount=parseInt(document.getElementById('lblTotal.ClientID%>').outerText);
vartxt=document.getElementById('txtCurrentPage.ClientID%>').value;
varcur=parseInt(txt);
if((cur|NaN)==0)
{
alert('Inputpagemustformatasinteger.');
event.cancelPostBack=true;
returnfalse;
}
if(cur>count||cur1)
{
alert('Inputpagenooutofrange.');
event.cancelPostBack=true;
returnfalse;
}
}
script>
TABLEID="Table1"CELLSPACING="0"CELLPADDING="0"WIDTH="100%"BORDER="0">
colgroup>
colwidth="400">
colwidth="50">
colwidth="50">
colwidth="40">
colwidth="20">
colwidth="40">
colwidth="40">
colwidth="50">
colwidth="70">
colgroup>
TRalign="right">
td>td>
TD>asp:LinkButtonid="btnFirstPage"runat="server"CommandArgument="First">第一页asp:LinkButton>TD>
TD>asp:LinkButtonid="btnPrevPage"runat="server"CommandArgument="Prev">上一页asp:LinkButton>TD>
TD>ASP:TEXTBOXID="txtCurrentPage"RUNAT="server"MAXLENGTH="3"Width="40">0ASP:TEXTBOX>TD>
TD>ASP:LABELID="labOf"RUNAT="server">ofASP:LABEL>TD>
TD>ASP:LABELID="lblTotal"RUNAT="server">0ASP:LABEL>TD>
TD>ASP:BUTTONID="btnGo"RUNAT="server"TEXT="转到"COMMANDARGUMENT="Go"ToolTip="转到">ASP:BUTTON>TD>
TD>asp:LinkButtonid="btnNextPage"runat="server"CommandArgument="Next">下一页asp:LinkButton>TD>
TD>asp:LinkButtonid="btnLastPage"runat="server"CommandArgument="Last">最后一页asp:LinkButton>TD>
TR>
TABLE>

1.2 cs代码

publicclassPager:System.Web.UI.UserControl
{
protectedSystem.Web.UI.WebControls.LabellblTotal;
protectedSystem.Web.UI.WebControls.LabellabOf;
protectedSystem.Web.UI.WebControls.TextBoxtxtCurrentPage;
protectedSystem.Web.UI.WebControls.ButtonbtnGo;
protectedSystem.Web.UI.WebControls.LinkButtonbtnFirstPage;
protectedSystem.Web.UI.WebControls.LinkButtonbtnPrevPage;
protectedSystem.Web.UI.WebControls.LinkButtonbtnNextPage;
protectedSystem.Web.UI.WebControls.LinkButtonbtnLastPage;
intsize=10;//可以在web.config中配置
publiceventSystem.EventHandlerNavigationClick;

privatevoidPage_Load(objectsender,System.EventArgse)
{
this.txtCurrentPage.Attributes.Add("onkeypress","callButtonEvent();");
this.btnGo.Attributes.Add("onclick","check();");
if(!this.IsPostBack)
{
SetStyle();
SetEnable();
}
}

WebFormDesignergeneratedcode#regionWebFormDesignergeneratedcode
overrideprotectedvoidOnInit(EventArgse)
{
//
//CODEGEN:ThiscallisrequiredbytheASP.NETWebFormDesigner.
//
InitializeComponent();
base.OnInit(e);
this.btnFirstPage.Click+=newSystem.EventHandler(this.btnGo_Click);
this.btnPrevPage.Click+=newSystem.EventHandler(this.btnGo_Click);
this.btnNextPage.Click+=newSystem.EventHandler(this.btnGo_Click);
this.btnLastPage.Click+=newSystem.EventHandler(this.btnGo_Click);
this.btnGo.Click+=newSystem.EventHandler(this.btnGo_Click);
}

/**////<summary></summary>
///RequiredmethodforDesignersupport-donotmodify
///thecontentsofthismethodwiththecodeeditor.
///
privatevoidInitializeComponent()
{
this.Load+=newSystem.EventHandler(this.Page_Load);

}
#endregion

btnGo_Click#regionbtnGo_Click
privatevoidbtnGo_Click(objectsender,System.EventArgse)
{
LinkButtonlinkbtn=senderasLinkButton;
if(null==linkbtn)//button
{
Buttonbtn=senderasButton;
if(null==btn)
{
return;
}
else
{
intselPage=-1;
try
{
selPage=Int32.Parse(txtCurrentPage.Text);
}
catch
{
selPage=-1;
}
if(selPage>0&&selPagePageCount)
{
ViewState["CurrentPageIndex"]=selPage;
}
else
{
return;
}
}
}
else//linkbutton
{
switch(linkbtn.CommandArgument.Trim())
{
case"First":
ViewState["CurrentPageIndex"]=1;
break;
case"Prev":
ViewState["CurrentPageIndex"]=(CurrentPageIndex>1)?CurrentPageIndex-1:1;
break;
case"Next":
ViewState["CurrentPageIndex"]=(PageCount>CurrentPageIndex)?CurrentPageIndex+1:PageCount;
break;
case"Last":
ViewState["CurrentPageIndex"]=PageCount;
break;
default:
break;
}
}
SetEnable();//设置显示样式
if(NavigationClick!=null)//调用事件
{
NavigationClick(sender,e);
}
}
#endregion

SetStyle#regionSetStyle
privatevoidSetStyle()
{
this.btnFirstPage.Attributes["style"]="CURSOR:hand";
this.btnLastPage.Attributes["style"]="CURSOR:hand";
this.btnNextPage.Attributes["style"]="CURSOR:hand";
this.btnPrevPage.Attributes["style"]="CURSOR:hand";
}
#endregion

SetEnable#regionSetEnable
//应根据当前的CurrentPageIndex和pageCount设定哪些按钮可用
privatevoidSetEnable()
{
this.lblTotal.Text=PageCount.ToString();

txtCurrentPage.Text=CurrentPageIndex.ToString();

btnPrevPage.Enabled=false;
btnNextPage.Enabled=false;

if(PageCount>1)
{
btnFirstPage.Enabled=btnPrevPage.Enabled=(CurrentPageIndex>1);
btnNextPage.Enabled=btnLastPage.Enabled=(CurrentPageIndexPageCount);
}
else
{
btnFirstPage.Enabled=false;
btnLastPage.Enabled=false;
btnPrevPage.Enabled=false;
btnNextPage.Enabled=false;
}
}

#endregion

Property#regionProperty
//获取或设置当前显示页的索引。
publicintCurrentPageIndex
{
get
{
objectcpage=ViewState["CurrentPageIndex"];
intpindex=(cpage==null)?1:(int)cpage;
if(pindex>PageCount&&PageCount>0)
returnPageCount;
elseif(pindex1)
return1;
returnpindex;
}
set
{
intcpage=value;
if(cpage1)
cpage=1;
elseif(cpage>this.PageCount)
cpage=this.PageCount;
ViewState["CurrentPageIndex"]=cpage;
}
}

//获取或设置需要分页的所有记录的总数。
publicintRecordCount
{
get
{
objectobj=ViewState["Recordcount"];
return(obj==null)?0:(int)obj;
}
set
{
ViewState["Recordcount"]=value;
SetEnable();
}
}

//获取当前页之后的页的总数。
publicintPagesRemain
{
get
{
returnPageCount-CurrentPageIndex;
}
}
//获取或设置每页显示的项数。
publicintPageSize
{
get
{
objectobj=ViewState["PageSize"];
if(obj==null)
{
obj=size;
}
return(obj==null)?size:(int)obj;
}
set
{
intpageSize=value;

if(Math.Abs(pageSize)==0)
pageSize=size;

ViewState["PageSize"]=pageSize;
}
}

//获取在当前页之后还未显示的剩余记录的项数。
publicintRecordsRemain
{
get
{
if(CurrentPageIndexPageCount)
{
returnRecordCount-(CurrentPageIndex*PageSize);
}
else
{
return0;
}
}
}

//获取所有要分页的记录需要的总页数。
publicintPageCount
{
get{return(RecordCount>0)?(int)Math.Ceiling((double)RecordCount/(double)PageSize):1;}
}

publicintXRecord
{
get
{
returnint.Parse(System.Configuration.ConfigurationSettings.AppSettings["XRecord"].Trim());
}
}
#endregionProperty
}

2.建立DataGridPage.aspx
3.copy如下html代码

HTML>
HEAD>
title>DataGridPagetitle>
metacontent="MicrosoftVisualStudio.NET7.1"name="GENERATOR">
metacontent="C#"name="CODE_LANGUAGE">
metacontent="JavaScript"name="vs_defaultClientScript">
metacontent="http://schemas.microsoft.com/intellisense/ie5"name="vs_targetSchema">
HEAD>
bodyMS_POSITIONING="GridLayout">
formid="Form1"method="post"runat="server">
asp:datagridid="DataGrid1"style="Z-INDEX:101;LEFT:632px;POSITION:absolute;TOP:40px"
runat="server">asp:datagrid>uc1:pagerid="Pager1"runat="server">uc1:pager>asp:datalistid="DataList1"style="Z-INDEX:102;LEFT:264px;POSITION:absolute;TOP:40px"
runat="server">
ItemTemplate>
table>
tr>
td>用户ID:td>
td>#DataBinder.Eval(Container.DataItem,"UserID")%>td>
td>用户名:td>
td>#DataBinder.Eval(Container.DataItem,"UserName")%>td>
tr>
table>
ItemTemplate>
asp:datalist>
asp:repeaterid="Repeater1"runat="server">
HEADERTEMPLATE>
tablecellpadding="0"cellspacing="0"border="0">
tr>
td>用户IDtd>
td>用户名:td>
tr>
HEADERTEMPLATE>
ITEMTEMPLATE>
tr>
td>
#DataBinder.Eval(Container.DataItem,"UserID")%>
td>
td>#DataBinder.Eval(Container.DataItem,"UserName")%>td>
tr>
ITEMTEMPLATE>
FOOTERTEMPLATE>
table>
FOOTERTEMPLATE>
asp:repeater>form>
body>
HTML>

4.拖入用户控件Pager.ascx
5.copy如下cs代码

publicclassDataGridPage:System.Web.UI.Page
{
protectedSystem.Web.UI.WebControls.DataGridDataGrid1;
protectedUserControl.PagerPager1;//定义用户控件，根据用户控件所在目录做适当的调整
protectedSystem.Web.UI.WebControls.DataListDataList1;
protectedSystem.Web.UI.WebControls.RepeaterRepeater1;
publicstaticstringConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

privatevoidPage_Load(objectsender,System.EventArgse)
{
if(!this.IsPostBack)
{
BindData();
}
}

BindData#regionBindData
privatevoidBindData()
{
intoutput=0;
this.SortExpression="UserIDasc";
this.FilterExpression="1=1";
ds=GetList(this.Pager1.CurrentPageIndex,this.Pager1.PageSize,this.SortExpression,this.FilterExpression,refoutput);

if(ds!=null&&ds.Tables.Count>0)
{
//DataGrid分页
DataGrid1.DataSource=ds.Tables[0];
DataGrid1.DataBind();
//DataList分页
DataList1.DataSource=ds.Tables[0];
DataList1.DataBind();
//Repeater分页
Repeater1.DataSource=ds.Tables[0];
Repeater1.DataBind();
}
Pager1.RecordCount=output;
}
#endregion

ExecSPDataSet#regionExecSPDataSet
publicstaticDataSetExecSPDataSet(stringsql,System.Data.IDataParameter[]paramers)
{
SqlConnectionconn=newSqlConnection(ConnectionString);
SqlCommandsqlcom=newSqlCommand(sql,conn);
sqlcom.CommandType=CommandType.StoredProcedure;

foreach(System.Data.IDataParameterparamerinparamers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();

SqlDataAdapterda=newSqlDataAdapter();
da.SelectCommand=sqlcom;
DataSetds=newDataSet();
da.Fill(ds);

conn.Close();
returnds;
}

#endregion

GetList#regionGetList
publicstaticDataSetGetList(intpage_num,introw_in_page,stringorder_column,stringcomb_condition,refintoutput)
{
stringsql="tp_Fetch_List";
System.Data.SqlClient.SqlParameter[]p=newSqlParameter[5];

p[0]=newSqlParameter();
p[0].ParameterName="@page_num";
p[0].Value=page_num;
p[0].DbType=System.Data.DbType.Int32;

p[1]=newSqlParameter();
p[1].ParameterName="@row_in_page";
p[1].Value=row_in_page;
p[1].DbType=System.Data.DbType.Int32;

p[2]=newSqlParameter();
p[2].ParameterName="@order_column";
p[2].Value=order_column;
p[2].DbType=System.Data.DbType.String;

p[3]=newSqlParameter();
p[3].ParameterName="@row_total";
p[3].Direction=System.Data.ParameterDirection.Output;
p[3].DbType=System.Data.DbType.Int32;

p[4]=newSqlParameter();
p[4].ParameterName="@comb_condition";
p[4].Value=comb_condition;
p[4].DbType=System.Data.DbType.String;
DataSetds=ExecSPDataSet(sql,p);
if(p[3].Value!=DBNull.Value&&p[3].Value.ToString()!=string.Empty)
output=Convert.ToInt32(p[3].Value);
returnds;
}
#endregion

property#regionproperty
privateDataSetds
{
get
{
returnViewState["ds"]asDataSet;
}
set
{
ViewState["ds"]=value;
}
}
publicstringFilterExpression
{
get
{
if(this.ViewState["FilterExpression"]!=null)
return(string)this.ViewState["FilterExpression"];
returnstring.Empty;
}
set
{
this.ViewState["FilterExpression"]=value;

}
}

publicstringSortExpression
{
get
{
if(this.ViewState["SortExpression"]!=null)
return(string)this.ViewState["SortExpression"];
returnstring.Empty;
}
set
{
this.ViewState["SortExpression"]=value;

}
}

#endregion

WebFormDesignergeneratedcode#regionWebFormDesignergeneratedcode
overrideprotectedvoidOnInit(EventArgse)
{
//
//CODEGEN:ThiscallisrequiredbytheASP.NETWebFormDesigner.
//
InitializeComponent();
base.OnInit(e);
this.Pager1.NavigationClick+=newEventHandler(Pager1_NavigationClick);
}

/**////<summary></summary>
///RequiredmethodforDesignersupport-donotmodify
///thecontentsofthismethodwiththecodeeditor.
///
privatevoidInitializeComponent()
{
this.Load+=newSystem.EventHandler(this.Page_Load);

}
#endregion

privatevoidPager1_NavigationClick(objectsender,EventArgse)
{
BindData();
}
}

6.测试表结构

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[TestGrid]')andOBJECTPROPERTY(id,N'IsUserTable')=1)
droptable[dbo].[TestGrid]
GO

CREATETABLE[dbo].[TestGrid](
[UserID][int]NOTNULL,
[UserName][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,
[Country][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,
[State][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,
[Enabled][bit]NULL
)ON[PRIMARY]
GO

7.测试存储过程

createPROCEDUREtp_Fetch_List(
@page_numINT,
@row_in_pageINT,
@order_columnVARCHAR(50),
@row_totalINTOUTPUT,
@comb_conditionVARCHAR(500)
)
AS
BEGIN
SETNOCOUNTON

DECLARE
@jcc_statusINT,
@sqlNVARCHAR(4000),
@row_aheadINT

SET@jcc_status=0

SET@row_ahead=(@page_num-1)*@row_in_page


SET@sql='SELECTTOP'+cast(@row_in_pageasvarchar(255))+'*FROM('
SET@sql=@sql+'SELECT*

FROMTestGrid
)asAwhere1=1'

IFLEN(@comb_condition)>0
SET@sql=@sql+'AND('+@comb_condition+')'

SET@sql=@sql+'andUserIDnotin(selectUserIDfrom('
SET@sql=@sql+'SELECTTOP'+cast(@row_aheadasvarchar(255))+'*From('
SET@sql=@sql+'SELECT*

FROMTestGrid
)asAwhere1=1'
IFLEN(@comb_condition)>0
SET@sql=@sql+'AND('+@comb_condition+')'

IFLEN(@order_column)>0
BEGIN
SET@sql=@sql+'ORDERBY'+@order_column+')ASB)'
END
ELSE
BEGIN
SET@sql=@sql+')ASB)'
END

IFLEN(@order_column)>0
BEGIN
SET@sql=@sql+'ORDERBY'+@order_column
END

print@sql

EXEC(@sql)

SET@sql=N'SELECT@row_total=COUNT(*)FROM('
SET@sql=@sql+'SELECT*

FROMTestGrid
)asAwhere1=1'
IFLEN(@comb_condition)>0
SET@sql=@sql+'AND('+@comb_condition+')'

print@sql

EXECsp_executesql@sql,N'@row_totalINTOUT',@row_totalOUT

IF@@ERROR!=0
BEGIN
SELECT@jcc_status=-98
END


exit_bk:

--exitwithMSSQLServererror
IF@jcc_status=-98
BEGIN
RAISERROR('MSSQLServererror,pleasecontactyoursystemadministrator.',16,1)WITHNOWAIT
RETURN(@jcc_status)
END

--normalexit
RETURN(0)
END

GO

--declare@aaint
--exectp_Fetch_List1,10,'',@aaout,'1=1'
--select@aa

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1649459