这几天有朋友问起数据分页的问题,再加群(Coolite群1925495)中朋友提起分页。我也想把自己整理的数据分页存储拿出来,供大家指正下。
思路:在读取数据表中的数据时无论读取多少行记录。我们最常用的是读取top N 到 topN+pageSize (通常按ID desc来取)。但分页有正序与倒序之分,具体怎么处理?先将代码粘出来,接下来再做详细说明。
Code
1![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*********************************************************************************
2
* *
3
* Function: SuperPaging *
4
* Description: *
5
* 超强通用分页存储过程 *
6
* Author: *
7
* 秦彦杰 *
8
* 360362530@qq.com *
9
* Finish DateTime: *
10
* 2009年9月24日 *
11
*
12
* Example: *
13
* SuperPaging @TableName='表名',@Orderfld='排序列名' *
14
*********************************************************************************/
15
CREATE PROCEDURE SupesoftPage_NumOut
16
(
17
@RecordCount NVARCHAR(50) OUTPUT,----总页数
18
@TableName NVARCHAR(50), -- 表名
19
@ReturnFields NVARCHAR(2000) = '*', -- 需要返回的列
20
@PageSize INT = 10, -- 每页记录数
21
@PageNum INT = 0, -- 当前页码数
22
@strWhere NVARCHAR(2000) = '', -- 查询条件
23
@Orderfld NVARCHAR(2000), -- 排序字段名 最好为唯一主键
24
@OrderType INT = 1 -- 排序类型 1:降序 其它为升序
25
)
26
AS
27
DECLARE @TotalRecord INT,
28
@TotalPage INT,
29
@CurrentPageSize INT,
30
@TotalRecordForPageIndex INT,
31
@OrderBy NVARCHAR(255),
32
@CutOrderBy NVARCHAR(255),
33
@_Sql NVARCHAR(4000),
34
@_where NVARCHAR(4000)
35
36
37
IF ( @strWhere = '' )
38
BEGIN
39
SET @_where = ' where 1=1'
40
END
41
ELSE
42
BEGIN
43
SET @_where = ' where 1=1 ' + @strWhere
44
END
45
if(@PageSize=-1) set @PageSize=0
46
if(@pageNum=-1) set @pageNum=0
47
IF @OrderType = 1
48
BEGIN
49
SET @OrderBy = ' Order by ' + @Orderfld + ' desc '
50
SET @CutOrderBy = ' Order by ' + @Orderfld + ' asc '
51
END
52
ELSE
53
BEGIN
54
SET @OrderBy = ' Order by ' + @Orderfld + ' asc '
55
SET @CutOrderBy = ' Order by ' + @Orderfld + ' desc '
56
END
57
58
59
-- 记录总数
60
DECLARE @countSql NVARCHAR(4000)
61
SET @countSql = 'SELECT @TotalRecord=Count(*) From ' + @TableName + ' '
62
+ @_where
63
EXECUTE sp_executesql @countSql, N'@TotalRecord int out', @TotalRecord OUT
64
65![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
66
SELECT @RecordCount = @TotalRecord
67
PRINT ( @countSql )
68
-- 返回总页数和总记录数
69![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
70
PRINT ( @RecordCount )
71![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
72
IF ( @OrderType = 1 ) --倒序
73
BEGIN
74
SET @_Sql = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR) + ' '
75
+ @Orderfld + '
76
FROM ' + @TableName + '
77
WHERE ' + @Orderfld + ' <
78
(
79
SELECT ISNULL( MIN(a.' + @Orderfld + '),0)
80
FROM
81
(SELECT TOP ' + CAST(@PageNum AS NVARCHAR) + ' ' + @Orderfld
82
+ ' FROM ' + @TableName + ' ' + @_where + ' ' + @OrderBy
83
+ ' ) A
84
) ' + @strWhere + '
85
' + @OrderBy
86
END
87
ELSE
88
BEGIN
89
SET @_Sql = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR) + ' '
90
+ @Orderfld + '
91
FROM ' + @TableName + '
92
WHERE ' + @Orderfld + ' >
93
(
94
SELECT ISNULL(MAX(a.' + @Orderfld + '),0)
95
FROM
96
(SELECT TOP ' + CAST(@PageNum AS NVARCHAR) + ' ' + @Orderfld
97
+ ' FROM ' + @TableName + ' ' + @_where + ' ' + @OrderBy
98
+ ' ) A
99
)' + @strWhere + '
100
' + @OrderBy
101
END
102![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
103
EXEC( 'select ' + @ReturnFields + ' from ' + @TableName + ' where '
104
+ @Orderfld + ' in (' + @_Sql + ')' +@CutOrderBy)
105
106
PRINT ( 'select ' + @ReturnFields + ' from ' + @TableName + ' where '
107
+ @Orderfld + ' in (' + @_Sql + ')' +@CutOrderBy)
108![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
109![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
110
RETURN @RecordCount
111
GO
112
说明下:
1、读取时,我以主键(Orderfld)为准,根据传的页码数(pageSize),当前传入页数(@PageNum )在表@TableName 中先对主键进行第一次筛选,读取这个区间内的相关ID,当然排序有正与倒,它们所取的数据有Max,min之分了,具体么请看代码。
2、根据这个区间ID再读取@TableName 表中相关数据。
3、返回 @TableName 表中记录总数@RecordCount
总体来说:对于数据列过多的表,性能可能有所下降,但又想了一下无论怎么操作,都需要对表中数据进行筛选。我选择先对主键进行第一次筛选,然后再对数据进行读取。——希望高手对我这个筛选存储留下你宝贵的建议!在此先谢谢了
二、接下说下在DAL中的引用
代码如下:
Code
1![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
2
/// qyj:2009-9-4
3
/// 通用分页返回结果 :带out int (RowCount) :主要应用于Extjs 数据分页
4
/// </summary>
5
/// <param name="TableName">表名</param>
6
/// <param name="ReturnFields">需要返回的列</param>
7
/// <param name="PageSize">每页记录数</param>
8
/// <param name="PageNum">当前页码总数</param>
9
/// <param name="strWhere">查询条件</param>
10
/// <param name="orderFID">排序字段名 最好为唯一主键</param>
11
/// <param name="OrderType">排序类型 1:降序 其它为升序</param>
12
/// <param name="RowCount">总记数</param>
13
/// <returns></returns>
14
public static DataSet SupesoftPage_NumOut(string TableName, string ReturnFields, int PageSize, int
15
PageNum, string strWhere, string orderFID, int OrderType, out int RowCount)
16![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
17![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
SqlParameter[] parameters =
{
18
SqlHelper.MakeOutParam("@RecordCount", SqlDbType.VarChar, 50),
19
SqlHelper.MakeInParam("@tableName", SqlDbType.VarChar, 255,TableName),
20
SqlHelper.MakeInParam("@Orderfld", SqlDbType.VarChar, 255,orderFID),
21
SqlHelper.MakeInParam("@ReturnFields",SqlDbType.NVarChar,4000,ReturnFields),
22
SqlHelper.MakeInParam("@PageSize", SqlDbType.Int,PageSize),
23
SqlHelper.MakeInParam("@PageNum", SqlDbType.Int,PageNum),
24
SqlHelper.MakeInParam("@strWhere", SqlDbType.NVarChar,4000,strWhere),
25
SqlHelper.MakeInParam("@OrderType", SqlDbType.Bit,0)
26
};
27![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28
DataSet ds= SqlHelper.RunProcedure("SupesoftPage_NumOut", parameters, "ds");
29
RowCount = Convert.ToInt32(parameters[0].Value.ToString());
30
return ds;
31
}
在这里我把分页、读取单个数据返回实体类。。。。都放在sqlTable这个类中,在DAL中调用起来就更加方便了
如下:
Code
1![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
2
/// 读取带分页:数据集(out RowCount)
3
/// </summary>
4
/// <param name="PageSize">页码</param>
5
/// <param name="PageIndex">当前索引</param>
6
/// <param name="strWhere">条件</param>
7
/// <param name="orderby">1倒序 0 正序</param>
8
/// <param name="RowCount">输出条数</param>
9
/// <returns></returns>
10
public DataSet Page_NumOut(int PageSize, int PageStart, string strWhere, int orderby, out int RowCount)
11![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
12
return SqlTable.SupesoftPage_NumOut("new_Select_V", "*", PageSize, PageStart, strWhere, "ID", orderby, out RowCount);
13
}
基于Sqlhelper基础上进行数据访问操作。
第三、在Coolite中实际中应用。
Code
1 #region 0 数据源显示
2 protected void Store1_RefershData(object sender, StoreRefreshDataEventArgs e)
3 {
4 int Count = 0;
5 Hidden1.Text = e.Limit.ToString();
6 CheckLoad(ref Count, e.Start, e.Limit, int.Parse(groupID));
7 this.Store1.DataBind();
8 e.TotalCount = Count;
9 }
10 private void CheckLoad(ref int Count, int start, int limit, int groupID)
11 {
12 string uName = txt_user.Text != "" ? " and addUName like '%" + txt_user.Text + "%' " : "";
13 string title = txt_title.Text != "" ? " and title like '%" + txt_title.Text + "%' " : "";
14 string mark = ta_Mark.Text != "" ? " and content like '%" + ta_Mark.Text + "%' " : "";
15
16 ds = uD.Page_NumOut(limit, start, uName+title+mark+" and pass=1 and classID= " +groupID+"", 0, out Count);
17 if (ds != null && ds.Tables[0].Rows.Count > 0)
18 {
19 this.Store1.DataSource = ds;
20 }
21 else
22 {
23 Count = 0;
24 this.Store1.DataSource = "";
25 }
26 }
27 #endregion
在实际应用中就非常方便了。比如,我在读取news表中的数据,而又要在别的页面中读取pro表数据时,我只需改变DAL中 指定到pro表,前台面只需要改变下store的相关数据字段就可以。很是方便。