private void AppendFullTemplate(StringBuilder sb, Table table, ModelRoot model)
{
try
{
var moduleSuffix = string.Empty;
if (!string.IsNullOrEmpty(_model.ModuleName))
moduleSuffix = _model.ModuleName + "_";
sb.AppendLine("if exists(select * from sys.objects where name = '" + GetStoredProcedureName(table, model, moduleSuffix) + "' and type = 'P' and type_desc = 'SQL_STORED_PROCEDURE')");
sb.AppendLine(" drop procedure [" + table.GetSQLSchema() + "].[" + GetStoredProcedureName(table, model, moduleSuffix) + "]");
sb.AppendLine("GO");
sb.AppendLine();
//Just drop the procedure if no CRUD SP
if (!_model.Database.UseGeneratedCRUD)
return;
sb.AppendLine("CREATE PROCEDURE [" + table.GetSQLSchema() + "].[" + GetStoredProcedureName(table, model, moduleSuffix) + "]");
sb.AppendLine("(");
sb.AppendLine(BuildParameterList(table, model));
sb.AppendLine(")");
sb.AppendLine("AS");
sb.AppendLine("SET NOCOUNT OFF;");
sb.AppendLine();
sb.Append(SQLGeneratedBodyHelper.SQLInsertBusinessObjectBody(table, model));
sb.AppendLine("GO");
sb.AppendLine();
if (model.Database.GrantExecUser != string.Empty)
{
sb.AppendFormat("GRANT EXECUTE ON [" + table.GetSQLSchema() + "].[{0}] TO [{1}]", GetStoredProcedureName(table, model, moduleSuffix), model.Database.GrantExecUser).AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
}
}
catch (Exception ex)
{
throw;
}
}
public static string GetSQLCreateTable(ModelRoot model, Table table)
{
try
{
if (table.TypedTable == TypedTableConstants.EnumOnly)
return string.Empty;
var sb = new StringBuilder();
var tableName = Globals.GetTableDatabaseName(model, table);
sb.AppendLine("--CREATE TABLE [" + table.DatabaseName + "]");
sb.AppendLine("if not exists(select * from sysobjects where name = '" + tableName + "' and xtype = 'U')");
sb.AppendLine("CREATE TABLE [" + table.GetSQLSchema() + "].[" + tableName + "] (");
var firstLoop = true;
foreach (var column in table.GeneratedColumns.OrderBy(x => x.SortOrder))
{
if (!firstLoop) sb.AppendLine(",");
else firstLoop = false;
sb.Append("\t" + AppendColumnDefinition(column, allowDefault: true, allowIdentity: true));
}
AppendModifiedAudit(model, table, sb);
AppendCreateAudit(model, table, sb);
AppendTimestamp(model, table, sb);
AppendTenantField(model, table, sb);
//Emit PK
var tableIndex = table.TableIndexList.FirstOrDefault(x => x.PrimaryKey);
if (tableIndex != null)
{
var indexName = "PK_" + table.DatabaseName.ToUpper();
sb.AppendLine(",");
sb.AppendLine("\t" + "CONSTRAINT [" + indexName + "] PRIMARY KEY " + (tableIndex.Clustered ? "CLUSTERED" : "NONCLUSTERED"));
sb.AppendLine("\t" + "(");
sb.AppendLine("\t\t" + Globals.GetSQLIndexField(table, tableIndex));
sb.AppendLine("\t" + ")");
}
else
sb.AppendLine();
sb.AppendLine(")");
return sb.ToString();
}
catch (Exception ex)
{
throw;
}
}
public static string GetSqlDropAuditPK(Table table)
{
var tableName = "__AUDIT__" + table.DatabaseName.ToUpper();
var pkName = "PK_" + tableName.ToUpper();
var sb = new StringBuilder();
sb.AppendLine("--DROP PRIMARY KEY FOR TABLE [" + tableName + "]");
sb.AppendLine("if exists(select * from sys.objects where name = '" + pkName + "' and type = 'PK' and type_desc = 'PRIMARY_KEY_CONSTRAINT')");
sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + tableName + "] DROP CONSTRAINT [" + pkName + "]");
sb.AppendLine("GO");
sb.AppendLine();
return sb.ToString();
}
public static void AppendModifiedAudit(Table table, ModelRoot model, StringBuilder sb)
{
try
{
var dateTimeString = (model.SQLServerType == Common.GeneratorFramework.SQLServerTypeConstants.SQL2005) ? "[DateTime]" : "[DateTime2]";
sb.AppendLine("--APPEND AUDIT TRAIL MODIFY FOR TABLE [" + table.DatabaseName + "]");
sb.AppendLine("if exists(select * from sys.objects where name = '" + table.DatabaseName + "' and type = 'U') and not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + model.Database.ModifiedByColumnName + "' and o.name = '" + table.DatabaseName + "')");
sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] ADD [" + model.Database.ModifiedByColumnName + "] [NVarchar] (50) NULL");
var dfName = "DF__" + table.DatabaseName + "_" + model.Database.ModifiedDateColumnName;
dfName = dfName.ToUpper();
sb.AppendLine("if exists(select * from sys.objects where name = '" + table.DatabaseName + "' and type = 'U') and not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + model.Database.ModifiedDateColumnName + "' and o.name = '" + table.DatabaseName + "')");
sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] ADD [" + model.Database.ModifiedDateColumnName + "] " + dateTimeString + " CONSTRAINT [" + dfName + "] DEFAULT " + model.GetSQLDefaultDate() + " NULL");
sb.AppendLine("GO");
sb.AppendLine();
}
catch (Exception ex)
{
throw;
}
}
private static string BuildStoredProcedure(Table table, ModelRoot model, List<Column> allColumns)
{
var sb = new StringBuilder();
var index = 0;
sb.AppendLine("CREATE TABLE #tmpTable");
sb.AppendLine("(");
foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
sb.Append("\t[" + column.DatabaseName + "] " + column.GetSQLDefaultType());
if (index < table.PrimaryKeyColumns.Count - 1) sb.Append(",");
sb.AppendLine();
index++;
}
//sb.Remove(sb.Length - 3, 3);
sb.AppendLine(")");
sb.AppendLine();
sb.AppendLine("DECLARE @total__ivqatedr int");
sb.AppendLine("DECLARE @orderByColumnIndex int");
sb.AppendLine("-- remove top x values from the temp table based upon the specific page requested");
sb.AppendLine("SET @total__ivqatedr = (@pageSize * @page)");
sb.AppendLine("IF (@total__ivqatedr <> 0)");
sb.AppendLine("BEGIN");
sb.AppendLine(" SET ROWCOUNT @total__ivqatedr");
sb.AppendLine("END");
sb.AppendLine("INSERT INTO #tmpTable");
sb.AppendLine("(");
sb.Append(Globals.BuildPrimaryKeySelectList(model, table, false));
sb.AppendLine(")");
//SELECT CLAUSE
sb.AppendLine("SELECT");
sb.Append(Globals.BuildPrimaryKeySelectList(model, table, true));
sb.AppendLine("FROM");
sb.AppendLine(table.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
var likeList = allColumns.Where(x =>
x.DataType != System.Data.SqlDbType.Xml &&
x.DataType != System.Data.SqlDbType.Text &&
x.DataType != System.Data.SqlDbType.NText &&
x.DataType != System.Data.SqlDbType.Image)
.ToList();
for (var ii = 0; ii < likeList.Count; ii++)
{
var column = likeList[ii];
//If this is text then do a like, other wise equals
var comparer = "=";
if (ModelHelper.IsTextType(column.DataType))
comparer = "LIKE";
var t = column.ParentTableRef.Object as Table;
var tableName = "[" + t.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, t) + "]";
sb.Append(" (@orderByColumn = '" + column.DatabaseName + "' and (((@filter is null) or (" + tableName + ".[" + column.DatabaseName + "] is null)) or (@filter is not null and " + tableName + ".[" + column.DatabaseName + "] " + comparer + " @filter)))");
if (ii < likeList.Count - 1)
{
sb.AppendLine();
sb.Append("or");
}
sb.AppendLine();
}
//ORDER BY CLAUSE
sb.AppendLine("ORDER BY");
for (var ii = 0; ii < likeList.Count; ii++)
{
var column = likeList[ii];
var t = column.ParentTableRef.Object as Table;
var tableName = "[" + t.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, t) + "]";
sb.AppendLine(" CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN '" + column.DatabaseName + "' THEN " + tableName + ".[" + column.DatabaseName + "] END END DESC, ");
sb.Append(" CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN '" + column.DatabaseName + "' THEN " + tableName + ".[" + column.DatabaseName + "] END END");
if (ii < likeList.Count - 1)
{
sb.Append(", ");
}
sb.AppendLine();
}
sb.AppendLine();
sb.AppendLine("-- set @count based on the rows moved in the previous statement");
//sb.AppendLine("SET @count = ( SELECT count(*) FROM [#tmpTable] )");
//REPEAT SELECT CLAUSE FOR COUNT
sb.AppendLine("SET ROWCOUNT 0");
sb.AppendLine("SET @count = (");
sb.AppendLine("SELECT count(*)");
sb.AppendLine("FROM");
sb.AppendLine(table.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
for (var ii = 0; ii < likeList.Count; ii++)
{
var column = likeList[ii];
var t = column.ParentTableRef.Object as Table;
var tableName = "[" + t.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, t) + "]";
var comparer = "=";
if (ModelHelper.IsTextType(column.DataType))
comparer = "LIKE";
sb.Append(" (@orderByColumn = '" + column.DatabaseName + "' and (((@filter is null) or (" + tableName + ".[" + column.DatabaseName + "] is null)) or (@filter is not null and " + tableName + ".[" + column.DatabaseName + "] " + comparer + " @filter)))");
if (ii < likeList.Count - 1)
{
sb.AppendLine();
sb.Append("or");
}
sb.AppendLine();
}
sb.AppendLine(")");
sb.AppendLine();
sb.AppendLine("-- remove top x values from the temp table based upon the specific page requested");
sb.AppendLine("SET @total__ivqatedr = (@pageSize * @page) - @pageSize");
sb.AppendLine("IF (@total__ivqatedr <> 0)");
sb.AppendLine("BEGIN");
sb.AppendLine(" SET ROWCOUNT @total__ivqatedr");
sb.AppendLine(" DELETE FROM #tmpTable");
sb.AppendLine("END");
sb.AppendLine();
sb.AppendLine("-- return the number of rows requested as the page size");
sb.AppendLine("SET ROWCOUNT @pageSize");
sb.AppendLine("SELECT");
sb.Append(Globals.BuildSelectList(table, model, true));
sb.AppendLine("FROM");
sb.AppendLine(" [#tmpTable]");
sb.Append(" INNER JOIN " + table.GetFullHierarchyTableJoin() + " ON ");
var pkFirstTime = true;
foreach (var pkColumn in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
if (!pkFirstTime)
{
sb.AppendLine(" AND");
}
else
{
pkFirstTime = false;
}
sb.AppendFormat("#tmpTable.[{0}] = [{2}].[{1}].[{0}]", pkColumn.DatabaseName, Globals.GetTableDatabaseName(model, table).ToUpper(), table.GetSQLSchema());
}
sb.AppendLine();
sb.AppendLine("ORDER BY");
for (var ii = 0; ii < likeList.Count; ii++)
{
var column = likeList[ii];
var t = column.ParentTableRef.Object as Table;
var tableName = "[" + t.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, t) + "]";
sb.AppendLine(" CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN '" + column.DatabaseName + "' THEN " + tableName + ".[" + column.DatabaseName + "] END END DESC, ");
sb.Append(" CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN '" + column.DatabaseName + "' THEN " + tableName + ".[" + column.DatabaseName + "] END END");
if (ii < likeList.Count - 1)
{
sb.Append(", ");
}
sb.AppendLine();
}
sb.AppendLine();
sb.AppendLine("DROP TABLE #tmpTable");
sb.AppendLine();
sb.AppendLine("GO");
return sb.ToString();
}
private static string BuildSelectWhereStatement(Table table, ModelRoot model)
{
try
{
var output = new StringBuilder();
var index = 0;
foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
output.Append("[" + table.GetSQLSchema() + "].[" + table.DatabaseName + "].[" + column.DatabaseName + "] = @");
if (model.EFVersion == EFVersionConstants.EF4)
{
output.Append("Original_");
}
output.Append(column.ToDatabaseCodeIdentifier());
if (index < table.PrimaryKeyColumns.Count - 1)
output.Append(" AND" + Environment.NewLine + "\t");
index++;
}
return output.ToString();
}
catch (Exception ex)
{
throw;
}
}
private static string BuildInsertSelectWhereStatement(Table table, ModelRoot model)
{
var output = new StringBuilder();
var primaryKeyCols = new List<Column>(table.PrimaryKeyColumns.OrderBy(x => x.Name));
for (var ii = 0; ii < primaryKeyCols.Count; ii++)
{
var column = primaryKeyCols[ii];
output.Append("[" + table.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, table) + "].[" + column.DatabaseName + "]");
output.Append(" = ");
if (column.Identity == IdentityTypeConstants.Database)
{
output.Append("SCOPE_IDENTITY()");
}
else
{
output.AppendFormat("@{0}", column.ToDatabaseCodeIdentifier());
}
if (ii < primaryKeyCols.Count - 1)
output.Append(" AND" + Environment.NewLine + "\t");
}
return output.ToString();
}
public static string GetSqlUpdateStaticData(Table table)
{
try
{
var sb = new StringBuilder();
var model = (ModelRoot)table.Root;
//Generate static data
if (table.StaticData.Count > 0)
{
sb.AppendLine("--UPDATE STATIC DATA FOR TABLE [" + Globals.GetTableDatabaseName(model, table) + "]");
sb.AppendLine("--IF YOU WISH TO UPDATE THIS STATIC DATA UNCOMMENT THIS SQL");
foreach (var rowEntry in table.StaticData.AsEnumerable<RowEntry>())
{
var fieldValues = new Dictionary<string, string>();
foreach (var cellEntry in rowEntry.CellEntries.ToList())
{
var column = cellEntry.ColumnRef.Object as Column;
var sqlValue = cellEntry.GetSQLData();
if (sqlValue == null) //Null is actually returned if the value can be null
{
if (!string.IsNullOrEmpty(column.Default))
{
if (ModelHelper.IsTextType(column.DataType) || ModelHelper.IsDateType(column.DataType))
{
if (column.DataType == SqlDbType.NChar || column.DataType == SqlDbType.NText || column.DataType == SqlDbType.NVarChar)
fieldValues.Add(column.Name, "N'" + column.Default.Replace("'", "''") + "'");
else
fieldValues.Add(column.Name, "'" + column.Default.Replace("'", "''") + "'");
}
else
{
fieldValues.Add(column.Name, column.Default);
}
}
else
{
fieldValues.Add(column.Name, "NULL");
}
}
else
{
if (column.DataType == SqlDbType.Bit)
{
sqlValue = sqlValue.ToLower().Trim();
if (sqlValue == "true") sqlValue = "1";
else if (sqlValue == "false") sqlValue = "0";
else if (sqlValue != "1") sqlValue = "0"; //catch all, must be true/false
}
if (column.DataType == SqlDbType.NChar || column.DataType == SqlDbType.NText || column.DataType == SqlDbType.NVarChar)
fieldValues.Add(column.Name, "N" + sqlValue);
else
fieldValues.Add(column.Name, sqlValue);
}
}
// this could probably be done smarter
// but I am concerned about the order of the keys and values coming out right
var fieldList = new List<string>();
var valueList = new List<string>();
var updateSetList = new List<string>();
var primaryKeyColumnNames = table.PrimaryKeyColumns.Select(x => x.Name);
foreach (var kvp in fieldValues)
{
fieldList.Add("[" + kvp.Key + "]");
valueList.Add(kvp.Value);
if (!primaryKeyColumnNames.Contains(kvp.Key))
{
updateSetList.Add(kvp.Key + " = " + kvp.Value);
}
}
var fieldListString = string.Join(",", fieldList);
var valueListString = string.Join(",", valueList);
var updateSetString = string.Join(",", updateSetList);
var ii = 0;
var pkWhereSb = new StringBuilder();
foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
var pkData = rowEntry.CellEntries[column.Name].GetSQLData();
pkWhereSb.Append("([" + column.DatabaseName + "] = " + pkData + ")");
if (ii < table.PrimaryKeyColumns.Count - 1)
pkWhereSb.Append(" AND ");
ii++;
}
sb.AppendLine("--UPDATE [" + table.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, table) + "] SET " + updateSetString + " WHERE " + pkWhereSb.ToString() + ";");
}
sb.AppendLine();
}
return sb.ToString();
}
catch (Exception ex)
{
throw;
}
}
public static string GetSqlDropTable(Table t)
{
if (t.TypedTable == TypedTableConstants.EnumOnly)
return string.Empty;
var sb = new StringBuilder();
#region Delete Parent Relations
for (var ii = t.ParentRoleRelations.Count - 1; ii >= 0; ii--)
{
var parentR = (Relation) t.ParentRoleRelations[ii];
var parentT = (Table) parentR.ParentTableRef.Object;
var childT = (Table) parentR.ChildTableRef.Object;
for (var jj = parentT.ParentRoleRelations.Count - 1; jj >= 0; jj--)
{
//Relation chlidR = (Relation)parentT.ParentRoleRelations[jj];
if (parentR.ParentTableRef.Object == t)
{
var objectNameFK = "FK_" +
parentR.DatabaseRoleName + "_" + Globals.GetTableDatabaseName((ModelRoot) t.Root, childT) +
"_" + Globals.GetTableDatabaseName((ModelRoot) t.Root, parentT);
sb.AppendLine("--REMOVE FOREIGN KEY");
sb.AppendLine("if exists(select * from sys.objects where name = '" + objectNameFK + "' and type = 'F' and type_desc = 'FOREIGN_KEY_CONSTRAINT')");
sb.AppendLine("ALTER TABLE [" + childT.GetSQLSchema() + "].[" + childT.DatabaseName + "] DROP CONSTRAINT [" + objectNameFK + "]");
sb.AppendLine();
}
}
}
#endregion
#region Delete Child Relations
for (var ii = t.ChildRoleRelations.Count - 1; ii >= 0; ii--)
{
var childR = (Relation) t.ChildRoleRelations[ii];
var parentT = (Table) childR.ParentTableRef.Object;
var childT = (Table) childR.ChildTableRef.Object;
for (var jj = parentT.ParentRoleRelations.Count - 1; jj >= 0; jj--)
{
var parentR = (Relation) parentT.ParentRoleRelations[jj];
if (parentR.ChildTableRef.Object == t)
{
var objectNameFK = "FK_" +
parentR.DatabaseRoleName + "_" + Globals.GetTableDatabaseName((ModelRoot) t.Root, childT) +
"_" + Globals.GetTableDatabaseName((ModelRoot) t.Root, parentT);
objectNameFK = objectNameFK.ToUpper();
sb.AppendLine("--REMOVE FOREIGN KEY");
sb.AppendLine("if exists(select * from sys.objects where name = '" + objectNameFK + "' and type = 'F' and type_desc = 'FOREIGN_KEY_CONSTRAINT')");
sb.AppendLine("ALTER TABLE [" + childT.GetSQLSchema() + "].[" + childT.DatabaseName + "] DROP CONSTRAINT [" + objectNameFK + "]");
sb.AppendLine();
}
}
}
#endregion
#region Delete Primary Key
var objectNamePK = "PK_" + Globals.GetTableDatabaseName((ModelRoot)t.Root, t);
sb.AppendLine("--DELETE PRIMARY KEY FOR TABLE [" + t.DatabaseName + "]");
sb.AppendLine("if exists(select * from sys.objects where name = '" + objectNamePK + "' and type = 'PK' and type_desc = 'PRIMARY_KEY_CONSTRAINT')");
sb.AppendLine("ALTER TABLE [" + t.GetSQLSchema() + "].[" + t.DatabaseName + "] DROP CONSTRAINT [" + objectNamePK + "]");
sb.AppendLine();
#endregion
#region Delete Unique Constraints
foreach (var c in t.GetColumns().Where(x => x.IsUnique))
{
var indexName = "IX_" + t.Name.Replace("-", "") + "_" + c.Name.Replace("-", string.Empty);
indexName = indexName.ToUpper();
sb.AppendLine("--DELETE UNIQUE CONTRAINT");
sb.AppendLine("if exists(select * from sysobjects where name = '" + indexName + "' and xtype = 'UQ')");
sb.AppendLine("ALTER TABLE [" + t.DatabaseName + "] DROP CONSTRAINT [" + indexName + "]");
sb.AppendLine();
}
#endregion
#region Delete Indexes
foreach (var c in t.GetColumns().Where(x => !x.IsUnique))
{
var indexName = "IX_" + t.Name.Replace("-", "") + "_" + c.Name.Replace("-", string.Empty);
indexName = indexName.ToUpper();
sb.AppendLine("--DELETE UNIQUE CONTRAINT");
sb.AppendLine("if exists(select * from sysobjects where name = '" + indexName + "' and xtype = 'UQ')");
sb.AppendLine("ALTER TABLE [" + t.DatabaseName + "] DROP CONSTRAINT [" + indexName + "]");
sb.AppendLine();
indexName = CreateIndexName(t, c);
indexName = indexName.ToUpper();
sb.AppendLine("--DELETE INDEX");
sb.AppendLine("if exists (select * from sys.indexes where name = '" + indexName + "')");
sb.AppendLine("DROP INDEX [" + indexName + "] ON [" + t.DatabaseName + "]");
sb.AppendLine();
}
#endregion
//Drop the actual table
sb.AppendLine("--DELETE TABLE [" + t.DatabaseName + "]");
sb.AppendLine("if exists (select * from sysobjects where name = '" + t.DatabaseName + "' and xtype = 'U')");
sb.AppendLine("DROP TABLE [" + t.DatabaseName + "]");
return sb.ToString();
}
public static string GetSQLCreateIndex(Table table, TableIndex index, bool includeDrop)
{
var sb = new StringBuilder();
var model = table.Root as ModelRoot;
var tableName = Globals.GetTableDatabaseName(model, table);
var columnList = GetIndexColumns(table, index);
var indexName = GetIndexName(table, index);
if (columnList.Count > 0)
{
if (includeDrop)
{
//If this is to be a clustered index then check if it exists and is non-clustered and remove it
//If this is to be a non-clustered index then check if it exists and is clustered and remove it
sb.AppendLine("--DELETE INDEX");
if (index.Clustered)
{
sb.AppendLine("if exists(select * from sys.indexes where name = '" + indexName + "' and type_desc = 'NONCLUSTERED')");
sb.AppendLine("DROP INDEX [" + indexName + "] ON [" + table.GetSQLSchema() + "].[" + tableName + "]");
sb.AppendLine("GO");
}
else
{
sb.AppendLine("if exists(select * from sys.indexes where name = '" + indexName + "' and type_desc = 'CLUSTERED')");
sb.AppendLine("DROP INDEX [" + indexName + "] ON [" + table.GetSQLSchema() + "].[" + tableName + "]");
sb.AppendLine("GO");
}
sb.AppendLine();
}
//Do not create unique index for PK (it is already unique)
if (!index.PrimaryKey)
{
var checkSqlList = new List<string>();
foreach(var c in columnList)
{
checkSqlList.Add("exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + c.Value.DatabaseName + "' and o.name = '" + table.DatabaseName + "')");
}
sb.AppendLine("--INDEX FOR TABLE [" + table.DatabaseName + "] COLUMNS:" + string.Join(", ", columnList.Select(x => "[" + x.Value.DatabaseName + "]")));
sb.AppendLine("if not exists(select * from sys.indexes where name = '" + indexName + "') and " + string.Join(" and ", checkSqlList));
sb.Append("CREATE " + (index.IsUnique ? "UNIQUE " : string.Empty) + (index.Clustered ? "CLUSTERED " : "NONCLUSTERED ") + "INDEX [" + indexName + "] ON [" + table.GetSQLSchema() + "].[" + tableName + "] (");
sb.Append(string.Join(",", columnList.Select(x => "[" + x.Value.DatabaseName + "] " + (x.Key.Ascending ? "ASC" : "DESC"))));
sb.AppendLine(")");
}
}
return sb.ToString();
}
public static string GetSqlRenameTable(Table oldTable, Table newTable)
{
//RENAME TABLE
var sb = new StringBuilder();
sb.AppendLine("--RENAME TABLE '" + oldTable.DatabaseName + "' TO '" + newTable.DatabaseName + "'");
sb.AppendLine("if exists(select * from sysobjects where name = '" + oldTable.DatabaseName + "' and xtype = 'U')");
sb.AppendLine("exec sp_rename '" + oldTable.DatabaseName + "', '" + newTable.DatabaseName + "';");
sb.AppendLine("GO");
sb.AppendLine();
if (newTable.EnforcePrimaryKey)
{
//RENAME PRIMARY KEY (it will be readded in create script)
var oldIndexName = "PK_" + oldTable.DatabaseName.ToUpper();
var newIndexName = "PK_" + newTable.DatabaseName.ToUpper();
sb.AppendLine("--RENAME PRIMARY KEY FOR TABLE '" + oldTable.DatabaseName + "'");
sb.AppendLine("if exists (select * from sys.indexes where name = '" + oldIndexName + "')");
sb.AppendLine("exec sp_rename '" + oldIndexName + "', '" + newIndexName + "';");
sb.AppendLine();
}
//Rename all FK
foreach (var relation in newTable.GetRelationsWhereChild())
{
var oldIndexName = "FK_" + relation.RoleName + "_" + oldTable.DatabaseName + "_" + relation.ParentTable.DatabaseName;
oldIndexName = oldIndexName.ToUpper();
var newIndexName = "FK_" + relation.RoleName + "_" + newTable.DatabaseName + "_" + relation.ParentTable.DatabaseName;
newIndexName = newIndexName.ToUpper();
sb.AppendLine("--RENAME FK [" + newTable.DatabaseName + "].[" + oldIndexName + "]");
sb.AppendLine("if exists (select * from sys.foreign_keys where name = '" + oldIndexName + "')");
sb.AppendLine("exec sp_rename @objname='" + newTable.GetSQLSchema() + "." + oldIndexName + "', @newname='" + newIndexName + "', @objtype='OBJECT';");
sb.AppendLine();
}
//Rename all indexes for this table's fields
foreach (var column in newTable.GetColumns())
{
var oldColumn = oldTable.GetColumns().FirstOrDefault(x => x.Key == column.Key);
if (oldColumn != null)
{
var oldIndexName = CreateIndexName(oldTable, oldColumn);
var newIndexName = CreateIndexName(newTable, column);
sb.AppendLine("--RENAME INDEX [" + newTable.DatabaseName + "].[" + oldIndexName + "]");
sb.AppendLine("if exists (select * from sys.indexes where name = '" + oldIndexName + "')");
sb.AppendLine("exec sp_rename @objname='" + newTable.GetSQLSchema() + "." + newTable.DatabaseName + "." + oldIndexName + "', @newname='" + newIndexName + "', @objtype='INDEX';");
sb.AppendLine();
}
}
//rename all indexes for this table
foreach (var index in newTable.TableIndexList)
{
var oldIndex = oldTable.TableIndexList.FirstOrDefault(x => x.Key == index.Key);
if (oldIndex != null)
{
var oldIndexName = GetIndexName(oldTable, oldIndex);
var newIndexName = GetIndexName(newTable, index);
sb.AppendLine("--RENAME INDEX [" + newTable.DatabaseName + "].[" + oldIndexName + "]");
sb.AppendLine("if exists (select * from sys.indexes where name = '" + oldIndexName + "')");
sb.AppendLine("exec sp_rename @objname='" + newTable.GetSQLSchema() + "." + newTable.DatabaseName + "." + oldIndexName + "', @newname='" + newIndexName + "', @objtype='INDEX';");
sb.AppendLine();
}
}
var model = newTable.Root as ModelRoot;
//Change the default name for all audit fields
if (oldTable.AllowCreateAudit)
{
var defaultName = ("DF__" + oldTable.DatabaseName + "_" + model.Database.CreatedDateColumnName).ToUpper();
var defaultName2 = ("DF__" + newTable.DatabaseName + "_" + model.Database.CreatedDateColumnName).ToUpper();
sb.AppendLine("--CHANGE THE DEFAULT NAME FOR CREATED AUDIT");
sb.AppendLine("if exists (select * from sys.default_constraints where name = '" + defaultName + "')");
sb.AppendLine("exec sp_rename @objname='" + defaultName + "', @newname='" + defaultName2 + "';");
sb.AppendLine();
}
if (oldTable.AllowModifiedAudit)
{
var defaultName = ("DF__" + oldTable.DatabaseName + "_" + model.Database.ModifiedDateColumnName).ToUpper();
var defaultName2 = ("DF__" + newTable.DatabaseName + "_" + model.Database.ModifiedDateColumnName).ToUpper();
sb.AppendLine("--CHANGE THE DEFAULT NAME FOR MODIFIED AUDIT");
sb.AppendLine("if exists (select * from sys.default_constraints where name = '" + defaultName + "')");
sb.AppendLine("exec sp_rename @objname='" + defaultName + "', @newname='" + defaultName2 + "';");
sb.AppendLine();
}
return sb.ToString();
}
public static void DropTimestampAudit(Table table, ModelRoot model, StringBuilder sb)
{
sb.AppendLine("--REMOVE AUDIT TRAIL TIMESTAMP FOR TABLE [" + table.DatabaseName + "]");
sb.AppendLine("if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + model.Database.TimestampColumnName + "' and o.name = '" + table.DatabaseName + "')");
sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] DROP COLUMN [" + model.Database.TimestampColumnName + "]");
sb.AppendLine("GO");
sb.AppendLine();
}
public static void DropModifiedAudit(Table table, ModelRoot model, StringBuilder sb)
{
sb.AppendLine("--REMOVE AUDIT TRAIL MODIFY FOR TABLE [" + table.DatabaseName + "]");
sb.AppendLine("if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + model.Database.ModifiedByColumnName + "' and o.name = '" + table.DatabaseName + "')");
sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] DROP COLUMN [" + model.Database.ModifiedByColumnName + "]");
var dfName = "DF__" + table.DatabaseName + "_" + model.Database.ModifiedDateColumnName;
dfName = dfName.ToUpper();
sb.AppendLine("if exists (select * from sys.objects where name = '" + dfName + "' and [type] = 'D')");
sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] DROP CONSTRAINT [" + dfName + "]");
sb.AppendLine("if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + model.Database.ModifiedDateColumnName + "' and o.name = '" + table.DatabaseName + "')");
sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] DROP COLUMN [" + model.Database.ModifiedDateColumnName + "]");
sb.AppendLine("GO");
sb.AppendLine();
}
public static void AppendTimestampAudit(Table table, ModelRoot model, StringBuilder sb)
{
try
{
sb.AppendLine("--APPEND AUDIT TRAIL TIMESTAMP FOR TABLE [" + table.DatabaseName + "]");
sb.AppendLine("if exists(select * from sys.objects where name = '" + table.DatabaseName + "' and type = 'U') and not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + model.Database.TimestampColumnName + "' and o.name = '" + table.DatabaseName + "')");
sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] ADD [" + model.Database.TimestampColumnName + "] [ROWVERSION] NOT NULL");
sb.AppendLine("GO");
sb.AppendLine();
}
catch (Exception ex)
{
throw;
}
}
public static string GetSQLCreateTableSecurityFunction(Table table, ModelRoot model, bool isInternal)
{
var function = table.Security;
var sb = new StringBuilder();
var objectName = ValidationHelper.MakeDatabaseIdentifier("__security__" + table.Name);
sb.AppendLine("if exists(select * from sys.objects where name = '" + objectName + "' and type in('FN','IF','TF'))");
sb.AppendLine("drop function [" + table.GetSQLSchema() + "].[" + objectName + "]");
if (isInternal)
{
sb.AppendLine("--MODELID: " + function.Key);
}
sb.AppendLine("GO");
sb.AppendLine();
sb.AppendLine("CREATE FUNCTION [" + table.GetSQLSchema() + "].[" + objectName + "]");
sb.AppendLine("(");
if (function.Parameters.Count > 0)
sb.Append(BuildFunctionParameterList(function.GetParameters()));
sb.AppendLine(")");
sb.AppendLine("RETURNS TABLE AS RETURN (");
var realColumns = table.GetColumns().Select(x => x.DatabaseName).ToList();
var facadeColumns = table.GetColumns().Select(x => x.GetCodeFacade()).ToList();
if (table.AllowCreateAudit)
{
realColumns.Add(model.Database.CreatedByColumnName);
realColumns.Add(model.Database.CreatedDateColumnName);
facadeColumns.Add(model.Database.CreatedByColumnName);
facadeColumns.Add(model.Database.CreatedDateColumnName);
}
if (table.AllowModifiedAudit)
{
realColumns.Add(model.Database.ModifiedByColumnName);
realColumns.Add(model.Database.ModifiedDateColumnName);
facadeColumns.Add(model.Database.ModifiedByColumnName);
facadeColumns.Add(model.Database.ModifiedDateColumnName);
}
if (table.AllowTimestamp)
{
realColumns.Add(model.Database.TimestampColumnName);
facadeColumns.Add(model.Database.TimestampColumnName);
}
var ql = new List<string>();
for (var ii = 0; ii < realColumns.Count; ii++)
{
ql.Add("[" + realColumns[ii] + "] AS [" + facadeColumns[ii] + "]");
}
sb.AppendLine("WITH Z AS (");
sb.AppendLine(function.SQL);
sb.AppendLine(")");
sb.AppendLine("select " + string.Join(",", ql) + " from Z");
sb.AppendLine(")");
if (isInternal)
{
sb.AppendLine("--MODELID,BODY: " + function.Key);
}
sb.AppendLine("GO");
sb.AppendLine();
return sb.ToString();
}
public static string GetSQLDropIndex(Table table, TableIndex index)
{
var sb = new StringBuilder();
var model = table.Root as ModelRoot;
var tableName = Globals.GetTableDatabaseName(model, table);
var columnList = GetIndexColumns(table, index);
var indexName = GetIndexName(table, index);
if (columnList.Count > 0)
{
sb.AppendLine("--DELETE INDEX");
sb.AppendLine("if exists(select * from sys.indexes where name = '" + indexName + "')");
sb.AppendLine("DROP INDEX [" + indexName + "] ON [" + table.GetSQLSchema() + "].[" + tableName + "]");
}
return sb.ToString();
}
public static string GetSqlTenantIndex(ModelRoot model, Table table)
{
var indexName = "IDX_" + table.DatabaseName.Replace("-", string.Empty) + "_" + model.TenantColumnName;
indexName = indexName.ToUpper();
var sb = new StringBuilder();
sb.AppendLine("--INDEX FOR TABLE [" + table.DatabaseName + "] TENANT COLUMN: [" + model.TenantColumnName + "]");
sb.AppendLine("if not exists(select * from sys.indexes where name = '" + indexName + "')");
sb.Append("CREATE NONCLUSTERED INDEX [" + indexName + "] ON [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] (");
sb.Append("[" + model.TenantColumnName + "])");
sb.AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
return sb.ToString();
}
public static string GetSQLCreateAuditTable(ModelRoot model, Table table)
{
if (table.TypedTable == TypedTableConstants.EnumOnly)
return string.Empty;
var dateTimeString = (model.SQLServerType == nHydrate.Generator.Common.GeneratorFramework.SQLServerTypeConstants.SQL2005) ? "[DateTime]" : "[DateTime2]";
var sb = new StringBuilder();
var tableName = "__AUDIT__" + Globals.GetTableDatabaseName(model, table);
sb.AppendLine("if not exists(select * from sysobjects where name = '" + tableName + "' and xtype = 'U')");
sb.AppendLine("CREATE TABLE [" + table.GetSQLSchema() + "].[" + tableName + "] (");
sb.AppendLine("\t[__rowid] [INT] NOT NULL IDENTITY,");
sb.AppendLine("\t[__action] [INT] NOT NULL,");
sb.AppendLine("\t[__insertdate] " + dateTimeString + " CONSTRAINT [DF__" + table.DatabaseName + "__AUDIT] DEFAULT " + model.GetSQLDefaultDate() + " NOT NULL,");
if (table.AllowCreateAudit || table.AllowModifiedAudit)
sb.AppendLine("\t[" + model.Database.ModifiedByDatabaseName + "] [NVarchar] (50) NULL,");
var columnList = table.GetColumns().Where(x => x.Generated).ToList();
foreach (var column in columnList)
{
if (!(column.DataType == System.Data.SqlDbType.Text || column.DataType == System.Data.SqlDbType.NText || column.DataType == System.Data.SqlDbType.Image))
{
sb.Append("\t" + AppendColumnDefinition(column, allowDefault: false, allowIdentity: false, forceNull: true, allowFormula: false, allowComputed: false));
if (columnList.IndexOf(column) < columnList.Count - 1) sb.Append(",");
sb.AppendLine();
}
}
sb.Append(")");
sb.AppendLine();
return sb.ToString();
}
public static string GetSqlTenantView(ModelRoot model, Table table, StringBuilder grantSB)
{
try
{
var itemName = model.TenantPrefix + "_" + table.DatabaseName;
var sb = new StringBuilder();
sb.AppendLine("if exists (select * from sys.objects where name = '" + itemName + "' and [type] in ('V'))");
sb.AppendLine("drop view [" + itemName + "]");
sb.AppendLine("GO");
sb.AppendLine();
sb.AppendLine("CREATE VIEW [" + table.GetSQLSchema() + "].[" + itemName + "] ");
sb.AppendLine("AS");
sb.AppendLine("select * from [" + table.DatabaseName + "]");
sb.AppendLine("WHERE ([" + model.TenantColumnName + "] = SYSTEM_USER)");
sb.AppendLine("GO");
if (!string.IsNullOrEmpty(model.Database.GrantExecUser))
{
grantSB.AppendFormat("GRANT ALL ON [" + table.GetSQLSchema() + "].[{0}] TO [{1}]", itemName, model.Database.GrantExecUser).AppendLine();
grantSB.AppendLine("--MODELID: " + table.Key);
grantSB.AppendLine("GO");
grantSB.AppendLine();
}
return sb.ToString();
}
catch (Exception ex)
{
throw;
}
}
private static void AppendInsertionStatement(StringBuilder sb, Table table, ModelRoot model)
{
var pkIdentites = new List<Column>();
foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
if (column.Identity == IdentityTypeConstants.Database)
{
pkIdentites.Add(column);
}
//else
//{
// var col2 = table.GetBasePKColumn(column);
// if (col2 != null)
// if (col2.Identity == IdentityTypeConstants.Database) pkIdentites.Add(column);
//}
}
//Null out identites if < 0, so the row will get an autonumber
foreach (var column in pkIdentites)
{
sb.AppendLine("IF (@" + column.ToDatabaseCodeIdentifier() + " < 0) SET @" + column.ToDatabaseCodeIdentifier() + " = NULL;");
}
if (pkIdentites.Count > 0)
{
sb.Append("if (");
foreach (var column in pkIdentites)
{
sb.Append("(@" + column.ToDatabaseCodeIdentifier() + " IS NULL)");
if (pkIdentites.IndexOf(column) < pkIdentites.Count - 1)
sb.Append(" AND ");
}
sb.AppendLine(")");
sb.AppendLine("BEGIN");
}
//bool hasPrimaryInsert = (table.GetColumns().Count(x => !x.PrimaryKey) > 0);
//if (hasPrimaryInsert)
//{
sb.AppendLine("INSERT INTO [" + table.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, table) + "]");
sb.AppendLine("(");
sb.Append(BuildInsertColumns(table, model));
sb.AppendLine(")");
sb.AppendLine("VALUES");
sb.AppendLine("(");
sb.Append(BuildInsertValues(table, model));
sb.AppendLine(");");
sb.AppendLine();
sb.AppendLine("if (@@RowCount = 0) return;");
sb.AppendLine();
if (pkIdentites.Count > 0)
{
sb.AppendLine("END");
sb.AppendLine("ELSE");
sb.AppendLine("BEGIN");
sb.AppendLine("SET identity_insert [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] on");
sb.AppendLine("INSERT INTO [" + table.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, table) + "]");
sb.AppendLine("(");
sb.Append(BuildInsertColumns(table, model, pkIdentites));
sb.AppendLine(")");
sb.AppendLine("VALUES");
sb.AppendLine("(");
sb.Append(BuildInsertValues(table, model, pkIdentites));
sb.AppendLine(");");
sb.AppendLine();
sb.AppendLine("if (@@RowCount = 0) return;");
sb.AppendLine();
sb.AppendLine("SET identity_insert [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] off");
sb.AppendLine("END");
}
sb.AppendLine();
}
public static string GetSqlCreateTenantColumn(ModelRoot model, Table table)
{
var sb = new StringBuilder();
sb.AppendLine("--ADD COLUMN [" + table.DatabaseName + "].[" + model.TenantColumnName + "]");
sb.AppendLine("if exists(select * from sys.objects where name = '" + table.DatabaseName + "' and type = 'U') AND not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + model.TenantColumnName + "' and o.name = '" + table.DatabaseName + "')");
sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] ADD [" + model.TenantColumnName + "] [nvarchar] (128) NOT NULL CONSTRAINT [DF__" + table.DatabaseName.ToUpper() + "_" + model.TenantColumnName.ToUpper() + "] DEFAULT (suser_sname())");
return sb.ToString();
}
private static string BuildUpdateWhereStatement(Table table, ModelRoot model, bool isTimeStamp)
{
try
{
var output = new StringBuilder();
var index = 0;
foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
output.Append("[" + table.GetSQLSchema() + "].[" + table.DatabaseName + "].[" + column.DatabaseName);
output.Append("] = @");
if (model.EFVersion == EFVersionConstants.EF4)
{
output.Append("Original_");
}
output.Append(column.ToDatabaseCodeIdentifier());
if (index < table.PrimaryKeyColumns.Count - 1 || isTimeStamp)
output.Append(" AND" + Environment.NewLine + "\t");
index++;
}
if (isTimeStamp)
{
output.AppendFormat("[" + table.GetSQLSchema() + "].[" + table.DatabaseName + "].[{0}] = ", model.Database.TimestampColumnName);
if (model.EFVersion == EFVersionConstants.EF4)
{
output.AppendFormat("@Original_{0}", model.Database.TimestampColumnName);
}
else if (model.EFVersion == EFVersionConstants.EF6)
{
output.AppendFormat("@{0}_Original", model.Database.TimestampColumnName);
}
else
{
throw new NotImplementedException(string.Format("model.EFVersion [{0}] not supported", model.EFVersion));
}
}
output.AppendLine();
return output.ToString();
}
catch (Exception ex)
{
throw new Exception("BuildSetStatement failed: " + table.DatabaseName, ex);
}
}
public static string GetSqlCreatePK(Table table)
{
try
{
var sb = new StringBuilder();
var tableIndex = table.TableIndexList.FirstOrDefault(x => x.PrimaryKey);
if (tableIndex != null)
{
var indexName = "PK_" + table.DatabaseName.ToUpper();
sb.AppendLine("--PRIMARY KEY FOR TABLE [" + table.DatabaseName + "]");
sb.AppendLine("if not exists(select * from sysobjects where name = '" + indexName + "' and xtype = 'PK')");
sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] WITH NOCHECK ADD ");
sb.AppendLine("CONSTRAINT [" + indexName + "] PRIMARY KEY " + (tableIndex.Clustered ? "CLUSTERED" : "NONCLUSTERED"));
sb.AppendLine("(");
sb.AppendLine("\t" + Globals.GetSQLIndexField(table, tableIndex));
sb.Append(")");
sb.AppendLine();
}
return sb.ToString();
}
catch (Exception ex)
{
throw;
}
}
public static string GetBody(Table table, ModelRoot model)
{
if (table.Immutable) return string.Empty;
var sb = new StringBuilder();
sb.AppendLine("DELETE FROM");
sb.AppendLine(" [" + table.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, table) + "] ");
sb.AppendLine("WHERE ");
sb.AppendLine(" " + BuildDeleteWhereStatement(table, model) + ";");
sb.AppendLine();
sb.AppendLine("if (@@RowCount = 0) return;");
sb.AppendLine();
if (table.ParentTable != null)
{
var moduleName = (string.IsNullOrEmpty(model.ModuleName) ? string.Empty : "_" + model.ModuleName);
sb.Append("exec [" + table.ParentTable.GetSQLSchema() + "].[" + model.GetStoredProcedurePrefix() + "_" + table.ParentTable.PascalName + moduleName + "_Delete]");
var pkIndex = 0;
foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
sb.Append(" @Original_" + column.ToDatabaseCodeIdentifier() + " = @Original_" + column.ToDatabaseCodeIdentifier());
pkIndex++;
if (pkIndex < table.PrimaryKeyColumns.Count)
sb.Append(",");
}
}
sb.AppendLine();
return sb.ToString();
}
public static string GetSqlCreateAuditPK(Table table)
{
var tableName = "__AUDIT__" + table.DatabaseName.ToUpper();
var indexName = "PK_" + tableName.ToUpper();
var sb = new StringBuilder();
sb.AppendLine("--PRIMARY KEY FOR TABLE [" + tableName + "]");
sb.AppendLine("if not exists(select * from sysobjects where name = '" + indexName + "' and xtype = 'PK')");
sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + tableName + "] WITH NOCHECK ADD");
sb.Append("CONSTRAINT [" + indexName + "] PRIMARY KEY CLUSTERED ([__rowid])");
sb.AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
return sb.ToString();
}
protected string BuildUpdateWhereStatement(Table table)
{
try
{
var IsTimeStamp = (table == _currentComponent.Parent.GetAbsoluteBaseTable()) && _currentComponent.Parent.AllowTimestamp;
var output = new StringBuilder();
var index = 0;
foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
output.Append("[" + table.GetSQLSchema() + "].");
output.Append("[" + table.DatabaseName + "].");
output.Append("[" + column.DatabaseName + "] = ");
output.Append("@Original_");
output.Append(column.ToDatabaseCodeIdentifier());
if (index < table.PrimaryKeyColumns.Count - 1 || IsTimeStamp)
output.Append(" AND" + Environment.NewLine + "\t");
index++;
}
if (IsTimeStamp)
{
output.AppendFormat("[" + _currentComponent.Parent.GetAbsoluteBaseTable().GetSQLSchema() + "].[" + _currentComponent.Parent.GetAbsoluteBaseTable().DatabaseName + "].[{0}] = @Original_{0}", _model.Database.TimestampColumnName);
}
output.AppendLine();
return output.ToString();
}
catch (Exception ex)
{
throw new Exception("BuildSetStatement failed: " + table.DatabaseName, ex);
}
}
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();
}