高效分页的例子:
后台代码:
private void Page_Load( object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
if ( ! IsPostBack)
{
ViewState[ " Page " ] = " 1 " ;
// 绑定跳转到第几页下拉列表
string SearchContent = " WHERE (Type <> '转帐') AND (Type <> '年费') " ;
DataSet ds = Common.SqlCard.GetNewsRecord(SearchContent,ViewState[ " Page " ].ToString(), "" );
int SumNo = int .Parse(ds.Tables[ 1 ].Rows[ 0 ][ 0 ].ToString());
ddlNum.Items.Insert( 0 , " --转到-- " );
for ( int j = 0 ;j < ((SumNo - 1 ) / 20 + 1 ) ;j ++ )
{
ddlNum.Items.Insert(j + 1 , " 第 " + (j + 1 ).ToString() + " 页 " );
}
BindGrid();
}
}
private void BindGrid()
{
string SearchContent = " WHERE (Type <> '转帐') AND (Type <> '年费') " ;
// 绑定列表
DataSet ds = Common.SqlCard.GetNewsRecord(SearchContent,ViewState[ " Page " ].ToString(), "" );
DataTable dtList = ds.Tables[ 0 ];
dtList.Columns.Add( " CustomerName " );
for ( int i = dtgList.CurrentPageIndex * 20 ; i < dtList.Rows.Count && i < dtgList.CurrentPageIndex * 20 + 20 ; i ++ )
{
// dt.Rows[i]["ShortDate"] = ((DateTime)dt.Rows[i]["Date"]).ToString("yyyy/MM/dd hh:mm tt");
// 通过CustomerID值绑定定点或目标账号
if (dtList.Rows[i][ " CustomerID " ].ToString() != "" )
{
try
{
// 获取商家名称[打折消费、不打折消费]
dtList.Rows[i][ " CustomerName " ] = Common.SqlCard.GetCustomerName(dtList.Rows[i][ " CustomerID " ].ToString());
}
catch
{
dtList.Rows[i][ " CustomerName " ] = " 未知数据…… " ;
}
}
}
dtgList.DataSource = dtList.DefaultView;
dtgList.DataBind();
// 移动的效果
for ( int i = 0 ; i < dtgList.Items.Count ; i ++ )
{
dtgList.Items[i].Attributes.Add( " onMouseOver " , " this.style.backgroundColor='#F5F5F5' " );
dtgList.Items[i].Attributes.Add( " onMouseOut " , " this.style.backgroundColor='#FFFFFF' " );
}
if (ViewState[ " Page " ].ToString() != " 1 " )
lbnFormerPage.Visible = true ; else lbnFormerPage.Visible = false ;
// 绑定页数
int SumNo = int .Parse(ds.Tables[ 1 ].Rows[ 0 ][ 0 ].ToString());
lblSumNo.Text = SumNo.ToString();
lblSumPage.Text = ((SumNo - 1 ) / 20 + 1 ).ToString();
if (ViewState[ " Page " ].ToString() != lblSumPage.Text)
lbnNextPage.Visible = true ; else lbnNextPage.Visible = false ;
lblCurrentPage.Text = ViewState[ " Page " ].ToString();
}
Web 窗体设计器生成的代码 #region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base .OnInit(e);
}
/**/ /// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this .lbnFormerPage.Click += new System.EventHandler( this .lbnFormerPage_Click);
this .lbnNextPage.Click += new System.EventHandler( this .lbnNextPage_Click);
this .ddlNum.SelectedIndexChanged += new System.EventHandler( this .ddlNum_SelectedIndexChanged);
this .Load += new System.EventHandler( this .Page_Load);
}
#endregion
private void lbnFormerPage_Click( object sender, System.EventArgs e)
{
ViewState[ " Page " ] = int .Parse(ViewState[ " Page " ].ToString()) - 1 ;
BindGrid();
}
private void lbnNextPage_Click( object sender, System.EventArgs e)
{
ViewState[ " Page " ] = int .Parse(ViewState[ " Page " ].ToString()) + 1 ;
BindGrid();
}
private void ddlNum_SelectedIndexChanged( object sender, System.EventArgs e)
{
// Response.Write("<script>alert('" + ddlNum.SelectedIndex + "')</script>");
ViewState[ " Page " ] = ddlNum.SelectedIndex;
BindGrid();
}
数据操作层的代码:
/**/ /// <summary>
/// 获得最新消费记录
/// </summary>
/// <param name="SearchContent"></param>
/// <param name="Page"></param>
/// <param name="Orderby"></param>
/// <returns></returns>
public static DataSet GetNewsRecord( string SearchContent, string Page, string Orderby)
{
SqlParameter[] mySqlParameter = new SqlParameter[ 7 ];
mySqlParameter[ 0 ] = new SqlParameter( " @PageSize " , 20 );
mySqlParameter[ 1 ] = new SqlParameter( " @PageIndex " ,Page);
// 查询条件
mySqlParameter[ 2 ] = new SqlParameter( " @Condition " ,SearchContent);
// 表名
mySqlParameter[ 3 ] = new SqlParameter( " @TheTable " , " card_Record " );
// 选择的字段
mySqlParameter[ 4 ] = new SqlParameter( " @SelectField " , " CardNo,Date,CustomerID,Consumption,Discount,Type,ScoreGet,ScoreLeft " );
// 排序方式
if (Orderby == "" )
Orderby = " order by [Date] desc " ;
mySqlParameter[ 5 ] = new SqlParameter( " @OrderBy " ,Orderby);
mySqlParameter[ 6 ] = new SqlParameter( " @TableID " , " ID " );
return ExecuteStoreDataset( " card_高效分页 " ,mySqlParameter);
}
/**/ /// <summary>
/// 执行存储过程,返回DataSet数据集
/// </summary>
/// <param name="StoreName"></param>
/// <param name="par"></param>
/// <returns></returns>
protected static DataSet ExecuteStoreDataset( string StoreName,SqlParameter[] par)
{
SqlConnection conn = CreateConnection();
SqlDataAdapter myDataAdapter = new SqlDataAdapter(StoreName,conn);
myDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
for ( int i = 0 ;i < par.Length;i ++ )
{
myDataAdapter.SelectCommand.Parameters.Add(par[i]);
}
DataSet ds = new DataSet();
myDataAdapter.Fill(ds);
return ds;
}
存储过程:
CREATE PROCEDURE [ dbo ] . [ card_高效分页 ]
(
@PageSize int , -- 每页记录
@PageIndex int , -- 当前页数,1开始
@Condition varchar ( 8000 ), -- 查询条件,包括and,where,必须有一个条件如where 2>1
@TheTable varchar ( 8000 ), -- 表名
@SelectField varchar ( 8000 ), -- 要选择的字段
@OrderBy varchar ( 8000 ), -- OrderBy字句,包括order
@TableID varchar ( 8000 ) -- table主键
)
AS
begin
declare @Sql varchar ( 8000 )
-- 返回记录
set @Sql = ' select top ' + cast ( @PageSize as varchar ( 10 )) + ' ' + @SelectField + ' from ' + @TheTable + ' ' + @Condition + ' and '
+ @TableID + ' not in (select top ' + cast (( @PageSize * ( @PageIndex - 1 )) as varchar ( 10 )) + ' ' + @TableID + ' from ' + @TheTable + ' ' + @Condition
+ ' ' + @OrderBy + ' ) ' + @OrderBy
exec ( @sql )
-- 返回总数
set @Sql = ' select count( ' + @TableID + ' ) from ' + @TheTable + ' ' + @Condition
exec ( @sql )
end
GO
前台代码(部分):
< td align ="right" >
< TABLE height ="23" cellSpacing ="0" cellPadding ="0" border ="0" >
< tr >
< td width ="3" >< IMG src ="../images/SelectBg.gif" ></ td >
< td vAlign ="middle" background ="../images/SelectBorder.gif" bgColor ="white" >< span style ="LEFT: 3px; OVERFLOW: hidden; POSITION: relative; HEIGHT: 17px" >< asp:dropdownlist id ="ddlNum" Runat ="server" AutoPostBack ="True" CssClass ="Border" ></ asp:dropdownlist ></ span ></ td >
< td width ="3" >< IMG style ="FILTER: fliph" src ="../images/SelectBg.gif" ></ td >
</ tr >
</ TABLE >
</ td >
posted on
2005-04-26 10:32
№阿儒№
阅读(
3049 )
评论()
编辑
收藏
举报