代码改变世界

ListView高效分页

2016-08-11 09:57  奔波儿灞  阅读(254)  评论(0)  编辑  收藏  举报

使用控件自带的分页功能时，会先将所查询的数据全部加载出来，若数据量较大，则造成浏览器端等待时间过长。

然而在庞大的数据量，用户所需要的不过是那么几条，甚至只要其中的一条数据，所以，为了减少开销，每次只从数据库中读取10条数据。

以下是我做过的一个例子，该数据库表中有101万行数据，只要将数据库连接修改一下，即可复制，粘贴使用。

以下是aspx中的代码：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="test.aspx.cs" Inherits="test" %>

<!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 rel="stylesheet" type="text/css" href="../Styles/bootstrap.min.css" />
    <link rel="stylesheet" type="text/css" href="../Styles/admin-all.css" />
    <link rel="stylesheet" type="text/css" href="../Styles/ui-lightness/jquery-ui-1.8.22.custom.css" />
    <link rel="stylesheet" type="text/css" href="../Styles/web.css" />
    <script type="text/javascript" src="../Scripts/jquery-1.7.2.js"></script>
    <script type="text/javascript" src="../Scripts/jquery-ui-1.8.22.custom.min.js"></script>
    <script type="text/javascript" src="../Scripts/My97DatePicker/WdatePicker.js"></script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table id="Search" class="table table-striped table-bordered table-condensed">
        <thead>
            <tr>
                <td colspan="6">
                    患者管理查询
                </td>
            </tr>
        </thead>
        <tr>
            <td rowspan="2">
                精确查找
            </td>
            <td>
                编号：
            </td>
            <td>
                <asp:TextBox ID="TextBoxIDNumber" runat="server"></asp:TextBox>
            </td>
            </tr>
            <tr>
                <td>
                    姓名：
                </td>
                <td>
                    <asp:TextBox ID="TextBoxName" runat="server"></asp:TextBox>
                </td>
            </tr>
        <tr>
            <td rowspan="2">
                模糊查询：
            </td>
            <td>
                编号：
            </td>
            <td>
                <asp:TextBox ID="TextBoxNumberx" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                姓名：
            </td>
            <td>
                <asp:TextBox ID="TextBoxNamex" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>

            </td>
            <td>
                排列方式
            </td>
            <td>
                <asp:DropDownList ID="DropDownList1" runat="server">
                    <asp:ListItem Value="1">按ID正序</asp:ListItem>
                    <asp:ListItem Value="2">按ID倒序</asp:ListItem>
                </asp:DropDownList>
            </td>
        </tr>
        <tr>
            <td colspan="3">
                <asp:Button ID="ButtonSearch" runat="server" Text="查询" OnClick="ButtonSearch_Click" />
                &nbsp;&nbsp;
                <asp:Button ID="ButtonExcel" runat="server" Text="导出" OnClick="ButtonExcel_Click" />
            </td>
        </tr>
        
    </table>
        <asp:Repeater ID="Repeater1" runat="server">
             <HeaderTemplate>
                 <table>
                      <tr>
                          <td style="width:50px;">
                             ID
                          </td>
                          <td style="width:150px;">
                              编号
                         </td>
                         <td style="width:100px;">
                             姓名
                         </td>
                     </tr>
                 
             </HeaderTemplate>
             <ItemTemplate>
                 <tr>
                     <td style=" color:Green;">
                         <%#Eval("ID")%>
                     </td>
                     <td style="color:Blue;">
                         <%#Eval("Number")%>
                     </td>
                     <td style="color:Red;">
                         <%#Eval("Name")%>
                     </td>
                 </tr>
             </ItemTemplate>
             <FooterTemplate>
    </table>
               </FooterTemplate>

        </asp:Repeater>
         <table>
             <tr >
                 <td>
                     <asp:LinkButton ID="LinkButtonFirst" runat="server" onclick="LinkButtonFirst_Click">首页</asp:LinkButton></td>
                 <td>
                     <asp:TextBox ID="TextBoxGo" runat="server" Width="29px"></asp:TextBox></td>
                    
                 <td>
                     <asp:LinkButton ID="LinkButtonGo" runat="server" onclick="LinkButtonGo_Click">前往</asp:LinkButton></td>
                 <td>
                     <asp:Label ID="Label1" runat="server" Text="第"></asp:Label></td>
                 <td>
                     <asp:Label ID="index" runat="server" Text="1"></asp:Label></td>
                 <td>
                     <asp:Label ID="Label3" runat="server" Text="页"></asp:Label></td>
                 <td>
                     <asp:Label ID="Label2" runat="server" Text="共"></asp:Label></td>
                 <td>
                     <asp:Label ID="labcount" runat="server" Text=""></asp:Label></td>
                 <td>
                     <asp:Label ID="Label5" runat="server" Text="页"></asp:Label></td>
                 <td>
                     <asp:LinkButton ID="LinkButtonBefore" runat="server" onclick="LinkButtonBefore_Click">上一页</asp:LinkButton></td>
                 <td>
                     <asp:LinkButton ID="LinkButtonAfter" runat="server" onclick="LinkButtonAfter_Click">下一页</asp:LinkButton></td>
                 <td>
                     <asp:LinkButton ID="LinkButtonLast" runat="server" onclick="LinkButtonLast_Click">尾页</asp:LinkButton></td>
             </tr>
         </table>
     </div>
    </form>
