/// <summary>
/// 单表分页
/// </summary>
/// <param name="condition">查询条件</param>
/// <param name="sortField">排序字段</param>
/// <param name="des">是否顺序</param>
/// <param name="pageSize">每页数据条数</param>
/// <param name="pageIndex">页索引(0开始)</param>
/// <param name="recordCount">总记录条数(传入值小于0时返回)</param>
/// <returns></returns>
protected DataTable GetList(string table, string fields, string condition, string sortField, bool des, int pageSize, int pageIndex, ref int recordCount, params DbParameter[] parameterValues)
{
string tableWithWhere = table;
if (condition != null && condition != "")
tableWithWhere += " where " + condition;
string sql = "";
if (recordCount < 0)
{
string countSql = "select count(*) from {0}";
if ((parameterValues == null) || (parameterValues.Length <= 0))
{
recordCount = Convert.ToInt32(Beyondbit.Framework.DataAccess.DataAccess.ExecuteScalar(string.Format(countSql, tableWithWhere), CommandType.Text));
}
else
{
countSql = string.Format(countSql, tableWithWhere);
recordCount = Convert.ToInt32(Beyondbit.Framework.DataAccess.DataAccess.ExecuteScalar(countSql, CommandType.Text, parameterValues));
}
}
if (pageIndex == 0)
{
sql = "select TOP {0} {1} FROM {2} ORDER BY {3}";
if (des)
sql = string.Format(sql, pageSize, fields, tableWithWhere, sortField);
else
sql = string.Format(sql, pageSize, fields, tableWithWhere, sortField + " desc");
}
else
{
int pageCount = (recordCount / pageSize);
int lastPage = (recordCount % pageSize);
if (lastPage > 0)
pageCount++;
sql = "select * FROM (SELECT TOP {0} * FROM (select TOP {1} {5} FROM {2} ORDER BY {3}) AS table1 ORDER BY {4}) AS table2 ORDER BY {3}";
if ((pageIndex + 1) * pageSize <= recordCount)
{
if (des)
sql = string.Format(sql, pageSize, pageSize * (1 + pageIndex), tableWithWhere, sortField, sortField + " desc", fields);
else
sql = string.Format(sql, pageSize, pageSize * (1 + pageIndex), tableWithWhere, sortField + " desc", sortField, fields);
}
else
{
if (des)
sql = string.Format(sql, lastPage
, pageSize * (1 + pageIndex), tableWithWhere, sortField, sortField + " desc", fields);
else
sql = string.Format(sql, lastPage, pageSize * (1 + pageIndex), tableWithWhere, sortField + " desc", sortField, fields);
}
}
if ((parameterValues == null) || (parameterValues.Length <= 0))
{
return Beyondbit.Framework.DataAccess.DataAccess.ExecuteDataset(sql, CommandType.Text).Tables[0];
}
else
{
return Beyondbit.Framework.DataAccess.DataAccess.ExecuteDataset(sql, CommandType.Text, parameterValues).Tables[0];
}
}