第一、首先在sqlserver中创建一个存储过程
USE [BZY]
GO
/*
***** 对象: StoredProcedure [dbo].[up_ProcCustomPage2005_New] 脚本日期: 12/24/2013 11:17:03 *****
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[up_ProcCustomPage2005_New]
@SelectSql varchar(
4000
)
,@OrderBy varchar(
4000
)
,@PageSize
int
,@PageIndex
int
,@PageCount
int
output
,@RecordCount
int
output
AS
--SET @SelectSql =
'
select * from table_3
'
--SET @OrderBy =
'
order by id desc
'
Declare @sql nvarchar(
4000
)
SET @sql
=
'
select @RecourdCount = count(*) from (
'
+ @SelectSql +
'
) as tt@#$
'
EXEC sp_executesql @sql,N
'
@RecourdCount int OUTPUT
'
,@RecordCount OUTPUT
SET @PageCount
= CEILING( (@RecordCount +
0.0
) /
@PageSize)
IF @PageIndex
>
@PageCount
SET @PageIndex
=
@PageCount
IF @PageIndex
<
1
SET @PageIndex
=
1
DECLARE @StartIndex
int
DECLARE @EndIndex
int
SET @StartIndex
= (@PageIndex-
1
) * @PageSize +
1
SET @EndIndex
= @PageIndex *
@PageSize
SET @sql
=
'
select * from (select row_number()over(
'
+ @OrderBy +
'
) as rownumber,* from (
'
+ @SelectSql +
'
) AS tt@#$) as tt@#$result where rownumber between
'
+ cast(@StartIndex AS varchar) +
'
and
'
+
cast(@EndIndex AS varchar)
PRINT @sql
EXEC sp_executesql @sql
二、c#代码：
private
static
DataTable GetExecuteCustomPage_New(
string
selectSql,
string
orderBy,
int
pageSize,
int
pageIndex,
out
int
pageCount,
out
int
recordCount)
{
MatchCollection mc
= Regex.Matches(orderBy,
@"
([^ ]*)\.[^ ]*
"
);
if
(mc.Count >
0
)
{
foreach
(Match item
in
mc)
{
orderBy
= orderBy.Replace(item.Groups[
1
].Value +
"
.
"
,
""
);
}
}
SqlParameter SelectSql
=
new
SqlParameter() { ParameterName =
"
@SelectSql
"
, Size =
4000
, Value =
selectSql };
SqlParameter OrderBy
=
new
SqlParameter() { ParameterName =
"
@OrderBy
"
, Size =
4000
, Value =
orderBy };
SqlParameter PageSize
=
new
SqlParameter() { ParameterName =
"
@PageSize
"
, Size =
4
, Value = pageSize , DbType =
DbType.Int32 };
SqlParameter PageIndex
=
new
SqlParameter() { ParameterName =
"
@PageIndex
"
, Size =
4
, Value = pageIndex , DbType =
DbType.Int32 };
SqlParameter PageCount
=
new
SqlParameter() { ParameterName =
"
@PageCount
"
, Size =
4
, Direction = ParameterDirection.Output , DbType =
DbType.Int32 };
SqlParameter RecordCount
=
new
SqlParameter() { ParameterName =
"
@RecordCount
"
, Size =
4
, Direction = ParameterDirection.Output , DbType =
DbType.Int32 };
DataSet ds
=
new
DataSet();
using
(SqlConnection cnn =
new
SqlConnection(ConfigurationManager.ConnectionStrings[
"
DBConfig
"
].ToString()))
{
SqlCommand cmd
=
cnn.CreateCommand();
cmd.CommandText
=
"
up_ProcCustomPage2005_New
"
;
cmd.CommandType
=
CommandType.StoredProcedure;
cmd.Connection
=
cnn;
cmd.Parameters.Add(SelectSql);
cmd.Parameters.Add(OrderBy);
cmd.Parameters.Add(PageSize);
cmd.Parameters.Add(PageIndex);
cmd.Parameters.Add(PageCount);
cmd.Parameters.Add(RecordCount);
SqlDataAdapter sda
=
new
SqlDataAdapter(cmd);
cnn.Open();
sda.Fill(ds);
pageCount
=
Convert.ToInt32(PageCount.Value);
recordCount
=
Convert.ToInt32(RecordCount.Value);
}
return
ds.Tables[
0
];
}
三、引用例子
public
DataSet GetPurchaserSalesVolumeAnalysis(
string
where
,
string
sFilter_Condition,
int
iPage_Size,
int
iPage_Index,
string
sTaxisField,
int
iTaxis_Sign,
out
int
iPageCount,
out
int
iiRecord_Count)
{
string
sql =
@"
select NewT.ptPaymentDate,NewT.fsStatus from
Student
NewT ";
string
orderwhere =
"
ORDER BY NewT.ptPaymentDate
"
;
return
BaitourDAO.Common.DBHelper.GetExecuteCustomPage_New(
sql
, orderwhere
, iPage_Size
, iPage_Index
,
out
iPageCount
,
out
iiRecord_Count);
}
