/// <summary>
/// 获取分页语句
/// </summary>
/// <param name="isWithSyntax">是否with语法</param>
/// <param name="sql">原始sql语句</param>
/// <param name="parameter">参数</param>
/// <param name="orderField">排序字段</param>
/// <param name="isAscending">是否升序排序</param>
/// <param name="pageSize">每页数量</param>
/// <param name="pageIndex">当前页码</param>
/// <returns></returns>
public override string GetPageSql(bool isWithSyntax, string sql, object parameter, string orderField, bool isAscending, int pageSize, int pageIndex)
{
//排序字段
string order;
if (orderField.IsNotNullOrEmpty())
{
if (orderField.Contains(@"(/\*(?:|)*?\*/)|(\b(ASC|DESC)\b)", RegexOptions.IgnoreCase))
{
order = $"ORDER BY {orderField}";
}
else
{
order = $"ORDER BY {orderField} {(isAscending ? "ASC" : "DESC")}";
}
}
else
{
order = "ORDER BY (SELECT 0)";
}
string sqlQuery;
var next = pageSize;
var offset = pageSize * (pageIndex - 1);
var rowStart = pageSize * (pageIndex - 1) + 1;
var rowEnd = pageSize * pageIndex;
var serverVersion = int.Parse(Connection.ServerVersion.Split('.')[0]);
//判断是否with语法
if (isWithSyntax)
{
sqlQuery = $"{sql} SELECT {CountSyntax} AS [TOTAL] FROM T;";
if (serverVersion > 10)
{
sqlQuery += $"{sql.Remove(sql.LastIndexOf(")"), 1)} {(orderField.IsNotNullOrEmpty() ? order : "")}) SELECT * FROM T OFFSET {offset} ROWS FETCH NEXT {next} ROWS ONLY;";
}
else
{
sqlQuery += $"{sql},R AS (SELECT ROW_NUMBER() OVER ({order}) AS [ROWNUMBER], * FROM T) SELECT * FROM R WHERE [ROWNUMBER] BETWEEN {rowStart} AND {rowEnd};";
}
}
else
{
sqlQuery = $"SELECT {CountSyntax} AS [TOTAL] FROM ({sql}) AS T;";
if (serverVersion > 10)
{
sqlQuery += $"{sql} {(orderField.IsNotNullOrEmpty() ? order : "")} OFFSET {offset} ROWS FETCH NEXT {next} ROWS ONLY;";
}
else
{
sqlQuery += $"SELECT * FROM (SELECT ROW_NUMBER() OVER ({order}) AS [ROWNUMBER], * FROM ({sql}) AS T) AS N WHERE [ROWNUMBER] BETWEEN {rowStart} AND {rowEnd};";
}
}
sqlQuery = SqlIntercept?.Invoke(sqlQuery, parameter) ?? sqlQuery;
return(sqlQuery);
}
/// <summary>
/// 获取分页语句
/// </summary>
/// <param name="isWithSyntax">是否with语法</param>
/// <param name="sql">原始sql语句</param>
/// <param name="parameter">参数</param>
/// <param name="orderField">排序字段</param>
/// <param name="isAscending">是否升序排序</param>
/// <param name="pageSize">每页数量</param>
/// <param name="pageIndex">当前页码</param>
/// <returns></returns>
public override string GetPageSql(bool isWithSyntax, string sql, object parameter, string orderField, bool isAscending, int pageSize, int pageIndex)
{
//排序字段
if (orderField.IsNotNullOrEmpty())
{
if (orderField.Contains(@"(/\*(?:|)*?\*/)|(\b(ASC|DESC)\b)", RegexOptions.IgnoreCase))
{
orderField = $"ORDER BY {orderField}";
}
else
{
orderField = $"ORDER BY {orderField} {(isAscending ? "ASC" : "DESC")}";
}
}
string sqlQuery;
var next = pageSize;
var offset = pageSize * (pageIndex - 1);
var rowStart = pageSize * (pageIndex - 1) + 1;
var rowEnd = pageSize * pageIndex;
var serverVersion = int.Parse(Connection.ServerVersion.Split('.')[0]);
//判断是否with语法
if (isWithSyntax)
{
sqlQuery = $"{sql} SELECT {CountSyntax} AS \"TOTAL\" FROM T;";
if (serverVersion > 11)
{
sqlQuery += $"{sql.Remove(sql.LastIndexOf(")"), 1)} {orderField}) SELECT * FROM T OFFSET {offset} ROWS FETCH NEXT {next} ROWS ONLY";
}
else
{
sqlQuery += $"{sql.Remove(sql.LastIndexOf(")"), 1)} {orderField}),R AS (SELECT ROWNUM AS ROWNUMBER,T.* FROM T WHERE ROWNUM <= {rowEnd}) SELECT * FROM R WHERE ROWNUMBER>={rowStart}";
}
}
else
{
sqlQuery = $"SELECT {CountSyntax} AS \"TOTAL\" FROM ({sql}) T;";
if (serverVersion > 11)
{
sqlQuery += $"{sql} {orderField} OFFSET {offset} ROWS FETCH NEXT {next} ROWS ONLY";
}
else
{
sqlQuery += $"SELECT * FROM (SELECT X.*,ROWNUM AS \"ROWNUMBER\" FROM ({sql} {orderField}) X WHERE ROWNUM <= {rowEnd}) T WHERE \"ROWNUMBER\" >= {rowStart}";
}
}
sqlQuery = SqlIntercept?.Invoke(sqlQuery, parameter) ?? sqlQuery;
return(sqlQuery);
}
/// <summary>
/// 获取分页语句
/// </summary>
/// <param name="isWithSyntax">是否with语法</param>
/// <param name="sql">原始sql语句</param>
/// <param name="parameter">参数</param>
/// <param name="orderField">排序字段</param>
/// <param name="isAscending">是否升序排序</param>
/// <param name="pageSize">每页数量</param>
/// <param name="pageIndex">当前页码</param>
/// <returns></returns>
public override string GetPageSql(bool isWithSyntax, string sql, object parameter, string orderField, bool isAscending, int pageSize, int pageIndex)
{
//排序字段
if (!orderField.IsNullOrEmpty())
{
if (orderField.Contains(@"(/\*(?:|)*?\*/)|(\b(ASC|DESC)\b)", RegexOptions.IgnoreCase))
{
orderField = $"ORDER BY {orderField}";
}
else
{
orderField = $"ORDER BY {orderField} {(isAscending ? "ASC" : "DESC")}";
}
}
string sqlQuery;
var limit = pageSize;
var offset = pageSize * (pageIndex - 1);
//判断是否with语法
if (isWithSyntax)
{
sqlQuery = $"{sql} SELECT {CountSyntax} AS \"TOTAL\" FROM T;";
sqlQuery += $"{sql} SELECT * FROM T {orderField} LIMIT {limit} OFFSET {offset};";
}
else
{
sqlQuery = $"SELECT {CountSyntax} AS \"TOTAL\" FROM ({sql}) AS T;";
sqlQuery += $"SELECT * FROM ({sql}) AS T {orderField} LIMIT {limit} OFFSET {offset};";
}
sqlQuery = SqlIntercept?.Invoke(sqlQuery, parameter) ?? sqlQuery;
return(sqlQuery);
}
