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