分页存储过程（一）使用SQL Server2005的新函数构造分页存储过程

使用SQL Server2005的新函数构造分页存储过程

         分页存储过程一直是很多人经常用到的东西，怎么样才能设计一个既实用，又强大，还可以解决大数据量的分页存储过程呢？其实在很多时候设计的度还是要把握的，不至于让自己陷入【非要把它设计成万能的分页存储过程】的怪圈中才是最重要的，因为我们还要留出时间还解决其他的很多问题，个人认为适度就可以了，留出一定的空间。也因为万能是不存在的，万物在一定的范畴之内都是合理的，出了范畴可能就没有合理的了。

         分页存储过程大致有下列几种

1、 利用Not in 和select top

 

 

2、 利用id大于多少和select top

 

3、 利用sql中的游标

 

 

4、临时表

         可以参看网上的以下链接

         http://www.zxbc.cn/html/20090625/71918.html

  http://read.newbooks.com.cn/info/174545.html

         在2005中我们的选择就多了，可以利用新语法CTE（公用表表达式）,关于CTE的介绍大家可以参看博客园中一位仁兄的系列教程

         http://www.cnblogs.com/nokiaguy/archive/2009/01/31/1381562.html

 

         或者干脆上微软的官网

http://msdn.microsoft.com/zh-cn/library/ms190766(SQL.90).aspx

 

查看具体内容。

         除此之外还可以利用在2005中新增的一些函数，分别是：row_number(),rank,dense_rank,ntile，这些新函数是您可以有效的分析数据以及向查询饿结果行提供排序值。您可能发现这些新函数有用的典型方案包括：将连续整数分配给结果行，以便进行表示、分页、计分和绘制直方图。

         详细介绍参见下列链接

        

         http://blog.csdn.net/htl258/archive/2009/03/20/4006717.aspx

 

 

         我这里主要使用的就是row_number()结合新语法CTE，先贴上我的存储过程。设计，开发，测试存储过程和相关的C#代码就花费我两天的时间，不过后面的相似界面就很快了，一上午就可以搞两个分页显示的页面，就算是复杂的查询，一上午也可以搞定。

　　下面的存储过程没有将总页数和总条目数返回，如果你有兴趣，可以自己加上，可以参看http://www.zxbc.cn/html/20090625/71918.html中的下列部分

　　Declare @sql nvarchar(4000);
Declare @totalRecord int;
--计算总记录数
if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数

--计算总页数

select @TotalPage=@totalRecord --CEILING((@totalRecord+0.0)/@PageSize)

 　　

　　　　存储过程SQL如下，支持不定列，不定条件，多表联合，排序任意

 

 

代码

set  ANSI_NULLS  ON
set  QUOTED_IDENTIFIER  ON
go







 

 
-- Declare @sql nvarchar(4000); 
-- Declare @totalRecord int; 
-- --计算总记录数 
-- if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL) 
-- set @sql = 'select @totalRecord = count(*) from ' + @TableName 
-- else 
-- set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere 
-- EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 
--
-- --计算总页数 
--
-- select @TotalPage=@totalRecord --CEILING((@totalRecord+0.0)/@PageSize) 

--  =============================================

--  Author:    shiwenbin

--  MSN:　　　　jorden008@hotmail.com

--  Email:　　  jorden008@163.com

--  Create date: 2009-10-20

--  Description:   分页存储过程，根据传递的参数返回分页的结果

--  Parameters：

