利用存储过程进行高效分页的例子

最新推荐文章于 2024-11-09 16:20:10 发布

Teng_s2000

最新推荐文章于 2024-11-09 16:20:10 发布

阅读量1k

点赞数

分类专栏： ASP.NET Web开发文章标签：存储 dataset string object sql border

本文链接：https://blog.csdn.net/Teng_s2000/article/details/502602

版权

ASP.NET Web开发专栏收录该内容

99 篇文章 0 订阅

订阅专栏

高效分页的例子：
后台代码：

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主键

)

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

前台代码（部分）：

< 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 >