/// <summary>
/// 可自定义排序字段的翻页
/// 资源消耗随页数的增大激增。
/// </summary>
/// <param name="SelectCols">要选择的字段</param>
/// <param name="TableStr">要查询的表或表联合</param>
/// <param name="WhereStr">查询条件</param>
/// <param name="Index">页号</param>
/// <param name="PageSize">页大小</param>
/// <param name="orderbycol">排序字段</param>
/// <param name="Isdesc">是否反向</param>
/// <returns></returns>
public PageInfo GetBrandPage(string SelectCols, string TableStr, string WhereStr, Int32 Index, Int16 PageSize, string orderbycol, Boolean Isdesc, SqlParam param)
{
int all = getAll(TableStr, WhereStr, param.Copy());
int _index = getIndex(all, Index, PageSize);
if (PageSize < 1)
{
return(new PageInfo(all, _index, new DataTable(), PageSize));
}
string sc = SelectCols;
if (SelectCols.Contains("," + orderbycol))
{
sc += "," + orderbycol;
}
int ps = (all - (_index - 1) * PageSize) >= PageSize ? PageSize : all - (_index - 1) * PageSize;
StringBuilder sql = new StringBuilder();
sql.Append("select * from \r\n");
sql.Append(" (\r\n");
sql.Append(" select top "+ ps + " * from \r\n");
sql.Append(" (\r\n");
sql.Append(" select top "+ (_index) * PageSize + " " + SelectCols + " from " + TableStr + (string.IsNullOrEmpty(WhereStr) ? "" : " where ") + WhereStr + " order by " + orderbycol + " " + (Isdesc ? "desc" : "asc"));
sql.Append(" ) as t \r\n");
sql.Append(" order by t. IsCommend asc,t.CommendSort desc ,t.BrandId desc \r\n");
sql.Append(" ) as s \r\n");
sql.Append(" order by "+ getTempKey(orderbycol, "s") + " " + (Isdesc ? "desc" : "asc") + "\r\n");
return(new PageInfo(all, _index, AccessData.GetDt(sql.ToString(), param), PageSize));
}
/// <summary>
/// 根据主键排序的翻页。
/// 应对海量数据翻页，资源消耗与页数几乎无关。
/// </summary>
/// <param name="SelectCols">要选择的字段，主键必须在其中</param>
/// <param name="TableStr"></param>
/// <param name="WhereStr"></param>
/// <param name="Key"></param>
/// <param name="Index"></param>
/// <param name="PageSize"></param>
/// <param name="IsDesc"></param>
/// <returns></returns>
public PageInfo GetPage(string SelectCols, string TableStr, string WhereStr, string Key, Int32 Index, Int16 PageSize, bool IsDesc, SqlParam param)
{
int all = getAll(TableStr, WhereStr, param.Copy());
string saftwhere = (string.IsNullOrEmpty(WhereStr) ? "" : " where " + WhereStr);
string saftandwhere = (string.IsNullOrEmpty(WhereStr) ? "" : " and " + WhereStr);
int _index = getIndex(all, Index, PageSize);
if (PageSize < 1)
{
return(new PageInfo(all, _index, new DataTable(), PageSize));
}
StringBuilder sql = new StringBuilder();
if (_index == 1)
{
sql.Append("select top " + PageSize + " " + SelectCols + " from " + TableStr + saftwhere + " order by " + Key + " " + (IsDesc ? "desc" : ""));
}
else
{
string tempkey = getTempKey(Key, "temptable");
if (IsDesc)
{
//反序
sql.Append("select top " + PageSize + " " + SelectCols + " \r\n");
sql.Append("from " + TableStr + " \r\n");
sql.Append("where " + Key + "< \r\n");
sql.Append(" (select min (" + tempkey + ") from \r\n");
sql.Append(" (select top ((" + _index + "-1)*" + PageSize + ") " + Key + " from " + TableStr + saftwhere + " order by " + Key + " desc) as temptable \r\n");
sql.Append(" ) " + saftandwhere + " \r\n");
sql.Append("order by " + Key + " desc\r\n");
}
else
{
//正序
sql.Append("select top " + PageSize + " " + SelectCols + " \r\n");
sql.Append("from " + TableStr + " \r\n");
sql.Append("where " + Key + "> \r\n");
sql.Append(" (select max (" + tempkey + ") from \r\n");
sql.Append(" (select top ((" + _index + "-1)*" + PageSize + ") " + Key + " from " + TableStr + saftwhere + " order by " + Key + ") as temptable \r\n");
sql.Append(" ) " + saftandwhere + " \r\n");
sql.Append("order by " + Key + " \r\n");
}
}
//Console.WriteLine(sql.ToString());
return(new PageInfo(all, _index, AccessData.GetDt(sql.ToString(), param), PageSize));
}