一个通用的分页类_asp.net技巧

结合一个存储过程，将分页做成最简单，请看以下源码

此分页类所操作的存储过程#region 此分页类所操作的存储过程
/**//*********************************************************
 *
 * 功能强大，配合以下这个存储过程
 *
 * *******************************************************/
/**//*
— Pager 1,10,0,0, EmployeeID>2 and EmployeeID<5 , Employees,*,LastName,0
CREATE PROCEDURE Pager
    @PageIndex             int,–索引页 1
    @PageSize              int,–每页数量2
    @RecordCount        int out,–总行数3
    @PageCount             int out,–总页数4
    @WhereCondition         Nvarchar(1000),–查询条件5
    @TableName          nvarchar(500),–查询表名6
    @SelectStr          nvarchar(500) = *,–查询的列7
    @Order              nvarchar(500),–排序的列8
    @OrderType            bit = 0,        — 设置排序类型, 非 0 值则降序 9
    @Groupby            NVarChar(100) =
AS

declare  @strSQL   nvarchar(2000)     — 主语句
declare @strTmp   nvarchar(1000)     — 临时变量
declare @strOrder nvarchar(1000)       — 排序类型

if @OrderType != 0
begin
    set @strTmp = <(select min
    set @strOrder = order by + @Order + desc
end
else
begin
    set @strTmp = >(select max
    set @strOrder = order by + @Order + asc
end

set @strSQL = select top + str(@PageSize) + + @SelectStr + from
    + @TableName + where + @Order + + @strTmp + ([
    + @Order + ]) from (select top + str((@PageIndex-1)*@PageSize) + [
    + @Order + ] from + @TableName + + @strOrder + ) as tblTmp)
    + @Groupby + @strOrder

if @WhereCondition !=
    set @strSQL = select top + str(@PageSize) + + @SelectStr + from
        + @TableName + where + @Order + + @strTmp + ([
        + @Order + ]) from (select top + str((@PageIndex-1)*@PageSize) + [
        + @Order + ] from + @TableName + where ( + @WhereCondition + )
        + @strOrder + ) as tblTmp) and ( + @WhereCondition + ) + @Groupby + @strOrder

if @PageIndex = 1
begin
    set @strTmp =
    if @WhereCondition !=
        set @strTmp = where ( + @WhereCondition + )

    set @strSQL = select top + str(@PageSize) + + @SelectStr + from
        + @TableName + + @strTmp + + @Groupby + @strOrder
end
exec (@strSQL)
–print @strSQL

    IF @WhereCondition <>
        Begin
            SET @strTmp = SELECT -1 FROM + @TableName + Where + (@WhereCondition)
        End
    ELSE
        Begin
            SET @strTmp = SELECT -1 FROM + @TableName
        End   
    EXEC SP_EXECUTESQL @strTmp
    SET @RecordCount    = @@RowCount
    —    获取总页数
    —    “CEILING”函数:取得不小于某数的最小整数
    SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
GO
*****************************************************************************/
/**//****************************************************************************
 *
 *    用法
 *
 * ***************************************************************************/
 /**//*
        Dim ts As String = Request.Form.Item(“txtDate”)

        If (ts = “” Or ts Is Nothing) Then
            ts = Request.QueryString(“txtDate”)
        End If

        Dim ts2 As String = Request.Form.Item(“txtDate2”)

        If (ts2 = “” Or ts2 Is Nothing) Then
            ts2 = Request.QueryString(“txtDate2”)
        End If

        Dim ps As String = Request.Form.Item(“pageIndex”)

        If (ps = “” Or ps Is Nothing) Then
            ps = Request.QueryString(“pageIndex”)
        End If

        Dim t As Integer = 2
        Dim p As Integer = 1
        If ts Is Nothing Then
            ts = “”
        End If
        If ps Is Nothing Then
            ps = “”
        End If

        If Not (ps = “”) Then
            p = Integer.Parse(ps)
        End If

        Dim pager As Pager = New Pager
        pager.PageIndex = p
        pager.PageSize = 20
        pager.PageMode = PageMode.Str
        pager.WhereCondition = “TheDate between convert(datetime,” + ts + “) and convert(datetime,” + ts2 + “)”
        pager.WhereCondition = ” convert(char(10),TheDate,120)=  ” + ts + “”
        pager.TableName = “LoadCountlog”
        pager.SelectStr = “*”
        pager.Order = “ID”
        pager.OrderType = False
        Dim dt As System.Data.DataTable = pager.GetDatas(p)
        myDataGrid.DataSource = dt
        myDataGrid.DataBind()
        Dim goUrl As String = “WebForm1.aspx?txtDate=” + ts + “&txtDate2=” + ts2
        Me.Label3.Text = “共：” + pager.PageCount.ToString + “页，” + pager.RecordCount.ToString() + “条 <strong>” + pager.OutPager(pager, goUrl, False) + “</strong>”
*/
#endregion
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Text;
namespace solucky
{
    /**//// <summary>
    /// 分页模式
    /// </summary>
    public enum PageMode
    {
        /**//// <summary>
        /// 数字分页
        /// </summary>
        Num    =0,
        /**//// <summary>
        /// 字符分页
        /// </summary>
        Str    =1
    }
    /**//// <summary>
    /// 分页类,能过存储过程进行分页,功能相当强大。
    /// </summary>
   
