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

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

        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 
= trueelse 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 
= trueelse 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>
posted on 2005-04-26 10:32  №阿儒№  阅读(3049)  评论(14编辑  收藏  举报