[转]千万级分页存储过程结合Repeater+Aspnetpager7.2实现

 1 set ANSI_NULLS ON
 2 set QUOTED_IDENTIFIER ON
 3 go
 4 
 5 --参数说明
 6 -------------------------------------------------------------
 7 /**//*
 8 @strTable --要显示的表或多个表的连接
 9 @strField --要查询出的字段列表,*表示全部字段
10 @intTop --最多读取记录数 
11 @pageSize --每页显示的记录个数
12 @pageIndex --要显示那一页的记录
13 @strWhere --查询条件,不需where
14 @strSortKey --用于排序的主键
15 @strSortField --用于排序，如：id desc (多个id desc,dt asc)
16 @strOrderBy --排序,0-顺序,1-倒序
17 @pageCount --查询结果分页后的总页数
18 @RecordCount --查询到的总记录数
19 @UsedTime --耗时测试时间差
20 */ 
21 CREATE PROCEDURE [dbo].[ThePagerIndex]
22 @strTable varchar(50) = '[dbo].[ttable]',
23 @strField varchar(50) = '*',
24 @intTop int = 5000,
25 @pageSize int = 20,
26 @pageIndex int = 1,
27 @strWhere varchar(50) = '1=1',
28 @strSortKey varchar(50) = 'id',
29 @strSortField varchar(50) = 'id DESC',
30 @strOrderBy bit = 1,
31 @pageCount int OUTPUT,
32 @RecordCount int OUTPUT
33 --@UsedTime int OUTPUT
34 AS
35 SET NOCOUNT ON
36 Declare @sqlcount INT
37 Declare @timediff DATETIME
38 select @timediff=getdate() 
39 Begin Tran
40 DECLARE @sql nvarchar(max),@where1 varchar(max),@where2 varchar(max)
41 IF @strWhere is null or rtrim(@strWhere)=''
42 BEGIN--没有查询条件 
43 SET @where1=' WHERE ' 
44 SET @where2=' '
45 END
46 ELSE
47 BEGIN--有查询条件 
48 SET @where1=' WHERE ('+@strWhere+') AND ' --本来有条件再加上此条件 
49 SET @where2=' WHERE ('+@strWhere+') ' --原本没有条件而加上此条件
50 END
51 --SET @sql='SELECT @intResult=COUNT(*) FROM '+@strTable+@where2
52 IF @intTop<=0
53 BEGIN
54 SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
55 END
56 ELSE
57 BEGIN
58 SET @sql='SELECT @sqlcount=COUNT(*) FROM (select top '+ cast(@intTop as varchar(max)) +' '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
59 END
60 --print @sql
61 
62 EXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --计算总记录数
63 SELECT @pageCount=CEILING((@sqlcount+0.0)/@pageSize) --计算总页数
64 SELECT @RecordCount = @sqlcount --设置总记录数
65 IF @pageIndex=1 --第一页
66 BEGIN
67 SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+' FROM '+@strTable+ 
68 @where2+'ORDER BY '+ @strSortField 
69 END
70 Else
71 BEGIN
72 IF @strOrderBy=0 
73 SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+ ' FROM '+@strTable+@where1+@strSortKey+'>(SELECT MAX('+@strSortKey+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(max))+' '+ 
74 @strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField
75 ELSE 
76 SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(max))+' '+@strField+' FROM '+@strTable+@where1+@strSortKey+'<(SELECT MIN('+@strSortKey+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(max))+' '+ 
77 @strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField+''
78 END
79 EXEC(@sql)
80 --print @sql
81 If @@Error <> 0
82 Begin
83 RollBack Tran
84 Return -1
85 End
86 Else
87 Begin
88 Commit TRAN
89 --set @UsedTime = datediff(ms,@timediff,getdate())
90 --select datediff(ms,@timediff,getdate()) as 耗时
91 Return @sqlcount
92 End

 

