一個通用的分頁類

2019-11-18 16:58:54

字體：大中小

來源：轉載

供稿：網友

結合一個存儲過程，將分頁做成最簡單，請看以下源碼

此分頁類所操作的存儲過程#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();
 }
 }