asp.net分页解决方法
2011-12-16 15:24 彩色超超 阅读(321) 评论(0) 编辑 收藏 举报之前一直使用一个同事提供的存储过程分页，只能按照标识列升降排列，不能按其他方式排序。后来分开了，他把他的存储过程改进了，而我的没有改进。虽然看了他的新存储过程有点不明白，使用时还会出现后几页分页数据减半的情况，我也懒得看了，因为他的存储过程写的太复杂了。于是自己百度好久终于找到一个可以使用的，学习了一个小时终于弄明白了，然后改进，放进项目中实例测试，最终于成功了。
现在可以通过任意分组，排序进行分页查询了。记录一下。
下存储过程适用于asp.net开发中使用aspnetpager等分页控件使用。
下面是存储过程代码，是我改进的，原作者已无法考证，在这里感谢他，感谢这位前辈。
1 set ANSI_NULLS ON
2 set QUOTED_IDENTIFIER ON
3 go
4
5
6
7 ALTER PROCEDURE [dbo].[Paging]
8 @TableNames VARCHAR(200), --表名，可以是多个表，但不能用别名
9 @PrimaryKey VARCHAR(100), --主键，可以为空，但@Order为空时该值不能为空
10 @Fields VARCHAR(200), --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
11 @PageSize INT, --每页记录数
12 @CurrentPage INT, --当前页，0表示第1页
13 @Filter VARCHAR(200) = '', --条件，可以为空，不用填 where
14 @Group VARCHAR(200) = '', --分组依据，可以为空，不用填 group by
15 @Order VARCHAR(200) = '', --排序，可以为空，为空默认按主键升序排列，不用填 order by
16 @Counts int output
17 AS
18 BEGIN
19 DECLARE @SortColumn VARCHAR(200)
20 DECLARE @Operator CHAR(2)
21 DECLARE @SortTable VARCHAR(200)
22 DECLARE @SortName VARCHAR(200)
23 IF @Fields = ''
24 SET @Fields = '*'
25 IF @Filter = ''
26 SET @Filter = ' WHERE 1=1 '
27 ELSE
28 SET @Filter = ' WHERE ' + @Filter
29 IF @Group <>''
30 SET @Group = ' GROUP BY ' + @Group
31
32 IF @Order <> ''
33 BEGIN
34 DECLARE @pos1 INT, @pos2 INT
35 SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
36 IF CHARINDEX(' DESC', @Order) > 0
37 IF CHARINDEX(' ASC', @Order) > 0
38 BEGIN
39 IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
40 SET @Operator = '<='
41 ELSE
42 SET @Operator = '>='
43 END
44 ELSE
45 SET @Operator = '<='
46 ELSE
47 SET @Operator = '>='
48 SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
49 SET @pos1 = CHARINDEX(',', @SortColumn)
50 IF @pos1 > 0
51 SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
52 SET @pos2 = CHARINDEX('.', @SortColumn)
53 IF @pos2 > 0
54 BEGIN
55 SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
56 IF @pos1 > 0
57 SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
58 ELSE
59 SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
60 END
61 ELSE
62 BEGIN
63 SET @SortTable = @TableNames
64 SET @SortName = @SortColumn
65 END
66 END
67 ELSE
68 BEGIN
69 SET @SortColumn = @PrimaryKey
70 SET @SortTable = @TableNames
71 SET @SortName = @SortColumn
72 SET @Order = @SortColumn
73 SET @Operator = '>='
74 END
75
76
77 DECLARE @type varchar(50)
78 DECLARE @prec int
79 SELECT @type=t.name, @prec=c.prec
80 FROM sysobjects o
81 JOIN syscolumns c on o.id=c.id
82 JOIN systypes t on c.xusertype=t.xusertype
83 WHERE o.name = @SortTable AND c.name = @SortName
84
85 IF CHARINDEX('char', @type) > 0
86 SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
87
88
89 DECLARE @TopRows INT
90 SET @TopRows = @PageSize * @CurrentPage + 1
91
92 PRINT @type
93 DECLARE @sql NVARCHAR(4000)
94
95 SET @Sql = 'DECLARE @SortColumnBegin ' + @type + '
96 SET ROWCOUNT ' + Cast(@TopRows as VARCHAR(10))+ ' SELECT @SortColumnBegin=' +
97 @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
98 SET ROWCOUNT ' + CAST(@PageSize AS VARCHAR(10)) + '
99 SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + ISNULL(@Group,'') + ' ORDER BY ' + @Order + ''
100
101 declare @sql2 nvarchar(500)
102 set @sql2= ' select @Counts=count(1) from '+ @TableNames + ' ' + @Filter
103 exec sp_executesql @sql2,N'@Counts int out ',@Counts out
104
105
106 -- Print(@sql)
107 Exec(@sql)
108
109
110 END
