ASP.NET技巧：一个通用的分页类

2022-07-15 80酷酷网 80kuku.com

　　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() + "条 " + pager.OutPager(pager, goUrl, False) + ""
*/
#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() +"条] ");
 }
 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 + "&pageIndex=" + (endInt + 1).ToString() + "\" title='第"+ (endInt + 1).ToString()+"页'>";
 endStr += ">>";
 endStr += "</a> ";
 }

if (pagers > 1)
 {
 returnOurWml.Append(" <a href=\"");
 returnOurWml.Append(url + "&pageIndex=" + (startInt - 1).ToString() + "\" title='第"+ (startInt - 1).ToString()+"页'>");
 returnOurWml.Append("<<");
 returnOurWml.Append("</a> ");
 }
 }

 for (i = startInt; i<=endInt;i++)
 {

 if (i!=pager.PageIndex)
 {
 returnOurWml.Append(" <a href=\"");
 returnOurWml.Append(url + "&pageIndex=" + i.ToString() + "\" title='第"+ i.ToString()+"页'>");
 returnOurWml.Append("["+i.ToString() + "]");
 returnOurWml.Append("</a> ");
 }
 else
 {
 returnOurWml.Append(""+ i.ToString() + "");
 }
 }

returnOurWml.Append(endStr);

return returnOurWml.Append(" ").ToString();
 }
 else
 {
 if ( pager.PageIndex > 1)
 {
 returnOurWml.Append(" <a href=\"");
 returnOurWml.Append(url + "&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 + "&pageIndex=" + (pager.PageIndex +1).ToString() + "\">");
 returnOurWml.Append("下一页");
 returnOurWml.Append("</a> ");
 }
 return returnOurWml.Append(" ").ToString();
 }
 }

#endregion
}
}