1.建立用户控件Pager.ascx
1.1 html
<
script
language
="javascript"
>
function callButtonEvent()
{
var keycode =window.event.keyCode;
if(keycode==13)
{
if(check()==true)
{
event.cancelBubble=true;
event.returnValue=false;
document.getElementById('<%=btnGo.ClientID%>').click();
}
}
}
function check()
{
var count = parseInt(document.getElementById('<%=lblTotal.ClientID%>').outerText);
var txt = document.getElementById('<%=txtCurrentPage.ClientID%>').value;
var cur = parseInt(txt);
if ((cur | NaN) ==0)
{
alert('Input page must format as integer.');
event.cancelPostBack=true;
return false;
}
if (cur > count || cur < 1)
{
alert('Input page no out of range.');
event.cancelPostBack=true;
return false;
}
}
</
script
>
<
TABLE
ID
="Table1"
CELLSPACING
="0"
CELLPADDING
="0"
WIDTH
="100%"
BORDER
="0"
>
<
colgroup
>
<
col
width
="400"
>
<
col
width
="50"
>
<
col
width
="50"
>
<
col
width
="40"
>
<
col
width
="20"
>
<
col
width
="40"
>
<
col
width
="40"
>
<
col
width
="50"
>
<
col
width
="70"
>
</
colgroup
>
<
TR
align
="right"
>
<
td
></
td
>
<
TD
><
asp:LinkButton
id
="btnFirstPage"
runat
="server"
CommandArgument
="First"
>
第一页
</
asp:LinkButton
></
TD
>
<
TD
><
asp:LinkButton
id
="btnPrevPage"
runat
="server"
CommandArgument
="Prev"
>
上一页
</
asp:LinkButton
></
TD
>
<
TD
><
ASP:TEXTBOX
ID
="txtCurrentPage"
RUNAT
="server"
MAXLENGTH
="3"
Width
="40"
>
0
</
ASP:TEXTBOX
></
TD
>
<
TD
><
ASP:LABEL
ID
="labOf"
RUNAT
="server"
>
of
</
ASP:LABEL
></
TD
>
<
TD
><
ASP:LABEL
ID
="lblTotal"
RUNAT
="server"
>
0
</
ASP:LABEL
></
TD
>
<
TD
><
ASP:BUTTON
ID
="btnGo"
RUNAT
="server"
TEXT
="转到"
COMMANDARGUMENT
="Go"
ToolTip
="转到"
></
ASP:BUTTON
></
TD
>
<
TD
><
asp:LinkButton
id
="btnNextPage"
runat
="server"
CommandArgument
="Next"
>
下一页
</
asp:LinkButton
></
TD
>
<
TD
><
asp:LinkButton
id
="btnLastPage"
runat
="server"
CommandArgument
="Last"
>
最后一页
</
asp:LinkButton
></
TD
>
</
TR
>
</
TABLE
>
1.2 cs代码
public
class
Pager : System.Web.UI.UserControl
{
protected System.Web.UI.WebControls.Label lblTotal;
protected System.Web.UI.WebControls.Label labOf;
protected System.Web.UI.WebControls.TextBox txtCurrentPage;
protected System.Web.UI.WebControls.Button btnGo;
protected System.Web.UI.WebControls.LinkButton btnFirstPage;
protected System.Web.UI.WebControls.LinkButton btnPrevPage;
protected System.Web.UI.WebControls.LinkButton btnNextPage;
protected System.Web.UI.WebControls.LinkButton btnLastPage;
int size=10;//可以在web.config中配置
public event System.EventHandler NavigationClick;
private void Page_Load(object sender, System.EventArgs e)
{
this.txtCurrentPage.Attributes.Add("onkeypress","callButtonEvent();");
this.btnGo.Attributes.Add("onclick","check();");
if(!this.IsPostBack)
{
SetStyle();
SetEnable();
}
}

Web Form Designer generated code#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
this.btnFirstPage.Click += new System.EventHandler(this.btnGo_Click);
this.btnPrevPage.Click += new System.EventHandler(this.btnGo_Click);
this.btnNextPage.Click += new System.EventHandler(this.btnGo_Click);
this.btnLastPage.Click += new System.EventHandler(this.btnGo_Click);
this.btnGo.Click += new System.EventHandler(this.btnGo_Click);
}

/**//// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion

