千万级分页存储过程结合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
93
94

 

 

数据访问层

  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 >

 

 

 

aspx.cs代码

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();
    }
}

 

代码比较全了,大家可以测试一下就知道了!

 

实体类就不用贴了吧 - -#

 

欢迎大家拍砖!

转载于:https://www.cnblogs.com/it560/archive/2009/11/30/1613410.html