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));
}
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);
}
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));
}
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);
}
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));
}