利用aspnetpager控件多种分页处理

浏览数：27 / 时间：2015年06月09日

个人学习记录：为了便于日后个人查阅，四种方案亲测可用（VS2010+sql server 2008）

首先进行页面布局

html代码如下：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="pagerTest.aspx.cs" Inherits="AspNetPagerTest.pagerTest" %>

<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <link href="style.css" rel="stylesheet" type="text/css" />
    <title>分页测试</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Repeater ID="rptList" runat="server">
            <HeaderTemplate>
                <table width="60%" border="0" cellspacing="0" cellpadding="0" class="msgtable">
                    <tr>
                        <th width="15%" align="left">
                            编号
                        </th>
                        <th width="15%" align="left">
                            用户名
                        </th>
                        <th width="15%" align="center">
                            密码
                        </th>
                        <th width="15%" align="left">
                            地址
                        </th>
                    </tr>
            </HeaderTemplate>
            <ItemTemplate>
                <tr>
                    <td>
                        <%#Eval("ID")%>
                    </td>
                    <td>
                        <%#Eval("UserName")%>
                    </td>
                    <td align="center">
                        <%#Eval("Pwd")%>
                    </td>
                    <td>
                        <%#Eval("Adress")%>
                    </td>
                </tr>
            </ItemTemplate>
            <FooterTemplate>
                <%#rptList.Items.Count == 0 ? "<tr><td align=\"center\" colspan=\"10\">暂无记录</td></tr>" : ""%>
                </table>
            </FooterTemplate>
        </asp:Repeater>
        <!--列表展示.结束-->
        <div class="page_box">
            <div id="PageContent" runat="server" class="flickr right">
            </div>
            <div class="divInfoRight">
                <webdiyer:AspNetPager ID="Pager" ShowCustomInfoSection="Left" ShowPageIndexBox="Never"
                    CustomInfoHTML="共 <B>%PageCount%</B> 页，当前为第 <B>%CurrentPageIndex%</B> 页，每页 <B>%PageSize%</B> 条"
                    runat="server" ShowNavigationToolTip="True" OnPageChanged="Pager_PageChanged"
                    CustomInfoTextAlign="left" HorizontalAlign="Right" PageSize="20" AlwaysShow="True">
                </webdiyer:AspNetPager>
            </div>
        </div>
    </div>
    </form>
</body>
</html>

View Code

先来张效果图

方案一：利用top和not in 分页

        // 方案一：top和not in 分页
        // select top pagesize * from tableName where field（一般用主键）not in (select top pagesize*(currpageindex-1))  field（一般用主键） from tablename
        private string SQL2()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TOP " + Pager.PageSize + " * FROM TB_User ");
            strSql.Append(" WHERE");
            strSql.Append(" ID NOT IN (");
            strSql.Append(" SELECT TOP " + Pager.PageSize * (Pager.CurrentPageIndex - 1) + " ID FROM TB_User");
            strSql.Append(" ORDER BY ID)");
            return strSql.ToString();
        }

View Code

方案二：利用top和max分页

        // 方案二：top和ID大于多少分页
        // SELECT TOP pagesize * FROM tablename WHERE (field(一般用主键) >(SELECT MAX(field(一般用主键)) FROM (SELECT TOP pagesize*(currpageindex-1) field(一般用主键) FROM tablename ORDER BY field(一般用主键)) AS T)) ORDER BY field(一般用主键)
        // 要判断currpageindex是否为1，因为当currpageindex=0时，top 0返回的是空，所以，为0时，直接赋值0
        private string SQL3()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TOP " + Pager.PageSize + " * FROM TB_User ");
            strSql.Append(" WHERE");
            strSql.Append(" ID > (");
            if (Pager.CurrentPageIndex != 1)
            {
                strSql.Append(" SELECT MAX(id) FROM (");
                strSql.Append(" SELECT TOP " + Pager.PageSize * (Pager.CurrentPageIndex - 1) + " ID FROM TB_User ORDER BY ID) AS T");
            }
            else
            {
                strSql.Append(" 0");
            }
            strSql.Append(" )ORDER BY ID");
            return strSql.ToString();
        }

View Code

方案三：利用row_number（）函数分页

       // 方案三：利用row_number（）函数给每条数据进行编号
        // select * from ( select *, ROW_NUMBER() OVER(Order by u.field(一般用主键)) AS RowNumber from tablename as u) as t where RowNumber BETWEEN (pagesize*(currpageindex-1)+1) and pagesize * currpageindex ORDER BY field(一般用主键)
        private string SQL4()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from (");
            strSql.Append(" select *, ROW_NUMBER() OVER(Order by u.ID) AS RowNumber from TB_User as u ");
            strSql.Append(" ) as t ");
            strSql.Append(" where RowNumber BETWEEN " + (Pager.PageSize * (Pager.CurrentPageIndex - 1) + 1) + " and " + Pager.PageSize * Pager.CurrentPageIndex + "");
            strSql.Append(" ORDER BY ID");
            return strSql.ToString();
        }

View Code

方案四：LINQ skip 和 take

        // 方案四：linq分页
        // 主要是对Skip和Take的应用
        private void DataBind1()
        {
            using (dbDataContextDataContext db = new dbDataContextDataContext())
            {
                var list = db.TB_User.OrderBy(Id => Id.ID)
                       .Select((p) => new
                       {
                           p.ID,
                           p.UserName,
                           p.Pwd,
                           p.Adress
                       });
                list = list.Skip(Pager.PageSize * (Pager.CurrentPageIndex - 1)).Take(Pager.PageSize);
                Pager.RecordCount = (from d in db.TB_User select d).Count();
                rptList.DataSource = list.ToList();
                rptList.DataBind();
            }
        }

View Code

数据绑定

      private void DataBind2()
        {
            string conn = ConfigurationManager.ConnectionStrings["PagerTestConnectionString"].ConnectionString;
            SqlConnection sqlconn = new SqlConnection(conn);
            sqlconn.Open();

            
            string strSql1 = "SELECT COUNT(ID) FROM TB_User";
            SqlCommand comm = new SqlCommand(strSql1,sqlconn);
            int count = (int)comm.ExecuteScalar();

            SqlDataAdapter da = new SqlDataAdapter(SQL4(), sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            Pager.RecordCount = count;
            rptList.DataSource = ds;
            rptList.DataBind();
        }