Example #1
0
        public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
        {
            var sql = string.Format("{0}\nROWS @{1} TO @{2}", parts.Sql, args.Length, args.Length + 1);

            args = args.Concat(new object[] { skip + 1, skip + take }).ToArray();
            return(sql);
        }
Example #2
0
        /// <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, 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);
        }
Example #3
0
        public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
        {
            if (parts.SqlSelectRemoved.StartsWith("*"))
            {
                throw new Exception("Query must alias '*' when performing a paged query.\neg. select t.* from table t order by t.id");
            }

            // Same deal as SQL Server
            return(Singleton <SqlServerDatabaseProvider> .Instance.BuildPageQuery(skip, take, parts, ref args));
        }
        public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
        {
            if (string.IsNullOrEmpty(parts.SqlOrderBy))
            {
                parts.Sql += " ORDER BY ABS(1)";
            }
            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);
        }
Example #5
0
        /// <summary>
        ///     Splits the given <paramref name="sql" /> into <paramref name="parts" />;
        /// </summary>
        /// <param name="sql">The SQL to split.</param>
        /// <param name="parts">The SQL parts.</param>
        /// <returns><c>True</c> if the SQL could be split; else, <c>False</c>.</returns>
        public bool SplitSQL(string sql, out SQLParts parts)
        {
            parts.Sql = sql;
            parts.SqlSelectRemoved = null;
            parts.SqlCount         = null;
            parts.SqlOrderBy       = null;

            // Extract the columns from "SELECT <whatever> FROM"
            var m = RegexColumns.Match(sql);

            if (!m.Success)
            {
                return(false);
            }

            // Save column list and replace with COUNT(*)
            var g = m.Groups[1];

            parts.SqlSelectRemoved = sql.Substring(g.Index);

            if (RegexDistinct.IsMatch(parts.SqlSelectRemoved))
            {
                parts.SqlCount = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
            }
            else
            {
                parts.SqlCount = sql.Substring(0, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);
            }

            // Look for the last "ORDER BY <whatever>" clause not part of a ROW_NUMBER expression
            m = SimpleRegexOrderBy.Match(parts.SqlCount);
            if (m.Success)
            {
                g = m.Groups[0];
                parts.SqlOrderBy = g + parts.SqlCount.Substring(g.Index + g.Length);
                parts.SqlCount   = parts.SqlCount.Substring(0, g.Index);
            }

            return(true);
        }
        public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
        {
            var helper = (PagingHelper)PagingUtility;

            // when the query does not contain an "order by", it is very slow
            if (helper.SimpleRegexOrderBy.IsMatch(parts.SqlSelectRemoved))
            {
                var m = helper.SimpleRegexOrderBy.Match(parts.SqlSelectRemoved);
                if (m.Success)
                {
                    var g = m.Groups[0];
                    parts.SqlSelectRemoved = parts.SqlSelectRemoved.Substring(0, g.Index);
                }
            }
            if (helper.RegexDistinct.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 ?? "ORDER BY (SELECT NULL)", parts.SqlSelectRemoved, args.Length, args.Length + 1);

            args = args.Concat(new object[] { skip, skip + take }).ToArray();
            return(sqlPage);
        }
Example #7
0
 public override string BuildPageQuery(long skip, long take, SQLParts parts, ref object[] args)
 {
     throw new NotSupportedException("The Access provider does not support paging.");
 }