public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args, string primaryKey = null) { parts.sqlSelectRemoved = PagingHelper.rxOrderBy.Replace(parts.sqlSelectRemoved, "", 1); if (PagingHelper.rxDistinct.IsMatch(parts.sqlSelectRemoved)) { parts.sqlSelectRemoved = "peta_inner.* FROM (SELECT " + parts.sqlSelectRemoved + ") peta_inner"; } string text = primaryKey ?? string.Empty; if (primaryKey.Contains('.') && !primaryKey.EndsWith(".")) { text = primaryKey.Substring(primaryKey.LastIndexOf(".") + 1); } string result = string.Format("SELECT peta_paged.{4} FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>@{2} AND peta_rn<=@{3}", new object[] { (parts.sqlOrderBy == null) ? "ORDER BY (SELECT NULL)" : parts.sqlOrderBy, parts.sqlSelectRemoved, args.Length, args.Length + 1, text }); args = args.Concat(new object[] { skip, skip + take }).ToArray<object>(); return result; }
public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args, string primaryKey = null) { if (parts.sqlSelectRemoved.StartsWith("*")) { throw new System.Exception("Query must alias '*' when performing a paged query.\neg. select t.* from table t order by t.id"); } return Singleton<SqlServerDatabaseType>.Instance.BuildPageQuery(skip, take, parts, ref args, null); }
public virtual string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args, string primaryKey = null) { string result = string.Format("{0}\nLIMIT @{1} OFFSET @{2}", parts.sql, args.Length, args.Length + 1); args = args.Concat(new object[] { take, skip }).ToArray<object>(); return result; }
public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args) { parts.sqlSelectRemoved = PagingHelper.rxOrderBy.Replace(parts.sqlSelectRemoved, "", 1); if (PagingHelper.rxDistinct.IsMatch(parts.sqlSelectRemoved)) { parts.sqlSelectRemoved = "peta_inner.* FROM (SELECT " + parts.sqlSelectRemoved + ") peta_inner"; } var sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>@{2} AND peta_rn<=@{3}", parts.sqlOrderBy == null ? "ORDER BY (SELECT NULL)" : parts.sqlOrderBy, parts.sqlSelectRemoved, args.Length, args.Length + 1); args = args.Concat(new object[] { skip, skip + take }).ToArray(); return sqlPage; }
public static bool SplitSQL(string sql, out PagingHelper.SQLParts parts) { parts.sql = sql; parts.sqlSelectRemoved = null; parts.sqlCount = null; parts.sqlOrderBy = null; Match match = PagingHelper.rxColumns.Match(sql); if (!match.Success) { return false; } Group group = match.Groups[1]; parts.sqlSelectRemoved = sql.Substring(group.Index); if (PagingHelper.rxDistinct.IsMatch(parts.sqlSelectRemoved)) { parts.sqlCount = string.Concat(new string[] { sql.Substring(0, group.Index), "COUNT(", match.Groups[1].ToString().Trim(), ") ", sql.Substring(group.Index + group.Length) }); } else { parts.sqlCount = sql.Substring(0, group.Index) + "COUNT(*) " + sql.Substring(group.Index + group.Length); } match = PagingHelper.rxOrderBy.Match(parts.sqlCount); if (!match.Success) { parts.sqlOrderBy = null; } else { group = match.Groups[0]; parts.sqlOrderBy = group.ToString(); parts.sqlCount = parts.sqlCount.Substring(0, group.Index) + parts.sqlCount.Substring(group.Index + group.Length); } return true; }
/// <summary> /// Builds an SQL query suitable for performing page based queries to the database /// </summary> /// <param name="skip">The number of rows that should be skipped by the query</param> /// <param name="take">The number of rows that should be retruend by the query</param> /// <param name="parts">The original SQL query after being parsed into it's component parts</param> /// <param name="args">Arguments to any embedded parameters in the SQL query</param> /// <returns>The final SQL query that should be executed.</returns> public virtual string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args) { var sql = string.Format("{0}\nLIMIT @{1} OFFSET @{2}", parts.sql, args.Length, args.Length + 1); args = args.Concat(new object[] { take, skip }).ToArray(); return sql; }
public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args) { var sqlPage = string.Format("{0}\nOFFSET @{1} ROWS FETCH NEXT @{2} ROWS ONLY", parts.sql, args.Length, args.Length + 1); args = args.Concat(new object[] { skip, take }).ToArray(); return sqlPage; }