第一、首先在mysql中創建一個存儲過程
BEGIN
/*
@selectSql VARCHAR(5000), --sql語句
@orderWhere VARCHAR(200), --排序條件
@pageSize int, -- 每頁多少條記錄
@pageIndex int = 1 , -- 指定當前為第幾頁
@TotalPage int output , -- 返回總頁數
@totalCount int output -- 返回總記錄數
*/
SET @str
=CONCAT(
"
SET @tCount=(SELECT COUNT(1) FROM (
"
,selectSql,
"
) as t );
"
);
PREPARE stmt1 FROM @str;
EXECUTE stmt1;
--
總頁數
SET @tPage
=CEILING((@tCount+
0.0
)/
PageSize);
SET TotalCount
=
@tCount;
SET TotalPage
=
@tPage;
SET @str
=CONCAT(selectSql,
"
"
,orderWhere,
"
LIMIT
"
,(PageIndex-
1
)*PageSize,
"
,
"
,PageSize,
"
;
"
);
PREPARE stmt1 FROM @str;
EXECUTE stmt1;
END
二、c#代碼：
public
static
DataSet GetExecuteCustomPageDataSetMySql(
string
selectSql,
string
orderwhere,
int
iPage_Size,
int
iPage_Index,
out
int
iPageCount,
out
int
iiRecord_Count)
{
DataSet ds
=
null
;
try
{
MySql.Data.MySqlClient.MySqlParameter[] param
=
new
MySql.Data.MySqlClient.MySqlParameter[]
{
//
IN selectSql varchar(4000),IN orderWhere varchar(200),IN PageSize int,IN PageIndex int,OUT TotalPage int,OUT TotalCount int
new
MySql.Data.MySqlClient.MySqlParameter(
"
?selectSql
"
,selectSql),
new
MySql.Data.MySqlClient.MySqlParameter(
"
?orderWhere
"
,orderwhere),
new
MySql.Data.MySqlClient.MySqlParameter(
"
?pageSize
"
,iPage_Size),
new
MySql.Data.MySqlClient.MySqlParameter(
"
?pageIndex
"
,iPage_Index),
new
MySql.Data.MySqlClient.MySqlParameter(
"
?TotalPage
"
, MySql.Data.MySqlClient.MySqlDbType.Int32),
new
MySql.Data.MySqlClient.MySqlParameter(
"
?TotalCount
"
, MySql.Data.MySqlClient.MySqlDbType.Int32)
};
param[
4
].Direction =
System.Data.ParameterDirection.Output;
param[
5
].Direction =
System.Data.ParameterDirection.Output;
//
cmd.CommandText = "up_ProcCustomPage2005";
//
string sql="select * FROM Cabin order by AddTime desc LIMIT 0,20;";
iPageCount =
0
;
iiRecord_Count
=
0
;
ds
= MySqlHelper.ExecuteDataset(MySqlHelper.ConnectionString, CommandType.StoredProcedure,
"
proc_page
"
, param);
try
{
iPageCount
= Convert.ToInt32(param[
4
].Value);
iiRecord_Count
= Convert.ToInt32(param[
5
].Value);
}
catch
(Exception ex)
{
iPageCount
=
0
;
iiRecord_Count
=
0
;
throw
;
}
finally
{
//
param.cl.Clear();
}
}
catch
(Exception e)
{
ds
=
null
;
iPageCount
=
0
;
iiRecord_Count
=
0
;
throw
;
}
finally
{
//
cnn.Close();
//
cnn.Dispose();
}
return
ds;
}
三、引用例子
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 DISTINCT NewT.ptPaymentDate FlightOrderSub NewT
"
;
string
orderwhere =
"
ORDER BY NewT.ptPaymentDate
"
;
return
BaitourDAO.Common.DBHelper.GetExecuteCustomPageDataSetMySql(
sql
, orderwhere
, iPage_Size
, iPage_Index
,
out
iPageCount
,
out
iiRecord_Count);
}
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
