分页原来可以如此简单：
一、分页存储过程
1
/**//*
2
declare @P1 int,@p2 int
3set @P1=NULL
4exec sp_PageView @tbname = N'sim_NoteBook', @FieldKey = N'Id', @PageCurrent = 3, @PageSize = 10, @FieldShow = N'', @FieldOrder = N'Id', @Where = N'', @ItemCount=@p2 output,@PageCount = @P1 output
5select @P1
6select @p2
7
8exec sp_PageView 'sim_NoteBook', 'Id',@ItemCount=NULL,@PageCount=NULL
9
10
11exec sp_PageView @tbname = N'sim_NoteBook', @FieldKey = N'Id', @PageCurrent = 1, @PageSize = 10, @FieldShow = N'', @FieldOrder = N'Id Desc', @Where = N'', @ItemCount = 0, @PageCount = 0
12
*/
13
CREATE PROC sp_PageView
14@tbname sysname, --要分页显示的表名
15@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
16@PageCurrent int=1, --要显示的页码
17@PageSize int=10, --每页的大小(记录数)
18@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
19@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC,用于指定排序顺序
20@Where nvarchar(1000)='', --查询条件
21@ItemCount int OUTPUT,
22@PageCount int OUTPUT --总页数
23AS
24SET NOCOUNT ON
25--检查对象是否有效
26IF OBJECT_ID(@tbname) IS NULL
27BEGIN
28 RAISERROR(N'对象"%s"不存在',1,16,@tbname)
29 RETURN
30END
31IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
32 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
33 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
34BEGIN
35 RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
36 RETURN
37END
38--分页字段检查
39IF ISNULL(@FieldKey,N'')=''
40BEGIN
41 RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
42 RETURN
43END
44--其他参数检查及规范
45IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
46IF ISNULL(@PageSize,0)<1 SET @PageSize=10
47IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
48IF ISNULL(@FieldOrder,N'')=N''
49 SET @FieldOrder=N''
50ELSE
51 SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
52IF ISNULL(@Where,N'')=N''
53 SET @Where=N''
54ELSE
55 SET @Where=N'WHERE ('+@Where+N')'
56--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
57
58 DECLARE @sql nvarchar(4000)
59 SET @sql=N'SELECT @ItemCount=COUNT(*)'
60 +N' FROM '+@tbname
61 +N' '+@Where
62 EXEC sp_executesql @sql,N'@ItemCount int OUTPUT',@ItemCount OUTPUT
63 SET @PageCount=(@ItemCount+@PageSize-1)/@PageSize
64
65print @PageCount
66print @ItemCount
67--计算分页显示的TOPN值
68DECLARE @TopN varchar(20),@TopN1 varchar(20)
69SELECT @TopN=@PageSize,
70 @TopN1=(@PageCurrent-1)*@PageSize
71--第一页直接显示
72IF @PageCurrent=1
73 EXEC(N'SELECT TOP '+@TopN
74 +N' '+@FieldShow
75 +N' FROM '+@tbname
76 +N' '+@Where
77 +N' '+@FieldOrder)
78ELSE
79BEGIN
80 --处理别名
81 IF @FieldShow=N'*'
82 SET @FieldShow=N'a.*'
83 --生成主键(惟一键)处理条件
84 DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),
85 @s nvarchar(1000),@Field sysname
86 SELECT @Where1=N'',@Where2=N'',@s=@FieldKey
87 WHILE CHARINDEX(N',',@s)>0
88 SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),
89 @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
90 @Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,
91 @Where2=@Where2+N' AND b.'+@Field+N' IS NULL',
92 --@Where=REPLACE(@Where,@Field,N'a.'+@Field),
93 @FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),
94 @FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)
95 SELECT --@Where=REPLACE(@Where,@s,N'a.'+@s),
96 @FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),
97 @FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),
98 @Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),
99 @Where2=CASE
100 WHEN @Where='' THEN N'WHERE ('
101 ELSE @Where+N' AND ('
102 END+N'b.'+@s+N' IS NULL'+@Where2+N')'
103 DECLARE @QUERYSTRING varchar(2000)
104 SET @QUERYSTRING = N'SELECT TOP '+@TopN
105 +N' '+@FieldShow
106 +N' FROM '+@tbname
107 +N' a LEFT JOIN(SELECT TOP '+@TopN1
108 +N' '+@FieldKey
109 +N' FROM '+@tbname
110 +N' a '+@Where
111 +N' '+@FieldOrder
112 +N')b ON '+@Where1
113 +N' '+@Where2
114 +N' '+@FieldOrder
115 PRINT @QUERYSTRING
116 --执行查询
117 EXEC(@QUERYSTRING)
118
119
120END
121GO
122
二、需要分页的用户控件基类（里面还有一些信息提示的功能）：
1using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Web.UI;
5using System.Web.UI.WebControls;
6using Wuqi.Webdiyer;
7
8namespace Foundway.Project.Web
9
{
10 public class UserControlBase : UserControl
11
{
12 protected override void OnLoad(EventArgs e)
13
{
14 base.OnLoad(e);
15
16 Label labelMsg = this.FindControl("labelMsg") as Label;
17 if (labelMsg != null)
18 {
19 labelMsg.Visible = false;
20
}
21 }
22
23 protected void ShowMessage(string message)
24 {
25 Label labelMsg = this.FindControl("labelMsg") as Label;
26 if (labelMsg != null)
27 {
28 labelMsg.Visible = true;
29 labelMsg.Text = message;
30 }
31 }
32
33 protected void ShowMessage(string message, Exception exp)
34 {
35 Label labelMsg = this.FindControl("labelMsg") as Label;
36 if (labelMsg != null)
37 {
38 labelMsg.Visible = true;
39 labelMsg.Text = message + "。详情为：" + exp.Message;
40 }
41 }
42
43 protected void AlertMessage(string message)
44 {
45 Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", String.Format(@"alert(""{0}"");", message), true);
46 }
47
48 protected void AlertMessage(string message, string redirecturl)
49 {
50 AlertMessage(message, redirecturl, false);
51 }
52
53 protected void AlertMessage(string message, string redirecturl, bool canBack)
54 {
55 Response.Clear();
56
57 if (canBack)
58 Response.Write(String.Format(@"<script>alert(""{0}"");location.href='{1}';</script>", message, redirecturl));
59 else
60 Response.Write(String.Format(@"<script>alert(""{0}"");location.replace('{1}');</script>", message, redirecturl));
61
62 Response.End();
63 }
64 }
65
66 public class ListUserControlBase : UserControlBase
67 {
68 // Fields
69 protected int ItemCount;
70
71 // Properties
72 public string OrderBy
73 {
74 get
75 {
76 if (this.ViewState["__OrderBy"] == null)
77 {
78 this.ViewState["__OrderBy"] = "";
79 }
80 return this.ViewState["__OrderBy"].ToString();
81 }
82 set
83 {
84 this.ViewState["__OrderBy"] = value;
85 }
86 }
87
88 public string Where
89 {
90 get
91 {
92 if (this.ViewState["__Where"] == null)
93 {
94 this.ViewState["__Where"] = "1=1";
95 }
96 return this.ViewState["__Where"].ToString();
97 }
98 set
99 {
100 this.ViewState["__Where"] = value;
101 }
102 }
103 }
104
105 public class PageUserControlBase : ListUserControlBase
106 {
107 // Fields
108 protected int PageCount;
109 protected AspNetPager pagerBottom;
110 protected AspNetPager pagerTop;
111
112 // Methods
113 protected virtual void BindData(int pageIndex)
114 {
115 this.PageIndex = pageIndex;
116 if (this