/// <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