public override string GetPageSql(string sql, Pager pager, string order, bool isDesc)
{
return this.GetPageSqlByTop(sql,pager, order, isDesc);
}
protected string GetPageSqlByTop(string sql, Pager pager,string order,bool isDesc)
{
string result = string.Empty;
if(sql.StartsWith("select",true,System.Globalization.CultureInfo.CurrentCulture))
{
sql=sql.Substring(6);
}
int pageSize=pager.PageSize;
if (pager.CurrentPage==1)
{
#region 第一页代码
result = "select top " + pageSize + " " +sql + " order by " + order + " " + ((isDesc) ? "desc" : "asc") + "";
#endregion
}
else if (pager.CurrentPage>1&&pager.CurrentPage<pager.TotalPages)
{
//if ("id" == order)
//result = "select top " + pageSize + " " + field + " from " + tableName + " where " + primary + "" + ((isDesc) ? "<" : ">") + "(select " + ((isDesc) ? "min" : "max") + "(" + primary + ") from(select top " + pageSize * (currentPage - 1) + " " + primary + " from " + tableName + " " + ((condition != "") ? "where " + condition + "" : "") + " order by " + order + " " + ((isDesc) ? "desc" : "asc") + " )) " + ((condition != "") ? "and " + condition + "" : "") + " order by " + order + " " + ((isDesc) ? "desc" : "asc") + "";
//else
result = "select top " + pageSize + " * from (select top " + (pager.TotalRecord - (pager.CurrentPage - 1) * pageSize) + " " + sql + " order by " + order + " " + ((isDesc) ? "asc" : "desc") + ") order by " + order + " " + ((isDesc) ? "desc" : "asc") + "";
}
else if (pager.CurrentPage==pager.TotalPages)
{
#region 最后页
// sql = "select " + this.field + " from (select top " + (this.totalCount - (this.TotalPages() - 1) * this.pageSize) + " " + this.field + " from " + tableName + " " + ((condition != "") ? "where " + condition + "" : "") + " order by " + order + " " + ((isDesc) ? "asc" : "desc") + ") order by " + order + " " + ((isDesc) ? "desc" : "asc") + "";
result = "select * from (select top " + (pager.TotalRecord - (pager.TotalPages - 1) * pageSize) + sql + " order by " + order + " " + ((isDesc) ? "asc" : "desc") + ") order by " + order + " " + ((isDesc) ? "desc" : "asc") + "";
#endregion
}
return result;
}
public abstract string GetPageSql(string sql, Pager pager,string order,bool isDesc);
protected string GetPageSqlByLimit(string sql, Pager pager, string order, bool isDesc)
{
string result = string.Empty;
if (sql.StartsWith("select", true, System.Globalization.CultureInfo.CurrentCulture))
{
sql = sql.Substring(6);
}
int pageSize = pager.PageSize;
int offset = (pager.CurrentPage - 1) * pager.PageSize;
sql = "select " + sql + " order by " + order + " " + ((isDesc) ? "desc" : "asc") + " limit "+pager.PageSize.ToString()+","+offset.ToString();
return result;
}
public override string GetPageSql(string sql, Pager pager, string order, bool isDesc)
{
throw new Exception("The method or operation is not implemented.");
}