C# web 分页控件

最新推荐文章于 2022-11-24 16:31:11 发布

东明之羞

最新推荐文章于 2022-11-24 16:31:11 发布

阅读量3.5k

点赞数 1

分类专栏： C# 自定义控件文章标签： c# 分页控件总页数查询总数量缓存

本文链接：https://blog.csdn.net/qq_28254093/article/details/77948396

版权

C# 同时被 2 个专栏收录

16 篇文章 0 订阅

订阅专栏

自定义控件

1 篇文章 0 订阅

订阅专栏

在页面显示中，通常会用到分页，

比如：1

又比如：2

上面是总体的两种分页。分页1 没有总页数。优点：利于查询速度，用于数据较大百万级以上的查询。缺点也很明显，不能明显的知道查询的数据页数。

分页2 有总页数。优点：可以便利的感知到查询数据提条数和页数，但缺点也很明显，百万级数据明显会拖慢查询速度。【有次查询数据106万，查询语句优化到2.5秒，但查询总数据条数耗费13.86秒。用户实际上观察到的数据或许只有第一页，也就是 2，3 十条，但花费13秒定然会让用户不爽】。

那有没有更好的方式呢，或者说折中办法，有。如下就介绍这种办法。先上图

图3

图4

图5

图6

图3 截图太大，下面截图放大浏览器百分比截取的，点击图4 的然后出图 5 ，等待一会就回变成图6，当然等待当中，并不影响操作，页面不会卡死。

好了，有了效果图，我们下面看代码，没有兴趣的可以直接去寻找下一个符合自己要求兴趣的代码案例。

主体目的：做成分页控件，每个有需要的页面可以引用。

主体思路：需要页面实现分页控件定义的事件，需要查询的数据SQL，在执行分页事件。

具备功能：在不需要得知总页数条数的情况下最大优化速度，在需要知道数据条数时，不影响查询速度。

总数据经过一次查询后，在当前登录用户下会进行缓存，下次相同条件结构的查询无需去数据库查询数据总数。减少资源消耗。

控件代码：

控件对应页面代码： PageControls.ascx.cs

<%@ Control Language="C#" ClientIDMode="Static" AutoEventWireup="true" CodeBehind="PageControls.ascx.cs"
    Inherits="Web.UserControl.PageControls" %>
<link href="/Themes/Styles/pagination.css" rel="stylesheet" type="text/css" />
<link href="/Themes/Styles/style.css" rel="stylesheet" type="text/css" />
<link href="/Themes/Styles/Site.css" rel="stylesheet" type="text/css" />
<%--自定义用户分页控件--%>

<style type="text/css">
    .box-left {
        border-left: 1px solid #0fa2a2;
        height: 25px;
        padding-left: 8px;
        padding-right: 5px;
        padding-top: 2px;
        float: left;
    }
    .hideDiv {
        display:none;
    }

</style>
        <div style="display:none"><asp:Label runat="server" Text="" ID="PageGuid"></asp:Label></div>
        <div style="height:25px ;float:left"  align="left" >
            <div class="box-left" style="margin-left:4px" id="CountsDiv"> 共 <asp:Label runat="server" Text="0" ID="Counts"></asp:Label> 条数据 </div>
            <div class="box-left"> <asp:Label runat="server" Text="" ID="ShowCountNumber"></asp:Label> </div>
            <div class="box-left"> <asp:Label runat="server" Text="" ID="ShowTime"></asp:Label> </div>
        </div>
        <div style="float:right;" align="right" >
            <div class="box-left" runat="server" ><asp:LinkButton runat="server"  title="首 页" OnClick="HomeClick"><span id="spfirst" class="first">     </span></asp:LinkButton></div>
            <div class="box-left" runat="server" ><asp:LinkButton runat="server" title="上 页" OnClick="PrevClick"> <span id="spprev" class="prev">     </span></asp:LinkButton></div>
            <div class="box-left" > 第 <asp:Label runat="server" Text="1" ID="NowPage"></asp:Label> 页 </div>
            <div class="box-left hideDiv" id="PageCountDiv" > 共 <asp:Label runat="server" Text="1" ID="PageCount"></asp:Label> 页 </div>
            <div class="box-left" runat="server" ><asp:LinkButton runat="server" title="下 页" OnClick="NextClick"><span id="spnext" class="next" >     </span></asp:LinkButton></div>
             <%--<div class="box-left" runat="server" ><asp:LinkButton runat="server" title="尾 页" OnClick="LastClick"><span id="splast" class="last" >     </span></asp:LinkButton></div>--%>
            
            <div class="box-left hideDiv" id="flushImg" ><span class="flushImg" >     </span></div>
            <div class="box-left hideDiv" id="ShowCountDiv" runat="server" ><asp:LinkButton runat="server" title="查询总数量" OnClick="ShowCount" OnClientClick="ShowFlush()"><span id="flush" class="flush" >     </span></asp:LinkButton></div>
            <div class="box-left" style="padding-right: 0px;padding-left:0px;padding-top:0px;border-right:1px solid #0fa2a2;margin-right:4px" >
                <asp:DropDownList ID="PageSizes" class="select" style="width: 50px; font-size: 9pt;" runat="server"  AutoPostBack="true" OnSelectedIndexChanged="PageSizeChange" >
                    <asp:ListItem>20</asp:ListItem>
                    <asp:ListItem>30</asp:ListItem>
                    <asp:ListItem>50</asp:ListItem>
                    <asp:ListItem>80</asp:ListItem>
                </asp:DropDownList>
            </div>
        </div>
