分页存储过程的应用简单示例随笔（附所有代码）

  1   set  ANSI_NULLS ON
  2   set  QUOTED_IDENTIFIER ON
  3   go
  4  
  5  
  6  
  7   ALTER PROCEDURE [dbo].[ProcPagedSelectMax] 
  8    ( 
  9     @tblName     nvarchar( 200 ),         ---- 要显示的表或多个表的连接 
 10     @fldName     nvarchar( 500 )  =   ' * ' ,     ---- 要显示的字段列表 
 11     @pageSize     int   =   10 ,         ---- 每页显示的记录个数 
 12    @page         int   =   1 ,         ---- 要显示那一页的记录 
 13    @fldSort    nvarchar( 200 )  =   null ,     ---- 排序字段列表或条件 
 14    @Sort        bit  =   0 ,         ---- 排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记) -- 程序传参如： '  SortA Asc,SortB Desc,SortC  ' ) 
 15    @strCondition    nvarchar( 1000 )  =   null ,     ---- 查询条件,不需where 
 16    @ID        nvarchar( 150 ),         ---- 主表的主键 
 17    @Dist                 bit  =   0 ,            ---- 是否添加查询字段的 DISTINCT 默认0不添加 / 1添加 
 18    @pageCount     int   =   1  output,             ---- 查询结果分页后的总页数 
 19    @Counts     int   =   1  output                 ---- 查询到的记录数 
 20    ) 
 21    AS 
 22    SET NOCOUNT ON 
 23    Declare @sqlTmp nvarchar( 1000 )         ---- 存放动态生成的SQL语句 
 24    Declare @strTmp nvarchar( 1000 )         ---- 存放取得查询结果总数的查询语句 
 25    Declare @strID     nvarchar( 1000 )         ---- 存放取得查询开头或结尾ID的查询语句 
 26    
 27    Declare @strSortType nvarchar( 10 )     ---- 数据排序规则A 
 28    Declare @strFSortType nvarchar( 10 )     ---- 数据排序规则B 
 29    
 30    Declare @SqlSelect nvarchar( 50 )          ---- 对含有DISTINCT的查询进行SQL构造 
 31    Declare @SqlCounts nvarchar( 50 )           ---- 对含有DISTINCT的总数查询进行SQL构造 
 32    
 33    declare @timediff datetime   -- 耗时测试时间差 
 34    select @timediff = getdate() 
 35    
 36     if  @Dist   =   0  
 37    begin 
 38         set  @SqlSelect  =   ' select  '  
 39         set  @SqlCounts  =   ' Count(*) '  
 40    end 
 41     else  
 42    begin 
 43         set  @SqlSelect  =   ' select distinct  '  
 44         set  @SqlCounts  =   ' Count(DISTINCT  ' + @ID + ' ) '  
 45    end 
 46    
 47     if  @Sort = 0  
 48    begin 
 49         set  @strFSortType = '  ASC  '  
 50         set  @strSortType = '  DESC  '  
 51    end 
 52     else  
 53    begin 
 54         set  @strFSortType = '  DESC  '  
 55         set  @strSortType = '  ASC  '  
 56    end 
 57  
 58     -------- 生成查询语句 --------  
 59     -- 此处@strTmp为取得查询结果数量的语句 
 60     if  @strCondition  is   null  or @strCondition = ''       -- 没有设置显示条件 
 61    begin 
 62         set  @sqlTmp  =   @fldName  +   '  From  '   +  @tblName 
 63         set  @strTmp  =  @SqlSelect + '  @Counts= ' + @SqlCounts + '  FROM  ' + @tblName 
 64         set  @strID  =   '  From  '   +  @tblName 
 65    end 
 66     else  
 67    begin 
 68         set  @sqlTmp  =   +  @fldName  +   ' From  '   +  @tblName  +   '  where (1>0)  '   +  @strCondition 
 69         set  @strTmp  =  @SqlSelect + '  @Counts= ' + @SqlCounts + '  FROM  ' + @tblName  +   '  where (1>0)  '   +  @strCondition 
 70         set  @strID  =   '  From  '   +  @tblName  +   '  where (1>0)  '   +  @strCondition 
 71    end 
 72    
 73     ---- 取得查询结果总数量 -----  
 74    exec sp_executesql @strTmp,N ' @Counts int out  ' ,@Counts  out  
 75    declare @tmpCounts  int  
 76     if  @Counts  =   0  
 77         set  @tmpCounts  =   1  
 78     else  
 79         set  @tmpCounts  =  @Counts 
 80    
 81         -- 取得分页总数 
 82         set  @pageCount = (@tmpCounts + @pageSize - 1 ) / @pageSize 
 83    
 84        /**//**//**//* *当前页大于总页数 取最后一页* */  
 85         if  @page > @pageCount 
 86             set  @page = @pageCount 
 87    
 88         -- /* -----数据分页2分处理------- */  
 89        declare @pageIndex  int   -- 总数 / 页大小 
 90        declare @lastcount  int   -- 总数 % 页大小  
 91    
 92         set  @pageIndex  =  @tmpCounts / @pageSize 
 93         set  @lastcount  =  @tmpCounts % @pageSize 
 94         if  @lastcount  >   0  
 95             set  @pageIndex  =  @pageIndex  +   1  
 96         else  
 97             set  @lastcount  =  @pagesize 
 98    
 99        -- // ***显示分页 
