AspNetPager结合ListView进行高效分页

最新推荐文章于 2017-05-06 15:06:27 发布

x123jing

最新推荐文章于 2017-05-06 15:06:27 发布

阅读量2.7k

点赞数

分类专栏： asp.net_AspNetPager asp.net_listview 文章标签： listview server asp button 存储 object

本文链接：https://blog.csdn.net/x123jing/article/details/7054575

版权

asp.net_listview 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

asp.net_AspNetPager

3 篇文章 0 订阅

订阅专栏

环境：VS2010，SQLServer2008 express

使用控件：ListView，AspNetPager v7.32

AspNetPager分页存储过程

ALTER PROCEDURE [dbo].[fy] 
(@startIndex int,
@endIndex int)
AS
BEGIN
 with temptbl as (
SELECT ROW_NUMBER() OVER (ORDER BY UGID asc)AS Row, * from DJ_UserUnit )
 SELECT * FROM temptbl where Row between @startIndex and @endIndex
END

AspNetPager分页控件获取记录总数存储过程

ALTER PROCEDURE [dbo].[fycount]

AS
BEGIN

select count(*) from DJ_UserUnit;
--RETURN

END

以上两个存储过程是必须的。也可以只要第一个分页存储过程，第二个总数用SQL语句。

下面开始编程：

数据层：

DAL数据层基本操作方法：

第一个ExecuteScalar为获取记录总数的方法：

        #region 返回执行的SQL语句或存储过程的第一行第一列，忽视其它行列，返回类型为object
        /// <summary>
        /// 返回执行的SQL语句或存储过程的第一行第一列，忽视其它行列，返回类型为object
        /// </summary>
        /// <param name="cmdText">存储过程或SQL语句</param>
        /// <param name="procType">cmd类型，CommandType.StoredProcedure为存储过程，CommandType.Text为SQL</param>
        /// <returns>返回类型为object</returns>
        public object ExecuteScalar(string cmdText, CommandType procType)
        {
            object res;
            using(cmd=new SqlCommand(cmdText,GetConn()))
            {
                cmd.CommandType = procType;
                res=cmd.ExecuteScalar();
            }
            return res;
        }
        #endregion

第二个ExecuteQuery为分页存储过程操作方法

        #region 执行带参数查询SQL语句或存储过程，返回DataTable
        /// <summary>
        /// 执行带参数查询SQL语句或存储过程，返回DataTable
        /// </summary>
        /// <param name="cmdText">存储过程名或SQL语句</param>
        /// <param name="paras">参数</param>
        /// <param name="procType">cmd类型，CommandType.StoredProcedure为存储过程，CommandType.Text为SQL</param>
        /// <returns>返回DataTable</returns>
        public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType procType)
        {
            //TODO:执行带参数查询SQL语句或存储过程
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmdText, GetConn());
            cmd.CommandType = procType;  //这里判断是否是存储过程。CommandType.StoredProcedure为存储过程，CommandType.Text为SQL
            cmd.Parameters.AddRange(paras);
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }
        #endregion

结合以上两个基本方法，编写BLL业务逻辑层分页包装方法：

        #region 获得记录总数
        /// <summary>
        /// 获得记录总数
        /// </summary>
        /// <returns></returns>
        public int UserUnit_Count() {
            int res;
            string cmdText = "fycount";
            res = (int)sqlhelper.ExecuteScalar(cmdText, CommandType.StoredProcedure);
            return res;
        }
        #endregion

        #region 分页显示
        /// <summary>
        /// 分页显示
        /// </summary>
        /// <param name="startIndex">AspNetPager开始索引</param>
        /// <param name="endIndex">AspNetPager结束索引</param>
        /// <returns></returns>
        public DataTable fy(int startIndex, int endIndex)
        {
            DataTable dta = new DataTable();
            string cmdText = "fy";
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@startIndex",startIndex),
                new SqlParameter("@endIndex",endIndex)
            };
            dta = sqlhelper.ExecuteQuery(cmdText, paras, CommandType.StoredProcedure);
            return dta;
        }
        #endregion

有了这些方法，就可以开始编写界面层了。

结合母版，随便写个ListView，然后拉入AspNetPager控件。

拉入AspNetPager控件的方法：

1、将AspNetPager.dll和AspNetPager.xml放入界面层Bin目录，然后引用AspNetPager.dll

2、在vs2010工具栏上点右键，选择“选择项”，然后选择AspNetPager.dll，这样，它就进入了工具栏。

界面前台，没什么说的，除了母版，就是一个listview和一个aspnetpager：

