存储过程:
1
--
----------------------------------
2
--
用途:分页存储过程(对有主键的表效率极高)
3
--
说明:
4
--
----------------------------------
5
6
ALTER
PROCEDURE
UP_GetRecordByPage
7
@tblName
varchar
(
255
),
--
表名
8
@fldName
varchar
(
255
),
--
主键字段名
9
@PageSize
int
=
10
,
--
页尺寸
10
@PageIndex
int
=
1
,
--
页码
11
@RowCount
int
output,
--
返回记录总数
12
@OrderType
bit
=
0
,
--
设置排序类型, 非 0 值则降序
13
@strWhere
varchar
(
1000
)
=
''
--
查询条件 (注意: 不要加 where)
14
AS
15![None.gif](/Images/OutliningIndicators/None.gif)
16
declare
@strSQL
varchar
(
6000
)
--
主语句
17
declare
@strTmp
varchar
(
100
)
--
临时变量
18
declare
@strOrder
varchar
(
400
)
--
排序类型
19
20
if
@OrderType
!=
0
21
begin
22
set
@strTmp
=
'
<(select min
'
23
set
@strOrder
=
'
order by [
'
+
@fldName
+
'
] desc
'
24
end
25
else
26
begin
27
set
@strTmp
=
'
>(select max
'
28
set
@strOrder
=
'
order by [
'
+
@fldName
+
'
] asc
'
29
end
30![None.gif](/Images/OutliningIndicators/None.gif)
31
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
* from [
'
32
+
@tblName
+
'
] where [
'
+
@fldName
+
'
]
'
+
@strTmp
+
'
([
'
33
+
@fldName
+
'
]) from (select top
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
[
'
34
+
@fldName
+
'
] from [
'
+
@tblName
+
'
]
'
+
@strOrder
+
'
) as tblTmp)
'
35
+
@strOrder
36![None.gif](/Images/OutliningIndicators/None.gif)
37
if
@strWhere
!=
''
38
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
* from [
'
39
+
@tblName
+
'
] where [
'
+
@fldName
+
'
]
'
+
@strTmp
+
'
([
'
40
+
@fldName
+
'
]) from (select top
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
[
'
41
+
@fldName
+
'
] from [
'
+
@tblName
+
'
] where
'
+
@strWhere
+
'
'
42
+
@strOrder
+
'
) as tblTmp) and
'
+
@strWhere
+
'
'
+
@strOrder
43![None.gif](/Images/OutliningIndicators/None.gif)
44
if
@PageIndex
=
1
45
begin
46
set
@strTmp
=
''
47
if
@strWhere
!=
''
48
set
@strTmp
=
'
where
'
+
@strWhere
49![None.gif](/Images/OutliningIndicators/None.gif)
50
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
* from [
'
51
+
@tblName
+
'
]
'
+
@strTmp
+
'
'
+
@strOrder
52
end
53![None.gif](/Images/OutliningIndicators/None.gif)
54![None.gif](/Images/OutliningIndicators/None.gif)
55
declare
@sql
NVARCHAR
(
400
)
56
set
@sql
=
N
'
select @RowCount=count(*) from
'
+
@tblName
+
'
where
'
+
@strWhere
57
EXEC
sp_executesql
@sql
,N
'
@RowCount int OUT
'
,
@RowCount
OUT
58![None.gif](/Images/OutliningIndicators/None.gif)
59
exec
(
@strSQL
)
![None.gif](/Images/OutliningIndicators/None.gif)
2
![None.gif](/Images/OutliningIndicators/None.gif)
3
![None.gif](/Images/OutliningIndicators/None.gif)
4
![None.gif](/Images/OutliningIndicators/None.gif)
5
![None.gif](/Images/OutliningIndicators/None.gif)
6
![None.gif](/Images/OutliningIndicators/None.gif)
7
![None.gif](/Images/OutliningIndicators/None.gif)
8
![None.gif](/Images/OutliningIndicators/None.gif)
9
![None.gif](/Images/OutliningIndicators/None.gif)
10
![None.gif](/Images/OutliningIndicators/None.gif)
11
![None.gif](/Images/OutliningIndicators/None.gif)
12
![None.gif](/Images/OutliningIndicators/None.gif)
13
![None.gif](/Images/OutliningIndicators/None.gif)
14
![None.gif](/Images/OutliningIndicators/None.gif)
15
![None.gif](/Images/OutliningIndicators/None.gif)
16
![None.gif](/Images/OutliningIndicators/None.gif)
17
![None.gif](/Images/OutliningIndicators/None.gif)
18
![None.gif](/Images/OutliningIndicators/None.gif)
19
![None.gif](/Images/OutliningIndicators/None.gif)
20
![None.gif](/Images/OutliningIndicators/None.gif)
21
![None.gif](/Images/OutliningIndicators/None.gif)
22
![None.gif](/Images/OutliningIndicators/None.gif)
23
![None.gif](/Images/OutliningIndicators/None.gif)
24
![None.gif](/Images/OutliningIndicators/None.gif)
25
![None.gif](/Images/OutliningIndicators/None.gif)
26
![None.gif](/Images/OutliningIndicators/None.gif)
27
![None.gif](/Images/OutliningIndicators/None.gif)
28
![None.gif](/Images/OutliningIndicators/None.gif)
29
![None.gif](/Images/OutliningIndicators/None.gif)
30
![None.gif](/Images/OutliningIndicators/None.gif)
31
![None.gif](/Images/OutliningIndicators/None.gif)
32
![None.gif](/Images/OutliningIndicators/None.gif)
33
![None.gif](/Images/OutliningIndicators/None.gif)
34
![None.gif](/Images/OutliningIndicators/None.gif)
35
![None.gif](/Images/OutliningIndicators/None.gif)
36
![None.gif](/Images/OutliningIndicators/None.gif)
37
![None.gif](/Images/OutliningIndicators/None.gif)
38
![None.gif](/Images/OutliningIndicators/None.gif)
39
![None.gif](/Images/OutliningIndicators/None.gif)
40
![None.gif](/Images/OutliningIndicators/None.gif)
41
![None.gif](/Images/OutliningIndicators/None.gif)
42
![None.gif](/Images/OutliningIndicators/None.gif)
43
![None.gif](/Images/OutliningIndicators/None.gif)
44
![None.gif](/Images/OutliningIndicators/None.gif)
45
![None.gif](/Images/OutliningIndicators/None.gif)
46
![None.gif](/Images/OutliningIndicators/None.gif)
47
![None.gif](/Images/OutliningIndicators/None.gif)
48
![None.gif](/Images/OutliningIndicators/None.gif)
49
![None.gif](/Images/OutliningIndicators/None.gif)
50
![None.gif](/Images/OutliningIndicators/None.gif)
51
![None.gif](/Images/OutliningIndicators/None.gif)
52
![None.gif](/Images/OutliningIndicators/None.gif)
53
![None.gif](/Images/OutliningIndicators/None.gif)
54
![None.gif](/Images/OutliningIndicators/None.gif)
55
![None.gif](/Images/OutliningIndicators/None.gif)
56
![None.gif](/Images/OutliningIndicators/None.gif)
57
![None.gif](/Images/OutliningIndicators/None.gif)
58
![None.gif](/Images/OutliningIndicators/None.gif)
59
![None.gif](/Images/OutliningIndicators/None.gif)
数据层也相应的做了些修改:
1
///
<summary>
2 /// 分页获取数据列表
3 /// </summary>
4 public DataSet GetList( int PageSize, int PageIndex, string strWhere, ref int Output)
5 {
6 SqlParameter[] parameters = {
7 new SqlParameter( " @tblName " , SqlDbType.VarChar, 255 ),
8 new SqlParameter( " @fldName " , SqlDbType.VarChar, 255 ),
9 new SqlParameter( " @PageSize " , SqlDbType.Int),
10 new SqlParameter( " @PageIndex " , SqlDbType.Int),
11 new SqlParameter( " @RowCount " , SqlDbType.Int),
12 new SqlParameter( " @OrderType " , SqlDbType.Bit),
13 new SqlParameter( " @strWhere " , SqlDbType.VarChar, 1000 ),
14 };
15 parameters[ 0 ].Value = " TM_TeachInfo " ;
16 parameters[ 1 ].Value = " InfoID " ;
17 parameters[ 2 ].Value = PageSize;
18 parameters[ 3 ].Value = PageIndex;
19 parameters[ 4 ].Direction = System.Data.ParameterDirection.Output;
20 parameters[ 5 ].Value = 1 ;
21 parameters[ 6 ].Value = strWhere;
22
23 DataSet ds = DbHelperSQL.RunProcedure( " UP_GetRecordByPage " ,parameters, " ds " );
24
25 if (parameters[ 4 ].Value != DBNull.Value && parameters[ 4 ].Value.ToString() != string .Empty)
26 {
27 Output = Convert.ToInt32(parameters[ 4 ].Value);
28 }
29
30 return ds;
31 }
2 /// 分页获取数据列表
3 /// </summary>
4 public DataSet GetList( int PageSize, int PageIndex, string strWhere, ref int Output)
5 {
6 SqlParameter[] parameters = {
7 new SqlParameter( " @tblName " , SqlDbType.VarChar, 255 ),
8 new SqlParameter( " @fldName " , SqlDbType.VarChar, 255 ),
9 new SqlParameter( " @PageSize " , SqlDbType.Int),
10 new SqlParameter( " @PageIndex " , SqlDbType.Int),
11 new SqlParameter( " @RowCount " , SqlDbType.Int),
12 new SqlParameter( " @OrderType " , SqlDbType.Bit),
13 new SqlParameter( " @strWhere " , SqlDbType.VarChar, 1000 ),
14 };
15 parameters[ 0 ].Value = " TM_TeachInfo " ;
16 parameters[ 1 ].Value = " InfoID " ;
17 parameters[ 2 ].Value = PageSize;
18 parameters[ 3 ].Value = PageIndex;
19 parameters[ 4 ].Direction = System.Data.ParameterDirection.Output;
20 parameters[ 5 ].Value = 1 ;
21 parameters[ 6 ].Value = strWhere;
22
23 DataSet ds = DbHelperSQL.RunProcedure( " UP_GetRecordByPage " ,parameters, " ds " );
24
25 if (parameters[ 4 ].Value != DBNull.Value && parameters[ 4 ].Value.ToString() != string .Empty)
26 {
27 Output = Convert.ToInt32(parameters[ 4 ].Value);
28 }
29
30 return ds;
31 }