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

时间:2008-10-07 12:08:13  来源:第二电脑网上收集  作者:

  第二电脑网导读:个存储过程  *  * *******************************************************//**//*-- Pager 1,10,0,0, 'EmployeeID>2 and EmployeeID<5 ' , 'Employees','*','LastName',0CREATE PROCEDURE Pager     @PageIndex             int,--索引页 1&n...
  正文:

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

此分页类所操作的存储过程#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 photoshop/ target=_blank class=infotextkey>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.ASP/ target=_blank class=infotextkey>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;
            }
        }


"ASP.NET技巧:一个通用的分页类"由第二电脑网原创提供,转载请注明:http://www.002pc.com/master/College/Programming/ASP/2008-10-07/3080.html


关键字:

关于《ASP.NET技巧:一个通用的分页类》文章的评论

站内搜索: 高级搜索

热门搜索: Windows style 系统 tr IP QQ CPU 安装 function 注册 if td