第一、首先在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-)*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[].Direction = System.Data.ParameterDirection.Output;
param[].Direction = System.Data.ParameterDirection.Output; //cmd.CommandText = "up_ProcCustomPage2005";
//string sql="select * FROM Cabin order by AddTime desc LIMIT 0,20;";
iPageCount = ;
iiRecord_Count = ;
ds = MySqlHelper.ExecuteDataset(MySqlHelper.ConnectionString, CommandType.StoredProcedure, "proc_page", param); try
{
iPageCount = Convert.ToInt32(param[].Value);
iiRecord_Count = Convert.ToInt32(param[].Value);
}
catch (Exception ex)
{ iPageCount = ;
iiRecord_Count = ;
throw;
}
finally
{
//param.cl.Clear();
}
}
catch (Exception e)
{
ds = null; iPageCount = ;
iiRecord_Count = ;
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);
}