/// <summary>
/// 分页查询
/// </summary>
/// <param name="commandText"></param>
/// <param name="pageCode">页码（从1开始）</param>
/// <param name="pageSize">页行数</param>
/// <param name="orderColumn">排序列</param>
/// <param name="asc">true:升序；false:降序</param>
/// <returns></returns>
public DataTable ExecutePageDatatable(string commandText, int pageCode, int pageSize, string orderColumn, bool asc = true)
{
string SQLString = string.Format(@"
SELECT COUNT(1) FROM ( {0} ) ObjectData;
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY {1} {2} ) AS ID , *
FROM ( {0} ) ObjectData
) PageTemp
WHERE PageTemp.ID BETWEEN {3} AND {4};",
commandText, orderColumn, asc ? " ASC " : " DESC ", (pageCode - 1) * pageSize + 1, pageCode * pageSize);
try
{
DataSet ResultSet = MSSQLDBServer.ExecuteDataset(Connectstring, SQLString);
int Total = int.Parse(ResultSet.Tables[0].Rows[0][0].ToString());
DataTable ResultTable = ResultSet.Tables[1];
ResultTable.ExtendedProperties.Add("Total", Total);
return(ResultTable);
}
catch
{
string Error = string.Format("SQL:{0}", SQLString.Replace('\n', ' '));
throw new Exception(Error);
}
}
/// <summary>
/// 返回查询集合
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <param name="commandType"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public DataSet ExecuteDataset(string commandText, IDataParameter[] commandParameters = null, CommandType commandType = CommandType.Text,
IDbTransaction transaction = null)
{
return(MSSQLDBServer.ExecuteDataset(Connectstring, commandText, commandParameters, commandType, transaction));
}