private static string ScriptForAuditTableCreation(TableConfig parentTable, ConnectionData connData) { SqlSync.TableScript.ResourceHelper resHelper = new SqlSync.TableScript.ResourceHelper(); SqlSync.DbInformation.ColumnInfo[] columns = SqlSync.DbInformation.InfoHelper.GetColumnNamesWithTypes(parentTable.TableName, connData); string schema; string parentTableName; InfoHelper.ExtractNameAndSchema(parentTable.TableName, out parentTableName, out schema); string auditTableName = String.Format(auditTableNameFormat, parentTableName); //InfoHelper.ExtractNameAndSchema(auditTableName, out auditTableName, out schema); StringBuilder sb = new StringBuilder(); //If Table Exists sb.Append(Properties.Resources.AuditTableCreate); TableTemplateReplacements(ref sb, auditTableName, "", "", "", parentTableName, schema); bool addCharSize; for (int i = 0; i < columns.Length; i++) { if (columns[i].DataType.ToLower() == "text" || columns[i].DataType.ToLower() == "ntext" || columns[i].DataType.ToLower() == "image") { continue; } addCharSize = columns[i].CharMaximum > 0 && (columns[i].DataType.ToLower() == "varchar" || columns[i].DataType.ToLower() == "char" || columns[i].DataType.ToLower() == "nvarchar" || columns[i].DataType.ToLower() == "nchar"); if (columns[i].DataType.ToLower() == "timestamp") { addCharSize = true; columns[i].DataType = "binary"; columns[i].CharMaximum = 8; } sb.Append(Properties.Resources.AuditColumnCreate); TableTemplateReplacements(ref sb, auditTableName, columns[i].ColumnName, columns[i].DataType, (addCharSize) ? "(" + columns[i].CharMaximum + ")" : string.Empty, parentTable.TableName, schema); if (addCharSize) { sb.Append(Properties.Resources.AuditColumnCharSize); TableTemplateReplacements(ref sb, auditTableName, columns[i].ColumnName, columns[i].DataType, columns[i].CharMaximum.ToString(), parentTableName, schema); } } return(sb.ToString()); }
public static string GetAuditScript(TableConfig tableCfg, AuditScriptType type, ConnectionData connData) { switch (type) { case AuditScriptType.CreateAuditTable: return(AuditHelper.ScriptForAuditTableCreation(tableCfg, connData)); case AuditScriptType.CreateInsertTrigger: case AuditScriptType.CreateUpdateTrigger: case AuditScriptType.CreateDeleteTrigger: return(AuditHelper.ScriptForAuditTriggers(tableCfg, connData, type, false)); case AuditScriptType.TriggerDisable: return(AuditHelper.ScriptForAuditTriggerEnableDisable(tableCfg.TableName, connData, false)); case AuditScriptType.TriggerEnable: return(AuditHelper.ScriptForAuditTriggerEnableDisable(tableCfg.TableName, connData, true)); case AuditScriptType.MasterTable: return(AuditHelper.ScriptForAuditMasterTable(false)); } return(string.Empty); }
public static void TriggerTemplateReplacements(ref StringBuilder sb, string auditTable, string masterTableName, string columnList, string triggerName, TableConfig cfg) { string schema; InfoHelper.ExtractNameAndSchema(masterTableName, out masterTableName, out schema); TriggerTemplateReplacements(ref sb, auditTable, masterTableName, columnList, triggerName, schema); if (cfg.ConfigData != null) { if (cfg.ConfigData.IndividualIDColumn.Length == 0) { sb.Replace(ReplaceConstants.IndividualIDColumn, "NULL"); } else { sb.Replace(ReplaceConstants.IndividualIDColumn, cfg.ConfigData.IndividualIDColumn); } if (cfg.ConfigData.InsertByColumn.Length == 0) { sb.Replace(ReplaceConstants.InsertByColumn, "NULL"); } else { sb.Replace(ReplaceConstants.InsertByColumn, cfg.ConfigData.InsertByColumn); } if (cfg.ConfigData.ObjectTypeColumn.Length == 0) { sb.Replace(ReplaceConstants.ObjectTypeColumn, "NULL"); } else { sb.Replace(ReplaceConstants.ObjectTypeColumn, cfg.ConfigData.ObjectTypeColumn); } } }
private static string ScriptForAuditTriggers(TableConfig parentTable, ConnectionData connData, AuditScriptType type, bool useDS) { SqlSync.TableScript.ResourceHelper resHelper = new SqlSync.TableScript.ResourceHelper(); ColumnInfo[] columns = SqlSync.DbInformation.InfoHelper.GetColumnNamesWithTypes(parentTable.TableName, connData); StringBuilder cols = new StringBuilder(); for (int i = 0; i < columns.Length; i++) { if (columns[i].DataType.ToLower() == "text" || columns[i].DataType.ToLower() == "ntext" || columns[i].DataType.ToLower() == "image") { continue; } cols.AppendFormat("[{0}],", columns[i].ColumnName); } cols.Length = cols.Length - 1; string colList = cols.ToString(); string auditTableName = String.Format(auditTableNameFormat, parentTable.TableName); string schema, parentTableName; InfoHelper.ExtractNameAndSchema(auditTableName, out auditTableName, out schema); InfoHelper.ExtractNameAndSchema(parentTable.TableName, out parentTableName, out schema); StringBuilder sb = new StringBuilder(); if (type == AuditScriptType.CreateInsertTrigger) { //Insert Trigger if (!useDS) { sb.Append(Properties.Resources.AuditInsertTrigger); } else { sb.Append(Properties.Resources.AuditInsertTriggerDS); } TriggerTemplateReplacements(ref sb, auditTableName, parentTableName, colList, String.Format(triggerNameFormat, parentTableName, "INSERT"), schema); } //Update Trigger if (type == AuditScriptType.CreateUpdateTrigger) { if (!useDS) { sb.Append(Properties.Resources.AuditUpdateTrigger); } else { sb.Append(Properties.Resources.AuditUpdateTriggerDS); } TriggerTemplateReplacements(ref sb, auditTableName, parentTableName, colList, String.Format(triggerNameFormat, parentTableName, "UPDATE"), schema); } //Delete Trigger if (type == AuditScriptType.CreateDeleteTrigger) { if (!useDS) { sb.Append(Properties.Resources.AuditDeleteTrigger); } else { sb.Append(Properties.Resources.AuditDeleteTriggerDS); } TriggerTemplateReplacements(ref sb, auditTableName, parentTableName, colList, String.Format(triggerNameFormat, parentTableName, "DELETE"), schema); } return(sb.ToString()); }