private static void AddReverseCrossReferenceJoin( StringBuilder sql, ReverseCrossReferenceFieldDefinition join, TableAliasCollection aliasCollection) { sql.AppendLine() .Append("\t") .AppendFormat( "INNER JOIN {0} ON {0}.[{1}] = {2}.[{3}]", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), join.ReferenceField.SystemName, aliasCollection.GetTableAlias(join.DeclaringProcess), Constants.IdColumnName) .AppendLine() .Append("\t") .AppendFormat( "INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{2}]", join.ReferencedProcess.SystemName, aliasCollection.GetTableAlias(join.ReferencedProcess), Constants.IdColumnName, aliasCollection.GetReverseCrossReferenceItemsQueryName(join)); }
private void AddChecklistJoin(StringBuilder sql, ChecklistFieldDefinition join, TableAliasCollection aliasCollection) { sql.AppendLine() .Append("\t") .AppendFormat( "INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{4}]", GetJoinTableName(join), aliasCollection.GetJoinTableAlias(join), GetJoinTableMasterKey(join), aliasCollection.GetTableAlias(join.DeclaringProcess), Constants.IdColumnName) .AppendLine() .Append("\t") .AppendFormat( "INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{4}] AND {1}.[{5}] = 0", join.ReferencedProcess.SystemName, aliasCollection.GetTableAlias(join.ReferencedProcess), Constants.IdColumnName, aliasCollection.GetJoinTableAlias(join), GetJoinTableChildKey(join), Constants.IsRemovedColumnName); }
private static void AddBaseTableJoins( StringBuilder sql, ProcessDefinition process, ProcessDefinition lastProcess, TableAliasCollection aliasCollection) { if (process == lastProcess || process.BaseProcess == null) return; sql.AppendLine() .Append("\t") .AppendFormat( @"INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{4}]", process.BaseProcess.SystemName, aliasCollection.GetTableAlias(process.BaseProcess), Constants.IdColumnName, aliasCollection.GetTableAlias(process), Constants.BaseIdColumnName); AddBaseTableJoins(sql, process.BaseProcess, lastProcess, aliasCollection); }
private static void AddSingleCrossReferenceJoin(StringBuilder sql, SingleCrossReferenceFieldDefinition join, TableAliasCollection aliasCollection) { sql.AppendLine() .Append("\t") .AppendFormat( @"INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{4}] AND {1}.[{5}] = 0", join.ReferencedProcess.SystemName, aliasCollection.GetTableAlias(join.ReferencedProcess), Constants.IdColumnName, aliasCollection.GetTableAlias(join.DeclaringProcess), join.SystemName, Constants.IsRemovedColumnName); }
private string GenerateExpressionAffectedItemsQuery(ExpressionDependency dependency) { var sql = new StringBuilder(); var aliasCollection = new TableAliasCollection(); var queries = GetJoinQueries(dependency, aliasCollection); if (queries.Count > 0) { sql.AppendLine("WITH"); sql.AppendLine(string.Join(Environment.NewLine + ",", queries)); } sql.AppendFormat(@"SELECT DISTINCT {0}.[Id] FROM [dbo].[{1}] {0}", aliasCollection.GetTableAlias(dependency.DependentProcess), dependency.DependentProcess.SystemName); var currentProcess = dependency.DependentProcess; foreach (var @join in dependency.JoinFields) { AddBaseTableJoins(sql, currentProcess, @join.DeclaringProcess, aliasCollection); AddJoin(sql, @join, aliasCollection); currentProcess = @join.ReferencedProcess; } sql.AppendLine().AppendFormat( "WHERE {0}.[{1}] = 0 AND {2}.[{3}] = @id", aliasCollection.GetTableAlias(dependency.DependentProcess), Constants.IsRemovedColumnName, aliasCollection.GetTableAlias(dependency.JoinFields.Last().ReferencedProcess), Constants.IdColumnName); return sql.ToString(); }