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

<asp:Button ID="Button1" runat="server" Text="查找" />

</div>

<asp:Repeater ID="Repeater1" runat="server">

<li>

</li>

</HeaderTemplate>

<li>

<div><asp:Button ID="btnDel" runat="server" Text="删除" CommandArgument='<%# Eval("UserID")%>' CommandName="Del" /></div>

</li>

</ItemTemplate>

</asp:Repeater>

</ul>

<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>

aspx.cs代码

刘季宗 - heyjob.net -it560.com

理想:成为一名.net架构师

公告