Repeater+AspNetPager+存储过程实现分页

最近在开发时遇到了一些关于分页显示的问题，在网上找了很多资源但是都不是自己想要的形式，所以最后自己整合了一个资源来实现分页
本Demo是用Repeater+AspNetPager+存储过程实现分页
首先是布局文件：

    <form id="form1" runat="server">
    <div>
        <asp:Repeater ID="Repeater1" runat="server">
            <HeaderTemplate>
                <table width="100%" border="0" cellspacing="0" cellpadding="0" class="msgtable">
                    <tr>
                        <th width="50">
                            学员编号
                        </th>
                        <th width="50" >
                            学员姓名
                        </th>
                        <th width="50">
                            年龄
                        </th>
                        <th width="50">
                            电话
                        </th>
                        <th width="50">
                            报名时间
                        </th>
                        <th width="50">
                            备注信息
                        </th>
                        <th width="25">
                            操作
                        </th>
                    </tr>
            </HeaderTemplate>
            <ItemTemplate>
                <tr>
                    <td align="center">
                        <%#Eval("xid")%>
                    </td>
                    <td align="center">
                        <%#Eval("xname")%>
                    </td>
                    <td align="center">
                        <%#Eval("xage")%>
                    </td>
                    <td align="center">
                        <%#Eval("xtel")%>
                    </td>
                    <td align="center">
                        <%#Eval("bmtime","{0:yyyy-MM-dd}")%>
                    </td>
                    <td align="center">
                        <%#Eval("introduce")%>
                    </td>
                    <td align="center">
                         <span><a href="#">查看</a></span> <span><a href="#">编辑</a></span> 
                          <span><asp:LinkButton ID="lbDel" CommandName="Del" runat="server" OnClientClick="return confirm( '确定要删除吗？ ')">删除</asp:LinkButton></span>
                    </td>
                </tr>
            </ItemTemplate>
            <FooterTemplate>
                </table>
            </FooterTemplate>
        </asp:Repeater>
        <div style="line-height: 30px; height: 30px;">
            <%--实现分页技术--%>
            <div class="right">
            </div>
            <webdiyer:AspNetPager ID="AspNetPager1" runat="server" CssClass="paginator"
                onpagechanging="AspNetPager1_PageChanging" Width="100%" UrlPaging="true" 
                FirstPageText="首页" LastPageText="尾页"
                NextPageText="下一页" PrevPageText="上一页"
                ShowInputBox="Always" ShowPageIndexBox="Never"
                CurrentPageButtonClass="active" PageSize="10" 
                onpagechanged="AspNetPager1_PageChanged">

     </webdiyer:AspNetPager>
        </div>
    </div>
    </form>

然后是存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--参数说明-------------------------------------------------------------
/**//*
@strTable --要显示的表或多个表的连接
@strField --要查询出的字段列表,*表示全部字段
@intTop --最多读取记录数
@pageSize --每页显示的记录个数
@pageIndex --要显示那一页的记录
@strWhere --查询条件,不需where
@strSortKey --用于排序的主键
@strSortField --用于排序，如：id desc (多个id desc,dt asc)
@strOrderBy --排序,0-顺序,1-倒序
@pageCount --查询结果分页后的总页数
@RecordCount --查询到的总记录数
@UsedTime --耗时测试时间差
*/
CREATE PROCEDURE ThePagerIndex
@strTable varchar(50) = '[dbo].[ttable]',
@strField varchar(50) = '*',
@intTop int = 5000,
@pageSize int = 20,
@pageIndex int = 1,
@strWhere varchar(50) = '1=1',
@strSortKey varchar(50) = 'id',
@strSortField varchar(50) = 'id DESC',
@strOrderBy bit = 1,
@pageCount int OUTPUT,
@RecordCount int OUTPUT
--@UsedTime int OUTPUT
AS
SET NOCOUNT ON
Declare @sqlcount INT
Declare @timediff DATETIME
select @timediff=getdate()
Begin Tran
DECLARE @sql nvarchar(max),@where1 varchar(max),@where2 varchar(max)
IF @strWhere is null or rtrim(@strWhere)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@strWhere+') AND ' --本来有条件再加上此条件
SET @where2=' WHERE ('+@strWhere+') ' --原本没有条件而加上此条件
END
--SET @sql='SELECT @intResult=COUNT(*) FROM '+@strTable+@where2
IF @intTop<=0
BEGIN
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
END
ELSE
BEGIN
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select top '+ cast(@intTop as varchar(max)) +' '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
END
--print @sql

EXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --计算总记录数
SELECT @pageCount=CEILING((@sqlcount+0.0)/@pageSize) --计算总页数
SELECT @RecordCount = @sqlcount --设置总记录数
IF @pageIndex=1 --第一页
BEGIN
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+' FROM '+@strTable+
@where2+'ORDER BY '+ @strSortField
END
Else
BEGIN
IF @strOrderBy=0
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+ ' FROM '+@strTable+@where1+@strSortKey+'>(SELECT MAX('+@strSortKey+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(max))+' '+
@strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField
ELSE
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+' FROM '+@strTable+@where1+@strSortKey+'<(SELECT MIN('+@strSortKey+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(max))+' '+
@strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField+''
END
EXEC(@sql)
--print @sql
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit TRAN
--set @UsedTime = datediff(ms,@timediff
*getdate())
--select datediff(ms,@timediff,getdate()) as 耗时
Return @sqlcount
End

然后是布局文件对应的.cs文件

 public partial class Test : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //if (!IsPostBack)
            //{
            //    bind(); 
            //}
            if (!IsPostBack)
            {
                Bind();
            }

        }
        private readonly Question_bll bll = new Question_bll();

        int currPage = 1;
        int PageSize = 5;

        public void Bind()
        {

            int pageCount;
            int RecordCount;

            Repeater1.DataSource = bll.GetPage(currPage, PageSize, out pageCount, out RecordCount);

            Repeater1.DataBind();

            this.AspNetPager1.RecordCount = RecordCount;

            this.AspNetPager1.CurrentPageIndex = currPage;

            this.AspNetPager1.PageSize = PageSize;

        }
        protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
        {
            //bind();
            //BindGridView();
        }

        protected void AspNetPager1_PageChanged(object sender, EventArgs e)
        {
            int pageCount;
            int RecordCount;

            Repeater1.DataSource = bll.GetPage(this.AspNetPager1.CurrentPageIndex, PageSize, out pageCount, out RecordCount);

      Repeater1.DataBind();

        }
    }

然后构建一个对应分页表的实体类

public class Question_model
    {
        public Question_model()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }
        int xid;

        public int Xid
        {
            get { return xid; }
            set { xid = value; }
        }
        string xname;

        public string Xname
        {
            get { return xname; }
            set { xname = value; }
        }

         int xage;
         public int Xage
         {
             get { return xage; }
             set { xage = value; }
         }

         int xtel;
         public int Xtel
         {
             get { return xtel; }
             set { xtel = value; }
         }

         int bmtime;
         public int Bmtime
         {
             get { return bmtime; }
             set { bmtime = value; }
         }

         int introduce;
         public int Introduce
         {
             get { return introduce; }
            `
set { introduce = value; }
         }
    }

最后写一个适配文件

public class Question_bll
    {
        public IList<Question_model> GetPage(int pageindex, int _pageSize, out int pageCount, out int RecordCount)
        {
            pageCount = 0;
            RecordCount = 0;
            IList<Question_model> list = new List<Question_model>();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                SqlCommand objcmd = new SqlCommand(".ThePagerIndex", conn);

                objcmd.CommandType = CommandType.StoredProcedure;

                SqlParameter[] para ={

       new SqlParameter("@strTable",SqlDbType.VarChar,-1),

       new SqlParameter("@strField",SqlDbType.VarChar,-1),

       new SqlParameter("@pageSize",SqlDbType.Int),

       new SqlParameter("@pageIndex",SqlDbType.Int),

       new SqlParameter("@strSortKey",SqlDbType.VarChar,-1),

       new SqlParameter("@strSortField",SqlDbType.VarChar,-1),

       new SqlParameter("@strOrderBy",SqlDbType.Bit),

       new SqlParameter("@pageCount",SqlDbType.Int),

       new SqlParameter("@RecordCount",SqlDbType.Int),

       new SqlParameter("@inttop",SqlDbType.Int,-1)

       };

                para[0].Value = "XueYuan";

                para[1].Value = "*";

                para[2].Value = _pageSize;

                para[3].Value = pageindex;

                para[4].Value = "xid";

                para[5].Value = "xid desc";

                para[6].Value = 1;

                para[7].Value = pageCount;

                para[7].Direction = ParameterDirection.Output;

                para[8].Value = RecordCount;

                para[8].Direction = ParameterDirection.Output;

                para[9].Value = -1;

                objcmd.Parameters.AddRange(para);

                conn.Open();

                using (SqlDataReader reader = objcmd.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    while (reader.Read())
                    {

                        Question_model model = new Question_model();

                        model.Xid = Convert.ToInt32(reader["xid"]);

                        model.Xname = Convert.ToString(reader["xname"]);

                        list.Add(model);
                    }

                }
                RecordCount = Convert.ToInt32(objcmd.Parameters["@RecordCount"].Value);
                pageCount = Convert.ToInt32(objcmd.Parameters["@pageCount"].Value);
                conn.Close();

                conn.Dispose();


            }
            return list;
        }
    }

最后附上XueYuan表和效果图