//public static void FindUpdateColumnNames(string tableName, ConnectionData connData, out string updateByColumn, out bool updateByExists, out string updateDateColumn, out bool updateDateExists)
//{
// string[] columns = SqlSync.DbInformation.InfoHelper.GetColumnNames(tableName,connData);
// updateByColumn = SqlSync.DbInformation.InfoHelper.UpdateIdFields[0];
// updateByExists = false;
// for(int i=0;i<columns.Length;i++)
// {
// for(int j=0;j<SqlSync.DbInformation.InfoHelper.UpdateIdFields.Length;j++)
// {
// if(columns[i].ToLower() == SqlSync.DbInformation.InfoHelper.UpdateIdFields[j].ToLower())
// {
// updateByColumn = SqlSync.DbInformation.InfoHelper.UpdateIdFields[j];
// updateByExists = true;
// break;
// }
// }
// if(updateByExists)
// break;
// }
// updateDateColumn = SqlSync.DbInformation.InfoHelper.UpdateDateFields[0];
// updateDateExists = false;
// for(int i=0;i<columns.Length;i++)
// {
// for(int j=0;j<SqlSync.DbInformation.InfoHelper.UpdateDateFields.Length;j++)
// {
// if(columns[i].ToLower() == SqlSync.DbInformation.InfoHelper.UpdateDateFields[j].ToLower())
// {
// updateDateColumn = SqlSync.DbInformation.InfoHelper.UpdateDateFields[j];
// updateDateExists = true;
// break;
// }
// }
// if(updateDateExists)
// break;
// }
//}
//public static void FindUpdateColumnNames(string tableName, ConnectionData connData, out string updateByColumn, out string updateDateColumn)
//{
// bool notUsed1;
// bool notUsed2;
// FindUpdateColumnNames(tableName,connData,out updateByColumn,out notUsed1,out updateDateColumn,out notUsed2);
//}
public static UpdateAutoDetectData[] AutoDetectUpdateTriggers(ConnectionData connData, TableSize[] allTable)
{
string[] allTriggers = SqlSync.DbInformation.InfoHelper.GetTriggers(connData);
ArrayList lst = new ArrayList();
for (int i = 0; i < allTriggers.Length; i++)
{
for (int j = 0; j < allTable.Length; j++)
{
//TODO: account for the . in the table name
string table, schema;
InfoHelper.ExtractNameAndSchema(allTable[j].TableName, out table, out schema);
if (allTriggers[i] == String.Format(shortTrigFormat, table))
{
UpdateAutoDetectData dat = new UpdateAutoDetectData();
dat.TableName = allTable[j].TableName;
dat.RowCount = allTable[j].RowCount;
dat.HasUpdateTrigger = true;
lst.Add(dat);
break;
}
}
}
UpdateAutoDetectData[] data = new UpdateAutoDetectData[lst.Count];
lst.CopyTo(data);
return(data);
}
private static string ScriptForAuditTriggerEnableDisable(string parentTable, ConnectionData connData, bool isEnable)
{
string schema;
InfoHelper.ExtractNameAndSchema(parentTable, out parentTable, out schema);
SqlSync.TableScript.ResourceHelper resHelper = new SqlSync.TableScript.ResourceHelper();
StringBuilder sb = new StringBuilder();
string template;
if (isEnable)
{
template = Properties.Resources.AuditEnableTrigger;
}
else
{
template = Properties.Resources.AuditDisableTrigger;
}
//Insert
sb.Append(template);
TriggerTemplateReplacements(ref sb, String.Format(auditTableNameFormat, parentTable), parentTable, "", String.Format(triggerNameFormat, parentTable, "INSERT"), schema);
//Update
sb.Append(template);
TriggerTemplateReplacements(ref sb, String.Format(auditTableNameFormat, parentTable), parentTable, "", String.Format(triggerNameFormat, parentTable, "UPDATE"), schema);
//Delete
sb.Append(template);
TriggerTemplateReplacements(ref sb, String.Format(auditTableNameFormat, parentTable), parentTable, "", String.Format(triggerNameFormat, parentTable, "DELETE"), schema);
return(sb.ToString());
}
internal static ArrayList GetPrimaryKeyColumnsWithType(string tableName, ConnectionData connData)
{
string schema;
InfoHelper.ExtractNameAndSchema(tableName, out tableName, out schema);
SqlConnection conn = SqlSync.Connection.ConnectionHelper.GetConnection(connData.DatabaseName, connData.SQLServerName, connData.UserId, connData.Password, connData.AuthenticationType, connData.ScriptTimeout);
string command = @"
select cc.column_Name,c.data_type +
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ''
ELSE '('+CONVERT(varchar(50),CHARACTER_MAXIMUM_LENGTH)+')' END from
information_schema.TABLE_CONSTRAINTS TC
INNER JOIN information_schema.CONSTRAINT_COLUMN_USAGE cc ON cc.constraint_name = tc.constraint_Name
INNER JOIN information_schema.COLUMNS c ON c.TABLE_NAME = tc.Table_Name and c.column_name = cc.column_name
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY' and
tc.TABLE_NAME = @TableName and
tc.TABLE_SCHEMA = @Schema ";
SqlCommand cmd = new SqlCommand(command, conn);
cmd.Parameters.AddWithValue("@TableName", tableName);
cmd.Parameters.AddWithValue("@Schema", schema);
ArrayList list = new ArrayList();
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
list.Add(new string[] { reader[0].ToString(), reader[1].ToString() });
}
reader.Close();
}
return(list);
}
private SqlCommand BuildCommand(string tableName, string[] pkName, ref ColumnInfo[] columns)
{
string schemaOwner;
InfoHelper.ExtractNameAndSchema(tableName, out tableName, out schemaOwner);
string[] nonSorting = new string[] { "binary", "varbinary", "text", "image", "ntext" };
string[] binaryData = new string[] { "binary", "varbinary", "image" };
ArrayList toKeep = new ArrayList();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
for (int i = 0; i < columns.Length; i++)
{
if (Array.IndexOf(binaryData, columns[i].DataType) == -1)
{
sb.Append("[" + columns[i].ColumnName + "],");
toKeep.Add(columns[i]);
}
}
columns = new ColumnInfo[toKeep.Count];
toKeep.CopyTo(columns);
sb.Length = sb.Length - 1;
sb.Append("\r\nFROM [" + schemaOwner + "].[" + tableName + "] ");
if (pkName.Length > 0)
{
sb.Append("\r\nORDER BY ");
for (int i = 0; i < pkName.Length; i++)
{
sb.Append(pkName[i] + " ASC ,");
}
sb.Length = sb.Length - 1;
}
else
{
sb.Append("\r\n ORDER BY ");
for (int i = 0; i < columns.Length; i++)
{
if (Array.IndexOf(nonSorting, columns[i].DataType) == -1)
{
sb.Append("[" + columns[i].ColumnName + "] ASC ,");
}
}
sb.Length = sb.Length - 1;
}
SqlConnection conn = SqlSync.Connection.ConnectionHelper.GetConnection(connData.DatabaseName, connData.SQLServerName, connData.UserId, connData.Password, connData.AuthenticationType, connData.ScriptTimeout);
SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
return(cmd);
}
private void mnuViewObjectScript_Click(object sender, System.EventArgs e)
{
if (lstResults.SelectedItems.Count == 0)
{
return;
}
ListViewItem item = lstResults.SelectedItems[0];
SqlSync.ObjectScript.ObjectScriptHelper helper = new ObjectScriptHelper(this.connData);
string script = string.Empty;
string desc = string.Empty;
string message;
string fullObjName = item.SubItems[0].Text;
string name = fullObjName;
string schema;
InfoHelper.ExtractNameAndSchema(name, out name, out schema);
switch (item.SubItems[1].Text.Trim())
{
case "V":
helper.ScriptDatabaseObject(SqlSync.Constants.DbObjectType.View, name, schema, ref script, ref desc, out message);
break;
case "P":
helper.ScriptDatabaseObject(SqlSync.Constants.DbObjectType.StoredProcedure, name, schema, ref script, ref desc, out message);
break;
case "FN":
helper.ScriptDatabaseObject(SqlSync.Constants.DbObjectType.UserDefinedFunction, name, schema, ref script, ref desc, out message);
break;
default:
message = string.Empty;
break;
}
if (script.Length > 0 || message.Length > 0)
{
ScriptDisplayForm frmDisplay;
if (script.Length > 0)
{
frmDisplay = new ScriptDisplayForm(script, this.connData.SQLServerName, item.SubItems[0].Text);
}
else
{
frmDisplay = new ScriptDisplayForm(message, this.connData.SQLServerName, item.SubItems[0].Text);
}
frmDisplay.ShowDialog();
}
}
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 ScriptForMissingColumns(CodeTableAudit codeTable)
{
//if(SqlSync.DbInformation.InfoHelper.UpdateDateFields.Length == 0 || SqlSync.DbInformation.InfoHelper.UpdateIdFields.Length == 0)
if (!SqlSync.DbInformation.InfoHelper.codeTableAuditCols.IsValid)
{
SqlSync.DbInformation.InfoHelper.SetUpdateColumnNames();
}
string tableName = codeTable.TableName;
string schema;
InfoHelper.ExtractNameAndSchema(tableName, out tableName, out schema);
//Create the columns as null
StringBuilder sb = new StringBuilder();
if (codeTable.CreateIdColumn.Length == 0)
{
sb.Append(ScriptTemplates.ADD_UPDATE_ID_COLUMN.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", SqlSync.DbInformation.InfoHelper.codeTableAuditCols.CreateIdColumns[0])
.Replace("<<userName>>", System.Environment.UserName));
}
if (codeTable.CreateDateColumn.Length == 0)
{
sb.Append(ScriptTemplates.ADD_UPDATE_DATE_COLUMN.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", SqlSync.DbInformation.InfoHelper.codeTableAuditCols.CreateDateColumns[0])
.Replace("<<date>>", DateTime.Now.ToString()));
}
if (codeTable.UpdateIdColumn.Length == 0)
{
sb.Append(ScriptTemplates.ADD_UPDATE_ID_COLUMN.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateIdColumns[0])
.Replace("<<userName>>", System.Environment.UserName));
}
if (codeTable.UpdateDateColumn.Length == 0)
{
sb.Append(ScriptTemplates.ADD_UPDATE_DATE_COLUMN.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateDateColumns[0])
.Replace("<<date>>", DateTime.Now.ToString()));
}
return(sb.ToString());
}
private void viewStoredProcedureScriptToolStripMenuItem_Click(object sender, EventArgs e)
{
string script = "";
string desc = "";
string message;
if (this.treeView1.SelectedNode == null)
{
return;
}
StoredProcedure sp = null;
if (this.treeView1.SelectedNode.Tag is StoredProcedure)
{
sp = (StoredProcedure)this.treeView1.SelectedNode.Tag;
}
else if (this.treeView1.SelectedNode.Parent.Tag is StoredProcedure)
{
sp = (StoredProcedure)this.treeView1.SelectedNode.Parent.Tag;
}
if (sp == null)
{
return;
}
SqlSync.ObjectScript.ObjectScriptHelper helper = new SqlSync.ObjectScript.ObjectScriptHelper(this.connData);
string name = sp.Name;
string schemaOwner;
InfoHelper.ExtractNameAndSchema(name, out name, out schemaOwner);
helper.ScriptDatabaseObject(SqlSync.Constants.DbObjectType.StoredProcedure, name, schemaOwner, ref script, ref desc, out message);
ScriptDisplayForm frmDisplay;
if (script.Length > 0)
{
frmDisplay = new ScriptDisplayForm(script, this.connData.SQLServerName, sp.Name);
}
else
{
frmDisplay = new ScriptDisplayForm(message, this.connData.SQLServerName, sp.Name);
}
frmDisplay.Show();
}
public static string ScriptColumnDefaultsReset(CodeTableAudit codeTable)
{
string tableName = codeTable.TableName;
string schema;
InfoHelper.ExtractNameAndSchema(tableName, out tableName, out schema);
StringBuilder sb = new StringBuilder();
if (codeTable.UpdateIdColumn.Length > 0)
{
sb.Append(ScriptTemplates.DropExistingDefaultConstraint.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", codeTable.UpdateIdColumn)
.Replace("<<defaultValue>>", "SYSTEM_USER"));
}
if (codeTable.UpdateDateColumn.Length > 0)
{
sb.Append(ScriptTemplates.DropExistingDefaultConstraint.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", codeTable.UpdateDateColumn)
.Replace("<<defaultValue>>", "getdate()"));
}
if (codeTable.CreateIdColumn.Length > 0)
{
sb.Append(ScriptTemplates.DropExistingDefaultConstraint.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", codeTable.CreateIdColumn)
.Replace("<<defaultValue>>", "SYSTEM_USER"));
}
if (codeTable.CreateDateColumn.Length > 0)
{
sb.Append(ScriptTemplates.DropExistingDefaultConstraint.Replace("<<tableName>>", tableName)
.Replace("<<schema>>", schema)
.Replace("<<columnName>>", codeTable.CreateDateColumn)
.Replace("<<defaultValue>>", "getdate()"));
}
return(sb.ToString());
}
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 DataTable GetTableValues(string tableName, DateTime selectUpdateDate, out string selectSql)
{
string schemaOwner;
InfoHelper.ExtractNameAndSchema(tableName, out tableName, out schemaOwner);
string where = string.Empty;
string fullSelect = string.Empty;
if (this.syncData != null)
{
DataRow[] dbRows = this.syncData.Database.Select(this.syncData.Database.NameColumn.ColumnName + " ='" + this.data.DatabaseName + "'");
if (dbRows.Length > 0)
{
SQLSyncData.LookUpTableRow[] tableRows = ((SQLSyncData.DatabaseRow)dbRows[0]).GetLookUpTableRows();
for (int i = 0; i < tableRows.Length; i++)
{
if (tableRows[i].Name.ToLower() == tableName.ToLower() && tableRows[i].WhereClause.Length > 0)
{
if (tableRows[i].UseAsFullSelect)
{
fullSelect = tableRows[i].WhereClause;
}
else
{
where = tableRows[i].WhereClause;
}
}
}
}
}
//add the date selection
if (selectByUpdateDate != DateTime.MinValue)
{
string updateDateCol = string.Empty;
string[] columns = SqlSync.DbInformation.InfoHelper.GetColumnNames(schemaOwner + "." + tableName, this.data);
for (int i = 0; i < SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateDateColumns.Count; i++)
{
for (int j = 0; j < columns.Length; j++)
{
if (columns[j].ToLower() == SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateDateColumns[i].ToLower())
{
updateDateCol = SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateDateColumns[i];
break;
}
}
if (updateDateCol != string.Empty)
{
break;
}
}
if (updateDateCol.Length > 0)
{
if (where.Length > 0)
{
where += " AND ";
}
else
{
where = " WHERE ";
}
where += "[" + updateDateCol + "]>='" + selectByUpdateDate.ToShortDateString() + "' ";
}
}
if (fullSelect.Length > 0)
{
selectSql = fullSelect;
}
else
{
selectSql = "SELECT * FROM [" + schemaOwner + "].[" + tableName + "]" + where;
}
SqlCommand cmd = new SqlCommand(selectSql, dbConn);
DataTable table = new DataTable();
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
try
{
adapt.Fill(table);
}
catch
{
}
return(table);
}
private string GenerateIfNotExistsScript(string tableName, string[] columnNames, DataRow row, string[] checkKeyColumns)
{
string schemaOwner;
InfoHelper.ExtractNameAndSchema(tableName, out tableName, out schemaOwner);
//Instead of checking values on all columns, just use
//The specified check columns
if (checkKeyColumns.Length > 0 && checkKeyColumns[0].Length > 0)
{
columnNames = checkKeyColumns;
}
StringBuilder sb = new StringBuilder("IF NOT EXISTS (SELECT 1 FROM [" + schemaOwner + "].[" + tableName + "] WITH (NOLOCK) WHERE ");
for (int i = 0; i < columnNames.Length; i++)
{
bool skipField = false;
List <string> updateDateCols = SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateDateColumns;
//Exclude the update date field from the select statement
for (int j = 0; j < updateDateCols.Count; j++)
{
if (columnNames[i].ToUpper() == updateDateCols[j].ToUpper())
{
skipField = true;
break;
}
}
//Exclude the update Id field from the select statement
List <string> updateIdCols = SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateIdColumns;
for (int j = 0; j < updateIdCols.Count; j++)
{
if (columnNames[i].ToUpper() == updateIdCols[j].ToUpper())
{
skipField = true;
break;
}
}
//Exclude Date data types since the string formatting
//won't necessarily match
if (row[columnNames[i]].GetType() == typeof(DateTime))
{
skipField = true;
}
//Skip the field if selected.
if (skipField)
{
continue;
}
sb.Append("[" + columnNames[i] + "]");
if (row[columnNames[i]] == DBNull.Value)
{
sb.Append(" IS NULL ");
}
else
{
if (row[columnNames[i]].GetType() == typeof(Boolean))
{
sb.Append("=" + Convert.ToByte(row[columnNames[i]]));
}
else
{
sb.Append("='" + row[columnNames[i]].ToString().TrimEnd().Replace("'", "''") + "'");
}
}
if (i < columnNames.Length - 1)
{
sb.Append(" AND ");
}
}
if (sb.ToString().EndsWith(" AND "))
{
sb.Length = sb.Length - 5;
}
sb.Append(")");
return(sb.ToString());
}
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());
}
private string GenerateInsertScript(string tableName, string[] columnNames, DataRow row)
{
string val;
string schemaOwner;
InfoHelper.ExtractNameAndSchema(tableName, out tableName, out schemaOwner);
StringBuilder sb = new StringBuilder("INSERT INTO [" + schemaOwner + "].[" + tableName + "] (");
for (int i = 0; i < columnNames.Length; i++)
{
sb.Append("[" + columnNames[i] + "]");
if (i < columnNames.Length - 1)
{
sb.Append(",");
}
}
sb.Append(") VALUES (");
for (int i = 0; i < columnNames.Length; i++)
{
val = string.Empty;
//Check to see if we need to insert our new values
if (this.ReplaceDateAndId == true)
{
//Update date
for (int j = 0; j < SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateDateColumns.Count; j++)
{
if (columnNames[i].ToUpper() == SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateDateColumns[j].ToUpper())
{
val = DateTime.Now.ToString();
break;
}
}
//Update Id
for (int j = 0; j < SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateIdColumns.Count; j++)
{
if (columnNames[i].ToUpper() == SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateIdColumns[j].ToUpper())
{
val = System.Environment.UserName;
break;
}
}
}
if (row[columnNames[i]] == DBNull.Value && val == string.Empty)
{
sb.Append(" NULL ");
}
else
{
if (val == string.Empty)
{
if (row[columnNames[i]].GetType() == typeof(Boolean))
{
sb.Append(Convert.ToByte(row[columnNames[i]]));
}
else
{
sb.Append("'" + row[columnNames[i]].ToString().TrimEnd().Replace("'", "''") + "'");
}
}
else
{
sb.Append("'" + val + "'");
}
}
if (i < columnNames.Length - 1)
{
sb.Append(",");
}
}
sb.Append(")");
return(sb.ToString());
}
private string GenerateUpdateScript(string tableName, string[] columnNames, DataRow row, string[] updateKeyColumns)
{
string schemaOwner;
InfoHelper.ExtractNameAndSchema(tableName, out tableName, out schemaOwner);
string val;
StringBuilder sb = new StringBuilder("UPDATE [" + schemaOwner + "].[" + tableName + "] SET ");
for (int i = 0; i < columnNames.Length; i++)
{
//Skip columns defined as key columns
bool skipColumn = false;
for (int j = 0; j < updateKeyColumns.Length; j++)
{
if (columnNames[i] == updateKeyColumns[j])
{
skipColumn = true;
break;
}
}
if (skipColumn)
{
continue;
}
//Column Name
sb.Append("[" + columnNames[i] + "]=");
val = string.Empty;
//Check to see if we need to insert our new values
if (this.ReplaceDateAndId == true)
{
//Update date
for (int j = 0; j < SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateDateColumns.Count; j++)
{
if (columnNames[i].ToUpper() == SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateDateColumns[j].ToUpper())
{
val = DateTime.Now.ToString();
break;
}
}
//Update Id
for (int j = 0; j < SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateIdColumns.Count; j++)
{
if (columnNames[i].ToUpper() == SqlSync.DbInformation.InfoHelper.codeTableAuditCols.UpdateIdColumns[j].ToUpper())
{
val = System.Environment.UserName;
break;
}
}
}
if (row[columnNames[i]] == DBNull.Value && val == string.Empty)
{
sb.Append(" NULL ");
}
else
{
if (val == string.Empty)
{
if (row[columnNames[i]].GetType() == typeof(Boolean))
{
sb.Append(Convert.ToByte(row[columnNames[i]]));
}
else
{
sb.Append("'" + row[columnNames[i]].ToString().TrimEnd().Replace("'", "''") + "'");
}
}
else
{
sb.Append("'" + val + "'");
}
}
if (i < columnNames.Length - 1)
{
sb.Append(",");
}
}
if (sb.ToString().EndsWith(","))
{
sb.Length = sb.Length - 1;
}
//Where clause
sb.Append(" WHERE ");
for (int i = 0; i < updateKeyColumns.Length; i++)
{
sb.Append("[" + updateKeyColumns[i] + "]=");
for (int j = 0; j < columnNames.Length; j++)
{
if (columnNames[j] == updateKeyColumns[i])
{
sb.Append("'" + row[columnNames[j]].ToString().TrimEnd().Replace("'", "''") + "'");
break;
}
}
if (i < updateKeyColumns.Length - 1)
{
sb.Append(" AND ");
}
}
return(sb.ToString());
}
public static string ScriptForUpdateTrigger(CodeTableAudit auditTable, ConnectionData connData)
{
string updateDateCol = auditTable.UpdateDateColumn;
string updateIdCol = auditTable.UpdateIdColumn;
string tableName = auditTable.TableName;
string schema;
InfoHelper.ExtractNameAndSchema(tableName, out tableName, out schema);
string triggerName = String.Format(updateTrigFormat, tableName, schema);
string shortTrigger = String.Format(shortTrigFormat, tableName);
StringBuilder remove = new StringBuilder();
remove.Append("IF EXISTS (SELECT name FROM sys.objects WHERE name = '" + shortTrigger + "' AND type = 'TR')\r\n");
remove.Append("\tDROP TRIGGER " + triggerName + "\r\n");
remove.Append("GO\r\n\r\n");
if (updateDateCol == string.Empty || updateIdCol == string.Empty)
{
return(string.Empty);
}
ArrayList pkCols = GetPrimaryKeyColumnsWithType(auditTable.TableName, connData);
if (pkCols.Count == 0)
{
return(string.Empty);
}
StringBuilder sb = new StringBuilder();
sb.Append("CREATE TRIGGER " + triggerName + " ON [" + schema + "].[" + tableName + "] FOR UPDATE, INSERT\r\nAS\r\nBEGIN\r\n");
for (int i = 0; i < pkCols.Count; i++)
{
string[] col = (string[])pkCols[i];
sb.Append("\tDECLARE @" + col[0].Replace(" ", "") + " " + col[1] + "\r\n");
}
sb.Append("\r\n\tSELECT ");
for (int i = 0; i < pkCols.Count; i++)
{
string[] col = (string[])pkCols[i];
sb.Append("@" + col[0].Replace(" ", "") + "=[" + col[0] + "],");
}
sb.Length = sb.Length - 1;
sb.Append(" FROM inserted\r\n\r\n");
sb.Append("\tIF NOT EXISTS(SELECT 1 FROM [" + schema + "].[" + tableName + "] WHERE ");
for (int i = 0; i < pkCols.Count; i++)
{
string[] col = (string[])pkCols[i];
sb.Append("[" + col[0] + "]=@" + col[0].Replace(" ", "") + " AND ");
}
sb.Length = sb.Length - 4;
sb.Append(")\r\n");
sb.Append("\t\tRAISERROR('Unable to complete UpdateId/UpdateDate auditing trigger for [" + schema + "].[" + tableName + "] table. PK Value Not Found. Change Rolled Back.',16,1)\r\n\r\n");
sb.Append("\r\n\tSELECT @@nestlevel");
sb.Append("\r\n\tIF @@nestlevel <= 10");
sb.Append("\r\n\tBEGIN");
sb.Append("\r\n\t\tUPDATE [" + schema + "].[" + tableName + "] SET [" + updateDateCol + "] = getdate(), [" + updateIdCol + "] = SYSTEM_USER\r\n");
sb.Append("\t\tWHERE ");
for (int i = 0; i < pkCols.Count; i++)
{
string[] col = (string[])pkCols[i];
sb.Append("[" + col[0] + "]=@" + col[0].Replace(" ", "") + " AND ");
}
sb.Length = sb.Length - 4;
sb.Append("\r\n\r\n\t\tIF(@@Error <> 0)\r\n");
sb.Append("\t\t\tRAISERROR('Unable to complete UpdateId/UpdateDate auditing trigger for [" + schema + "].[" + tableName + "] table. Change Rolled Back.',16,1)\r\n");
sb.Append("\r\n\tEND");
sb.Append("\r\nEND");
sb.Append("\r\nGO\r\n");
return(remove.ToString() + sb.ToString());
}
private void lnkScript_LinkClicked(object sender, System.Windows.Forms.LinkLabelLinkClickedEventArgs e)
{
//check to see if we need to ask for a directory
bool needDir = false;
foreach (ListViewItem item in lstComparison.CheckedItems)
{
if (item.SubItems[5].Text.Length == 0)
{
needDir = true;
break;
}
}
if (needDir && this.defaultSavePath.Length == 0)
{
DialogResult result = fldrSaveDir.ShowDialog();
if (result == DialogResult.OK)
{
this.defaultSavePath = fldrSaveDir.SelectedPath;
}
else
{
return;
}
}
//Loop through checked files
bool scripted = false;
foreach (ListViewItem item in lstComparison.CheckedItems)
{
string name = item.SubItems[0].Text + item.SubItems[1].Text;
string schema;
InfoHelper.ExtractNameAndSchema(name, out name, out schema);
string shortName = schema + "." + name.Replace("\\", "-");
string longName;
string message;
if (item.SubItems[5].Text.Length == 0)
{
longName = Path.Combine(this.defaultSavePath, shortName);
}
else
{
longName = item.SubItems[5].Text;
}
statStatus.Text = "Scripting " + shortName;
scripted = ScriptObject(name, schema, item.SubItems[1].Text, longName, out message);
if (scripted)
{
item.BackColor = Color.LawnGreen;
item.SubItems[4].Text = shortName;
item.SubItems[5].Text = longName;
item.Checked = false;
}
else
{
item.BackColor = Color.Magenta;
MessageBox.Show(message, "Error Scripting", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
helper.DisconnectServer();
statStatus.Text = "Scripting Complete";
}