c#存储过程实现的分页效果

  根据自己在项目过程中的总结，在此记录下这个比较高效的分页，是建立在三层架构的基础上的，现在贴出每层所写的代码。以下代码已经通过测试，没有问题。

 实现的效果如下：

 

page.aspx:

table width="100%" border="0" cellspacing="0" cellpadding="5" align="left">
                        <tr>
                            <td align="left">
                                共<span class="t13px_FF0000_B"><%=RowsCount%></span>首歌</td>
                            <td align="right"><%=PageIndex%> 页 / <%=MaxPage%> 页 | <%=RowsCount%> 条 
                                <a href="javascript:;" onclick="<%=PageIndex<=1?"return false;":""%>goPager(1)" <%=PageIndex<=1?"disabled=disabled":""%>>首页</a> | 
                                <a href="javascript:;" onclick="<%=PageIndex<=1?"return false;":""%>goPager(<%=PageIndex-1%>)" <%=PageIndex<=1?"disabled=disabled":""%>>上页</a> | 
                                <a href="javascript:;" onclick="<%=PageIndex>=MaxPage?"return false;":""%>goPager(<%=PageIndex+1%>)" <%=PageIndex>=MaxPage?"disabled=disabled":""%>>下页</a> | 
                                <a href="javascript:;" onclick="<%=PageIndex>=MaxPage?"return false;":""%>goPager(<%=MaxPage%>)" <%=PageIndex>=MaxPage?"disabled=disabled":""%>>尾页</a>
                                <select onchange="<%=MaxPage<=1?"return false;":""%>goPager(this.selectedIndex+1)" <%=MaxPage<=1?"disabled=disabled":""%>><%=SelectButton%></select>
                                <a href="javascript:;" ></a>
                            </td>
                        </tr>
                    </table>

page.aspx.cs :

    private void Bind()
    {
        SR.Lib.Space.Model.Pager p = new SR.Lib.Space.Model.Pager();
        p.PageIndex = PageIndex;//当前页
        p.PageSize = 15;//每页中显示的数据数
        p.TableName = "Space_Music";//用来分页的表名
        p.Order = "AddTime";//排序的字段
        p.OrderType = false;//排序规则
        p.SelectStr = " [ID],[UserID],Song,Songer,SongType,[Format],SongPath,Click,AddTime 

";//所要检索的数据字段
        if (UserID != 0)
        {
            p.WhereCondition = " UserID=" + UserID;//检索条件
        }
        SR.Lib.Space.BLL.SpaceMusic music = new SR.Lib.Space.BLL.SpaceMusic();
        RepSongList.DataSource = music.GetList(ref p);
        RepSongList.DataBind();
        this.maxPage = p.PageCount;//页总数
        this.rowsCount = p.RecordCount;//总数据数
    }
  private int rowsCount;
    protected int RowsCount
    {
        get { return rowsCount; }
        set { rowsCount = value; }
    }

    private int maxPage;
    protected int MaxPage
    {
        get { return maxPage; }
        set { maxPage = value; }
    }

    protected int PageIndex
    {
        get { return My.QueryInt("page", 1); }
    }
      protected string SelectButton
    {
        get
        {
            string reStr = string.Empty;
            for (int i = 1; i <= this.MaxPage; i++)
            {
                reStr += string.Format("<option value=\"{0}\" {1}>{0}</option>\n", i, i == 

this.PageIndex ? "Selected" : "");
            }
            return reStr;
        }
    }

存储过程：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


--GetPagingData 'Source','*','DigNum','ID',1,50,'UserName=''akisscn''and Type=0 and ClickNum>=3',0,0,0


ALTER PROCEDURE [dbo].[GetPagingData] 
    ( 
        @tablename varchar(100),--表名或视图表 
        @fieldlist varchar(4000)='*',--欲选择字段列表 
        @orderfield varchar(100),--排序字段 
        @keyfield varchar(100),--主键 
        @pageindex int,--页号,从0开始 
        @pagesize int=20,--页尺寸 
        @strwhere varchar(4000),--条件 
        @ordertype bit=1,--排序,1,降序,0,升序
    @RecordCount int out,
    @PageCount int out
    ) 