--  =============================================
ALTER   PROCEDURE   [ dbo ] . [ Proc_GetDataPaged ]  

     --  Add the parameters for the stored procedure here

    

     @StrSelect   varchar ( max ) = null ,     -- 欲显示的列（多列用逗号分开），例如：id,name

     @StrFrom   varchar ( max ) =   null ,     -- 表名称，或者是表连接字符串，多表连接例如:student as s inner join dwinfo as dw on s.dwbh=dw.bh

     @StrWhere   varchar ( max ) = null ,     -- 查询条件,''代表没有条件，单条件或者多条件，多条件例如：name='啊' and id=10

     @StrOrder   varchar ( max )  = null ,    -- 排序列(多个排序列用逗号分开），例如:id desc,name as 

     -- @PageCount int output,     --总页数

     @ItemCount   bigint  output,      -- 总记录数

     @PageSize   int   = 50 ,      -- 每页显示条数
     @BeginIndex   int = 1 , -- 记录开始数
     @DoCount   bit   = 0   -- 是否统计总数，为0不统计，为1统计
  --    @PageIndex int =1     --当前页

     -- @ClassCode char(10) =null,    --单位编号（班级编号）   

AS

BEGIN

     SET  NOCOUNT  ON ;

     
     Declare   @sql   nvarchar ( 4000 ); 
     Declare   @totalRecord   int ; 
     -- 计算总记录数 
     if  ( @StrWhere   = ''''   or   @StrWhere = ''   or   @StrWhere   is   NULL ) 
     set   @sql   =   ' select @totalRecord = count(*) from  '   +   @StrFrom  
     else  
     set   @sql   =   ' select @totalRecord = count(*) from  '   +   @StrFrom   +   '  where  '   +   @StrWhere  
     EXEC  sp_executesql  @sql ,N ' @totalRecord int OUTPUT ' , @ItemCount  OUTPUT -- 计算总记录数 

     declare   @SqlQuery   varchar ( max )

   --  if(@PageIndex=1)
   if ( @BeginIndex = 1   or   @BeginIndex = 0    or   @BeginIndex   < 0 )
        begin

         if ( @StrWhere   is   null ) -- if(@StrWhere='')

         set   @SqlQuery = ' select top  ' + convert ( varchar , @PageSize )

           +   '  row_number() over(order by  ' + @StrOrder + '  ) as RowNumber, ' + @StrSelect +

        '  from  ' + @StrFrom ;

         else

            -- set @sql='select top @PageSize * from @TableName order by id desc';

         -- select top @PageSize * from @TableName order by id desc;

         set   @SqlQuery = ' select top  ' + convert ( varchar , @PageSize )

           +   '  row_number() over(order by  ' + @StrOrder + '  ) as RowNumber, ' + @StrSelect + '  from  ' + @StrFrom + '  where  ' + @StrWhere ;

         -- exec (@SqlQuery)

--       @SqlQuery

        end

     else          

        begin

         if ( @StrWhere   is   null ) -- if(@StrWhere='')

         begin

            set   @SqlQuery = ' with cte as (

select row_number() over(order by  ' + @StrOrder + '  ) as RowNumber, ' + @StrSelect + '  from  ' + @StrFrom + '

)
select * from cte where RowNumber between  ' +  

-- convert(varchar,((@PageIndex-1)*@PageSize)+1)+' and '+
--
--       convert(varchar,@PageIndex*@PageSize)           
convert ( varchar , @BeginIndex ) + '  and  ' +

       convert ( varchar , @BeginIndex + @PageSize )   
            -- print @SqlQuery

         end

         else

         begin

          

            set   @SqlQuery = ' with cte as (

select row_number() over(order by  ' + @StrOrder + '  ) as RowNumber, ' + @StrSelect + '  from  ' + @StrFrom + '  where  ' + @StrWhere + '

)

select * from cte where RowNumber between  ' +  
-- convert(varchar,((@PageIndex-1)*@PageSize)+1)+' and '+
--
--         convert(varchar,@PageIndex*@PageSize)
convert ( varchar , @BeginIndex ) + '  and  ' +

       convert ( varchar , @BeginIndex + @PageSize )  
            -- print @SqlQuery

           end

        end

     -- set @SqlQuery=@SqlQuery+';select @ItemCount =count(*) from '+@TableName

     -- set @PageCount=@ItemCount/@PageSize

     -- print '共'+@PageConut+'页'+@ItemCount+'条'

     -- print @ItemCount

     print   @SqlQuery

     exec  ( @SqlQuery )

END

 

 

 

 　　c#相关代码的数据库访问使用的是微软的企业库 V4.1

　　

　　Enterprise Library 4.1 下载地址：

 

　　http://www.microsoft.com/downloads/details.aspx?FamilyId=1643758B-2986-47F7-B529-3E41584B6CE5&displaylang=en

 　　示例代码，前台页面，前台为用户控件

　　

Code
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="StudentDetailsTable.ascx.cs" Inherits="Kimbanx.UCS.ForeignStudentAdmin.UserControl.UserControl.StudentDetailsTable" %>
<link href="../css/core.css" rel="stylesheet" type="text/css" />

<table class="StudentPagingTablePanel">
    <tr>
        <td> 单位：<asp:Label ID="lblClassName" runat="server" Text="Label"></asp:Label></td>
        <td>级别：<asp:Label ID="lblClassLevel" runat="server" Text="Label"></asp:Label>级节点</td>
    </tr>
    <tr>
        <td>该单位共有<asp:Label ID="lblStudentType" runat="server" Text="Label"></asp:Label>学员
        <asp:Label ID="lblStudentCount" runat="server" Text="Label"></asp:Label>人</td>
        <td>每页显示<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="True" 
                onselectedindexchanged="ddlPageSize_SelectedIndexChanged">
            </asp:DropDownList>人&nbsp;&nbsp;共<asp:Label ID="lblPageCount" runat="server" Text="Label"></asp:Label>页
            &nbsp;&nbsp;现为第<asp:Label ID="lblPageIndex" runat="server" Text="Label"></asp:Label>页&nbsp;&nbsp;
            <asp:LinkButton ID="firstPage" runat="server" CommandArgument="first" 
                oncommand="LinkButton_Command">首页</asp:LinkButton>&nbsp;&nbsp;
            
            <asp:LinkButton ID="nextPage" runat="server" CommandArgument="next" 
                oncommand="LinkButton_Command">下一页</asp:LinkButton>&nbsp;&nbsp;
                <asp:LinkButton ID="prevPage" runat="server" CommandArgument="prev" 
                oncommand="LinkButton_Command">上一页</asp:LinkButton>&nbsp;&nbsp;
            <asp:LinkButton ID="lastPage" runat="server" CommandArgument="last" 
                oncommand="LinkButton_Command">末页</asp:LinkButton>
        </td>
    </tr>
</table>
<br />
<asp:GridView ID="gvStudent" runat="server" AutoGenerateColumns="False" 
    EmptyDataText="没有符合条件的数据">
    <Columns>
        <asp:TemplateField HeaderText="照片">
            <ItemTemplate>
                <asp:Image ID="Image1" CssClass="studentImage" ImageUrl =<%# GetStudentImageUrl(Eval("zpadress")) %> runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="姓名(中英简)">
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" Text='<%# Eval("xmjz") %>'></asp:Label>
                <br />
                <asp:Label ID="Label2" runat="server" Text='<%# Eval("xmjy") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="jx" HeaderText="军衔" />
        <asp:BoundField DataField="zw" HeaderText="职务" />
        <asp:BoundField DataField="gj" HeaderText="国家" />
        <asp:BoundField DataField="sjyqk" HeaderText="文化程度" />
        <asp:BoundField DataField="zj" HeaderText="宗教" />
        <asp:TemplateField HeaderText="出生/入伍">
            <ItemTemplate>
                <asp:Label ID="Label3" runat="server" Text='<%# SetBirthDate(Eval("csrq")) %>'></asp:Label>
                <br />
                <asp:Label ID="Label4" runat="server" Text='<%# SetEnrollDate(Eval("rwrq")) %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="xzz" HeaderText="房间/楼号" />
        <asp:TemplateField HeaderText="电话/小号">
            <ItemTemplate>
                <asp:Label ID="Label5" runat="server" Text='<%# Eval("dhd") %>'></asp:Label>
                <br />
                <asp:Label ID="Label6" runat="server" Text='<%# Eval("dhx") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="fcjp" HeaderText="返程机票" />
        <asp:BoundField DataField="xh" HeaderText="学号" />
    </Columns>
</asp:GridView>

 

 　　示例代码，后台代码

　　

Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Kimbanx.UCS.ForeignStudentAdmin.Model;
using Kimbanx.UCS.ForeignStudentAdmin.Common;

namespace Kimbanx.UCS.ForeignStudentAdmin.UserControl.UserControl
{
    public partial class StudentDetailsTable : System.Web.UI.UserControl
    {
        private Database _db = DatabaseFactory.CreateDatabase();
        private DbCommand _command;
        private DbConnection _connection;
        private DataSet _ds;
        private string _classCode;
        private string _classFullName;
        private string _studentType;
        private string _studentCount;
        private string _queryStringWhere;
        private DataTable _studentTable;
        protected string SetBirthDate(object obj)
        {
            string result = string.Empty;
            string temp = obj.ToString();
            result = DateTime.Parse(temp).ToShortDateString();
            return result;
        }
        protected string SetEnrollDate(object obj)
        {
            string result = string.Empty;
            string temp = obj.ToString();
            result = DateTime.Parse(temp).ToShortDateString();
            return result;
        }
        protected void Filldata_dllPageSize()
        {
            for (int i = 1; i < 100; i++)
            {
                ddlPageSize.Items.Add(i.ToString());
            }
            ddlPageSize.SelectedIndex = 14;
        }
        protected void InitSession()
        {
            //Session["PageSize"] = 0;
            Session["PageIndex"] = 1;
            Session["PageCount"] = int.Parse(_studentCount) / 15 + 1;
        }

        /// <summary>
        /// 获取QueryString传递参数
        /// </summary>
        protected void GetQueryStringPara()
        {
            _classCode = Request.QueryString["dwbh"];
            _classFullName =HttpUtility.UrlDecode( Request.QueryString["dwmc"]);
            _studentCount = Request.QueryString["studentCount"];
            _studentType =HttpUtility.UrlDecode( Request.QueryString["studentType"]);
            _queryStringWhere = Request.QueryString["where"];
        }
        protected void SetLabelText()
        {
            this.lblClassName.Text = _classFullName;
            this.lblClassLevel.Text = GetClassInfo(_classCode).Level.ToString();
            this.lblStudentCount.Text = _studentCount;
            this.lblStudentType.Text = _studentType;
        }
        #region
        ///// <summary>
        ///// 获取学员数据
        ///// </summary>
        ///// <param name="strSelect">显示的字段</param>
        ///// <param name="strFrom">用到的</param>
        /////<param name="strWhere">查询条件</param>
        ///// <param name="pageSize">每页显示条数</param>
        ///// <param name="pageIndex">当前页</param>
        ///// <returns></returns>
        //protected DataTable  GetStudentData(string strSelect,string strFrom,string strWhere,int pageSize,int pageIndex)
        //{
        //    _command = _db.GetStoredProcCommand("StudentPaging");
        //    _db.AddInParameter(_command, "StrSelect", DbType.String, "zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh");
        //    _db.AddInParameter(_command, "StrFrom", DbType.String, "tx_xyzl");
        //    _db.AddInParameter(_command, "StrWhere", DbType.String, strWhere );
        //    _db.AddInParameter(_command, "StrOrder", DbType.String, "id");
        //    _db.AddInParameter(_command, "PageSize", DbType.Int32, pageSize );
        //    _db.AddInParameter(_command, "PageIndex", DbType.Int32,pageIndex );

        //    _studentTable = _db.ExecuteDataSet(_command).Tables[0];
        //    return _studentTable;
        //}
        #endregion
        protected string GetStudentImageUrl(object imageUrl)
        {

            string serverUrl = http://192.168.0.1/admin;
            string imageurl = string.Empty;
            if (!(imageUrl == null))
            {

                string temp = imageUrl.ToString().Trim();
                if (!string.IsNullOrEmpty(temp))
                { imageurl = string.Format("{0}{1}", serverUrl, temp.Substring(temp.IndexOf("/"))); }
            }
            return imageurl;
        }
        /// <summary>
        /// 绑定分页之后的数据
        /// </summary>
        /// <param name="pageSize">每页显示的数据量</param>
        /// <param name="pageIndex">当前页</param>
        protected void BindStudentData(int pageSize, int pageIndex)
        {
            switch (_queryStringWhere)
            {
                case "jx":
                    this.gvStudent.DataSource = Helper.StudentPagingResult(
                        "zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh",
                        "student",
                        string.Format("dwbh='{0}' and jx='{1}'", _classCode, _studentType),
                        "id",
                      pageSize,
                       pageIndex);
                    this.gvStudent.DataBind();
                    break;
                case "gj":
                    this.gvStudent.DataSource = Helper.StudentPagingResult(
                        "zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh",
                        "student",
                        string.Format("dwbh='{0}' and gj='{1}'", _classCode, _studentType),
                        "id",
                       pageSize,
                       pageIndex);
                    this.gvStudent.DataBind();
                    break;
                case "allyear":
                    this.gvStudent.DataSource = Helper.StudentPagingResult(
                        "s.zpadress,s.xmjz,s.xmjy,s.jx,s.zw,s.gj,s.sjyqk,s.zj,s.csrq,s.rwrq,s.xzz,s.dhd,s.dhx,s.fcjp,s.hzh,s.xh",
                        "student as s inner join class as dw on s.dwbh=dw.bh",
                        string.Format(@"s.dwbh='{0}' and (dw.kxsj>=convert(datetime,'{1}'+'-01-01',120) and
                    dw.kxsj<=convert(datetime,'{1}'+'-12-31',120) or dw.bysj>=convert(datetime,'{1}'+'-01-01',120) and
                    dw.bysj<=convert(datetime,'{1}'+'-12-31',120)) ", _classCode, _studentType),
                        "s.id",
                      pageSize,
                       pageIndex);
                    this.gvStudent.DataBind();
                    break;
                case "new":
                    this.gvStudent.DataSource = Helper.StudentPagingResult(
                        "s.zpadress,s.xmjz,s.xmjy,s.jx,s.zw,s.gj,s.sjyqk,s.zj,s.csrq,s.rwrq,s.xzz,s.dhd,s.dhx,s.fcjp,s.hzh,s.xh",
                        "student as s inner join class as dw on s.dwbh=dw.bh",
                        string.Format(@"s.dwbh='{0}' and (dw.kxsj>=convert(datetime,'{1}'+'-01-01',120) and
                    dw.kxsj<=convert(datetime,'{1}'+'-12-31',120)) ", _classCode, _studentType),
                        "s.id",
                       pageSize,
                       pageIndex);
                    this.gvStudent.DataBind();
                    break;
            }
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (UserAuthHelper.GetUserAuthType("1") == UserAuthEnum.Admin||
                UserAuthHelper.GetUserAuthType("2") == UserAuthEnum.CurrentStudentDetails)
            {

                GetQueryStringPara();
                SetLabelText();
                if (GetStudentCount() == 0)
                {
                    StudentCountZero();
                    return;
                }
                    if (!IsPostBack)
                    {

                        Filldata_dllPageSize();
                        SetPageIndex(1);
                        SetPageCount();
                        BindStudentData(GetPageSize(), GetPageIndex());
                    }
                    else
                    {
                    }
               
            }
            else
            {
                this.Controls.Add(new LiteralControl("您没有相应的权限，请联系管理员"));
            }
        }
        /// <summary>
        /// 获取班级信息，班级全称，班级级别
        /// </summary>
        /// <param name="classCode">班级编号</param>
        /// <returns></returns>
        protected ClassEntity GetClassInfo(string classCode)
        {
            ClassEntity entity = new ClassEntity();
            entity.Code = classCode;
            _command = _db.GetStoredProcCommand("ClassInfo");
            _db.AddInParameter(_command, "bh", DbType.String, classCode);
            using (IDataReader reader = _db.ExecuteReader(_command))
            {
                while (reader.Read())
                {
                    entity.FullName = reader.GetString(1);
                    entity.Level = reader.GetInt32(2);
                }
            }
            return entity;
        }
        #region Get and Set PageSize

        protected int GetPageSize()
        {
            return int.Parse(ddlPageSize.SelectedValue);
        }
        protected void SetPageSize(int pageSize)
        {
            this.ddlPageSize.Text = pageSize.ToString();
        }
        #endregion
        #region Get and Set PageIndex
        protected int GetPageIndex()
        {
            return int.Parse(this.lblPageIndex.Text.Trim());
        }
        protected void SetPageIndex(int pageIndex)
        {
            this.lblPageIndex.Text = pageIndex.ToString();
        }
        #endregion
        #region Get and Set PageCount
        protected int GetPageCount()
        {
            return int.Parse(this.lblPageCount.Text.Trim());
        }
        protected void SetPageCount()
        {
            int studentCount = GetStudentCount();
            int pageSize = GetPageSize();
            if (studentCount % pageSize == 0)
            {
                this.lblPageCount.Text = (studentCount / pageSize).ToString();
            }
            else
            {
                this.lblPageCount.Text = (studentCount / pageSize + 1).ToString();
            }
        }
        #endregion
        #region Get and Set StudentCount
        protected int GetStudentCount()
        {
            return int.Parse(this.lblStudentCount.Text.Trim());
        }
        protected void SetStudentCount(int studentCount)
        {
            this.lblStudentCount.Text = studentCount.ToString();
        }
        #endregion
        protected void StudentCountZero()
        {
            this.lblPageIndex.Text = "0";
            this.lblPageCount.Text = "0";
        }
        protected void LinkButton_Command(object sender, CommandEventArgs e)
        {
            if (GetStudentCount() == 0)
            {
                StudentCountZero();
                return;
            }
            int pageCount = GetPageCount();
            int pageIndex = GetPageIndex();
            int pageSize = GetPageSize();
            switch (e.CommandArgument.ToString())
            {
                case "first":
                    if (pageIndex == 1) { }
                    else
                    {
                        pageIndex = 1;
                        SetPageIndex(pageIndex);
                        pageSize = GetPageSize();
                        SetPageCount();
                        BindStudentData(pageSize, pageIndex);
                    }
                    break;
                case "next":

                    if (pageCount == pageIndex & pageIndex == 1)
                    { }
                    else if (pageIndex == 1 && pageCount > pageIndex)
                    {
                        SetPageIndex(++pageIndex);
                        pageSize = GetPageSize();
                        SetPageCount();
                        BindStudentData(pageSize, pageIndex);
                    }
                    else if (pageIndex > 1 && pageCount == pageIndex)
                    { }
                    else
                    {
                        SetPageIndex(++pageIndex);
                        pageSize = GetPageSize();
                        SetPageCount();
                        BindStudentData(pageSize, pageIndex);
                    }
                    break;
                case "prev":
                    if (pageIndex == 1)
                    { }
                    else if (pageIndex == pageCount && pageIndex > 1)
                    {
                        SetPageIndex(--pageIndex);
                        pageSize = GetPageSize();
                        SetPageCount();
                        BindStudentData(pageSize, pageIndex);
                    }
                    else if (pageIndex == 2)
                    {
                        SetPageIndex(1);
                        pageSize = GetPageSize();
                        SetPageCount();
                        BindStudentData(pageSize, pageIndex);
                    }
                    else
                    {
                        SetPageIndex(--pageIndex);
                        pageSize = GetPageSize();
                        SetPageCount();
                        BindStudentData(pageSize, pageIndex);
                    }
                    break;
                case "last":
                    if (pageCount == pageIndex)
                    { }
                    else
                    {
                        SetPageIndex(pageCount);
                        pageIndex = GetPageIndex();
                        SetPageCount();
                        BindStudentData(pageSize, pageIndex);
                    }
                    break;
                default:
                    SetPageIndex(1);
                    pageSize = GetPageSize();
                    SetPageCount();
                    BindStudentData(pageSize, pageIndex);
                    break;
            }

        }

        protected void ddlPageSize_SelectedIndexChanged(object sender, EventArgs e)
        {
            int pageIndex = GetPageIndex();
            int pageCount = GetPageCount();
            int pageSize = GetPageSize();
            pageIndex = 1;
            SetPageIndex(pageIndex);
            SetPageSize(int.Parse(((DropDownList)sender).SelectedValue));
            pageSize=GetPageSize();
            SetPageCount();
            BindStudentData(pageSize, pageIndex);
        }

    }
}

 

最后再贴一个圆友的通用存储过程，原文地址：通用存储过程分页(使用ROW_NUMBER()和不使用ROW_NUMBER()两种情况)性能分析

 

代码

set  ANSI_NULLS  ON
set  QUOTED_IDENTIFIER  ON
go




--  =============================================
--  Author:        <jiangrod>
--  Create date: <2010-03-03>
--  Description:    <SQL2005及后续版本通用分页存储过程>调用方法: sp_Pager2005 'xtest','*','ORDER BY ID ASC','xname like ''%222name%''',2,20,0,0
--                     适合从单个表查询数据
--  =============================================
ALTER   PROCEDURE   [ dbo ] . [ Proc_GetDataPaged2 ]
@tblName     varchar ( 255 ),             --  表名如：'xtest'
@strGetFields   varchar ( 1000 )  =   ' * ' ,   --  需要返回的列如：'xname,xdemo'
@strOrder   varchar ( 255 ) = '' ,           --  排序的字段名如：'order by id desc'
@strWhere    varchar ( 1500 )  =   '' ,       --  查询条件(注意:不要加where)如:'xname like ''%222name%''' 
@beginIndex   int = 1 ,                   -- 开始记录位置
-- @pageIndex  int = 1,                -- 页码如：2
@pageSize     int   =   50 ,                --  每页记录数如：20
@recordCount   int  output,             --  记录总数
@doCount   bit = 0                          --  非0则统计,为0则不统计(统计会影响效率)
AS

declare   @strSQL   varchar ( 5000 )
declare   @strCount   nvarchar ( 1000 )
-- 总记录条数
if ( @doCount != 0 )
begin
     if ( @strWhere   != '' )
     begin
         set   @strCount = ' set @num=(select count(1) from  ' +   @tblName   +   '  where  ' + @strWhere + '  ) '
     end
     else
     begin
         set   @strCount = ' set @num=(select count(1) from  ' +   @tblName   +   '  ) '
     end
     EXECUTE  sp_executesql  @strCount  ,N ' @num INT output ' , @RecordCount  output
end

if   @strWhere   != ''
begin
     set   @strWhere = '  where  ' + @strWhere
end
set   @strSQL = ' SELECT * FROM (SELECT ROW_NUMBER() OVER ( ' + @strOrder + ' ) AS ROWID, '
set   @strSQL = @strSQL + @strGetFields + '  FROM [ ' + @tblName + ' ]  ' + @strWhere
set   @strSQL = @strSQL + ' ) AS sp WHERE ROWID BETWEEN  ' + str ( @beginIndex )
set   @strSQL = @strSQL + '  AND  ' + str ( @beginIndex + @PageSize )
-- set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
-- set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
exec  ( @strSQL )

 

 

　　再来一个

　　

 

 

代码

set  ANSI_NULLS  ON
set  QUOTED_IDENTIFIER  ON
go






--  =============================================
--  Author:        <Author,,Name>
--  Create date: <Create Date,,>
--  Description:    分页获取商品信息
-- 调用方法: Proc_GetProductPaged '2','*','','xname like ''%222name%''','ORDER BY ID ASC',20,2,0,0
--  =============================================
ALTER   PROCEDURE   [ dbo ] . [ Proc_GetProductPaged ]
     --  Add the parameters for the stored procedure here
     @ProductType   smallint = 1 , -- 商品类型,1全部2种子3农药4肥料
     @StrSelect   varchar ( max ) = '' , -- 显示字段
     @StrFrom   varchar ( max ) = '' , -- 查询来源
     @StrWhere   varchar ( max ) = '' , -- 查询条件
     @StrOrder   varchar ( max ) = '' , -- 排序规范
     @PageSize   int = 50 , -- 记录数
     @BeginIndex   int = 1 ,                   -- 开始记录位置
--     @PageIndex int=1,--页码
     @Count   int  output, -- 记录总数
     @DoCount   bit = 0 --  1则统计,为0则不统计(统计会影响效率)
AS
BEGIN
     --  SET NOCOUNT ON added to prevent extra result sets from
     --  interfering with SELECT statements.
     declare   @seedtype   int
     set   @seedtype = 2
     declare   @pestype   int
     set   @pestype = 3
     declare   @ferttype   int
     set   @ferttype = 4
    
     create   table  #product
    (    
      productid  uniqueidentifier ,
      productname  varchar ( 50 ),
      className  varchar ( 50 ),
      productType  int ,
      createdate  datetime ,
      modifydate  datetime
     --   companyid uniqueidentifier
    )
     declare   @strSQL   varchar ( max )
     declare   @strCount   nvarchar ( max )
     -- 计算总记录条数
     if ( @DoCount != 0 )
     begin
         if ( @StrWhere   != '' )
         begin
             if ( @ProductType = 1 )
                 begin
                     set   @strCount = ' set @num=(select count(1) from Seed where  ' + @StrWhere + '  ) ' +
                     ' +(select count(1) from pesticide where  ' + @StrWhere + '  ) ' +
                     ' +(select count(1) from fertilizer where  ' + @StrWhere + '  ) '
                 end
             else   if ( @ProductType = 2 )
                 begin
                     set   @strCount = ' set @num=(select count(1) from Seed where  ' + @StrWhere + '  ) '
                 end
             else   if ( @ProductType = 3 )
                 begin
                     set   @strCount = ' set @num=(select count(1) from pesticide where  ' + @StrWhere + '  ) '
                 end
             else   if ( @ProductType = 4 )
                 begin
                     set   @strCount = ' set @num=(select count(1) from fertilizer where  ' + @StrWhere + '  ) '
                 end             
         end
         else
         begin
             if ( @ProductType = 1 )
                 begin
                     set   @strCount = ' set @num=(select count(1) from Seed ) ' +
                     ' +(select count(1) from pesticide  ) ' +
                     ' +(select count(1) from fertilizer ) '
                    
                 end
             else   if ( @ProductType = 2 )
                 begin
                     set   @strCount = ' set @num=(select count(1) from Seed ) '
                 end
             else   if ( @ProductType = 3 )
                 begin
                     set   @strCount = ' set @num=(select count(1) from pesticide ) '
                 end
             else   if ( @ProductType = 4 )
                 begin
                     set   @strCount = ' set @num=(select count(1) from fertilizer ) '
                 end     
         end
         EXECUTE  sp_executesql  @strCount  ,N ' @num INT output ' , @Count  output
     end
     -- 分页获取数据
     if  ( @StrWhere   != ''  )
     begin
         set   @StrWhere = '  where  ' + @StrWhere
     end
     if ( @ProductType = 1 )
         begin
             set   @strSQL = ' insert into #product 
            select s.seedid,s.seedname,cc.cropclassname, ' + cast ( @seedtype   as   varchar ( 1 )) + ' ,s.createdate,s.modifydate
            from seed as s inner join cropclass as cc on s.cropclasscode=cc.cropclasscode '
             + @StrWhere +
             ' union
            select p.pesticideid,p.pesname,pc.pesclassname, ' + cast ( @pestype   as   varchar ( 1 )) + ' ,p.createdate,p.modifydate
            from pesticide as p inner join pesclass as pc on p.pesclasscode=pc.pesclasscode '
             + @StrWhere +
             ' union
            select f.fertilizerid,f.fertname,fc.fertclassname, ' + cast ( @ferttype   as   varchar ( 1 )) + ' ,f.createdate,f.modifydate
            from fertilizer as f inner join fertilizerclass as fc on f.fertclasscode=fc.fertclasscode '
             + @StrWhere

             set   @strSQL =   @strSQL + '  SELECT * FROM (SELECT ROW_NUMBER() OVER ( ' + @StrOrder + ' ) AS ROWID, '
             set   @strSQL = @strSQL + ' * FROM [#product]  ' -- +@StrWhere
             set   @strSQL = @strSQL + ' ) AS sp WHERE ROWID BETWEEN  ' + str ( @BeginIndex )
             set   @strSQL = @strSQL + '  AND  ' + str ( @BeginIndex + @PageSize - 1 )
--             set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
--             set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
         end
     else
         begin
             set   @strSQL = ' insert into #product select  ' + @StrSelect +
             '  from  ' + @StrFrom + @StrWhere
             exec  ( @strSQL )
             set   @strSQL = ''
             set   @strSQL = ' SELECT * FROM (SELECT ROW_NUMBER() OVER ( ' + @strOrder + ' ) AS ROWID, '
             set   @strSQL = @strSQL + ' * FROM [#product] ' + @strWhere
             set   @strSQL = @strSQL + ' ) AS sp WHERE ROWID BETWEEN  ' + str ( @BeginIndex )
             set   @strSQL = @strSQL + '  AND  ' + str ( @BeginIndex + @PageSize - 1 )
--             set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
--             set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
         end
--     else if(@ProductType=2)
--         begin
--             set @strSQL='insert into #product select '+@StrSelect+','+@seedtype+
--             'from '+@StrFrom+@StrWhere
--             exec @strSQL
--             set @strSQL=''
--             set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
--             set @strSQL=@strSQL+@StrSelect+' FROM[#product]'+@strWhere
--             set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
--             set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
--         end
--     else if(@ProductType=3)
--         begin
--             set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
--             set @strSQL=@strSQL+@StrSelect+' FROM[#product]'+@StrWhere
--             set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
--             set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
--         end
--     else if(@ProductType=4)
--         begin
--             set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
--             set @strSQL=@strSQL+@StrSelect+' FROM[#product]'+@StrWhere
--             set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
--             set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
--         end            
  
     exec  ( @strSQL )
    
     drop   table  #product
END