第一、首先在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);
}
