/// <summary>
/// 对命令文本进行分段处理，使之能够返回小范围内的数据。
/// </summary>
/// <param name="commandText">命令文本。</param>
/// <param name="segment">数据分段对象。</param>
/// <returns>处理后的分段命令文本。</returns>
/// <exception cref="SegmentNotSupportedException">当前数据库或版本不支持分段时，引发该异常。</exception>
public virtual string Segment(string commandText, IDataSegment segment)
{
var orderBy = DbUtility.FindOrderBy(commandText);
var regAlias = new Regex(@"(\w+)?\.");
//如果有排序
if (!string.IsNullOrEmpty(orderBy))
{
//去除子句中的Order并移到OVER后
commandText = string.Format(@"
SELECT T.* FROM
(
SELECT T.*, ROW_NUMBER() OVER ({2}) AS ROW_NUM
FROM ({0}) T
) T
WHERE {1}",
commandText.Replace(orderBy, string.Empty).Trim(),
segment.Condition("ROW_NUM"),
regAlias.Replace(orderBy, string.Empty));
}
else
{
commandText = string.Format(@"
SELECT T.* FROM
(
SELECT T.*, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ROW_NUM
FROM ({0}) T
) T
WHERE {1}",
commandText, segment.Condition("ROW_NUM"));
}
return(commandText);
}
/// <summary>
/// 2012以上版本支持的新分页语法。
/// </summary>
/// <param name="commandText"></param>
/// <param name="segment"></param>
/// <returns></returns>
private string SegmentWith2012(string commandText, IDataSegment segment)
{
var orderBy = DbUtility.FindOrderBy(commandText);
return(@$ "{commandText}
{(string.IsNullOrEmpty(orderBy) ? " ORDER BY 1 " : string.Empty)}{(segment.Start != null ? $" OFFSET {
segment.Start - 1
} ROW " : string.Empty)} FETCH NEXT {(segment.Length != 0 ? segment.Length : 1000)} ROWS ONLY");
}
/// <summary>
/// 2012以上版本支持的新分页语法。
/// </summary>
/// <param name="commandText"></param>
/// <param name="segment"></param>
/// <returns></returns>
private string SegmentWith2012(string commandText, IDataSegment segment)
{
var orderBy = DbUtility.FindOrderBy(commandText);
commandText = string.Format(@"{0}
{1}{2} FETCH NEXT {3} ROWS ONLY",
commandText,
string.IsNullOrEmpty(orderBy) ? "ORDER BY 1" : string.Empty,
segment.Start != null ? string.Format(" OFFSET {0} ROW", (segment.Start - 1)) : string.Empty,
segment.Length != 0 ? segment.Length : 1000);
return(commandText);
}
/// <summary>
/// 对命令文本进行分段处理，使之能够返回小范围内的数据。
/// </summary>
/// <param name="commandText">命令文本。</param>
/// <param name="segment">数据分段对象。</param>
/// <returns>处理后的分段命令文本。</returns>
/// <exception cref="SegmentNotSupportedException">当前数据库或版本不支持分段时，引发该异常。</exception>
public virtual string Segment(string commandText, IDataSegment segment)
{
var orderBy = DbUtility.FindOrderBy(commandText);
var regAlias = new Regex(@"(\w+)?\.");
//如果有排序
if (!string.IsNullOrEmpty(orderBy))
{
//去除子句中的Order并移到OVER后
return(@$ "
SELECT T.* FROM
(
SELECT T.*, ROW_NUMBER() OVER ({regAlias.Replace(orderBy, string.Empty)}) AS ROW_NUM