    public class Pager
    {
        private int pageIndex            = 0;
        private int recordCount            = 0;
        private int pageSize            = 20;
        private int pageCount            = 0;
        private int rowCount            = 0;
        private string tableName        = “”;
        private string whereCondition    = “1=1”;
        private string selectStr        = “*”;
        private string order            = “”;
        private string procedure        =”pager”;       
        private bool orderType            = true;
        private PageMode pageMode        =PageMode.Num;   
        private string sqlConnectionString                    = ConfigurationSettings.AppSettings[“database”];
        private string databaseOwner                        = “dbo”;

        数据连接#region 数据连接
        /**//// <summary>
        /// 数据连接字符串
        /// </summary>
        private string SqlConnectionString
        {
            get
            {
                return this.sqlConnectionString;
            }
            set
            {
                this.sqlConnectionString=value;
            }
        }

        /**//// <summary>
        ///获取连接实例
        /// </summary>
        /// <returns></returns>
        private SqlConnection GetSqlConnectionString()
        {
            try
            {
                return new SqlConnection(SqlConnectionString);
            }
            catch
            {
                throw new Exception(“SQL Connection String is invalid.”);
            }
        }

        /**//// <summary>
        /// 数据对象所有者
        /// </summary>
        private string DatabaseOwner
        {
            get
            {
                return this.databaseOwner;
            }
            set{
                this.databaseOwner=value;
            }
        }

        #endregion

        public Pager()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            //Enum.Parse(tyo
        }
        public Pager(string connstr )
        {
            if (connstr!=null)
                this.SqlConnectionString=connstr;
        }
        #region
        /**//// <summary>
        /// 所要操作的存储过程名称,已有默认的分页存储过程
        /// </summary>
        public string Procedure
        {
            get{
                return this.procedure ;
            }
            set {
                if (value==null || value.Length <=0)
                {
                    this.procedure=”pager”;
                }
                else
                {
                    this.procedure=value;
                }
            }
        }

        /**//// <summary>
        /// 当前所要显示的页面数
        /// </summary>
        public int PageIndex

        {
            get
            {
                return this.pageIndex;
            }
            set
            {
                this.pageIndex                    = value;
            }
        }

        /**//// <summary>
        /// 总的页面数
        /// </summary>
        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 PageMode PageMode
        {
            get
            {
                return this.pageMode;
            }
            set
            {
                this.pageMode                    = value;
            }
        }

        /**//// <summary>
        /// 得到当前返回的数量
        /// </summary>
        public int RowCount
        {
            get
            {
                return this.rowCount;
            }
        }

        private string groupby;
        public string Groupby
        {
            get
            {
                return this.groupby;
            }
            set
            {
                this.groupby                = value;
            }
        }