</body>
</html>

下面是后台cs代码：

public partial class test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.shuju();
        }

    }

    /// <summary>
    /// 获取数据，并设置页数
    /// </summary>
    /// <param name="pagenumber"></param>

    public void GetData(int pagenumber)
    {
        string str_sql1 = "select id,number,name from dbo.test ";
        string str_sql2 = "select count(*) from dbo.test ";              //获得数据总行数
        if (ViewState["where"] != null)
        {
            str_sql2 += ViewState["where"].ToString();
            str_sql1 += ViewState["where"].ToString();
        }
        if (ViewState["order"] != null)
        {
            str_sql1 += ViewState["order"].ToString();
        }
        else
        {
            str_sql1 += " order by id ";
        }
        str_sql1 += " offset " + pagenumber + " rows fetch next 10 rows only ";
        string rowsNumber = MySql.GetSingleStr(str_sql2);                  //获得数据总行数
        DataTable dt = MySql.GetDataTable(str_sql1);
        if (Convert.ToInt32(rowsNumber) % 10 != 0)
        {
            labcount.Text = (Convert.ToInt32(rowsNumber) / 10 + 1).ToString();     //返回总页数，不能被10整除则加一页
        }
        else
        {
            labcount.Text = (Convert.ToInt32(rowsNumber) / 10).ToString();     //返回总页数
        }
        this.Repeater1.DataSource = dt;
        this.Repeater1.DataBind();
    }

    /// <summary>
    /// 初始化数据
    /// </summary>
    public void shuju()
    {
        GetData(0);
        index.Text = "1";                                  //当前页为1

        //把首页和上一页设置为不可操作
        if (index.Text == "1")
        {
            LinkButtonFirst.Enabled = false;
            LinkButtonBefore.Enabled = false;
        }
        if (index.Text == labcount.Text)
        {
            LinkButtonAfter.Enabled = false;
            LinkButtonLast.Enabled = false;
        }
        else
        {
            LinkButtonAfter.Enabled = true;
            LinkButtonLast.Enabled = true;
        }
    }

    /// <summary>
    /// 首页按钮事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void LinkButtonFirst_Click(object sender, EventArgs e)
    {

        GetData(0);
        index.Text = "1";                                  //当前页为1

        //把首页和上一页设置为不可操作
        if (index.Text == "1")
        {
            LinkButtonFirst.Enabled = false;
            LinkButtonBefore.Enabled = false;
        }
        if (index.Text == labcount.Text)
        {
            LinkButtonAfter.Enabled = false;
            LinkButtonLast.Enabled = false;
        }
        else
        {
            LinkButtonAfter.Enabled = true;
            LinkButtonLast.Enabled = true;
        }
    }

    /// <summary>
    /// Go按钮事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void LinkButtonGo_Click(object sender, EventArgs e)
    {
        if (TextBoxGo.Text != "")
        {
            string pageindex = TextBoxGo.Text;                  //获取要前往的页数
            int intpageindex = Convert.ToInt32(pageindex);       //转为整型

            int pagecount = Convert.ToInt32(labcount.Text);
            if (intpageindex > pagecount&&intpageindex<=0)       //判断，如果输入的数字大于总页数就返回
            {
                return;
            }
            else
            {
                int pageNumber = Convert.ToInt32(TextBoxGo.Text)*10-10;
                GetData(pageNumber);
                if (TextBoxGo.Text == "1")       //判断如果输入1，首页和上一页不可操作，但是尾页和下一页可以操作
                {
                    LinkButtonFirst.Enabled = false;
                    LinkButtonBefore.Enabled = false;

                    LinkButtonAfter.Enabled = true;
                    LinkButtonLast.Enabled = true;
                }
                else if (TextBoxGo.Text == labcount.Text)        //判断如果输入的数等于总页数，首页和第一页可以操作，但是尾页和下一页不可操作
                {
                    LinkButtonAfter.Enabled = false;
                    LinkButtonLast.Enabled = false;

                    LinkButtonFirst.Enabled = true;
                    LinkButtonBefore.Enabled = true;
                }
                else                                           //如果是其他情况，首页和下一页，尾页和下一页都可操作
                {
                    LinkButtonAfter.Enabled = true;
                    LinkButtonLast.Enabled = true;

                    LinkButtonFirst.Enabled = true;
                    LinkButtonBefore.Enabled = true;
                }
            }

            index.Text = TextBoxGo.Text;             //设置当前页数等于输入的页数
        }
        else
        {
            return;
        }
    }

    /// <summary>
    /// 上一页按钮事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void LinkButtonBefore_Click(object sender, EventArgs e)
    {

        string pageindex = index.Text;                          //获取当前页数
        int pageNumber = Convert.ToInt32(pageindex) * 10 - 20;       //获取上一页之前的总行数
        GetData(pageNumber);

        int intindex = Convert.ToInt32(index.Text);
        index.Text = (intindex - 1).ToString();                   //当前页-1

        LinkButtonAfter.Enabled = true;
        LinkButtonLast.Enabled = true;

        if (index.Text == "1")
        {
            LinkButtonFirst.Enabled = false;
            LinkButtonBefore.Enabled = false;
        }

    }

    /// <summary>
    /// 下一页按钮事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void LinkButtonAfter_Click(object sender, EventArgs e)
    {
        string pageindex = index.Text;                                   //获取当前页数
        int pageNumber = Convert.ToInt32(pageindex)*10;                     //获取下一页之前的总行数
        GetData(pageNumber);

        int intindex = Convert.ToInt32(index.Text);
        index.Text = (intindex + 1).ToString();                  //当前页+1

        LinkButtonFirst.Enabled = true;
        LinkButtonBefore.Enabled = true;

        if (index.Text == labcount.Text)
        {
            LinkButtonAfter.Enabled = false;
            LinkButtonLast.Enabled = false;
        }
    }

    /// <summary>
    /// 尾页按钮事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void LinkButtonLast_Click(object sender, EventArgs e)
    {

        int pageNumber = (Convert.ToInt32(labcount.Text) - 1) * 10;               //获取最后一页之前的总行数
        GetData(pageNumber);

        index.Text = labcount.Text;


        LinkButtonFirst.Enabled = true;
        LinkButtonBefore.Enabled = true;

        LinkButtonAfter.Enabled = false;
        LinkButtonLast.Enabled = false;
    }
    protected void ButtonSearch_Click(object sender, EventArgs e)
    {
        string strWhere = " where ";
        string strOrder = " order by ID ";
        if (TextBoxIDNumber.Text != "")                              //填写编号
        {
            strWhere += "Number='" + TextBoxIDNumber.Text + "' and ";
        }
        if (TextBoxName.Text != "")                                  //填写姓名
        {
            strWhere += "Name='" + TextBoxName.Text + "' and ";
        }
        if (TextBoxNumberx.Text != "")                               //填写模糊编号
        {
            strWhere += "Number like '" + TextBoxNumberx.Text + "%' and ";
        }
        if (TextBoxNamex.Text != "")                                //编写模糊姓名
        {
            strWhere += "Name like '" + TextBoxNamex.Text + "%' and ";
        }
        strWhere += " 1=1 ";
        if (DropDownList1.SelectedValue == "2")                  //选择倒序
        {
            strOrder += " desc";
        }
        ViewState["where"] = strWhere;
        ViewState["order"] = strOrder;
        shuju();

    }

    protected void ButtonExcel_Click(object sender, EventArgs e)
    {
        string sql = "select id,number,name from dbo.test ";
        if(ViewState["where"]!=null)
        {
            sql += ViewState["where"].ToString();
        }
        if (ViewState["order"] != null)
        {
            sql += ViewState["order"].ToString();
        }
        DataTable dtexcel = MySql.GetDataTable(sql);
        Random ran = new Random();
        string temp = ran.Next(1000, 9999).ToString();
        string excel = "Excel-" + DateTime.Now.ToString("yyyyMMdd") + temp;
        CreateExcel(dtexcel, "application/ms-excel", excel);
    }

    /// <summary>
    /// 导出excel
    /// </summary>
    /// <param name="dt">数据源</param>
    /// <param name="FileType">文件类型</param>
    /// <param name="FileName">文件名</param>
    public void CreateExcel(DataTable dt, string FileType, string FileName)
    {
        Response.Clear();
        Response.Charset = "UTF-8";
        Response.Buffer = true;
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls\"");
        Response.ContentType = FileType;
        string colHeaders = string.Empty;
        string ls_item = string.Empty;
        ls_item += "ID\t编号\t姓名\n";
        DataRow[] myRow = dt.Select();
        int i = 0;
        int cl = dt.Columns.Count;
        foreach (DataRow row in myRow)
        {
            for (i = 0; i < cl; i++)
            {
                if (i == (cl - 1))
                {
                    ls_item += row[i].ToString() + "\n";
                }
                else
                {
                    ls_item += row[i].ToString() + "\t";
                }
            }
            Response.Output.Write(ls_item);
            ls_item = string.Empty;
        }
        Response.Output.Flush();
        Response.End();
    }
}

附带excel导出功能。