public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit) { sql = sql.Trim(); bool isForUpdate = false; if (sql.EndsWithCaseInsensitive(" for update")) { sql = sql.Substring(0, sql.Length - 11); isForUpdate = true; } string selectColumns = ExtractColumnOrAliasNames(sql); var pagingSelect = new SqlStringBuilder(sql.Parts.Count + 10); if (offset != null) { pagingSelect.Add("select " + selectColumns + " from ( select row_.*, rownum rownum_ from ( "); } else { pagingSelect.Add("select " + selectColumns + " from ( "); } pagingSelect.Add(sql); if (offset != null && limit != null) { pagingSelect.Add(" ) row_ where rownum <=").Add(limit).Add(") where rownum_ >").Add(offset); } else if (limit != null) { pagingSelect.Add(" ) where rownum <=").Add(limit); } else { // offset is specified, but limit is not. pagingSelect.Add(" ) row_ ) where rownum_ >").Add(offset); } if (isForUpdate) { pagingSelect.Add(" for update"); } return pagingSelect.ToSqlString(); }
public override SqlString GetLimitString(SqlString sql, int offset, int limit, int? offsetParameterIndex, int? limitParameterIndex) { sql = sql.Trim(); bool hasOffset = offset > 0; bool isForUpdate = false; if (sql.EndsWithCaseInsensitive(" for update")) { sql = sql.Substring(0, sql.Length - 11); isForUpdate = true; } var pagingSelect = new SqlStringBuilder(sql.Parts.Count + 10); if (hasOffset) { pagingSelect.Add("select * from ( select row_.*, rownum rownum_ from ( "); } else { pagingSelect.Add("select * from ( "); } pagingSelect.Add(sql); if (hasOffset) { pagingSelect.Add(" ) row_ where rownum <=").AddParameter(limitParameterIndex.Value).Add(") where rownum_ >").AddParameter(offsetParameterIndex.Value); } else { pagingSelect.Add(" ) where rownum <=").AddParameter(limitParameterIndex.Value); } if (isForUpdate) { pagingSelect.Add(" for update"); } return pagingSelect.ToSqlString(); }