Esempio n. 1
0
        DbQueryRowNumberBuilder IDbSelectQuery.BuildRowNumber(DataSource ds, long top, bool join, string order, bool reverse)
        {
            DbQueryRowNumberBuilder builder = new DbQueryRowNumberBuilder("WITH CTE AS(SELECT TOP ");

            builder.Append(top).Append(" ROW_NUMBER() OVER(").Append(order).Append(")AS _RowNumber,");
            if (_select == null || _select.Length == 0)
            {
                if (join)
                {
                    builder.Append((new DbSelect <T>()).Build(ds));
                }
                else
                {
                    builder.Append((new DbSelect()).Build(ds));
                }
            }
            else
            {
                for (int i = 0; i < _select.Length; ++i)
                {
                    if (i > 0)
                    {
                        builder.Append(',');
                    }
                    builder.Append(_select[i].Build(ds));
                }
            }
            builder.Append(" FROM ").Append(ds.Provider.EscapeName(DbTable.GetTableName <T>()));
            return(builder);
        }
Esempio n. 2
0
        private IList <dynamic> ExecuteImpl(int size, long page, out long count)
        {
            bool           group = false;
            DbQueryBuilder qb    = _query.BuildCount(_query.Query.DataSource, 0, false, ref group);

            if (group)
            {
                qb.Append(") AS T").Append(_query.Query.DataSource.PsCount);
            }
            count = Convert.ToInt64(_query.Query.DataSource.ExecuteScalar(qb.Sql, qb.Parameters));

            if (_query.Query.Provider.SupperRowNumber)
            {
                long half    = count / 2;
                long lower   = (page - 1) * size;
                long upper   = page * size;
                bool reverse = lower > half;
                DbQueryRowNumberBuilder builder = _query.BuildRowNumber(_query.Query.DataSource, reverse ? (count - lower) : upper, false, null, reverse);
                builder.Append(")SELECT * FROM CTE WHERE _RowNumber>");
                if (reverse)
                {
                    builder.Append(count - upper);
                }
                else
                {
                    builder.Append(lower);
                }
                if (builder.OrderBy != null)
                {
                    builder.Append(' ').Append(builder.OrderBy);
                }
                builder.Append(';');
                return(_query.Query.DataSource.ExecuteReader(builder.Sql, builder.Parameters));
            }

            if (_query.Query.Provider.SupperLimit)
            {
                DbQueryBuilder builder = _query.Build(_query.Query.DataSource, 0, false);
                builder.Append(" LIMIT ").Append(size).Append(" OFFSET ").Append((page - 1) * size);
                builder.Append(';');
                return(_query.Query.DataSource.ExecuteReader(builder.Sql, builder.Parameters));
            }

            if (_query.Query.Provider.SupperTop)
            {
                DbQueryBuilder builder = _query.Build(_query.Query.DataSource, page * size, false);
                builder.Append(';');
                IList <dynamic> list  = _query.Query.DataSource.ExecuteReader(builder.Sql, builder.Parameters);
                List <dynamic>  array = new List <dynamic>(size);
                for (long i = ((page - 1) * size); i < list.Count; ++i)
                {
                    array.Add(list[(int)i]);
                }
                return(array);
            }

            throw new NotSupportedException();
        }
Esempio n. 3
0
        DbQueryRowNumberBuilder IDbSelectQuery.BuildRowNumber(DataSource ds, long top, bool join, string order, bool reverse)
        {
            StringBuilder osb = new StringBuilder(" ORDER BY ");

            for (int i = 0; i < _order.Length; ++i)
            {
                if (i > 0)
                {
                    osb.Append(',');
                }
                osb.Append(_order[i].BuildBase(ds));
            }
            StringBuilder tsb = new StringBuilder(osb.Length);

            tsb.Append("ORDER BY ");
            if (reverse)
            {
                for (int i = 0; i < _order.Length; ++i)
                {
                    if (i > 0)
                    {
                        tsb.Append(',');
                    }
                    tsb.Append(_order[i].BuildReverse(ds));
                }
            }
            else
            {
                for (int i = 0; i < _order.Length; ++i)
                {
                    if (i > 0)
                    {
                        tsb.Append(',');
                    }
                    tsb.Append(_order[i].Build(ds));
                }
            }
            string torder = tsb.ToString();
            DbQueryRowNumberBuilder builder = _query.BuildRowNumber(ds, top, join, torder, reverse);

            builder.OrderBy = osb.ToString();
            builder.Append(' ').Append(torder);
            return(builder);
        }