<script type="text/javascript" >
    debugger;
    var Counts = $("#Counts").text();
    if (Counts == "0" || Counts == "1") {
        $("#CountsDiv").hide();
        $("#PageCountDiv").hide();
        $("#ShowCountDiv").show();
        $("#flush").show();
    }
    else {
        $("#CountsDiv").show();
        $("#PageCountDiv").show();
        $("#ShowCountDiv").hide();
        $("#flush").hide();
    }
    $("#flushImg").hide();

    function ShowFlush() {
        $("#ShowCountDiv").hide();
        $("#flushImg").show();
    }
</script>

代码对应页面css: pagination.css

/**分页 begin**/
.pagination
{
    border-left: solid 1px #bfdddd;
    border-right: solid 1px #bfdddd;
    /*background-image: url('../images/datagrid_header_bg.gif');*/
    background-repeat: repeat-x;
    margin: 4px;
    margin-top: 0px;
    margin-bottom: 0px;
    height: 27px;
    width: auto;
}
.pagination-btn-separator
{
    float: left;
    height: 24px;
    border-left: 1px solid #22acac;
    border-right: 1px solid #fff;
    margin: 1px;
}
.first
{
    background: url('../images/first.gif') no-repeat 0px 2px;
    cursor: pointer;
}
.prev
{
    background: url('../images/prev.gif') no-repeat 0px 2px;
    cursor: pointer;
}
.next
{
    background: url('../images/next.gif') no-repeat 0px 2px;
    cursor: pointer;
}
.last
{
    background: url('../images/last.gif') no-repeat 0px 2px;
    cursor: pointer;
}

.flush {
    background: url('../images/16/2013040601125064_easyicon_net_16.png') no-repeat 0px 2px;
    cursor: pointer;
}
.flushImg {
    background: url('../images/loading1.gif') no-repeat 0px 2px;
}
/**分页 end**/

控件对应的C#代码 PageControls .cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Common.DotNetCode;

namespace Web.UserControl
{
    public class PageControls : System.Web.UI.UserControl
    {
        /// <summary>
        ///     保存页面传递过来的Sql和Sql参数
        /// </summary>
        public static Dictionary<string, object[]> dictionary = new Dictionary<string, object[]>();


        /// <summary>
        ///     缓存得到已查询的语句总条数，
        /// </summary>
        public static Dictionary<string, int> dicCount = new Dictionary<string, int>();

        /// <summary>
        ///     下一页
        /// </summary>
        private int _next;

        //引用页面使用分页控件的唯一GUID
        private string _pageGuID;


        /// <summary>
        ///     每页显示数量
        /// </summary>
        private int _pageSize = 20;

        /// <summary>
        ///     上一页
        /// </summary>
        private int _prev;


        /// <summary>
        ///     首页
        /// </summary>
        private int home = 0;


        protected bool isChange;

        /// <summary>
        ///     排序的列
        /// </summary>
        public string orderName = "";

        /// <summary>
        ///     排序方式
        /// </summary>
        public string orderType = "ASC";