<%@ Page Title="" Language="C#" MasterPageFile="~/manage.Master" AutoEventWireup="true" CodeBehind="test.aspx.cs" Inherits="website.test" %>
<%@ Register assembly="AspNetPager" namespace="Wuqi.Webdiyer" tagprefix="webdiyer" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MenuTitle" runat="server">
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="SelectMenu" runat="server">
</asp:Content>
<asp:Content ID="Content4" ContentPlaceHolderID="Content" runat="server">
    <asp:ListView ID="ListView1" runat="server" DataKeyNames="UGID" InsertItemPosition="LastItem">
        <AlternatingItemTemplate>
            <tr style="background-color: #FAFAD2;color: #284775;">
                <td>
                    <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="删除" />
                    <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="编辑" />
                </td>
                <td>
                    <asp:Label ID="UGIDLabel" runat="server" Text='<%# Eval("UGID") %>' />
                </td>
                <td>
                    <asp:Label ID="UNameLabel" runat="server" Text='<%# Eval("UName") %>' />
                </td>
            </tr>
        </AlternatingItemTemplate>
        <EditItemTemplate>
            <tr style="background-color: #FFCC66;color: #000080;">
                <td>
                    <asp:Button ID="UpdateButton" runat="server" CommandName="Update" Text="更新" />
                    <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="取消" />
                </td>
                <td>
                    <asp:Label ID="UGIDLabel1" runat="server" Text='<%# Eval("UGID") %>' />
                </td>
                <td>
                    <asp:TextBox ID="UNameTextBox" runat="server" Text='<%# Bind("UName") %>' />
                </td>
            </tr>
        </EditItemTemplate>
        <EmptyDataTemplate>
            <table runat="server" 
                style="background-color: #FFFFFF;border-collapse: collapse;border-color: #999999;border-style:none;border-width:1px;">
                <tr>
                    <td>
                        未返回数据。</td>
                </tr>
            </table>
        </EmptyDataTemplate>
        <InsertItemTemplate>
            <tr style="">
                <td>
                    <asp:Button ID="InsertButton" runat="server" CommandName="Insert" Text="插入" />
                    <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="清除" />
                </td>
                <td>
                     </td>
                <td>
                    <asp:TextBox ID="UNameTextBox" runat="server" Text='<%# Bind("UName") %>' />
                </td>
            </tr>
        </InsertItemTemplate>
        <ItemTemplate>
            <tr style="background-color: #FFFBD6;color: #333333;">
                <td>
                    <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="删除" />
                    <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="编辑" />
                </td>
                <td>
                    <asp:Label ID="UGIDLabel" runat="server" Text='<%# Eval("UGID") %>' />
                </td>
                <td>
                    <asp:Label ID="UNameLabel" runat="server" Text='<%# Eval("UName") %>' />
                </td>
            </tr>
        </ItemTemplate>
        <LayoutTemplate>
            <table runat="server" width="100%">
                <tr runat="server">
                    <td runat="server">
                        <table ID="itemPlaceholderContainer" runat="server" border="1" 
                            style="background-color: #FFFFFF;border-collapse: collapse;border-color: #999999;border-style:none;border-width:1px;font-family: Verdana, Arial, Helvetica, sans-serif;" width="100%">
                            <tr runat="server" style="background-color: #FFFBD6;color: #333333;">
                                <th runat="server">
                                </th>
                                <th runat="server">
                                    UGID</th>
                                <th runat="server">
                                    UName</th>
                            </tr>
                            <tr ID="itemPlaceholder" runat="server">
                            </tr>
                        </table>
                    </td>
                </tr>
                <tr runat="server">
                    <td runat="server" 
                        style="text-align: center;background-color: #FFCC66;font-family: Verdana, Arial, Helvetica, sans-serif;color: #333333;">
                    </td>
                </tr>
            </table>
        </LayoutTemplate>
        <SelectedItemTemplate>
            <tr style="background-color: #FFCC66;font-weight: bold;color: #000080;">
                <td>
                    <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="删除" />
                    <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="编辑" />
                </td>
                <td>
                    <asp:Label ID="UGIDLabel" runat="server" Text='<%# Eval("UGID") %>' />
                </td>
                <td>
                    <asp:Label ID="UNameLabel" runat="server" Text='<%# Eval("UName") %>' />
                </td>
            </tr>
        </SelectedItemTemplate>
    </asp:ListView>
    </asp:Content>
<asp:Content ID="Content5" ContentPlaceHolderID="Footer" runat="server">
<%--AspNetPager样式，可随意调节--%>
    <webdiyer:AspNetPager ID="AspNetPager1" runat="server" FirstPageText="首页" 
        LastPageText="尾页" NextPageText="下一页" PageIndexBoxType="DropDownList" 
        PrevPageText="上一页" ShowPageIndexBox="Auto" SubmitButtonText="Go" 
        TextAfterPageIndexBox="页" TextBeforePageIndexBox="转到" 
        onpagechanged="AspNetPager1_PageChanged" 
        CustomInfoHTML="共%PageCount%页，当前为第%CurrentPageIndex%页，每页%PageSize%条" 
        ShowCustomInfoSection="Left">
    </webdiyer:AspNetPager>
</asp:Content>

界面后台，以下三个方法，必不可少：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BLL;
using Model;

namespace website
{
    public partial class test : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                int totalOrders = (int)new DJ_UserUnitManager().UserUnit_Count();   //获取记录集总数
                AspNetPager1.RecordCount = totalOrders;     //将总数赋给AspNetPager,此为不可少的参数
                bindData();
            }
        }

        void bindData()     //ListView绑定
        {
            ListView1.DataSource = new DJ_UserUnitManager().fy(AspNetPager1.StartRecordIndex, AspNetPager1.EndRecordIndex);     //通过分页存储过程进行ListView数据绑定。AspNetPager1.StartRecordIndex为开始参数，AspNetPager1.EndRecordIndex为结束参数，两参数为AspNetPager自带
            ListView1.DataBind();
        }

        protected void AspNetPager1_PageChanged(object src, EventArgs e)    //分页控件事件，当分页时，显示控件ListView跟着变化。缺此方法不会变化。
        {
            bindData();
        }
    }
}

通过上面界面后台的内容可以看出，aspnetpager的基本操作只需要三个部份，一、获取记录总数，二、使用aspnetpager的开始结束索引进行数据绑定，第三，点击分页序号后再次进行绑定，进行分页显示。很简单吧。