btnGo_Click#region btnGo_Click
private void btnGo_Click(object sender, System.EventArgs e)
{
LinkButton linkbtn = sender as LinkButton;
if( null == linkbtn )//button
{
Button btn = sender as Button;
if( null == btn )
{
return;
}
else
{
int selPage = -1;
try
{
selPage =Int32.Parse(txtCurrentPage.Text);
}
catch
{
selPage = -1;
}
if (selPage > 0 && selPage <= PageCount)
{
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#region SetStyle
private void SetStyle()
{
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#region SetEnable
// 应根据当前的CurrentPageIndex和pageCount设定哪些按钮可用
private void SetEnable()
{
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 = ( CurrentPageIndex < PageCount );
}
else
{
btnFirstPage.Enabled = false;
btnLastPage.Enabled = false;
btnPrevPage.Enabled = false;
btnNextPage.Enabled = false;
}
}
#endregion

Property#region Property
//获取或设置当前显示页的索引。
public int CurrentPageIndex
{
get
{
object cpage=ViewState["CurrentPageIndex"];
int pindex=(cpage==null)?1:(int)cpage;
if(pindex>PageCount&&PageCount>0)
return PageCount;
else if(pindex<1)
return 1;
return pindex;
}
set
{
int cpage=value;
if(cpage<1)
cpage=1;
else if(cpage>this.PageCount)
cpage=this.PageCount;
ViewState["CurrentPageIndex"]=cpage;
}
}
// 获取或设置需要分页的所有记录的总数。
public int RecordCount
{
get
{
object obj=ViewState["Recordcount"];
return (obj==null)?0:(int)obj;
}
set
{
ViewState["Recordcount"]=value;
SetEnable();
}
}
// 获取当前页之后的页的总数。
public int PagesRemain
{
get
{
return PageCount-CurrentPageIndex;
}
}
// 获取或设置每页显示的项数。
public int PageSize
{
get
{
object obj=ViewState["PageSize"];
if (obj==null)
{
obj= size;
}
return (obj==null)?size:(int)obj;
}
set
{
int pageSize = value;
if (Math.Abs(pageSize) == 0)
pageSize = size;
ViewState["PageSize"]=pageSize;
}
}
// 获取在当前页之后还未显示的剩余记录的项数。
public int RecordsRemain
{
get
{
if(CurrentPageIndex<PageCount)
{
return RecordCount-(CurrentPageIndex*PageSize);
}
else
{
return 0;
}
}
}
// 获取所有要分页的记录需要的总页数。
public int PageCount
{
get
{return (RecordCount > 0) ? (int)Math.Ceiling((double)RecordCount/(double)PageSize) : 1;}
}
public int XRecord
{
get
{
return int.Parse( System.Configuration.ConfigurationSettings.AppSettings["XRecord"].Trim() );
}
}
#endregion Property
}
2.建立DataGridPage.aspx
3.copy如下html代码
<
HTML
>
<
HEAD
>
<
title
>
DataGridPage
</
title
>
<
meta
content
="Microsoft Visual Studio .NET 7.1"
name
="GENERATOR"
>
<
meta
content
="C#"
name
="CODE_LANGUAGE"
>
<
meta
content
="JavaScript"
name
="vs_defaultClientScript"
>
<
meta
content
="http://schemas.microsoft.com/intellisense/ie5"
name
="vs_targetSchema"
>
</
HEAD
>
<
body
MS_POSITIONING
="GridLayout"
>
<
form
id
="Form1"
method
="post"
runat
="server"
>
<
asp:datagrid
id
="DataGrid1"
style
="Z-INDEX: 101; LEFT: 632px; POSITION: absolute; TOP: 40px"
runat
="server"
></
asp:datagrid
><
uc1:pager
id
="Pager1"
runat
="server"
></
uc1:pager
><
asp:datalist
id
="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:repeater
id
="Repeater1"
runat
="server"
>
<
HEADERTEMPLATE
>
<
table
cellpadding
="0"
cellspacing
="0"
border
="0"
>
<
tr
>
<
td
>
用户ID
</
td
>
<
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
>
5.copy如下cs代码
public
class
DataGridPage : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;
protected UserControl.Pager Pager1;//定义用户控件,根据用户控件所在目录做适当的调整
protected System.Web.UI.WebControls.DataList DataList1;
protected System.Web.UI.WebControls.Repeater Repeater1;
public static string ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
private void Page_Load(object sender, System.EventArgs e)
{
if (!this.IsPostBack)
{
BindData();
}
}

Bind Data#region Bind Data
private void BindData()
{
int output=0;
this.SortExpression="UserID asc";
this.FilterExpression="1=1";
ds = GetList(this.Pager1.CurrentPageIndex,this.Pager1.PageSize,this.SortExpression,this.FilterExpression,ref output);
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#region ExecSPDataSet
public static DataSet ExecSPDataSet(string sql,System.Data.IDataParameter[] paramers)
{
SqlConnection conn=new SqlConnection(ConnectionString);
SqlCommand sqlcom=new SqlCommand(sql,conn);
sqlcom.CommandType= CommandType.StoredProcedure ;
foreach(System.Data.IDataParameter paramer in paramers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=sqlcom;
DataSet ds=new DataSet();
da.Fill(ds);
conn.Close();
return ds;
}
#endregion

GetList#region GetList
public static DataSet GetList(int page_num,int row_in_page,string order_column,string comb_condition,ref int output)
{
string sql="tp_Fetch_List";
System.Data.SqlClient.SqlParameter[] p=new SqlParameter[5];
p[0]=new SqlParameter();
p[0].ParameterName ="@page_num";
p[0].Value =page_num;
p[0].DbType=System.Data.DbType.Int32;
p[1]=new SqlParameter();
p[1].ParameterName ="@row_in_page";
p[1].Value =row_in_page;
p[1].DbType=System.Data.DbType.Int32;
p[2]=new SqlParameter();
p[2].ParameterName ="@order_column";
p[2].Value =order_column;
p[2].DbType=System.Data.DbType.String;
p[3]=new SqlParameter();
p[3].ParameterName ="@row_total";
p[3].Direction=System.Data.ParameterDirection.Output;
p[3].DbType=System.Data.DbType.Int32;
p[4]=new SqlParameter();
p[4].ParameterName ="@comb_condition";
p[4].Value =comb_condition;
p[4].DbType=System.Data.DbType.String;
DataSet ds=ExecSPDataSet(sql,p);
if (p[3].Value!=DBNull.Value && p[3].Value.ToString()!=string.Empty )
output=Convert.ToInt32(p[3].Value);
return ds;
}
#endregion 

property#region property
private DataSet ds
{
get
{
return ViewState["ds"] as DataSet;
}
set
{
ViewState["ds"] = value;
}
}
public string FilterExpression
{
get
{
if (this.ViewState["FilterExpression"]!=null)
return (string)this.ViewState["FilterExpression"];
return string.Empty ;
}
set
{
this.ViewState["FilterExpression"]=value;
}
}
public string SortExpression
{
get
{
if (this.ViewState["SortExpression"]!=null)
return (string)this.ViewState["SortExpression"];
return string.Empty ;
}
set
{
this.ViewState["SortExpression"]=value;
}
}
#endregion

Web Form Designer generated code#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
this.Pager1.NavigationClick+=new EventHandler(Pager1_NavigationClick);
}

/**//// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void Pager1_NavigationClick(object sender, EventArgs e)
{
BindData();
}
}
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[TestGrid]
'
)
and
OBJECTPROPERTY
(id, N
'
IsUserTable
'
)
=
1
)
drop
table
[
dbo
]
.
[
TestGrid
]
GO

CREATE
TABLE
[
dbo
]
.
[
TestGrid
]
(
[
UserID
]
[
int
]
NOT
NULL
,
[
UserName
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
Country
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
State
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
Enabled
]
[
bit
]
NULL
)
ON
[
PRIMARY
]
GO
create
PROCEDURE
tp_Fetch_List(
@page_num
INT
,
@row_in_page
INT
,
@order_column
VARCHAR
(
50
),
@row_total
INT
OUTPUT,
@comb_condition
VARCHAR
(
500
)
)
AS
BEGIN
SET
NOCOUNT
ON

DECLARE
@jcc_status
INT
,
@sql
NVARCHAR
(
4000
),
@row_ahead
INT
SET
@jcc_status
=
0

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


SET
@sql
=
'
SELECT TOP
'
+
cast
(
@row_in_page
as
varchar
(
255
))
+
'
* FROM (
'
SET
@sql
=
@sql
+
'
SELECT *
FROM TestGrid
) as A where 1=1
'

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

SET
@sql
=
@sql
+
'
and UserID not in ( select UserID from (
'
SET
@sql
=
@sql
+
'
SELECT TOP
'
+
cast
(
@row_ahead
as
varchar
(
255
))
+
'
* From (
'
SET
@sql
=
@sql
+
'
SELECT *
FROM TestGrid
) as A where 1=1
'
IF
LEN
(
@comb_condition
)
>
0
SET
@sql
=
@sql
+
'
AND (
'
+
@comb_condition
+
'
)
'

IF
LEN
(
@order_column
)
>
0
BEGIN
SET
@sql
=
@sql
+
'
ORDER BY
'
+
@order_column
+
'
) AS B )
'
END
ELSE
BEGIN
SET
@sql
=
@sql
+
'
) AS B )
'
END

