public DataTable GetSubJoinStrings(DataTable pJoinStrings, JoinTypes pJoinTypes)
{
string schemaCondition = string.Empty;
switch (pJoinTypes)
{
case JoinTypes.JoinWithLookupTables: schemaCondition = "SelectPart LIKE 'LT%'"; break;
case JoinTypes.JoinWithRelatedTables: schemaCondition = "SelectPart LIKE 'RT%'"; break;
case JoinTypes.JoinWithDetailTables: schemaCondition = "SelectPart LIKE 'DT%'"; break;
case JoinTypes.JoinWithLookupAndRelatedTables: schemaCondition = "SelectPart LIKE 'LT%' OR SelectPart LIKE 'RT%'"; break;
case JoinTypes.JoinWithLookupAndDetailTables: schemaCondition = "SelectPart LIKE 'LT%' OR SelectPart LIKE 'DT%'"; break;
case JoinTypes.JoinWithRelatedAndDetailTables: schemaCondition = "SelectPart LIKE 'RT%' OR SelectPart LIKE 'DT%'"; break;
case JoinTypes.JoinAll: break;
}
DataTable oDt = new DataTable();
if (!string.IsNullOrWhiteSpace(schemaCondition))
{
oDt = pJoinStrings.FilterDataTable(schemaCondition);
}
return(oDt);
}
public static SelectQuery Join <TJoin, TSelect>(this SelectQuery query, JoinTypes joinTypes, string tableName, string joinAlias, string selectAlias, Expression <Func <TJoin, TSelect, bool> > selectors)
{
var joinComponent = new JoinComponent <TJoin, TSelect>(joinTypes, tableName, joinAlias, selectAlias, selectors);
query.Joins.Add(joinComponent);
return(query);
}
public void AddManyToManyJoin <T>(JoinTypes jointype, string viaSchema, string viaTable, string viaAlias, string myColumn, string parentColumn)
{
string join;
switch (jointype)
{
case JoinTypes.LeftJoin:
join = "LEFT JOIN";
break;
case JoinTypes.RightJoin:
join = "RIGHT JOIN";
break;
case JoinTypes.InnerJoin:
join = "INNER JOIN";
break;
default:
throw new NotSupportedException("Join types");
}
var typePair = GetTable <T>();
//e.g.LEFT JOIN dbo.CustomerAddress on dbo.CustomerAddress.customerId = customer.id
_joins.Add($"{join} [{viaSchema}].[{viaTable}] {viaAlias} ON {viaAlias}.[{3}Id] = {_fromAlias}.[Id]");
_joins.Add($"{join} [{typePair.Key.Schema}].[{typePair.Key.Table}] {typePair.Key.Alias}" +
$" ON {typePair.Key.Alias}.[Id] = {viaAlias}.[{typePair.Key.Table}Id]");
}
public void AddJoin <T>(JoinTypes jointype, string myColumn, string parentColumn)
{
string join;
switch (jointype)
{
case JoinTypes.LeftJoin:
join = "LEFT JOIN";
break;
case JoinTypes.RightJoin:
join = "RIGHT JOIN";
break;
case JoinTypes.InnerJoin:
join = "INNER JOIN";
break;
default:
throw new NotSupportedException("Join types");
}
var typePair = GetTable <T>();
_joins.Add($"{join} [{typePair.Key.Schema}].[{typePair.Key.Table}] {typePair.Key.Alias}" +
$" ON {typePair.Key.Alias}.[{myColumn}] = {_fromAlias}.[{parentColumn}]");
}
public DataTable GetJoinString(ref string pSelectPart, ref string pJoinPart, JoinTypes pJoinTypes, string pSchemaName, string pTableName, string pLookupTablePrefix)
{
string schemaCondition = string.Empty;
switch (pJoinTypes)
{
case JoinTypes.JoinWithLookupTables: schemaCondition = string.Join("", " AND TC.TABLE_SCHEMA LIKE '", pLookupTablePrefix, "%'"); break;
case JoinTypes.JoinWithDetailTables: schemaCondition = string.Join("", " AND TC.TABLE_SCHEMA NOT LIKE '", pLookupTablePrefix, "%'"); break;
case JoinTypes.JoinAll: break;
}
string sql = @"
WITH RELATION AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY TC.TABLE_NAME ASC) AS RowNo,
TC.TABLE_CATALOG + '.' + TC.TABLE_SCHEMA + '.' + TC.TABLE_NAME AS PKTableName,
CCU2.COLUMN_NAME AS PKColumnName,
CCU.COLUMN_NAME AS FKColumnName
FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU
INNER JOIN
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC ON CCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND CCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2 ON CCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
AND CCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON TC.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
WHERE
CCU.TABLE_SCHEMA = @pSchemaName AND
CCU.TABLE_NAME = @pTableName {0}
)
SELECT
'T' + CAST(RowNo AS VARCHAR(30)) + '.*' AS SelectPart,
'INNER JOIN ' + PKTableName + ' AS T' + CAST(RowNo AS VARCHAR(30)) + ' ON T' + CAST(RowNo AS VARCHAR(30)) + '.' + PKColumnName + ' = ' + ' MT.' + FKColumnName AS JoinPart
FROM
RELATION
";
sql = string.Format(sql, schemaCondition);
SqlCommand cmd = new SqlCommand(sql, Conn);
cmd.Parameters.AddWithValue("@pSchemaName", pSchemaName);
cmd.Parameters.AddWithValue("@pTableName", pTableName);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);
DataTable oDt = new DataTable();
oDt.Load(dr);
return(oDt);
}
public DataTable GetJoinString(ref string pSelectPart, ref string pJoinPart, JoinTypes pJoinTypes, string pSchemaName, string pTableName, string pLookupTablePrefix)
{
string schemaCondition = string.Empty;
switch (pJoinTypes)
{
case JoinTypes.JoinWithLookupTables: schemaCondition = string.Join("", " AND clm.table_name LIKE '", pLookupTablePrefix, "%'"); break;
case JoinTypes.JoinWithDetailTables: schemaCondition = string.Join("", " AND clm.table_name NOT LIKE '", pLookupTablePrefix, "%'"); break;
case JoinTypes.JoinAll: break;
}
string sql = @"
WITH RELATION AS
(
SELECT
row_number() OVER (ORDER BY clm.table_name ASC) AS RowNo,
cspk.owner || '.' || cspk.table_name AS PKTableName,
clm2.column_name AS PKColumnName,
clm.column_name AS FKColumnName
FROM
all_cons_columns clm
INNER JOIN
all_constraints cs ON cs.owner = clm.owner AND cs.constraint_name = clm.constraint_name
INNER JOIN
all_constraints cspk ON cspk.owner = cs.r_owner AND cspk.constraint_name = cs.r_constraint_name
INNER JOIN
all_cons_columns clm2 ON clm2.constraint_name = cspk.constraint_name AND clm2.owner = cspk.owner
WHERE
clm.owner = :pSchemaName AND
clm.Table_Name = :pTabloName AND
cs.constraint_type = 'R' {0}
)
SELECT
'T' || CAST(RowNo AS VARCHAR(30)) || '.*' AS SelectPart,
'INNER JOIN ' || PKTableName || ' T' || CAST(RowNo AS VARCHAR(30)) || ' ON T' || CAST(RowNo AS VARCHAR(30)) || '.' || PKColumnName || ' = ' || ' MT.' || FKColumnName AS JoinPart
FROM
RELATION
";
sql = string.Format(sql, schemaCondition);
//OracleCommand cmd = new OracleCommand(sql, Conn);
//cmd.Parameters.Add(":pSchemaName", pSchemaName);
//cmd.Parameters.Add(":pTabloName", pTableName);
//OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);
//DataTable oDt = new DataTable();
//oDt.Load(dr);
//return oDt;
return(null);
}
public Criteria(Type originalEntityType, string originalEntityProperty, Type joinedEntityType, string joinEntityProperty, JoinTypes joinType, ComparisonOperators comparisonOperators)
{
this.OriginalEntityTypeName = originalEntityType.GetField("TableName").GetRawConstantValue().ToString();
this.OriginalEntityProperty = originalEntityProperty;
this.JoinedEntityTypeName = joinedEntityType.GetField("TableName").GetRawConstantValue().ToString();
this.JoinEntityProperty = joinEntityProperty;
this.CriteriaType = CriteriaTypes.Join;
this.JoinType = joinType;
this.ComparisonOperator = comparisonOperators;
}
private string GetJoinTypeText(JoinTypes type)
{
if (type == JoinTypes.And)
{
return(PhraseLib.Lookup("term.and", LanguageID));
}
else
{
return(PhraseLib.Lookup("term.or", LanguageID));
}
}
public JoinComponent(JoinTypes joinTypes, string tableName, string joinAlias, string selectAlias, Expression <Func <TJoin, TSelect, bool> > selectors)
{
JoinAlias = joinAlias;
TableName = tableName;
JoinTypes = joinTypes;
var expressionJoinObjectProperties = selectors.Parameters[0].ToExpressionProperty(joinAlias);
var expressionSelectObjectProperties = selectors.Parameters[1].ToExpressionProperty(selectAlias);
ConditionalComponent = new ConditionalComponent((BinaryExpression)selectors.Body, new[] { expressionJoinObjectProperties, expressionSelectObjectProperties });
}
public SqlJoin(
string tableName,
JoinTypes joinType = JoinTypes.Inner,
string joinExpression = null,
string _as = null)
{
TableName = tableName;
JoinType = joinType;
JoinExpression = joinExpression;
As = _as;
}
public TableJoin(JoinTypes type, string joinTable, string table, string joinField, string tableField)
{
conditions = new QueryCondition();
Type = type;
JoinTable = joinTable;
Table = table;
JoinField = joinField;
TableField = tableField;
AddCondition(new DataField(joinTable, joinField).ToString(), new DataField(table, tableField));
}
public TableJoin(JoinTypes type, DataField joinField, DataField tableField)
{
conditions = new QueryCondition();
Type = type;
JoinTable = joinField.Table;
JoinField = joinField.Name;
Table = tableField.Table;
TableField = tableField.Name;
AddCondition(joinField.ToString(), tableField);
}
private void ToggelConditionJoinType(long ConditionID, int ConditionTypeID, JoinTypes jointype)
{
if (jointype == JoinTypes.And)
{
jointype = JoinTypes.Or;
}
else
{
jointype = JoinTypes.And;
}
if (objOffer.EngineID == 2 || objOffer.EngineID == 9 || objOffer.EngineID == 0)
{
m_Offer.ToggleOfferEligibleConditionJoinType(objOffer.OfferID, jointype, ConditionTypeID);
}
m_Offer.UpdateOfferStatusToModified(objOffer.OfferID, objOffer.EngineID, AdminUserID);
Response.Redirect(hdnPath.Value, false);
}
public static JoinTypes Parse(string code)
{
JoinTypes type = JoinTypes.Join;
if (code.Equals("left", StringComparison.OrdinalIgnoreCase))
{
type = JoinTypes.Left;
}
else if (code.Equals("right", StringComparison.OrdinalIgnoreCase))
{
type = JoinTypes.Right;
}
else if (code.Equals("joint", StringComparison.OrdinalIgnoreCase))
{
type = JoinTypes.Join;
}
else if (code.Equals("outer", StringComparison.OrdinalIgnoreCase))
{
type = JoinTypes.Outer;
}
return(type);
}
public static string Format(JoinTypes type)
{
string rezult = string.Empty;
if ((type & JoinTypes.Left) == JoinTypes.Left)
{
rezult = "left";
}
else if ((type & JoinTypes.Right) == JoinTypes.Right)
{
rezult = "right";
}
if ((type & JoinTypes.Outer) == JoinTypes.Outer)
{
rezult += " outer";
}
if (type != JoinTypes.Undefined)
{
rezult += " join";
}
return(rezult);
}
public Join CreateJoin(string targetTable, string sourceColumn, string targetColumn, string targetTableAlias = null, JoinTypes type = JoinTypes.Inner, bool isEqui = true)
{
if (targetTable == null)
{
throw new ArgumentNullException(nameof(targetTable));
}
if (sourceColumn == null)
{
throw new ArgumentNullException(nameof(sourceColumn));
}
if (targetColumn == null)
{
throw new ArgumentNullException(nameof(targetColumn));
}
Join join = new Join(targetTable, sourceColumn, targetColumn, targetTableAlias, type, true);
Joins.Add(join);
return(join);
}
/// <summary>
/// Adds a JOIN clause.
/// </summary>
/// <param name="table">The table.</param>
/// <param name="joinType">Type of the join.</param>
/// <param name="conditions">The conditions.</param>
public void addJoin(string table, JoinTypes joinType, WhereConds conditions)
{
this._joins.AddLast(new Join(joinType, table, conditions));
}
public Join(JoinTypes type, string table, WhereConds conditions)
{
joinType = type;
this.table = table;
joinConditions = conditions;
}
public Join(JoinTypes type, string table, WhereConds conditions)
{
joinType = type;
this.table = table;
joinConditions = conditions;
}
public TableJoin AddJoin(JoinTypes type, DataField joinField, DataField tableField)
{
TableJoin tj = new TableJoin(type, joinField, tableField);
joins.Add(tj);
return tj;
}
public JoinType(JoinTypes type)
{
Type = type;
}
/// <summary>
/// Adds a JOIN clause.
/// </summary>
/// <param name="table">The table.</param>
/// <param name="joinType">Type of the join.</param>
/// <param name="conditions">The conditions.</param>
public void addJoin(string table, JoinTypes joinType, WhereConds conditions)
{
this._joins.AddLast(new Join(joinType, table, conditions));
}
/// <summary>
///
/// </summary>
/// <param name="type">Type of table join</param>
/// <param name="table">Table adjoining the parent</param>
/// <param name="joinField">Join field in the table being adjoined to the parent</param>
/// <param name="tableField">Join field in the parent table</param>
public TableJoin AddJoin(JoinTypes type, string table, string joinField, string tableField)
{
TableJoin tj = new TableJoin(type, this.tables[0], table, joinField, tableField);
joins.Add(tj);
return tj;
}
/// <summary>
/// Adds a subsequent table to the query
/// </summary>
/// <param name="leftTable">Name of table on left side of join</param>
/// <param name="leftColumn">Name of column on left side of join</param>
/// <param name="newTable">Name of table being added</param>
/// <param name="rightColumn">Name of column on right side of join</param>
/// <param name="type">Join type</param>
public void AddTable(string leftTable, string leftColumn, string newTable, string rightColumn, JoinTypes type)
{
this._tables.Add(new TableInfo
{
Name = newTable,
JoinType = type,
LeftTable = leftTable,
LeftColumn = leftColumn,
RightColumn = rightColumn
});
}
public Join(string table, string on, JoinTypes joinType)
: this(table, on)
{
this.JoinType = joinType;
}
public Join(string table, string on, JoinTypes joinType)
: this(table, on)
{
this.JoinType = joinType;
}