        private readonly string showFormat = "显示{0}-{1}条";
        private readonly string showTimeFormat = "耗时：{0}秒";

        private int pageSize
        {
            get { return _pageSize; }
            set { _pageSize = value <= 0 ? 20 : value; }
        }

        /// <summary>
        ///     总页数
        /// </summary>
        private int pageCount
        {
            get { return int.Parse(PageCount.Text); }
            set
            {
                value = value < 1 ? 1 : value;
                PageCount.Text = value + "";
            }
        }

        /// <summary>
        ///     总条数
        /// </summary>
        private int count
        {
            get { return int.Parse(Counts.Text); }
            set
            {
                value = value < 1 ? 1 : value;
                Counts.Text = value + "";
                pageCount = value/pageSize + (value%pageSize == 0 ? 0 : 1);
            }
        }

        /// <summary>
        ///     当前页
        /// </summary>
        private int nowPage
        {
            get { return int.Parse(NowPage.Text); }
            set
            {
                value = value < 1 ? 1 : value;
                NowPage.Text = value + "";
            }
        }

        private int prev
        {
            get { return _prev; }
            set
            {
                value = value < 0 ? 0 : value;
                _prev = value;
            }
        }

        private int next
        {
            get { return _next; }
            set
            {
                value = value < 2 ? 2 : value;
                _next = value;
            }
        }

        /// <summary>
        ///     尾页
        /// </summary>
        private int last
        {
            get { return pageCount; }
            set { value = pageCount; }
        }


        /// <summary>
        ///     查询到的数据
        /// </summary>
        public DataTable DataSource { get; set; }

        public string PageGuID
        {
            get
            {
                if (PageGuid.Text == "")
                {
                    _pageGuID = Guid.NewGuid().ToString();
                }
                else
                {
                    _pageGuID = PageGuid.Text;
                }
                PageGuid.Text = _pageGuID;
                return _pageGuID;
            }
        }

        private staic OrcHelper 
        orcHelper ;

