Exemple #1
0
        public string BuildSelectCountSql(string tableA, string tableB, DataJoinType type, string aId, string bId, string where, string group = null)
        {
            string ta   = EscapeName(tableA);
            string tb   = EscapeName(tableB);
            string join = string.Concat(type.ToString().ToUpper(), " JOIN ", tb, " ON ", ta, '.', EscapeName(aId), '=', tb, '.', EscapeName(bId));

            return(BuildSelectCountSqlImpl(ta, join, where, group));
        }
Exemple #2
0
 internal SelectQuery JoinSelect(string ta, string aid, DataJoinType type, string tb, string bid, string sql, DataParameter[] ps)
 {
     if (ps != null && ps.Length > 0)
     {
         _ps.AddRange(ps);
     }
     _table  = string.Concat(_table, ' ', type.ToString().ToUpper(), " JOIN (", sql, ") AS ", tb, " ON ", ta, '.', aid, '=', tb, '.', bid);
     _prefix = true;
     return(this);
 }
Exemple #3
0
        public string BuildSelectJoinSql(string tableA, string tableB, DataJoinType type, string aId, string bId, string select = null, string where = null, string order = null, string group = null, int size = 0, long index = 1, bool append = false)
        {
            string ta = EscapeName(tableA);
            string tb = EscapeName(tableB);
            KeyValuePair <string, string> pair = GetTopOrLimit(size, index);

            if (string.IsNullOrEmpty(select))
            {
                select = string.Concat(ta, ".*,", tb, ".*");
            }
            string join = string.Concat(type.ToString().ToUpper(), " JOIN ", tb, " ON ", ta, '.', EscapeName(aId), '=', tb, '.', EscapeName(bId));

            return(BuildSelectSqlImpl(pair.Key, select, ta, join, where, group, order, pair.Value, append));
        }
Exemple #4
0
 internal SelectQuery JoinOn(string ta, string aid, DataJoinType type, string tb, string bid)
 {
     _table  = string.Concat(_table, ' ', type.ToString().ToUpper(), " JOIN ", tb, " ON ", ta, '.', aid, '=', tb, '.', bid);
     _prefix = true;
     return(this);
 }
Exemple #5
0
        private static IList <DataJoin <A, B> > ExecuteJoinReaderWithRowNumber <A, B>(DataSource ds, string select, string where, DataOrder[] os, string group, long index, int size, out long count, string aId, string bId, DataJoinType type, DataParameter[] ps) where A : DbTable, new() where B : DbTable, new()
        {
            StringBuilder sb = new StringBuilder();

            count = ExecuteCount <A, B>(ds, where, group, aId, bId, type, ps);
            string tableA = GetTableName <A>();
            string tableB = GetTableName <B>();
            string ta     = ds.Provider.EscapeName(tableA);
            string tb     = ds.Provider.EscapeName(tableB);
            long   half   = count / 2;
            long   lower  = (index - 1) * size;
            long   upper  = index * size;

            if (string.IsNullOrEmpty(select))
            {
                select = string.Concat(ta, ".*,", tb, ".*");
            }
            if (where == null)
            {
                where = string.Empty;
            }
            else
            {
                where = string.Concat("WHERE ", where);
            }
            string order  = DataProvider.GetSqlString(os, ds, true, true);
            string torder = DataProvider.GetSqlString(os, ds, true, false);

            if (order == null)
            {
                order = string.Empty;
            }
            if (torder == null)
            {
                torder = string.Empty;
            }

            if (lower > half)
            {
                if (torder.Length > 0)
                {
                    torder = Providers.MSSQLProvider.ORDER_REGEX.Replace(torder, new MatchEvaluator((m) =>
                    {
                        return("A".Equals(m.Groups[1].Value, StringComparison.OrdinalIgnoreCase) ? "DESC" : "ASC");
                    }));
                }
            }
            if (order.Length > 0)
            {
                order = string.Concat("ORDER BY ", order);
            }
            if (torder.Length > 0)
            {
                torder = string.Concat("ORDER BY ", torder);
            }

            long top = count - lower;

            if (top <= 0)
            {
                return(new List <DataJoin <A, B> >());
            }

            sb.Append("WITH CTE AS(SELECT TOP ");
            if (lower > half)
            {
                sb.Append(top);
            }
            else
            {
                sb.Append(upper);
            }
            sb.Append(" ROW_NUMBER() OVER(");
            sb.Append(torder);
            sb.Append(")AS _RowNumber,");
            sb.Append(select);
            sb.Append(" FROM ");
            sb.Append(ta);
            sb.Append(" ");
            sb.Append(type.ToString().ToUpper());
            sb.Append(" JOIN ");
            sb.Append(tb);
            sb.Append(" ON ");
            sb.Append(ta);
            sb.Append(".");
            sb.Append(ds.Provider.EscapeName(aId));
            sb.Append("=");
            sb.Append(tb);
            sb.Append(".");
            sb.Append(ds.Provider.EscapeName(bId));
            if (where.Length > 0)
            {
                sb.Append(" ");
                sb.Append(where);
            }
            if (torder.Length > 0)
            {
                sb.Append(" ");
                sb.Append(torder);
            }
            sb.Append(")SELECT * FROM CTE WHERE _RowNumber>");
            if (lower > half)
            {
                sb.Append(count - upper);
            }
            else
            {
                sb.Append(lower);
            }
            if (order.Length > 0)
            {
                sb.Append(' ').Append(order);
            }
            sb.Append(";");
            return(ds.ExecuteReader <DataJoin <A, B> >(sb.ToString(), ps));
        }