public IList<heyjob.Model.a_User> GetPage(int pageindex, int _pageSize, out int pageCount, out int RecordCount)
        {
            pageCount = 0;
            RecordCount = 0;
            IList<heyjob.Model.a_User> list = new List<heyjob.Model.a_User>();

            using (SqlConnection conn = new SqlConnection(PubConstant.ConnectionString))
            {
                SqlCommand objcmd = new SqlCommand(".ThePagerIndex", conn);
                objcmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] para ={ 
                new SqlParameter("@strTable",SqlDbType.VarChar,-1),
                new SqlParameter("@strField",SqlDbType.VarChar,-1),
                new SqlParameter("@pageSize",SqlDbType.Int),
                new SqlParameter("@pageIndex",SqlDbType.Int),
                new SqlParameter("@strSortKey",SqlDbType.VarChar,-1),
                new SqlParameter("@strSortField",SqlDbType.VarChar,-1),
                new SqlParameter("@strOrderBy",SqlDbType.Bit),
                new SqlParameter("@pageCount",SqlDbType.Int),
                new SqlParameter("@RecordCount",SqlDbType.Int),
                new SqlParameter("@inttop",SqlDbType.Int,-1)
            };

                para[0].Value = "a_User";
                para[1].Value = "*";
                para[2].Value = _pageSize;
                para[3].Value = pageindex;
                para[4].Value = "UserID";
                para[5].Value = "UserID desc";
                para[6].Value = 1;
                para[7].Value = pageCount;
                para[7].Direction = ParameterDirection.Output;
                para[8].Value = RecordCount;
                para[8].Direction = ParameterDirection.Output;
                para[9].Value = -1;
 
                objcmd.Parameters.AddRange(para);             
               
                conn.Open();
                using (SqlDataReader reader = objcmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        heyjob.Model.a_User model = new heyjob.Model.a_User();
                        model.UserID = Convert.ToInt32(reader["UserID"]);
                        model.LoginID = Convert.ToString(reader["LoginID"]);
                        model.Password = Convert.ToString(reader["Password"]);
                        list.Add(model);
                    }
                }

                RecordCount = Convert.ToInt32(objcmd.Parameters["@RecordCount"].Value);
                pageCount = Convert.ToInt32(objcmd.Parameters["@pageCount"].Value);
              
                conn.Close();
                conn.Dispose();
            }
            return list;
}

 

ASPX代码 
<div style="text-align: left">

        <div class="right_title">用户管理</div>
        <div class="right_title" style="text-align:left"><a>查找用户:<input id="Text1" type="text" value="输入登录名进行查找" onfocus='this.value=""' style="color:Gray" /></a>&nbsp;
            <asp:Button ID="Button1" runat="server" Text="查找" />
        </div>
        
        <div class="right_content">
            <ul class="right_ul">
                <asp:Repeater ID="Repeater1" runat="server">
                <HeaderTemplate>
                  <li>
                    <div>用户id</div>
                    <div>登录名</div>
                    <div>登录密码</div>     
                </li>
                </HeaderTemplate>
                    <ItemTemplate>
                        <li>
                            <div><%# Eval("UserID")%></div>
                            <div> <%# Eval("LoginID") %></div>
                            <div><%# Eval("Password") %></div>
                            <div><asp:Button ID="btnDel" runat="server" Text="删除" CommandArgument='<%# Eval("UserID")%>' CommandName="Del" /></div>
                        </li>
                    </ItemTemplate>
                </asp:Repeater>
            </ul>
            
             <div class="right_title" style="border-top:1px #808080 solid; text-align:left; padding-left:5px">
            <webdiyer:aspnetpager id="anp" runat="server" OnPageChanged="anp_PageChanged"  FirstPageText="首页" LastPageText="尾页" NextPageText="下一页" PrevPageText="上一页" ShowInputBox="Always" Font-Size="13px" ShowPageIndexBox="Never" PageSize="20"></webdiyer:aspnetpager>
            </div>
        </div>
    </div>

 

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 manage_usrlist : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind();
        }
    }

    private readonly heyjob.BLL.a_User bll = new heyjob.BLL.a_User();
    int currPage = 1;
    int PageSize = 10;
    
    public void Bind()
    {
        int pageCount,RecordCount;

        Repeater1.DataSource = bll.GetPage(currPage, PageSize, out pageCount, out RecordCount);
        Repeater1.DataBind();
        anp.RecordCount = RecordCount;
        anp.CurrentPageIndex = currPage;
        anp.PageSize = PageSize;
    }

    protected void anp_PageChanged(object sender, EventArgs e)
    {
        int pageCount, RecordCount;
        Repeater1.DataSource = bll.GetPage(anp.CurrentPageIndex, PageSize, out pageCount, out RecordCount);
        Repeater1.DataBind();
    }
}

 

文章转自 左右期限