分页存储过程的应用简单示例随笔（附所有代码）
需要的自己拿着去用起，很简单的分页存储过程示例！！！
一.在SQL SERVER中先创建一个数据库表：
1 CREATE TABLE [dbo].[zhq_content](
2 [content_id] [bigint] NOT NULL primary key identity,
3 [columns_id] [smallint] NOT NULL,
4 [title] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
5 [body] [text] COLLATE Chinese_PRC_CI_AS NULL,
6 [createdate] [datetime] NULL,
7 [modifieddate] [datetime] NULL,
8 [issuedate] [datetime] NULL
9 )
2 [content_id] [bigint] NOT NULL primary key identity,
3 [columns_id] [smallint] NOT NULL,
4 [title] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
5 [body] [text] COLLATE Chinese_PRC_CI_AS NULL,
6 [createdate] [datetime] NULL,
7 [modifieddate] [datetime] NULL,
8 [issuedate] [datetime] NULL
9 )
二，向表中随意插入数据
1 declare @id int
2 set @id=1
3
4 while(@id<360)
5 begin
6 INSERT INTO [ForeTechTest].[dbo].[zhq_contenttest]
7 ([columns_id]
8 ,[title]
9 ,[body]
10 ,[createdate]
11 ,[modifieddate]
12 ,[issuedate])
13 VALUES
14 (10
15 ,'title'+cast(@id as varchar)
16 ,'body'+cast(@id as varchar)
17 ,getdate()
18 ,getdate()
19 ,getdate());
20 set @id=@id+1;
21
22 end
23
2 set @id=1
3
4 while(@id<360)
5 begin
6 INSERT INTO [ForeTechTest].[dbo].[zhq_contenttest]
7 ([columns_id]
8 ,[title]
9 ,[body]
10 ,[createdate]
11 ,[modifieddate]
12 ,[issuedate])
13 VALUES
14 (10
15 ,'title'+cast(@id as varchar)
16 ,'body'+cast(@id as varchar)
17 ,getdate()
18 ,getdate()
19 ,getdate());
20 set @id=@id+1;
21
22 end
23
三.构建页面效果
1 <head runat="server">
2 <title>分页显示</title>
3 </head>
4 <body>
5 <form id="form1" runat="server">
6 <div>
7 <table><tr><td>
8 <ul>
9 <asp:Repeater EnableViewState="false" runat="server" ID="repeater">
10 <ItemTemplate>
11 <li> <%# DataBinder.Eval(Container, "DataItem.content_id")%> --- <%# DataBinder.Eval(Container, "DataItem.body").ToString()%> --- <%# DataBinder.Eval(Container, "DataItem.createdate").ToString()%></li>
12 </ItemTemplate>
13 </asp:Repeater>
14 </ul>
15 </td></tr></table>
16
17 <table><tr>
18 <td>
19 当前第<asp:Label ID="lblCurrentPage" runat="server" Text="Label"></asp:Label>页/总共<asp:Label
20 ID="lblPageCount" runat="server" Text="Label"></asp:Label>页/总共<asp:Label ID="lblCount"
21 runat="server" Text="Label"></asp:Label>条</td><td>
22 <input id="btnFirst" type="button" value="first" runat="server" onserverclick="btnFirst_ServerClick" /></td>
23 <td><input id="btnPriority" type="button" value="priority" runat="server" onserverclick="btnPriority_ServerClick" /></td>
24 <td><input id="btnNext" type="button" value="next" runat="server" onserverclick="btnNext_ServerClick" /></td>
25 <td><input id="btnLast" type="button" value="last" runat="server" onserverclick="btnLast_ServerClick" /></td>
26 </tr>
27 </table>
28 </div>
29 </form>
30 </body>
31 </html>
2 <title>分页显示</title>
3 </head>
4 <body>
5 <form id="form1" runat="server">
6 <div>
7 <table><tr><td>
8 <ul>
9 <asp:Repeater EnableViewState="false" runat="server" ID="repeater">
10 <ItemTemplate>
11 <li> <%# DataBinder.Eval(Container, "DataItem.content_id")%> --- <%# DataBinder.Eval(Container, "DataItem.body").ToString()%> --- <%# DataBinder.Eval(Container, "DataItem.createdate").ToString()%></li>
12 </ItemTemplate>
13 </asp:Repeater>
14 </ul>
15 </td></tr></table>
16
17 <table><tr>
18 <td>
19 当前第<asp:Label ID="lblCurrentPage" runat="server" Text="Label"></asp:Label>页/总共<asp:Label
20 ID="lblPageCount" runat="server" Text="Label"></asp:Label>页/总共<asp:Label ID="lblCount"
21 runat="server" Text="Label"></asp:Label>条</td><td>
22 <input id="btnFirst" type="button" value="first" runat="server" onserverclick="btnFirst_ServerClick" /></td>
23 <td><input id="btnPriority" type="button" value="priority" runat="server" onserverclick="btnPriority_ServerClick" /></td>
24 <td><input id="btnNext" type="button" value="next" runat="server" onserverclick="btnNext_ServerClick" /></td>
25 <td><input id="btnLast" type="button" value="last" runat="server" onserverclick="btnLast_ServerClick" /></td>
26 </tr>
27 </table>
28 </div>
29 </form>
30 </body>
31 </html>
四.别人写的二分制分页存储过程，不知道哪位大虾的，哈哈，拿过来用起，代码如下：
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
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
五。最主要的是编写后台代码，现在我把最简单的操作集成一下，写了我2个来小时，应该是没有BUG了的：
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
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
感觉速度还是相当快的..........如果要应用到其他的方面，修改下其他的参数即可。
显示如下：
*****************************************************************
为了安全通过控制
if (int.Parse(lblCurrentPage.Text.ToString()) <= 1)
{
pageIndex = 1;
}
else
{
pageIndex = int.Parse(lblCurrentPage.Text.ToString()) - 1;
}
和if (int.Parse(lblCurrentPage.Text.ToString()) >= pageCount)
{
pageIndex = pageCount;
}
else
{
pageIndex = int.Parse(lblCurrentPage.Text.ToString()) + 1;
}
控制范围不超出索引。
同样可以通过页面的生成过程来控制控件的显示状态：
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["pageIndex"] == null)
{
pageIndex = 1;
}
else
{
pageIndex = int.Parse(Request.QueryString["pageIndex"].ToString());
}
BindContent(out pageCount, out counts, 20, pageIndex);
lblPageCount.Text = pageCount.ToString();
lblCount.Text = counts.ToString();
lblCurrentPage.Text = pageIndex.ToString();
if (lblCurrentPage.Text.ToString() == pageCount.ToString())
{
btnNext.Visible = false;
}
else
{
btnNext.Visible = true;
}
if (lblCurrentPage.Text.ToString() == "1")
{
btnFirst.Visible = false;
}
else
{
btnFirst.Visible = true;
}
}
主要是需要掌握页面的生命周期，加以控制。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
