Exemple #1
0
        public virtual string PagingBuild(ref PartedSql partedSql, object args, long skip, long take)
        {
            if (string.IsNullOrEmpty(partedSql.OrderBy))
            {
                throw new InvalidOperationException("miss order by");
            }

            var hasDistinct = partedSql.Select.IndexOf("DISTINCT", StringComparison.OrdinalIgnoreCase) == 0;
            var select      = "SELECT";

            if (hasDistinct)
            {
                partedSql.Select = partedSql.Select.Substring("DISTINCT".Length);
                select           = "SELECT DISTINCT";
            }
            if (skip <= 0)
            {
                var sbSql = StringBuilderCache.Allocate().AppendFormat("{0} TOP {1} {2}", select, take, partedSql.Select)
                            .Append(" FROM ").Append(partedSql.Body).Append(" order by ").Append(partedSql.OrderBy);
                return(StringBuilderCache.ReturnAndFree(sbSql));
            }
            else
            {
                var sbSql = StringBuilderCache.Allocate()
                            .AppendFormat("SELECT * FROM (SELECT {0}, ROW_NUMBER() OVER " +
                                          "(order by {1}) As RowNum FROM {2}) AS RowConstrainedResult " +
                                          "WHERE RowNum > {3} AND RowNum <= {4}",
                                          partedSql.Select, partedSql.OrderBy, partedSql.Body, skip, skip + take);

                return(StringBuilderCache.ReturnAndFree(sbSql));
            }
        }
Exemple #2
0
        /// <summary>
        /// 分割SQL
        /// </summary>
        public static PartedSql SplitSql(string sql)
        {
            var parts = new PartedSql {
                Raw = sql
            };

            // Extract the sql from "SELECT <whatever> FROM"
            var s = _rexSelect1.Match(sql);

            if (s.Success)
            {
                parts.Select = s.Groups[1].Value;


                sql = sql.Substring(s.Length);
                s   = _rexOrderBy.Match(sql);
                if (s.Success)
                {
                    sql           = sql.Substring(0, s.Index);
                    parts.OrderBy = s.Groups[1].Value;
                }
                parts.Body = "(" + sql;

                return(parts);
            }

            var m = _rexSelect.Match(sql);

            if (!m.Success)
            {
                throw new ArgumentException("Unable to parse SQL statement for select");
            }
            parts.Select = m.Groups[1].Value;


            sql = sql.Substring(m.Length);
            m   = _rexOrderBy.Match(sql);
            if (m.Success)
            {
                sql           = sql.Substring(0, m.Index);
                parts.OrderBy = m.Groups[1].Value;
            }
            parts.Body = sql;

            return(parts);
        }
Exemple #3
0
        public virtual string PagingBuild(ref PartedSql partedSql, object args, long skip, long take)
        {
            var pageSql = $"{partedSql.Raw} LIMIT {take} OFFSET {skip}";

            return(pageSql);
        }
Exemple #4
0
 public static string GetCountSql(PartedSql sql)
 {
     return($"SELECT COUNT(*) FROM {sql.Body}");
 }