利用存储过程进行高效分页的例子
高效分页的例子：
后台代码：
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();
}
#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>