AS
/**//**//**//* 
名称:GetPagingRecord 
作用:按任意字段进行排序分页 
作者:菩提树(MARK MA) 
时间:2004-12-14 
声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明 
*/ 
    SET NOCOUNT ON 
    declare @sqlstr varchar(6000) 
    --处理SQL中危险字符,并且将条件处理成易嵌入的形式     
    set @sqlstr='declare @Rcount int;' 
    if @strWhere =''
    set @strWhere ='1=1'
    set @sqlstr=@sqlstr+'set @rcount=(select count('+@keyfield+') from '+@tablename+' where '+@strWhere+');'     
    set @strwhere=replace(@strwhere,'''','''''') 
    set @strwhere=replace(@strwhere,'--','') 
    set @strwhere=replace(@strwhere,';','') 
    set @sqlstr=@sqlstr+'declare @Rnum int;' 
    set @sqlstr=@sqlstr+'set @rnum=@rcount-'+cast(@pagesize as varchar)+'*'+cast(@pageindex as varchar)+';' 
    set @sqlstr=@sqlstr+'if(@rnum<0)set @rnum =@rcount;declare @sqlstr varchar(6000);' 
    if @ordertype=1 
    begin 
    set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@Pagesize as varchar)+' '+@fieldlist+' from (select top 100  
 
percent * from  (select top  ''+cast(@rnum as varchar)+'' * from '+@tablename+' where '+@strwhere+'  
 
order by '+@orderfield+' asc) as b order by '+@orderfield+' desc) as a order by '+@orderfield+' desc '';' 
    end 
    else 
    begin 
    set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@Pagesize as varchar)+' '+@fieldlist+' from (select top 100  
 
percent * from  (select top  ''+cast(@rnum as varchar)+'' * from '+@tablename+' where '+@strwhere+'  
 
order by '+@orderfield+' desc) as b order by '+@orderfield+' asc) as a order by '+@orderfield+' asc '';' 
    end 
    set @sqlstr=@sqlstr+'if @Rcount>0 begin execute(@sqlstr) end'     
   --print @sqlstr 
   execute(@sqlstr) 
    
    declare @strSelCount nvarchar(4000)
    IF @strwhere <>'1=1'
        Begin
            set @strwhere = replace(@strwhere,'''''','''') 
            SET @strSelCount = 'SELECT -1 FROM ' + @tablename + ' Where '+@strwhere
        End
    ELSE
        Begin
            SET @strSelCount = 'SELECT -1 FROM ' + @tablename
        End    
    --print @strSelCount
    EXEC SP_EXECUTESQL @strSelCount
    SET @RecordCount    = @@RowCount
    print @RecordCount
    --    获取总页数
    --    "CEILING"函数:取得不小于某数的最小整数
    SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
    print @PageCount

 

数据连接层 DAL：

    public static DataTable SelectPage(ref SR.Lib.Space.Model.Pager pager)
        {
            using (SqlConnection myConnection = new SqlConnection(connectionString))
            {
                // 调用分页
                SqlDataAdapter myCommand = new SqlDataAdapter("GetPagingData", myConnection);

                myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
                myCommand.SelectCommand.Parameters.Add(new SqlParameter("@tablename", SqlDbType.NVarChar));
                myCommand.SelectCommand.Parameters["@tablename"].Value = pager.TableName;
                myCommand.SelectCommand.Parameters.Add(new SqlParameter("@fieldlist", SqlDbType.NVarChar));
                myCommand.SelectCommand.Parameters["@fieldlist"].Value = pager.SelectStr;

                SqlParameter parameterOrder = new SqlParameter("@orderfield", SqlDbType.NVarChar);
                parameterOrder.Value = pager.Order;
                myCommand.SelectCommand.Parameters.Add(parameterOrder);

                SqlParameter parameterOrderType = new SqlParameter("@ordertype", SqlDbType.Bit);
                parameterOrderType.Value = pager.OrderType == true ? 0 : 1;
                myCommand.SelectCommand.Parameters.Add(parameterOrderType);

                myCommand.SelectCommand.Parameters.Add(new SqlParameter("@keyfield", SqlDbType.NVarChar));
                myCommand.SelectCommand.Parameters["@keyfield"].Value = pager.KeyField;

                myCommand.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int));
                myCommand.SelectCommand.Parameters["@pageindex"].Value = pager.PageIndex - 1;

                myCommand.SelectCommand.Parameters.Add(new SqlParameter("@pagesize", SqlDbType.Int));
                myCommand.SelectCommand.Parameters["@pagesize"].Value = pager.PageSize;

                myCommand.SelectCommand.Parameters.Add(new SqlParameter("@strwhere", SqlDbType.NVarChar));
                myCommand.SelectCommand.Parameters["@strwhere"].Value = pager.WhereCondition;

                myCommand.SelectCommand.Parameters.Add(new SqlParameter("@RecordCount", SqlDbType.Int));
                myCommand.SelectCommand.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

                myCommand.SelectCommand.Parameters.Add(new SqlParameter("@PageCount", SqlDbType.Int));
                myCommand.SelectCommand.Parameters["@PageCount"].Direction = ParameterDirection.Output;

                DataSet ds = new DataSet();
                myCommand.Fill(ds, "pager");
                pager.RecordCount = (int)myCommand.SelectCommand.Parameters["@RecordCount"].Value;
                pager.PageCount = (int)myCommand.SelectCommand.Parameters["@PageCount"].Value;
                myConnection.Close();
                return ds.Tables["pager"];
            }
        }

 