        #endregion
        /**//// <summary>
        /// 分页查寻结果
        /// </summary>
        public DataTable GetDatas(int pageIndex)
        {
            this.pageIndex  = pageIndex;
            Pager pager        = this;
            //pager.pageIndex    = pageIndex;
            DataTable returnTb  = Pagination(ref pager).Tables[0];
            this.rowCount    = returnTb.Rows.Count;
            return returnTb;
        }

        /**//// <summary>
        /// 分页操作存储过程函数
        /// </summary>
        /// <param name=”pager”></param>
        /// <returns></returns>
        private  DataSet Pagination(ref Pager pager)
        {
            using ( SqlConnection myConnection                = GetSqlConnectionString() )
            {
                SqlDataAdapter myCommand                    = new SqlDataAdapter(pager.databaseOwner + “.”+pager.Procedure, myConnection);
                myCommand.SelectCommand.CommandType            = CommandType.StoredProcedure;

                SqlParameter parameterPageIndex                = new SqlParameter(“@PageIndex”, SqlDbType.Int);
                parameterPageIndex.Value                    = pager.PageIndex;
                myCommand.SelectCommand.Parameters.Add(parameterPageIndex);

                SqlParameter parameterPageSize                = new SqlParameter(“@PageSize”, SqlDbType.Int);
                parameterPageSize.Value                        = pager.PageSize;
                myCommand.SelectCommand.Parameters.Add(parameterPageSize);

                SqlParameter parameterRecordCount            = new SqlParameter(“@RecordCount”, SqlDbType.Int);
                parameterRecordCount.Value                    = 0;
                parameterRecordCount.Direction                = ParameterDirection.InputOutput;
                myCommand.SelectCommand.Parameters.Add(parameterRecordCount);

                SqlParameter parameterPageCount                = new SqlParameter(“@PageCount”, SqlDbType.Int);
                parameterPageCount.Value                    = 0;
                parameterPageCount.Direction                = ParameterDirection.InputOutput;
                myCommand.SelectCommand.Parameters.Add(parameterPageCount);

                SqlParameter parameterWhereCondition        = new SqlParameter(“@WhereCondition”, SqlDbType.NVarChar,500);
                parameterWhereCondition.Value                = pager.WhereCondition;
                myCommand.SelectCommand.Parameters.Add(parameterWhereCondition);

                SqlParameter parameterTableName                = new SqlParameter(“@TableName”, SqlDbType.NVarChar,500);
                parameterTableName.Value                    = pager.TableName;
                myCommand.SelectCommand.Parameters.Add(parameterTableName);

                SqlParameter parameterOrder                    = new SqlParameter(“@Order”, SqlDbType.NVarChar,500);
                parameterOrder.Value                        = pager.Order;
                myCommand.SelectCommand.Parameters.Add(parameterOrder);

                SqlParameter parameterSelectStr                = new SqlParameter(“@SelectStr”, SqlDbType.NVarChar,500);
                parameterSelectStr.Value                    = pager.SelectStr;
                myCommand.SelectCommand.Parameters.Add(parameterSelectStr);

                SqlParameter parameterGroupby                = new SqlParameter(“@Groupby”, SqlDbType.NVarChar, 100);
                parameterGroupby.Value                        = pager.Groupby;
                myCommand.SelectCommand.Parameters.Add(parameterGroupby);

                SqlParameter parameterOrderType                = new SqlParameter(“@OrderType”, SqlDbType.Bit);
                parameterOrderType.Value                    = pager.OrderType==false?0:1;
                myCommand.SelectCommand.Parameters.Add(parameterOrderType);   
   

                DataSet returnDS                            = new DataSet();

                //SqlDataAdapter sqlDA                        = myCommand.crnew SqlDataAdapter(myCommand);
                myCommand.Fill(returnDS);

                pager.PageCount                                = (int)parameterPageCount.Value;
                pager.RecordCount                            = (int)parameterRecordCount.Value;

                return returnDS;
            }

        }
   
