/// <summary> /// Gets the column alias. /// </summary> /// <param name="mainTable">The main table.</param> /// <param name="columnListType">Type of the column list.</param> /// <param name="field">The field.</param> /// <returns>System.String.</returns> private static string GetColumnAlias(ITableDefinition mainTable, ColumnListType columnListType, TableFieldDefinition field) { var relationship = GetRelationship(mainTable, field); if (relationship == null) return string.Empty; if (columnListType == ColumnListType.InnerSelect || columnListType == ColumnListType.Select) return string.Format("{0}{1}", relationship.Prefix, field.ReferencedColumnName); return string.Empty; }
public static String BuildSqlColumnList(List <string> columnList, String tableName, ColumnListType columnListType) { //TODO: Use change to a string builder String sqlColumnList = string.Empty; String parameterPreface = "@"; foreach (String column in columnList) { if (columnListType == ColumnListType.List) { sqlColumnList += tableName + "." + column + ", "; } else if (columnListType == ColumnListType.ParameterizedList) { sqlColumnList += parameterPreface + column + ", "; } else if (columnListType == ColumnListType.UpdateParameterizedList) { sqlColumnList += tableName + "." + column + " = " + parameterPreface + column + ", "; } else if (columnListType == ColumnListType.WhereParameterizedList) { sqlColumnList += tableName + "." + column + " = " + parameterPreface + column + " AND "; } } if (columnListType == ColumnListType.WhereParameterizedList) { //Remove last " AND " sqlColumnList = sqlColumnList.Substring(0, sqlColumnList.Length - 5); } else { //Remove last ", " sqlColumnList = sqlColumnList.Substring(0, sqlColumnList.Length - 2); } return(sqlColumnList); }
/// <summary> /// Gets the column list. /// </summary> /// <param name="process">The process.</param> /// <param name="tablePrefix">The table prefix.</param> /// <param name="forceTag">if set to <c>true</c> [force tag].</param> /// <param name="skipMultiRefs">if set to <c>true</c> [skip multi refs].</param> /// <param name="resolveRefTypes">if set to <c>true</c> [resolve reference types].</param> /// <param name="fieldList">The field list.</param> /// <param name="columnListType">Type of the column list.</param> /// <returns>System.String.</returns> private static string GetColumnList(IProcessDefinition process, string tablePrefix, bool forceTag, bool skipMultiRefs, bool resolveRefTypes, IEnumerable<TableFieldDefinition> fieldList, ColumnListType columnListType) { var selectList = new List<string>(); var multiRefCount = 1; foreach (var field in fieldList.Where(f => f.GenerateDbColumn)) switch (field.ColumnType) { case ColumnTypes.Reference: if (resolveRefTypes) if (columnListType == ColumnListType.Select) selectList.Add(string.Format("\"\"{0}\"\"", GetColumnAlias(process.RootTable, columnListType, field))); else selectList.Add(string.Format("\"\"{0}\"\" {1}", OracleNamesTranslator.Translate(field.ReferencedColumnName), GetColumnAlias(process.RootTable, columnListType, field))); else goto default; break; case ColumnTypes.MultiReference: if (skipMultiRefs) continue; if (forceTag) selectList.Add(string.Format("\"\"{0}\"\"", OracleNamesTranslator.Translate("NameValues" + multiRefCount++))); else { selectList.Add(string.Format( @" ( SELECT {0} FROM ""{3}"" INNER JOIN ""{1}"" j ON j.""{3}Id"" = ""{3}"".""Id"" WHERE (j.""{2}"" = t0.""id"") FOR XML PATH('r') ) AS ""NameValues{4}"" ", GetMultiRefExpression(field.MultiRefDisplayFields), OracleNamesTranslator.Translate(field.ReferenceJoinTableName), OracleNamesTranslator.Translate(field.ReferencedColumnName), OracleNamesTranslator.Translate(field.ReferenceTableName), multiRefCount++)); } break; case ColumnTypes.Image: switch (columnListType) { case ColumnListType.Select: selectList.Add(string.Format("\"\"{2}_{0}\"\".\"\"{3}\"\" AS \"\"{0}\"\" {1}", OracleNamesTranslator.Translate(field.ColumnName), Environment.NewLine, tablePrefix, OracleNamesTranslator.Translate(Constants.BinaryContentColumnName))); selectList.Add(string.Format("\"\"{0}\"\" {1}", OracleNamesTranslator.Translate(field.ColumnName), Environment.NewLine)); break; case ColumnListType.GroupBy: selectList.Add(string.Format("t0.\"\"{0}\"\" {1}", OracleNamesTranslator.Translate(field.ColumnName), Environment.NewLine)); break; case ColumnListType.InnerSelect: selectList.Add(string.Format("t0.\"\"{0}\"\" {0} {1}", OracleNamesTranslator.Translate(field.ColumnName), Environment.NewLine)); break; } break; case ColumnTypes.Video: case ColumnTypes.Checklist: case ColumnTypes.ReverseReference: case ColumnTypes.ReverseMultiReference: case ColumnTypes.TreeView: case ColumnTypes.AuditTrail: continue; default: selectList.Add(string.Format("\"\"{0}\"\"{1}", OracleNamesTranslator.Translate(field.ColumnName), Environment.NewLine)); break; } if (process.HasBaseClass) selectList.Add(string.Format("\"\"{0}\"\"{1}", OracleNamesTranslator.Translate(Constants.ParentIdColumnName), Environment.NewLine)); return string.Join(",", selectList); }
private static string GetColumnList(IProcessDefinition process, string tablePrefix, bool forceTag, bool skipMultiRefs, bool resolveRefTypes, IEnumerable<TableFieldDefinition> fieldList, ColumnListType columnListType) { var selectList = new List<string>(); var multiRefCount = 1; foreach (var field in fieldList.Where(f => f.GenerateDbColumn)) switch (field.ColumnType) { case ColumnTypes.Reference: if (resolveRefTypes) if (columnListType == ColumnListType.Select) selectList.Add(string.Format("[{0}].[{1}]", tablePrefix, GetColumnAlias(process.RootTable, columnListType, field))); else selectList.Add(string.Format("[{0}].[{1}] [{2}]", forceTag ? tablePrefix : GetRelationship(process.RootTable, field).Prefix, field.ReferencedColumnName, GetColumnAlias(process.RootTable, columnListType, field))); else goto default; break; case ColumnTypes.MultiReference: if (skipMultiRefs) continue; if (forceTag) selectList.Add(string.Format("[{0}].[{1}{2}]", tablePrefix, "NameValues", multiRefCount++)); else selectList.Add(string.Format( @" ( SELECT {0} FROM {3} INNER JOIN {1} j ON j.{3}Id = {3}.Id WHERE (j.{2} = [t0].[id]) FOR XML PATH('r') ) AS NameValues{4} ", GetMultiRefExpression(field.MultiRefDisplayFields), field.ReferenceJoinTableName, field.ReferencedColumnName, field.ReferenceTableName, multiRefCount++)); break; case ColumnTypes.Image: switch (columnListType) { case ColumnListType.Select: selectList.Add(string.Format("[{2}_{0}].[{3}] AS [{0}] {1}", field.ColumnName, Environment.NewLine, tablePrefix, Constants.BinaryContentColumnName)); selectList.Add(string.Format("[{2}].[{0}] AS [{0}Id] {1}", field.ColumnName, Environment.NewLine, tablePrefix)); break; case ColumnListType.GroupBy: selectList.Add(string.Format("[t0].[{0}] {1}", field.ColumnName, Environment.NewLine)); break; case ColumnListType.InnerSelect: selectList.Add(string.Format("[t0].[{0}] {0} {1}", field.ColumnName, Environment.NewLine)); break; } break; case ColumnTypes.Frequency: var fieldPresentation = string.Format( "(CASE WHEN [{0}].[{1}{2}] = '{3}' THEN [{4}].[{5}] ELSE [{0}].[{1}] END) AS [{1}]{6}" , tablePrefix , field.ColumnName , Constants.FrequencyEventTypeFieldSuffix , FrequencyEventEnum.Event , FindFrequencyEventPrefix(field.SystemName) , Constants.EventFrequencyName , Environment.NewLine); selectList.Add(fieldPresentation); break; case ColumnTypes.Video: case ColumnTypes.Checklist: case ColumnTypes.ReverseReference: case ColumnTypes.ReverseMultiReference: case ColumnTypes.TreeView: case ColumnTypes.AuditTrail: continue; default: selectList.Add(string.Format("[{2}].[{0}]{1}", field.ColumnName, Environment.NewLine, tablePrefix)); break; } if (process.HasBaseClass) selectList.Add(string.Format("[{2}].[{0}]{1}", Constants.ParentIdColumnName, Environment.NewLine, tablePrefix)); return string.Join(",", selectList); }