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 GetBody(Table table, ModelRoot model) { var sb = new StringBuilder(); if (!table.Immutable) { if (table.AllowCreateAudit) { sb.AppendLine("if (@" + model.Database.CreatedDateColumnName + " IS NULL)"); sb.AppendLine("SET @" + model.Database.CreatedDateColumnName + " = " + model.GetSQLDefaultDate()); } if (table.AllowModifiedAudit) { if (model.EFVersion == EFVersionConstants.EF4) { sb.AppendLine("DECLARE @" + model.Database.ModifiedDateColumnName + " [DateTime]"); sb.AppendLine("SET @" + model.Database.ModifiedDateColumnName + " = " + model.GetSQLDefaultDate()); } else if (model.EFVersion == EFVersionConstants.EF6) { //Modified Date - This is where we override the placeholder parameter for EF6 runtime. sb.Append("SET @" + model.Database.ModifiedDateColumnName + " = " + model.GetSQLDefaultDate()); sb.AppendLine("--Entity Framework 6 Required Modified Date be passed in, overwrite it here."); } else { throw new NotImplementedException(string.Format("model.EFVersion [{0}] not supported", model.EFVersion)); } } foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name)) { if (column.Identity == IdentityTypeConstants.Code) { sb.AppendLine("SET @" + column.ToDatabaseCodeIdentifier() + " = (select case when max([" + column.DatabaseName + "]) is null then 1 else max([" + column.DatabaseName + "]) + 1 end from [" + Globals.GetTableDatabaseName(model, table) + "])"); } else if (column.Identity == IdentityTypeConstants.Database) { //sb.AppendLine("DECLARE @" + column.ToDatabaseCodeIdentifier() + " " + dc.DataType); } } if (table.ParentTable == null) { AppendInsertionStatement(sb, table, model); } else { var tableList = table.GetTableHierarchy(); foreach (var t in tableList) { AppendInsertionStatement(sb, t, model); //On the base table save the primary keys as variables if (t.ParentTable == null) sb.Append(BuildInheritedPkBaseTableVariables(t, model)); } } } sb.AppendLine(); sb.AppendLine("SELECT "); sb.AppendLine(Globals.BuildSelectList(table, model, true)); sb.AppendLine("FROM"); sb.AppendLine(table.GetFullHierarchyTableJoin()); sb.AppendLine("WHERE"); sb.AppendLine(" " + BuildInsertSelectWhereStatement(table, model) + ";"); return sb.ToString(); }
private static void AppendModifiedAudit(ModelRoot model, Table table, StringBuilder sb) { if (table.AllowModifiedAudit) { var dateTimeString = (model.SQLServerType == nHydrate.Generator.Common.GeneratorFramework.SQLServerTypeConstants.SQL2005) ? "[DateTime]" : "[DateTime2]"; var defaultName = "DF__" + table.DatabaseName + "_" + model.Database.ModifiedDateColumnName; defaultName = defaultName.ToUpper(); sb.AppendLine(","); sb.AppendLine("\t[" + model.Database.ModifiedByColumnName + "] [NVarchar] (50) NULL,"); sb.Append("\t[" + model.Database.ModifiedDateColumnName + "] " + dateTimeString + " CONSTRAINT [" + defaultName + "] DEFAULT " + model.GetSQLDefaultDate() + " NULL"); } }
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 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; } }