        生成分页#region 生成分页
        /**//// <summary>
        /// 生成分页格式
        /// </summary>
        /// <param name=”pager”></param>
        /// <param name=”url”></param>
        /// <param name=”isBr”></param>
        /// <returns></returns>
        public string OutPager(Pager pager,string url,bool isBr)
        {
            StringBuilder returnOurWml;
            if(isBr)
            {
                returnOurWml= new StringBuilder(“[“+ pager.PageCount.ToString() + “页,” + pager.RecordCount.ToString() +”条]<br/>”);
            }
            else
            {
                returnOurWml = new StringBuilder();
            }
            if (pager.PageMode == PageMode.Num)
            {
                //分页每行显示的数量
                int pagersCount = 10;
                int pagers        = 0;
                int startInt    = 1;
                int endInt        = pager.PageCount;
                int i            = 1;

                string endStr   = “”;

                if (pager.PageCount>pagersCount)
                {

                    //double        k = ;
                    pagers          = pager.PageIndex / pagersCount;
           
                    if (pagers == 0)
                    {
                        pagers = 1;
                    }
                    else if((pager.PageIndex % pagersCount)!=0)
                    {
                        pagers +=1;
                    }

                    endInt          = pagers * pagersCount;
                    if (pager.PageIndex <= endInt)
                    {
                        startInt = endInt +1 – pagersCount;
                        if (startInt <1)
                        {
                            startInt = 1;
                        }
                    }

                   
                    //显示数量不足时pagersCount
                    if (endInt>=pager.PageCount)
                    {
                        endInt = pager.PageCount;
                    }
                    else
                    {
                        //if (pager.PageIndex)
                        endStr        = ” <a href=\””;
                        endStr        += url + “&amp;pageIndex=” + (endInt + 1).ToString()  + “\” title=第”+ (endInt + 1).ToString()+”页>”;
                        endStr        += “&gt;&gt;”;
                        endStr        += “</a>  “;
                    }

                    if (pagers > 1)
                    {
                        returnOurWml.Append(” <a href=\””);
                        returnOurWml.Append(url + “&amp;pageIndex=” + (startInt – 1).ToString() + “\” title=第”+ (startInt – 1).ToString()+”页>”);
                        returnOurWml.Append(“&lt;&lt;”);
                        returnOurWml.Append(“</a>  “);
                    }
                }
               
                for (i = startInt; i<=endInt;i++)
                {
                   
                    if (i!=pager.PageIndex)
                    {
                        returnOurWml.Append(” <a href=\””);
                        returnOurWml.Append(url + “&amp;pageIndex=” + i.ToString() + “\” title=第”+ i.ToString()+”页>”);
                        returnOurWml.Append(“[“+i.ToString() + “]”);
                        returnOurWml.Append(“</a>  “);
                    }
                    else
                    {
                        returnOurWml.Append(“<u>”+ i.ToString() + “</u>”);
                    }
                }

                returnOurWml.Append(endStr);

                return returnOurWml.Append(“<br/>”).ToString();
            }
            else
            {
                if ( pager.PageIndex > 1)
                {
                    returnOurWml.Append(” <a href=\””);
                    returnOurWml.Append(url + “&amp;pageIndex=” + (pager.PageIndex -1).ToString() + “\”>”);
                    returnOurWml.Append(“上一页”);
                    returnOurWml.Append(“</a>  “);
                }
                if (pager.PageIndex < pager.PageCount)
                {
                    returnOurWml.Append(pager.PageIndex.ToString());
                    returnOurWml.Append(” <a href=\””);
                    returnOurWml.Append(url + “&amp;pageIndex=” + (pager.PageIndex +1).ToString() + “\”>”);
                    returnOurWml.Append(“下一页”);
                    returnOurWml.Append(“</a>  “);
                }
                return returnOurWml.Append(“<br/>”).ToString();
            }
        }

        #endregion
    }
}

http://www.cnblogs.com/solucky/archive/2006/09/20/509741.html