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