数据持久层model：

using System;
using System.Text;
using System.Data;
namespace SR.Lib.Space.Model
{

        public enum PageMode
        {
                Num =0,
                Str    =1
        }

        /**//// <summary>
        /// 分页类,能过存储过程进行分页,功能相当强大。
        /// </summary>
        public class Pager
        {
            private int pageIndex            = 0;
            private int recordCount            = 0;
            private int pageSize            = 0;
            private int pageCount            = 0;
            private string tableName        = "";
            private string whereCondition    = "";
            private string selectStr        = "";
            private string order            = "";

            private bool orderType            = true;
            private PageMode pageMode        = PageMode.Num;

            public Pager()
            {
            }
            private string keyfield = "id";
            /**//// <summary>
            ///搜索表或视图中的主键 如：ID
            /// </summary>
            public string KeyField
            {
                get { return this.keyfield; }
                set { this.keyfield = value; }
            }
            public int PageIndex
            {
                get
                {
                    return this.pageIndex;
                }
                set
                {
                    this.pageIndex                    = value;
                }
            }

            public PageMode PageMode
            {
                get
                {
                    return this.pageMode;
                }
                set
                {
                    this.pageMode                = value;
                }
            }

            public int PageCount
            {
                get
                {
                    return this.pageCount;
                }
                set
                {
                    this.pageCount                    = value;
                }
            }

            /**//// <summary>
            /// 总行数
            /// </summary>
            public int RecordCount
            {
                get
                {
                    return this.recordCount;
                }
                set
                {
                    this.recordCount                = value;
                }
            }

            /**//// <summary>
            /// 每页条数
            /// </summary>
            public int PageSize
            {
                get
                {
                    return this.pageSize;
                }
                set
                {
                    this.pageSize                    = value;
                }
            }

            /**//// <summary>
            /// 表名称
            /// </summary>
            public string TableName
            {
                get
                {
                    return tableName;
                }
                set
                {
                    this.tableName                    = value;
                }
            }

            /**//// <summary>
            /// 条件查询
            /// </summary>
            public string WhereCondition
            {
                get
                {
                    return whereCondition;
                }
                set
                {
                    whereCondition                    = value;
                }
            }

            /**//// <summary>
            /// 查询目标(搜索目标),比如:AddTime AS 时间,ID AS 编号
            /// </summary>
            public string SelectStr
            {
                get
                {
                    return selectStr;
                }
                set
                {
                    selectStr                        = value;
                }
            }

            /**//// <summary>
            /// 排序的列
            /// </summary>
            public string Order
            {
                get
                {
                    return order;
                }
                set
                {
                    order                            = value;
                }
            }

            /**//// <summary>
            /// 排序类型 true:asc false:desc
            /// </summary>
            public bool OrderType
            {
                get
                {
                    return orderType;
                }
                set
                {
                    orderType                            = value;
                }
            }
    
//            /// <summary>
//            /// 得到当前返回的数量
//            /// </summary>
//            public int RowCount
//            {
//                get
//                {
//                    return this.rowCount;
//                }
//            }

            private string groupby;
            public string Groupby
            {
                get
                {
                    return this.groupby;
                }
                set
                {
                    this.groupby    = value;
                }
            }
            public string OutPager(string url)
            {
                return Pager.OutPager(this,url);
            }

        }
    }