100        if  @strCondition  is   null  or @strCondition = ''       -- 没有设置显示条件 
101       begin 
102            if  @pageIndex < 2  or @page <= @pageIndex  /   2   +  @pageIndex  %   2     -- 前半部分数据处理 
103               begin  
104                    if  @page = 1  
105                       set  @strTmp = @SqlSelect + '  top  ' +  CAST(@pageSize  as  VARCHAR( 4 )) + '   ' +  @fldName + '  from  ' + @tblName                         
106                            + '  order by  ' +  @fldSort  + '   ' +  @strFSortType 
107                    else  
108                   begin 
109                        if  @Sort = 1  
110                       begin                     
111                        set  @strTmp = @SqlSelect + '  top  ' +  CAST(@pageSize  as  VARCHAR( 4 )) + '   ' +  @fldName + '  from  ' + @tblName 
112                            + '  where  ' + @ID + '  <(select min( ' +  @ID  + ' ) from ( ' +  @SqlSelect + '  top  ' +  CAST(@pageSize * (@page - 1 )  as  Varchar( 20 ))  + '   ' +  @ID  + '  from  ' + @tblName 
113                            + '  order by  ' +  @fldSort  + '   ' +  @strFSortType + ' ) AS TBMinID) '  
114                            + '  order by  ' +  @fldSort  + '   ' +  @strFSortType 
115                      end 
116                        else  
117                       begin 
118                        set  @strTmp = @SqlSelect + '  top  ' +  CAST(@pageSize  as  VARCHAR( 4 )) + '   ' +  @fldName + '  from  ' + @tblName 
119                            + '  where  ' + @ID + '  >(select max( ' +  @ID  + ' ) from ( ' +  @SqlSelect + '  top  ' +  CAST(@pageSize * (@page - 1 )  as  Varchar( 20 ))  + '   ' +  @ID  + '  from  ' + @tblName 
120                           + '  order by  ' +  @fldSort  + '   ' +  @strFSortType + ' ) AS TBMinID) '  
121                            + '  order by  ' +  @fldSort  + '   ' +  @strFSortType  
122                       end 
123                   end     
124               end 
125            else  
126               begin 
127                set  @page  =  @pageIndex - @page + 1   -- 后半部分数据处理 
128                    if  @page  <=   1   -- 最后一页数据显示                 
129                        set  @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +  CAST(@lastcount  as  VARCHAR( 4 )) + '   ' +  @fldName + '  from  ' + @tblName 
130                            + '  order by  ' +  @fldSort  + '   ' +  @strSortType + ' ) AS TempTB ' + '  order by  ' +  @fldSort  + '   ' +  @strFSortType  
131                    else  
132                        if  @Sort = 1  
133                       begin 
134                        set  @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +  CAST(@pageSize  as  VARCHAR( 4 )) + '   ' +  @fldName + '  from  ' + @tblName 
135                            + '  where  ' + @ID + '  >(select max( ' +  @ID  + ' ) from( ' +  @SqlSelect + '  top  ' +  CAST(@pageSize * (@page - 2 ) + @lastcount  as  Varchar( 20 ))  + '   ' +  @ID  + '  from  ' + @tblName 
136                            + '  order by  ' +  @fldSort  + '   ' +  @strSortType + ' ) AS TBMaxID) '  
137                            + '  order by  ' +  @fldSort  + '   ' +  @strSortType + ' ) AS TempTB ' + '  order by  ' +  @fldSort  + '   ' +  @strFSortType 
138                       end 
139                        else  
140                       begin 
141                        set  @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +  CAST(@pageSize  as  VARCHAR( 4 )) + '   ' +  @fldName + '  from  ' + @tblName 
142                            + '  where  ' + @ID + '  <(select min( ' +  @ID  + ' ) from( ' +  @SqlSelect + '  top  ' +  CAST(@pageSize * (@page - 2 ) + @lastcount  as  Varchar( 20 ))  + '   ' +  @ID  + '  from  ' + @tblName 
143                            + '  order by  ' +  @fldSort  + '   ' +  @strSortType + ' ) AS TBMaxID) '  
144                            + '  order by  ' +  @fldSort  + '   ' +  @strSortType + ' ) AS TempTB ' + '  order by  ' +  @fldSort  + '   ' +  @strFSortType  
145                       end 
146               end 
147       end 
148  
149        else   -- 有查询条件 
150       begin 
151            if  @pageIndex < 2  or @page <= @pageIndex  /   2   +  @pageIndex  %   2     -- 前半部分数据处理 
152           begin 
153                    if  @page = 1  
154                        set  @strTmp = @SqlSelect + '  top  ' +  CAST(@pageSize  as  VARCHAR( 4 )) + '   ' +  @fldName + '  from  ' + @tblName                         
155                            + '  where 1=1  '   +  @strCondition  +   '  order by  ' +  @fldSort  + '   ' +  @strFSortType 
156                    else   if (@Sort = 1 ) 
157                   begin                     
158                        set  @strTmp = @SqlSelect + '  top  ' +  CAST(@pageSize  as  VARCHAR( 4 )) + '   ' +  @fldName + '  from  ' + @tblName 
159                            + '  where  ' + @ID + '  <(select min( ' +  @ID  + ' ) from ( ' +  @SqlSelect + '  top  ' +  CAST(@pageSize * (@page - 1 )  as  Varchar( 20 ))  + '   ' +  @ID  + '  from  ' + @tblName 
160                            + '  where (1=1)  '   +  @strCondition  + '  order by  ' +  @fldSort  + '   ' +  @strFSortType + ' ) AS TBMinID) '  
161                            + '   ' +  @strCondition  + '  order by  ' +  @fldSort  + '   ' +  @strFSortType 
162                   end 
163                    else  
164                   begin 
165                        set  @strTmp = @SqlSelect + '  top  ' +  CAST(@pageSize  as  VARCHAR( 4 )) + '   ' +  @fldName + '  from  ' + @tblName 
166                            + '  where  ' + @ID + '  >(select max( ' +  @ID  + ' ) from ( ' +  @SqlSelect + '  top  ' +  CAST(@pageSize * (@page - 1 )  as  Varchar( 20 ))  + '   ' +  @ID  + '  from  ' + @tblName 
167                            + '  where (1=1)  '   +  @strCondition  + '  order by  ' +  @fldSort  + '   ' +  @strFSortType + ' ) AS TBMinID) '  
168                            + '   ' +  @strCondition  + '  order by  ' +  @fldSort  + '   ' +  @strFSortType  
169                   end            
170           end 
171            else  
172           begin  
173                set  @page  =  @pageIndex - @page + 1   -- 后半部分数据处理 
174                if  @page  <=   1   -- 最后一页数据显示 
175                        set  @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +  CAST(@lastcount  as  VARCHAR( 4 )) + '   ' +  @fldName + '  from  ' + @tblName 
176                            + '  where (1=1)  ' +  @strCondition  + '  order by  ' +  @fldSort  + '   ' +  @strSortType + ' ) AS TempTB ' + '  order by  ' +  @fldSort  + '   ' +  @strFSortType                      
177                else   if (@Sort = 1 ) 
178                        set  @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +  CAST(@pageSize  as  VARCHAR( 4 )) + '   ' +  @fldName + '  from  ' + @tblName 
179                            + '  where  ' + @ID + '  >(select max( ' +  @ID  + ' ) from( ' +  @SqlSelect + '  top  ' +  CAST(@pageSize * (@page - 2 ) + @lastcount  as  Varchar( 20 ))  + '   ' +  @ID  + '  from  ' + @tblName 
180                            + '  where (1=1)  ' +  @strCondition  + '  order by  ' +  @fldSort  + '   ' +  @strSortType + ' ) AS TBMaxID) '  
181                            + '   ' +  @strCondition + '  order by  ' +  @fldSort  + '   ' +  @strSortType + ' ) AS TempTB ' + '  order by  ' +  @fldSort  + '   ' +  @strFSortType     
182                else  
183                        set  @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +  CAST(@pageSize  as  VARCHAR( 4 )) + '   ' +  @fldName + '  from  ' + @tblName 
184                            + '  where  ' + @ID + '  <(select min( ' +  @ID  + ' ) from( ' +  @SqlSelect + '  top  ' +  CAST(@pageSize * (@page - 2 ) + @lastcount  as  Varchar( 20 ))  + '   ' +  @ID  + '  from  ' + @tblName 
185                            + '  where (1=1)  ' +  @strCondition  + '  order by  ' +  @fldSort  + '   ' +  @strSortType + ' ) AS TBMaxID) '  
186                            + '   ' +  @strCondition + '  order by  ' +  @fldSort  + '   ' +  @strSortType + ' ) AS TempTB ' + '  order by  ' +  @fldSort  + '   ' +  @strFSortType             
187           end     
188       end
189   print (@strTmp)
190   ------ 返回查询结果 -----  
191   exec sp_executesql @strTmp 
192   select datediff(ms,@timediff,getdate())  as  耗时 
193   -- print @strTmp 
194   SET NOCOUNT OFF 
195  
196  
197  
198  
199  
200  

  1   using  System;
  2   using  System.Data;
  3   using  System.Configuration;
  4   using  System.Collections;
  5   using  System.Web;
  6   using  System.Web.Security;
  7   using  System.Web.UI;
  8   using  System.Web.UI.WebControls;
  9   using  System.Web.UI.WebControls.WebParts;
 10   using  System.Web.UI.HtmlControls;
 11  
 12   using  System.Data.SqlClient;
 13  
 14   public   partial   class  PageSort_Default : System.Web.UI.Page
 15   {
 16        protected   int  pageCount;
 17        protected   int  counts;
 18        protected   int  pageIndex;
 19        protected   void  Page_Load( object  sender, EventArgs e)
 20       {
 21            if  (Request.QueryString[ " pageIndex " ]  ==   null )
 22           {
 23               pageIndex  =   1 ;
 24           }
 25            else
 26           {
 27               pageIndex  =   int .Parse(Request.QueryString[ " pageIndex " ].ToString());
 28           }
 29  
 30           BindContent( out  pageCount,  out  counts,  20 , pageIndex);
 31           lblPageCount.Text  =  pageCount.ToString();
 32           lblCount.Text  =  counts.ToString();
 33           lblCurrentPage.Text  =  pageIndex.ToString();
 34       }
 35  
 36        private   void  BindContent( out   int  pageCount, out   int  counts, int  pageSize, int  pageIndex )
 37       {
 38           DataSet ds  =  BindContent( " zhq_contenttest " , " * " ,pageSize,pageIndex, " and  columns_id=10 " , " content_id " , 1 , " content_id " , 0 , out  pageCount , out  counts);
 39            if (ds != null && ds.Tables[ 0 ].Rows.Count > 0 )
 40           {
 41               repeater.DataSource  =  ds;
 42               repeater.DataBind();
 43           }
 44       }
 45  
 46        private  DataSet BindContent( string  tbName,  string  filedName,  int  pageSize,  int  pageIndex,  string  condition,  string  sortedField,  int  sortType,  string  id,  int  distinctType,  out   int  pageCount,  out   int  count)
 47       {
 48           DataSet ds;
 49           SqlConnection conn;
 50            using  (conn  =   new  SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[ " foretechtest " ].ToString()))
 51           {
 52               SqlParameter[] paras  =   new  SqlParameter[ 11 ];
 53               paras[ 0 ]  =   new  SqlParameter( " @tblName " , SqlDbType.NVarChar,  200 );
 54               paras[ 0 ].Value  =  tbName;
 55               paras[ 1 ]  =   new  SqlParameter( " @fldName " , SqlDbType.NVarChar,  500 );
 56               paras[ 1 ].Value  =  filedName;
 57               paras[ 2 ]  =   new  SqlParameter( " @pageSize " , SqlDbType.Int);
 58               paras[ 2 ].Value  =  pageSize;
 59               paras[ 3 ]  =   new  SqlParameter( " @page " , SqlDbType.Int);
 60               paras[ 3 ].Value  =  pageIndex;
 61  
 62               paras[ 4 ]  =   new  SqlParameter( " @strCondition " , SqlDbType.NVarChar,  1000 );
 63               paras[ 4 ].Value  =  condition;
 64               paras[ 5 ]  =   new  SqlParameter( " @fldSort " , SqlDbType.NVarChar,  200 );
 65               paras[ 5 ].Value  =  sortedField;
 66               paras[ 6 ]  =   new  SqlParameter( " @Sort " , SqlDbType.Bit);
 67               paras[ 6 ].Value  =  sortType;
 68  
 69               paras[ 7 ]  =   new  SqlParameter( " @ID " , SqlDbType.NVarChar,  150 );
 70               paras[ 7 ].Value  =  id;
 71               paras[ 8 ]  =   new  SqlParameter( " @Dist " , SqlDbType.Bit);
 72               paras[ 8 ].Value  =  distinctType;
 73               paras[ 9 ]  =   new  SqlParameter( " @pageCount " , SqlDbType.Int);
 74               paras[ 9 ].Direction  =  ParameterDirection.Output;
 75               paras[ 10 ]  =   new  SqlParameter( " @Counts " , SqlDbType.Int);
 76               paras[ 10 ].Direction  =  ParameterDirection.Output;
 77                try
 78               {
 79                   conn.Open();
 80                   ds  =   new  DataSet();
 81                   SqlDataAdapter da  =   new  SqlDataAdapter( " ProcPagedSelectMax " , conn);
 82                   da.SelectCommand.CommandType  =  CommandType.StoredProcedure;
 83                    foreach (SqlParameter para  in  paras)
 84                   {
 85                       da.SelectCommand.Parameters.Add(para);
 86                   }
 87                   da.Fill(ds);
 88  
 89               }
 90                finally
 91               {
 92                   conn.Close();
 93                   pageCount  =   int .Parse(paras[ 9 ].Value.ToString());
 94                   count  =   int .Parse(paras[ 10 ].Value.ToString());
 95               }
 96                return  ds;
 97  
 98           }
 99            /*
100            * @tblName = N' zhq_contenttest',
101           @fldName = N'*',
102           @pageSize = 20,
103           @page = 5,
104           @strCondition=N' and  columns_id=11  ',
105           @fldSort = N' createdate ',
106           @Sort = 1,
107           @ID = N'content_id ',
108           @Dist = 0,
109           @pageCount = @pageCount OUTPUT,
110           @Counts = @Counts OUTPUT
111  
112   SELECT    @pageCount as N'@pageCount',
113           @Counts as N'@Counts' */
114       }
115  
116  
117        protected   void  btnPriority_ServerClick( object  sender, EventArgs e)
118       {
119            if  ( int .Parse(lblCurrentPage.Text.ToString())  <=   1 )
120           {//control this range  pageindex  >//=1
121               pageIndex  =   1 ;
122           }
123            else  
124           {//
125               pageIndex  =   int .Parse(lblCurrentPage.Text.ToString())  -   1 ;
126           }
127           Response.Redirect( " Default.aspx?pageIndex= "   + pageIndex);
128       }
129        protected   void  btnNext_ServerClick( object  sender, EventArgs e)
130       {
131            if  ( int .Parse(lblCurrentPage.Text.ToString())  >=  pageCount)
132           {
133               pageIndex  =  pageCount;
134           }
135            else  
136           {
137               pageIndex  =   int .Parse(lblCurrentPage.Text.ToString())  +   1 ;
138           }
139           Response.Redirect( " Default.aspx?pageIndex= "   +  pageIndex);
140       }
141        protected   void  btnLast_ServerClick( object  sender, EventArgs e)
142       {
143           Response.Redirect( " Default.aspx?pageIndex= " + lblPageCount.Text.ToString().Trim());
144       }
145        protected   void  btnFirst_ServerClick( object  sender, EventArgs e)
146       {
147           Response.Redirect( " Default.aspx?pageIndex=1 " );
148       }
149   }
150