/// <summary>
/// Constructor
/// </summary>
/// <param name="Name">Name</param>
/// <param name="Definition">Definition</param>
/// <param name="Type">Type</param>
/// <param name="ParentTable">Parent table</param>
public Trigger(string Name, string Definition, TriggerType Type, Table ParentTable)
{
this.Name = Name;
this.Definition = Definition;
this.Type = Type;
this.ParentTable = ParentTable;
}
/// <summary>
/// Gets the list of triggers associated with the table
/// </summary>
/// <param name="Table">Table object</param>
/// <returns>The string containing the various creation commands</returns>
private static string GetTriggerCommand(Table Table)
{
StringBuilder Builder = new StringBuilder();
foreach (Trigger Trigger in Table.Triggers)
{
string Definition = Regex.Replace(Trigger.Definition, "-- (.*)", "");
Builder.Append(Definition.Replace("\n", " ").Replace("\r", " ") + "\n");
}
return Builder.ToString();
}
/// <summary>
/// Gets the table creation commands
/// </summary>
/// <param name="Table">Table object</param>
/// <returns>The string containing the creation commands</returns>
private static string GetTableCommand(Table Table)
{
StringBuilder Builder = new StringBuilder();
Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE TABLE ").Append(Table.Name).Append("(");
string Splitter = "";
foreach (IColumn Column in Table.Columns)
{
Builder.Append(Splitter).Append(Column.Name).Append(" ").Append(Column.DataType.ToSqlDbType().ToString());
if (Column.DataType == SqlDbType.VarChar.ToDbType() || Column.DataType == SqlDbType.NVarChar.ToDbType())
{
if (Column.Length < 0 || Column.Length >= 4000)
{
Builder.Append("(MAX)");
}
else
{
Builder.Append("(").Append(Column.Length.ToString()).Append(")");
}
}
if (!Column.Nullable)
{
Builder.Append(" NOT NULL");
}
if (Column.Unique)
{
Builder.Append(" UNIQUE");
}
if (Column.PrimaryKey)
{
Builder.Append(" PRIMARY KEY");
}
if (!string.IsNullOrEmpty(Column.Default))
{
Builder.Append(" DEFAULT ").Append(Column.Default.Replace("(", "").Replace(")", "").Replace("'", "''"));
}
if (Column.AutoIncrement)
{
Builder.Append(" IDENTITY");
}
Splitter = ",";
}
Builder.Append(")'\n");
int Counter = 0;
foreach (IColumn Column in Table.Columns)
{
if (Column.Index && Column.Unique)
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE UNIQUE INDEX ");
Builder.Append("Index_").Append(Column.Name).Append(Counter.ToString()).Append(" ON ");
Builder.Append(Column.ParentTable.Name).Append("(").Append(Column.Name).Append(")");
Builder.Append("'\n");
}
else if (Column.Index)
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE INDEX ");
Builder.Append("Index_").Append(Column.Name).Append(Counter.ToString()).Append(" ON ");
Builder.Append(Column.ParentTable.Name).Append("(").Append(Column.Name).Append(")");
Builder.Append("'\n");
}
++Counter;
}
return Builder.ToString();
}
/// <summary>
/// Gets the foreign keys creation command
/// </summary>
/// <param name="Table">Table object</param>
/// <returns>The string creating the foreign keys</returns>
private static string GetForeignKeyCommand(Table Table)
{
StringBuilder Builder = new StringBuilder();
foreach (IColumn Column in Table.Columns)
{
if (Column.ForeignKey.Count > 0)
{
foreach (IColumn ForeignKey in Column.ForeignKey)
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'ALTER TABLE ");
Builder.Append(Column.ParentTable.Name).Append(" ADD FOREIGN KEY (");
Builder.Append(Column.Name).Append(") REFERENCES ").Append(ForeignKey.ParentTable.Name);
Builder.Append("(").Append(ForeignKey.Name).Append(")");
if (Column.OnDeleteCascade)
Builder.Append(" ON DELETE CASCADE");
if (Column.OnUpdateCascade)
Builder.Append(" ON UPDATE CASCADE");
if (Column.OnDeleteSetNull)
Builder.Append(" ON DELETE SET NULL");
Builder.Append("'\n");
}
}
}
return Builder.ToString();
}
/// <summary>
/// Gets a list of commands altering triggers
/// </summary>
/// <param name="Table">Desired table containing the triggers</param>
/// <param name="CurrentTable">Current table containing the current triggers</param>
/// <returns>A string containing a list of commands</returns>
private static string GetAlterTriggerCommand(Table Table, Table CurrentTable)
{
StringBuilder Builder = new StringBuilder();
foreach (Trigger Trigger in Table.Triggers)
{
foreach (Trigger Trigger2 in CurrentTable.Triggers)
{
if (Trigger.Name == Trigger2.Name && Trigger.Definition != Trigger2.Definition)
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'DROP TRIGGER ").Append(Trigger.Name).Append("'\n");
string Definition = Regex.Replace(Trigger.Definition, "-- (.*)", "");
Builder.Append(Definition.Replace("\n", " ").Replace("\r", " ") + "\n");
break;
}
}
}
return Builder.ToString();
}
/// <summary>
/// Gets alter commands for a table
/// </summary>
/// <param name="DesiredTable">Desired table structure</param>
/// <param name="CurrentTable">Current table structure</param>
/// <returns>A string containing a list of commands</returns>
private static string GetAlterTableCommand(Table DesiredTable, Table CurrentTable)
{
StringBuilder Builder = new StringBuilder();
foreach (IColumn Column in DesiredTable.Columns)
{
IColumn CurrentColumn = CurrentTable[Column.Name];
if (CurrentColumn == null)
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'ALTER TABLE ").Append(DesiredTable.Name)
.Append(" ADD ").Append(Column.Name).Append(" ").Append(Column.DataType.ToSqlDbType().ToString());
if (Column.DataType == SqlDbType.VarChar.ToDbType() || Column.DataType == SqlDbType.NVarChar.ToDbType())
{
if (Column.Length < 0 || Column.Length >= 4000)
{
Builder.Append("(MAX)");
}
else
{
Builder.Append("(").Append(Column.Length.ToString()).Append(")");
}
}
Builder.Append("'\n");
foreach (IColumn ForeignKey in Column.ForeignKey)
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'ALTER TABLE ").Append(DesiredTable.Name)
.Append(" ADD FOREIGN KEY (").Append(Column.Name).Append(") REFERENCES ")
.Append(ForeignKey.ParentTable.Name).Append("(").Append(ForeignKey.Name).Append(")");
if (Column.OnDeleteCascade)
Builder.Append(" ON DELETE CASCADE");
if (Column.OnUpdateCascade)
Builder.Append(" ON UPDATE CASCADE");
if (Column.OnDeleteSetNull)
Builder.Append(" ON DELETE SET NULL");
Builder.Append("'\n");
}
}
else if (CurrentColumn.DataType != Column.DataType
|| (CurrentColumn.DataType == Column.DataType
&& CurrentColumn.DataType == SqlDbType.NVarChar.ToDbType()
&& CurrentColumn.Length != Column.Length * 2
&& (CurrentColumn.Length != -1 && Column.Length != -1)))
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'ALTER TABLE ").Append(DesiredTable.Name)
.Append(" ALTER COLUMN ").Append(Column.Name).Append(" ").Append(Column.DataType.ToSqlDbType().ToString());
if (Column.DataType == SqlDbType.VarChar.ToDbType() || Column.DataType == SqlDbType.NVarChar.ToDbType())
{
if (Column.Length < 0 || Column.Length >= 4000)
{
Builder.Append("(MAX)");
}
else
{
Builder.Append("(").Append(Column.Length.ToString()).Append(")");
}
}
Builder.Append("'\n");
}
}
return Builder.ToString();
}
/// <summary>
/// Gets the foreign keys creation command
/// </summary>
/// <param name="Table">Table object</param>
/// <returns>The string creating the foreign keys</returns>
private static string GetForeignKeyCommand(Table Table)
{
StringBuilder Builder = new StringBuilder();
foreach (IColumn Column in Table.Columns)
{
if (Column.ForeignKey.Count > 0)
{
foreach (IColumn ForeignKey in Column.ForeignKey)
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'ALTER TABLE ");
Builder.Append(Column.ParentTable.Name).Append(" ADD FOREIGN KEY (");
Builder.Append(Column.Name).Append(") REFERENCES ").Append(ForeignKey.ParentTable.Name);
Builder.Append("(").Append(ForeignKey.Name).Append(")'\n");
}
}
}
return Builder.ToString();
}
/// <summary>
/// Gets alter commands for a table
/// </summary>
/// <param name="DesiredTable">Desired table structure</param>
/// <param name="CurrentTable">Current table structure</param>
/// <returns>A string containing a list of commands</returns>
private static string GetAlterTableCommand(Table DesiredTable, Table CurrentTable)
{
StringBuilder Builder = new StringBuilder();
foreach (Column Column in DesiredTable.Columns)
{
Column CurrentColumn = CurrentTable[Column.Name];
if (CurrentColumn == null)
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'ALTER TABLE ").Append(DesiredTable.Name)
.Append(" ADD ").Append(Column.Name).Append(" ").Append(Column.DataType.ToString());
if (Column.DataType == SqlDbType.VarChar || Column.DataType == SqlDbType.NVarChar)
{
if (Column.Length == -1 || Column.Length == 5000)
{
Builder.Append("(MAX)");
}
else
{
Builder.Append("(").Append(Column.Length.ToString()).Append(")");
}
}
Builder.Append("'\n");
foreach (Column ForeignKey in Column.ForeignKey)
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'ALTER TABLE ").Append(DesiredTable.Name)
.Append(" ADD FOREIGN KEY (").Append(Column.Name).Append(") REFERENCES ")
.Append(ForeignKey.ParentTable.Name).Append("(").Append(ForeignKey.Name).Append(")'\n");
}
}
else if (CurrentColumn.DataType != Column.DataType
|| (CurrentColumn.DataType == Column.DataType
&& CurrentColumn.DataType == SqlDbType.NVarChar
&& CurrentColumn.Length != Column.Length * 2
&& (CurrentColumn.Length != -1 && Column.Length != -1)))
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'ALTER TABLE ").Append(DesiredTable.Name)
.Append(" ALTER COLUMN ").Append(Column.Name).Append(" ").Append(Column.DataType.ToString());
if (Column.DataType == SqlDbType.VarChar || Column.DataType == SqlDbType.NVarChar)
{
if (Column.Length == -1 || Column.Length == 5000)
{
Builder.Append("(MAX)");
}
else
{
Builder.Append("(").Append(Column.Length.ToString()).Append(")");
}
}
Builder.Append("'\n");
}
}
return Builder.ToString();
}