        //页面加载
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                pageHandler(sender, e);
            }
            pageSize = int.Parse(PageSizes.Text);
        }

        //赋值，并得到数据
        public DataTable SetPageSqlToData(string keyGuid, object[] pageSql, string _orderName = "",
            string _orderType = "")
        {
            orderName = _orderName;
            orderType = _orderType;
            if (dictionary.Keys.Contains(keyGuid))
            {
                var _pageSql = dictionary[keyGuid];
                var _param = _pageSql[1] == null || ((SqlParam[]) _pageSql[1]).Length <= 0
                    ? null
                    : ((SqlParam[]) _pageSql[1])[0].FiledValue;
                var _param2 = pageSql[1] == null || ((SqlParam[]) pageSql[1]).Length <= 0
                    ? null
                    : ((SqlParam[]) pageSql[1])[0].FiledValue;
                var _sql = _pageSql[0].ToString();
                var _sql1 = pageSql[0].ToString();
                var flg1 = _sql.Equals(_sql1);
                var flg2 = false;
                if (_param2 != null && _param != null)
                {
                    flg2 = _param2.ToString().Equals(_param.ToString());
                }
                else
                {
                    flg2 = true;
                }
                if (flg1 == false || flg2 == false)
                {
                    count = 0;
                    var key = keyGuid + _sql1 + _param2;
                    if (dicCount.Keys.Contains(key))
                    {
                        count = dicCount[key];
                    }

                    dictionary[keyGuid] = pageSql; //保存查询需要的SQL和参数
                    isChange = true;
                }
                else
                {
                    isChange = false;
                }
                if ((flg1 && flg2 == false) || (flg1 == false && flg2 == false)) //点击页面检索按钮，页面当前页面赋值为1
                {
                    nowPage = 1;
                }
            }
            else
            {
                dictionary[keyGuid] = pageSql;
                isChange = true;
            }
            //调用查询语句 查询数据
            return GetDataSource();
        }

        public object[] GetPageSql()
        {
            return dictionary[PageGuID];
        }

        public event EventHandler pageHandler;


        /// <summary>
        ///     首页
        /// </summary>
        public void HomeClick(object sender, EventArgs e)
        {
            prev = 0;
            nowPage = 1;
            pageHandler(sender, e);
            isChange = false;
        }

        /// <summary>
        ///     尾页
        /// </summary>
        public void LastClick(object sender, EventArgs e)
        {
            if (pageCount > 1)
            {
                prev = last - 1;
                nowPage = last;
            }
            pageHandler(sender, e);
            isChange = false;
        }

        /// <summary>
        ///     上一页
        /// </summary>
        public void PrevClick(object sender, EventArgs e)
        {
            next = nowPage;
            nowPage = nowPage - 1;
            prev = nowPage - 1;
            pageHandler(sender, e);
            isChange = false;
        }

        /// <summary>
        ///     下一页
        /// </summary>
        public void NextClick(object sender, EventArgs e)
        {
            prev = nowPage;
            nowPage = nowPage + 1;
            if (pageCount > 1 && nowPage >= pageCount)
            {
                nowPage = pageCount;
                prev = pageCount - 1;
            }
            pageHandler(sender, e);
            isChange = false;
        }

        /// <summary>
        ///     页数变化事件
        /// </summary>
        public void PageSizeChange(object sender, EventArgs e)
        {
            pageSize = int.Parse(PageSizes.Text);
            pageHandler(sender, e);
            isChange = false;
        }

        /// <summary>
        ///     显示数据
        /// </summary>
        public DataTable GetDataSource()
        {
            var start = DateTime.Now;

            //得到插叙数据
            var num1 = (nowPage - 1)*pageSize;
            var num2 = nowPage*pageSize;
            var pageSql = GetPageSql();
            var sql = pageSql[0].ToString();
            sql =
                string.Format(
                    "SELECT S.* FROM ( SELECT ROWNUM AS ROWSNUM,T.* FROM ( {0} ) T WHERE ROWNUM<={2} ) S WHERE S.ROWSNUM > {1} ",
                    sql, num1, num2);
            var sqlWhere = orderName == ""
                ? ""
                : " ORDER BY " + orderName + (orderType == "" || orderType == "ASC" ? " ASC" : " DESC");
            sql = sql + sqlWhere;
            var param = pageSql[1];
            var _param = param == null ? null : (SqlParam[]) param;
            var _sql = new StringBuilder(sql);

            orcHelper = orcHelper ?? new orcHelper();
            //得到查询语句访问类 //计算消耗时间 
            var end = DateTime.Now;
            var time = end.Subtract(start);
            ShowCountNumber.Text = string.Format(showFormat, num1 + 1, num2);
            ShowTime.Text = string.Format(showTimeFormat, time.TotalSeconds.ToString("N3"));
            return dt;
        }

        /// <summary>
        ///     显示总条数
        /// </summary>
        public void GetPageDetial()
        {
            var pageSql = GetPageSql();
            var _pageSql = pageSql[0].ToString();
            var key = PageGuID + _pageSql +
                      (pageSql[1] == null || ((SqlParam[]) pageSql[1]).Length <= 0
                          ? null
                          : ((SqlParam[]) pageSql[1])[0].FiledValue);
            if (dicCount.Keys.Contains(_pageSql))
            {
                count = dicCount[_pageSql];
                if (count > 1)
                {
                    return;
                }
            }
            var sql = string.Format("select count(1) from ({0})", _pageSql);
            var param = pageSql[1];
            var _param = param == null ? null : (SqlParam[]) param;
            var _sql = new StringBuilder(sql);

            var dt = orcHelper.GetDataTableBySQL(_sql, _param);
            if (dt != null && dt.Rows.Count > 0)
            {
                count = int.Parse(dt.Rows[0][0].ToString());
            } //缓存得到的条数 
            dicCount[key] = count;
        }

        /// <summary> 
        /// 显示总条数 
        ///</summary>

        public void ShowCount(object sender, EventArgs e)
        {
            GetPageDetial();
        }
    }
}

控件对应的C#代码 OrcHelper.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;

namespace DataBase.SqlHelper
{
    public partial class OrcHelper : Page
    {

        public override DataTable GetDataTableBySQL(StringBuilder sql, SqlParam[] param)
        {
            try
            {
                dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                this.AddInParameter(dbCommand, param);
                return ReaderToIListHelper.DataTableToIDataReader(db.ExecuteReader(dbCommand));
            }
            catch (Exception e)
            {
                return null;
            }
        }
        /// <summary>
        /// 得到 查询语句
        /// </summary>
        public override object[] GetPageLists(string sql, SqlParam[] param)
        {
            var PageSql = new object[] {
                 sql,
                 param != null ? param : null,
                 0
            };
            return PageSql;
        }
    }
}

