第一、首先在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);
}
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
