public static string GetPagerSql(PagerDomain domain, string sqlBody, string strWhere, string orderby)
{
#region 查询总条数
var strSql = new StringBuilder();
strSql.Append("select count(1) ");
strSql.Append(sqlBody.Substring(sqlBody.ToUpper().IndexOf(" FROM ")));
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" where " + strWhere);
}
var allCount = ExeScalar(strSql.ToString());
domain.TotalCount = Convert.ToInt32(allCount);
#endregion
#region 拼接好的分页SQL
strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by " + orderby);
}
strSql.AppendFormat(")AS Row, {0} ", sqlBody.Substring(sqlBody.ToUpper().IndexOf("SELECT ") + 7));
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", (domain.CurrentPageIndex - 1) * domain.PageSize + 1, domain.CurrentPageIndex * domain.PageSize);
#endregion
return(strSql.ToString());
}
public static string GetPagerSql_Sum(PagerDomain domain, string sqlBody, string strWhere, string orderby)
{
#region 拼接好的分页SQL
var strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by " + orderby);
}
strSql.AppendFormat(")AS Row, {0} ", sqlBody.Substring(sqlBody.ToUpper().IndexOf("SELECT ") + 7));
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", (domain.CurrentPageIndex - 1) * domain.PageSize + 1, domain.CurrentPageIndex * domain.PageSize);
#endregion
return(strSql.ToString());
}