部门 Employees_DAL.cs

using System.Collections.Generic;
using System.Linq;
using System.Text;
using Common.DotNetCode;

namespace Busines.DAL
{
    public class Employees_DAL : Employees_IDAL
    {
        private static OrcHelper orchelper;

        public object[] GetPageDoctorInfo(StringBuilder sqlWhere, IList<SqlParam> param)
        {
            var sql = "select * from BASE_SYSDOCTOR ";
            if (sqlWhere != null)
            {
                sql += sqlWhere;
            }
            orchelper = orchelper ?? new OrcHelper();
            return orchelper.GetPageLists(sql, param.ToArray());
        }
    }
}

调用页面代码 Doctor_List

using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;
using Busines.IDAO;
using Common.DotNetCode;
using Common.DotNetUI;

namespace Web.SysBase.SysDoctor
{
    public class Doctor_List : Page
    {
        private JKDA_Employees_IDAL employeesI_Da;

        //页面加载事件
        protected void Page_Load(object sender, EventArgs e)
        {
            PageControl1.pageHandler += delegate { InitData(); }; //绑定分页控件事件
            if (!IsPostBack)
            {
                InitData();
            }
        }

        //检索查询数据，并绑定到页面
        public void InitData()
        {
            employeesI_Da = employeesI_Da ?? new Employees_DAL();
            var SqlWhere = new StringBuilder();
            SqlWhere.Append(" Where DELETEMARK=1 ");
            IList<SqlParam> IList_param = new List<SqlParam>();
            if (!string.IsNullOrEmpty(DOCTOR_NAME.Value))
            {
                SqlWhere.Append(" AND DOCTOR_NAME like :DOCTOR_NAME");
                IList_param.Add(new SqlParam(":DOCTOR_NAME", string.Format("%{0}%", DOCTOR_NAME.Value.Trim())));
            }

            var obj = employeesI_Da.GetPageDoctorInfo(SqlWhere, IList_param); //得到分页需要的查询语句
            var dt = PageControl1.SetPageSqlToData(PageControl1.PageGuID, obj, "SUBID", "ASC"); //传递需要的查询参数，并得到查询数据
            ControlBindHelper.BindRepeaterList(dt, DoctorInfo); //绑定查询数据
        }

        //页面查询按钮
        public void DoctorSearch_Click(object sender, EventArgs e)
        {
            InitData();
        }
    }
}

调用页面代码 Doctor_List

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Doctor_List.aspx.cs" Inherits="Web.SysBase.SysDoctor.Doctor_List" %>
<%@ Register TagPrefix="uc1" TagName="PageControls" Src="~/UserControl/PageControls.ascx" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title>医生信息管理</title>
     <link href="/Themes/Styles/Site.css" rel="stylesheet" type="text/css" />
    
</head>
<body>
    <form id="form1" runat="server">
    <div class="btnbartitle">
        <div>
            医生信息列表
        </div>
    </div>
    <div class="btnbarcontetn">
      
    </div>
    <div class="div-body">
       <table id="table1" class="grid" singleselect="true">
            <thead>
                <tr>
                    
                    <td style="width: 130px; text-align: center;">
                        医生名称
                    </td>
                     
                </tr> 
            </thead>
            <tbody>
                 <asp:Repeater ID="DoctorInfo" runat="server">
                     <ItemTemplate>
                         <tr>
                            
                           
                             <td style="width: 130px; text-align: center;">
                                <%#Eval("DOCTOR_NAME") %>
                            </td>
                            
                         </tr>
                     </ItemTemplate>
                     <FooterTemplate>
                        <% if (DoctorInfo != null)
                           {
                               if (DoctorInfo.Items.Count == 0)
                               {
                                   Response.Write("<tr><td colspan='11' style='color:red;text-align:center'>没有找到您要的相关数据！</td></tr>");
                               }
                           } %>
                    </FooterTemplate>
                 </asp:Repeater>
            </tbody>
        </table>
    </div>
     <uc1:PageControl ID="PageControl1" runat="server" />
    </form>

具体代码 http://download.csdn.net/download/qq_28254093/10051152