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

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

         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 >

转载于:https://www.cnblogs.com/zhongru_tu/archive/2005/04/26/145403.html