IF
LEN
(
@order_column
)
>
0
BEGIN
SET
@sql
=
@sql
+
'
ORDER BY
'
+
@order_column
END

print
@sql

EXEC
(
@sql
)
SET
@sql
=
N
'
SELECT @row_total=COUNT(*) FROM (
'
SET
@sql
=
@sql
+
'
SELECT *
FROM TestGrid
) as A where 1=1
'
IF
LEN
(
@comb_condition
)
>
0
SET
@sql
=
@sql
+
'
AND (
'
+
@comb_condition
+
'
)
'

print
@sql

EXEC
sp_executesql
@sql
,N
'
@row_total INT OUT
'
,
@row_total
OUT
IF
@@ERROR
!=
0
BEGIN
SELECT
@jcc_status
=
-
98
END


exit_bk:
--
exit with MS SQL Server error
IF
@jcc_status
=
-
98
BEGIN
RAISERROR
(
'
MS SQL Server error, please contact your system administrator.
'
,
16
,
1
)
WITH
NOWAIT
RETURN
(
@jcc_status
)
END

--
normal exit
RETURN
(
0
)
END

GO

--
declare @aa int
--
exec tp_Fetch_List 1,10,'',@aa out ,'1=1'
--
select @aa
本文介绍了一种自定义的分页控件实现方法,包括用户控件的创建及使用,通过存储过程进行数据分页,并展示了如何在Web页面中应用此控件以实现高效的数据展示。
950

被折叠的 条评论
为什么被折叠?



