分页实例

来源：互联网发布：spps软件编辑：程序博客网时间：2024/05/22 17:34

---存储过程

create PROCEDURE [dbo].[SP_PageList] 'SMS_log', 'id', 'id', 25, 1, 0, 1,''
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名(可包含如TABLE.FLDNAME形式）
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @fldName_t varchar(255) -- 在分页时用的排序字段名，不包含多表并列时的表名
set @fldName_t = right(@fldName,len(@fldName)-CHARINDEX('.',@fldName))
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = 'select count(*) as Total from ' + @tblName + ' where '+@strWhere
else
set @strSQL = 'select count(*) as Total from ' + @tblName + ''
end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName +' desc'
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName +' asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' where ' + @fldName + ' ' + @strTmp + ' ('+ @fldName_t + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' where ' + @fldName + ' ' + @strTmp + ' ('+ @fldName_t + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec (@strSQL)

//方法

/// <summary>
    /// 分页
    /// </summary>
    /// <param name="tblName">表名</param>
    /// <param name="strGetFields">查询字段</param>
    /// <param name="fldName">主键</param>
    /// <param name="PageSize">每页数</param>
    /// <param name="PageIndex">页码</param>
    /// <param name="doCount">返回总个数</param>
    /// <param name="OrderType">排序方式非0是顺序</param>
    /// <param name="strWhere">查询条件</param>
    /// <returns></returns>

public IDataReader GetPageList(string tblName, string strGetFields, string fldName, int PageSize,
        int PageIndex, int doCount, int OrderType, string strWhere)
    {
        Database db = new Database();
        SqlParameter[] parameters = {
     new SqlParameter("@tblName", SqlDbType.NVarChar,255),
                    new SqlParameter("@strGetFields", SqlDbType.NVarChar,1000),
     new SqlParameter("@fldName", SqlDbType.NVarChar,255),
     new SqlParameter("@PageSize", SqlDbType.Int,4),
     new SqlParameter("@PageIndex", SqlDbType.Int,4),
     new SqlParameter("@doCount", SqlDbType.Bit,1),
     new SqlParameter("@OrderType", SqlDbType.Bit,1),
     new SqlParameter("@strWhere", SqlDbType.NVarChar,1500)
            };
        parameters[0].Value = tblName;
        parameters[1].Value = strGetFields;
        parameters[2].Value = fldName;
        parameters[3].Value = PageSize;
        parameters[4].Value = PageIndex;
        parameters[5].Value = doCount;
        parameters[6].Value = OrderType;
        parameters[7].Value = strWhere;

        SqlDataReader reader;
        db.RunProc("SP_PageList", parameters, out reader);
        return reader;
    }

///调用分页方法

using (SqlDataReader reader = (SqlDataReader)类名.GetPageList("表名", "*", "id", PageSize, PageNo, 0, 1,strWhere))
        {
            if (reader.HasRows)
            {
                DataTable tb = new DataTable();
                tb.Load(reader);
                if (tb.Rows.Count != 0)
                {
                   // this.btnDelete.Visible = true;
                    //this.btnDelete.Attributes["onclick"] = "javascript:return confirm('您确认要删除吗?');";

dgMailList.DataSource = tb.DefaultView;
dgMailList.DataBind();

// MenuName = tb.Rows[0]["M_Name"].ToString();

                    using (IDataReader Reader = docdal.GetPageList("SMS_log", "id", "id", PageSize, PageNo, 1, 1, strWhere))
                    {
                        Reader.Read();
                        PageCtl1.RecordCount = int.Parse(Reader[0].ToString());
                        PageCtl1.PageSize = PageSize;
                        PageCtl1.PageNo = PageNo;
                        PageCtl1.Visible = true;
                        Reader.Close();
                    }
                }
            }

/// 页面显示

<%@ Register Src=”UserControl/PageCtl.ascx" TagName="PageCtl" TagPrefix="uc1" %>

////page控件代码

.asp代码

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="PageCtl.ascx.cs" Inherits="Controls_PageCtl" %>
<style type="text/css">
ul#hovershow{
list-style-type: none;
margin: 50px;
width:200px;
float: left;
display: inline;
clear: both;
}
ul#hovershow li{
float: left;
display: inline;
width:20px;
height: 20px;
margin: 2px;

}
ul#hovershow li a {

display: block;
width:20px;
height:20px;
border:1px #999 solid;
color:#0984FF;
background-color:#fff;
text-decoration: none;
line-height: 20px;
font-size: 12px;
text-align: center;
font-weight: bold;
}

ul#hovershow li a:hover{
position: absolute;
width:40px;
height: 40px;
line-height: 40px;
font-size: 32px;
z-index:100;
margin: -10px 0 0 -10px;
}
ul#hovershow li:hover + li a{
position: absolute;
width:30px;
height: 30px;
line-height: 30px;
font-size: 24px;
z-index:99;
margin: -5px 0 0 -5px;
}
</style>

