public static string GetBody(Table table, ModelRoot model)
{
var sb = new StringBuilder();
if (table.AllowModifiedAudit)
{
sb.AppendLine("IF (@" + model.Database.ModifiedDateColumnName + " IS NULL)");
sb.AppendLine("SET @" + model.Database.ModifiedDateColumnName + " = " + model.GetSQLDefaultDate() + ";");
sb.AppendLine();
}
sb.AppendLine("SET NOCOUNT ON;");
if (!table.Immutable)
{
var tableList = table.GetTableHierarchy();
foreach (var t in tableList)
{
//If there is nothing to set then do not do anything
var setStatment = BuildSetStatement(t, model);
if (!string.IsNullOrEmpty(setStatment))
{
sb.AppendLine("UPDATE ");
sb.AppendLine("\t[" + t.GetSQLSchema() + "].[" + t.DatabaseName + "] ");
sb.AppendLine("SET");
sb.AppendLine(setStatment);
sb.AppendLine("WHERE");
sb.AppendLine("\t" + BuildUpdateWhereStatement(t, model, ((table.GetAbsoluteBaseTable() == t) && table.AllowTimestamp)));
sb.AppendLine();
sb.AppendLine("if (@@RowCount = 0) return;");
sb.AppendLine();
}
}
}
sb.AppendLine("SELECT");
sb.Append(Globals.BuildSelectList(table, model, true));
sb.AppendLine("FROM ");
sb.AppendLine(table.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
sb.AppendLine("\t" + BuildSelectWhereStatement(table, model));
return sb.ToString();
}
public static string BuildSelectList(Table table, ModelRoot model, bool useFullHierarchy)
{
var index = 0;
var output = new StringBuilder();
var columnList = new List<Column>();
if (useFullHierarchy)
{
foreach (var c in table.GetColumnsFullHierarchy().Where(x => x.Generated).OrderBy(x => x.Name))
columnList.Add(c);
}
else
{
columnList.AddRange(table.GeneratedColumns);
}
foreach (var column in columnList.OrderBy(x => x.Name))
{
var parentTable = column.ParentTable;
output.AppendFormat("\t[{2}].[{0}].[{1}]", GetTableDatabaseName(model, parentTable), column.DatabaseName, parentTable.GetSQLSchema());
if ((index < columnList.Count - 1) || (table.AllowCreateAudit) || (table.AllowModifiedAudit) || (table.AllowTimestamp))
output.Append(",");
output.AppendLine();
index++;
}
if (table.AllowCreateAudit)
{
output.AppendFormat(" [{2}].[{0}].[{1}],", GetTableDatabaseName(model, table), model.Database.CreatedByColumnName, table.GetSQLSchema());
output.AppendLine();
output.AppendFormat(" [{2}].[{0}].[{1}]", GetTableDatabaseName(model, table), model.Database.CreatedDateColumnName, table.GetSQLSchema());
if ((table.AllowModifiedAudit) || (table.AllowTimestamp))
output.Append(",");
output.AppendLine();
}
if (table.AllowModifiedAudit)
{
output.AppendFormat(" [{2}].[{0}].[{1}],", GetTableDatabaseName(model, table), model.Database.ModifiedByColumnName, table.GetSQLSchema());
output.AppendLine();
output.AppendFormat(" [{2}].[{0}].[{1}]", GetTableDatabaseName(model, table), model.Database.ModifiedDateColumnName, table.GetSQLSchema());
if (table.AllowTimestamp)
output.Append(",");
output.AppendLine();
}
if (table.AllowTimestamp)
{
output.AppendFormat(" [{2}].[{0}].[{1}]", GetTableDatabaseName(model, table.GetAbsoluteBaseTable()), model.Database.TimestampColumnName, table.GetAbsoluteBaseTable().GetSQLSchema());
output.AppendLine();
}
return output.ToString();
}