internal JoinQuery(SelectQuery query, string table, string id, DataJoinType type = DataJoinType.Inner)
{
_query = query;
_table = table;
_id = id;
_type = type;
}
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));
}
public JoinQuery Join <T>(string id, DataJoinType type = DataJoinType.Inner) where T : IDbReader, new()
{
return(new JoinQuery(this, _ds.Provider.EscapeName(DbTable.GetTableName <T>()), _ds.Provider.EscapeName(id), type));
}
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);
}
public static IList <DataJoin <A, B> > ExecuteReader <A, B>(DataSource ds, int top, DataColumn[] columns, DataColumn[] group, DataOrder[] order, string aId, string bId, DataJoinType type = DataJoinType.Inner, DataWhereQueue ps = null) where A : DbTable, new() where B : DbTable, new()
{
return(ds.ExecuteReader <DataJoin <A, B> >(ds.Provider.BuildSelectJoinSql(GetTableName <A>(), GetTableName <B>(), type, aId, bId, DataProvider.GetSqlString(columns, ds, true, true), DataProvider.GetSqlString(ps, ds, true, false), DataProvider.GetSqlString(order, ds, true, false), DataProvider.GetSqlString(group, ds, true, false), top), DataWhereQueue.GetParameters(ps)));
}
public static V ExecuteScalar <A, B, V>(DataSource ds, DataColumn column, DataColumn[] group, DataOrder[] order, string aId, string bId, DataJoinType type = DataJoinType.Inner, DataWhereQueue ps = null) where A : DbTable, new() where B : DbTable, new()
{
return(ds.ExecuteScalar <V>(ds.Provider.BuildSelectJoinSql(GetTableName <A>(), GetTableName <B>(), type, aId, bId, column.GetSqlString(ds, true, true), DataProvider.GetSqlString(ps, ds, true, false), DataProvider.GetSqlString(order, ds, true, false), DataProvider.GetSqlString(group, ds, true, false)), DataWhereQueue.GetParameters(ps)));
}
public static IList <DataJoin <A, B> > ExecuteReader <A, B>(DataSource ds, DataColumn[] columns, DataColumn[] group, DataOrder[] order, int index, int size, out int count, string aId, string bId, DataJoinType type = DataJoinType.Inner, DataWhereQueue ps = null) where A : DbTable, new() where B : DbTable, new()
{
long total;
IList <DataJoin <A, B> > result = ExecuteReader <A, B>(ds, columns, group, order, index, size, out total, aId, bId, type, ps);
count = (int)total;
return(result);
}
public static IList <DataJoin <A, B> > ExecuteReader <A, B>(DataSource ds, DataColumn[] columns, DataColumn[] group, DataOrder[] order, long index, int size, out long count, string aId, string bId, DataJoinType type = DataJoinType.Inner, DataWhereQueue ps = null) where A : DbTable, new() where B : DbTable, new()
{
if (ds.Provider.SupperRowNumber)
{
return(ExecuteJoinReaderWithRowNumber <A, B>(ds, DataProvider.GetSqlString(columns, ds, true, true), DataProvider.GetSqlString(ps, ds, true, false), order, DataProvider.GetSqlString(group, ds, true, false), index, size, out count, aId, bId, type, DataWhereQueue.GetParameters(ps)));
}
IList <DataJoin <A, B> > list = ExecuteJoinReaderWithLimitOrTop <A, B>(ds, DataProvider.GetSqlString(columns, ds, true, true), DataProvider.GetSqlString(ps, ds, true, false), DataProvider.GetSqlString(order, ds, true, false), DataProvider.GetSqlString(group, ds, true, false), index, size, out count, aId, bId, type, DataWhereQueue.GetParameters(ps));
if (ds.Provider.SupperTop)
{
list = SplitJoinList(list, index, size);
}
return(list);
}
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));
}
private static IList <DataJoin <A, B> > ExecuteJoinReaderWithLimitOrTop <A, B>(DataSource ds, string select, string where, string order, 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()
{
count = ExecuteCount <A, B>(ds, where, group, aId, bId, type, ps);
return(ds.ExecuteReader <DataJoin <A, B> >(ds.Provider.BuildSelectJoinSql(GetTableName <A>(), GetTableName <B>(), type, aId, bId, select, where, order, group, size, index), ps));
}
public static DataJoin <A, B> ExecuteSingleRow <A, B>(DataSource ds, DataColumn[] columns, string aId, string bId, DataJoinType type = DataJoinType.Inner, DataWhereQueue ps = null) where A : DbTable, new() where B : DbTable, new()
{
return(ds.ExecuteSingleRow <DataJoin <A, B> >(ds.Provider.BuildSelectJoinSql(GetTableName <A>(), GetTableName <B>(), type, aId, bId, DataProvider.GetSqlString(columns, ds, true, true), DataProvider.GetSqlString(ps, ds, true, false)), DataWhereQueue.GetParameters(ps)));
}
private static long ExecuteCount <A, B>(DataSource ds, string where, string group, string aId, string bId, DataJoinType type, DataParameter[] ps) where A : DbTable where B : DbTable
{
return(Convert.ToInt64(ds.ExecuteScalar(ds.Provider.BuildSelectCountSql(GetTableName <A>(), GetTableName <B>(), type, aId, bId, where, group), ps)));
}
public static long ExecuteCount <A, B>(DataSource ds, DataColumn[] group, string aId, string bId, DataJoinType type = DataJoinType.Inner, DataWhereQueue ps = null) where A : DbTable where B : DbTable
{
return(ExecuteCount <A, B>(ds, DataProvider.GetSqlString(ps, ds, true, false), DataProvider.GetSqlString(group, ds, true, false), aId, bId, type, DataWhereQueue.GetParameters(ps)));
}