</ul>--%>
<script language="javascript">

function offEnter()
{
var k=window.event.keyCode;
//屏蔽回车键
if(k==10||k==13)
{
window.event.keyCode=0;
window.event.returnValue=false;
return false;
}
return true;
}
</script>

//.cs代码

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Controls_PageCtl : System.Web.UI.UserControl
{
    public int RecordCount = 0, PageSize, PageNo;
    private int PageCount = 0;
    private string PageList, FontColor = "#000000", url = "";
    //protected System.Web.UI.WebControls.Label lblRecordCount;
    //protected System.Web.UI.WebControls.Label lblPageCount;
    //protected System.Web.UI.WebControls.Label lblPageSize;
    //protected System.Web.UI.WebControls.Label lblPageList;

    protected void Page_Load(object sender, EventArgs e)
    {
        // 在此处放置用户代码以初始化页面
        if (!IsPostBack)
        {
            LoadData();
        }
    }
    void LoadData()
    {
        if (RecordCount == 0)
        {
            return;
        }

        if (RecordCount % PageSize == 0)
        {
            PageCount = RecordCount / PageSize;
        }
        else
        {
            PageCount = RecordCount / PageSize + 1;
        }

        //当前页
        if (PageNo < 1)
        {
            PageNo = 1;
        }
        if (PageNo > PageCount)
        {
            PageNo = PageCount;
        }
        //链接地址
        if (url == "")
        {
            string[] tmp;
            tmp = Request.ServerVariables["QUERY_STRING"].Split("&".ToCharArray());
            url = "?";
            for (int i = 0; i < tmp.Length; i++)
            {
                if (tmp[i].IndexOf("PageNo") == -1)
                {
                    if (url == "?")
                    {
                        url += tmp[i].ToString();
                    }
                    else
                    {
                        url += "&" + tmp[i].ToString();
                    }
                }
            }
            if (url != "?")
            {
                url += "&";
            }
        }
        else
        {

            if (url.IndexOf("?") == -1)
            {
                url += "?";
            }
            else
            {
                if (url.Substring(url.Length - 1, 1) != "?" && url.Substring(url.Length - 1, 1) != "&")
                {
                    url += "&";
                }

            }
        }
        //上页和首页
        if (PageNo > 1)
        {
            PageList = "<a href='" + url + "PageNo=1'><font color='" + FontColor + "'>首页</font></a> ";
            PageList+="<a href='"+url+"PageNo="+(PageNo-1)+"'><font color='"+FontColor+"'>上页</font></a>  ";
        }
        else
        {
            PageList = "<font color='" + FontColor + "'>首页</font> ";
            PageList+="<font color='"+FontColor+"'>上页</font>  ";
        }
        // 开始页+3=当前页=结束页-3
        int ksnum = 1, jsnum = 0;
        if (PageCount <= 12)
        {
            for (int i = 1; i <= PageCount; i++)
            {

                if (PageNo == i)
                {
                    PageList+="  <font color='#ff0000'>[</font> <font size=1 color='#ff0000'><b>"+i+"</b></font> <font color=red>]</font>  ";
                }
                else
                {
                    PageList += "<a href='" + url + "PageNo=" + i + "'><font color='" + FontColor + "'>" + i + "</font></a> ";
                }

}

        else
        {
            //开始页
            if (PageNo - 6 < 0)
            {
                ksnum = 1;
            }
            else
            {
                ksnum = PageNo - 3;
            }

            //结束页
            if (ksnum + 6 > PageCount)
            {
                jsnum = PageCount;
                ksnum += PageCount - (ksnum + 6);
            }
            else
            {
                jsnum = ksnum + 6;
            }
        }

        //显示当前页的前后页
        for (int i = ksnum; i <= jsnum; i++)
        {

            if (PageNo == i)
            {
                PageList += "<font color='#ff0000'><font color=red>[</font>" + i + "<font color=red>]</font></font> ";
            }
            else
            {
                PageList += "<a href='" + url + "PageNo=" + i + "'><font color='" + FontColor + "'>" + i + "</font></a> ";
            }
        }
        //下页和尾页
        if (PageNo < PageCount)
        {
            PageList+="<a href='"+url+"PageNo="+(PageNo+1)+"'><font color='"+FontColor+"'>   下页</font></a> ";
            PageList += "<a href='" + url + "PageNo=" + PageCount + "'><font color='" + FontColor + "'>尾页</font></a> ";
        }
        else
        {
            PageList+="<font color='"+FontColor+"'>   下页</font> ";
            PageList += "<font color='" + FontColor + "'>尾页</font>";
        }

        this.lblPageList.Text = PageList;
        this.lblPageCount.Text = PageCount.ToString();
        this.lblRecordCount.Text = RecordCount.ToString();
        this.lblPageSize.Text = this.PageSize.ToString();

}
}