private string GetChangeScript(Database original) { Console.WriteLine("Generating SQL file"); var listDiff = new SQLScriptList(); listDiff.AddRange(original.UserTypes.ToSqlDiff()); listDiff.AddRange(original.TablesTypes.ToSqlDiff()); listDiff.AddRange(original.Tables.ToSqlDiff()); return(listDiff.ToSQL()); }
public static string alter(ISchemaBase target, string connectionString) { var db = target.RootParent as IDatabase; using (SqlConnection connection = new SqlConnection(connectionString)) { if (db != null && DialogResult.Yes != MessageBox.Show(String.Format("Alter {0} {1} in {2}..{3}?\n(WARNING: No automatic backup is made!)", target.ObjectType, target.Name, connection.DataSource, connection.Database), "ALTER Destination?", MessageBoxButtons.YesNo, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button2)) { return("Cancelled."); } string result = string.Empty; SQLScriptList SqlDiff = target.ToSqlDiff(new List <ISchemaBase>()); string[] splitOn = { "GO" }; string[] tempList = SqlDiff.ToSQL().Split(splitOn, StringSplitOptions.RemoveEmptyEntries); List <string> scripts = new List <string>(tempList); foreach (string sql in scripts) { string script = sql; //script = script.Replace("\r", ""); //script = script.Replace("\t", ""); //script = script.Replace("\n", " "); if (target.ObjectType == Enums.ObjectType.StoredProcedure) { script = sql.Replace("CREATE PROCEDURE", "ALTER PROCEDURE"); } SqlCommand command = new SqlCommand(script, connection); try { connection.Open(); command.ExecuteNonQuery(); connection.Close(); } catch (Exception e) { result += target.Name + ": " + e.Message + "\n\n"; connection.Close(); } } return(result); } }
public static string rebuild(ISchemaBase target, string connectionString) { SQLScriptList SqlDiff = target.ToSqlDiff(); string[] splitOn = { "GO" }; string[] tempList = SqlDiff.ToSQL().Split(splitOn, StringSplitOptions.RemoveEmptyEntries); List <string> scripts = new List <string>(tempList); string result = string.Empty; string script = scripts[0]; if (target.ObjectType == Enums.ObjectType.Table) { script = script.Replace("CREATE TABLE", "ALTER TABLE"); } MessageBox.Show(script); return(result); }
public void OriginHasExtraColumn_NotChangedColumnSelected_ShouldBeEmptyScript() { int idStorage = 1; System.Func <int> getId = new Func <int>(() => ++ idStorage); Database originDatabase = new Database(); originDatabase.Info = new DatabaseInfo() { Collation = "SQL_Latin1_General_CP1_CI_AS" }; originDatabase.Id = getId(); originDatabase.Options = new Options.SqlOption(); Table originTable = new Table(originDatabase); originTable.Name = "Example"; originTable.Id = getId(); var originColumn1 = new Column(originTable) { Name = "Test", Type = "int", Id = getId() }; var originColumn2 = new Column(originTable) { Name = "Test2", Type = "varchar(20)", Id = getId() }; var originColumn3 = new Column(originTable) { Name = "Test3", Type = "bigint", Id = getId() }; originTable.Columns.Add(originColumn1); originTable.Columns.Add(originColumn3); originTable.Columns.Add(originColumn2); originDatabase.Tables.Add(originTable); Database destinationDatabase = new Database(); destinationDatabase.Info = new DatabaseInfo() { Collation = "SQL_Latin1_General_CP1_CI_AS" }; destinationDatabase.Id = getId(); destinationDatabase.Options = new Options.SqlOption(); Table destinationTable = new Table(destinationDatabase); destinationTable.Name = "Example"; destinationTable.Id = getId(); var destinationColumn1 = new Column(destinationTable) { Name = "Test", Type = "int", Id = getId() }; var destinationColumn3 = new Column(destinationTable) { Name = "Test3", Type = "bigint", Id = getId() }; destinationTable.Columns.Add(destinationColumn1); destinationTable.Columns.Add(destinationColumn3); destinationDatabase.Tables.Add(destinationTable); originTable.OriginalTable = (Table)originTable.Clone((Database)originTable.Parent); new CompareColumns().GenerateDifferences <Table>(originTable.Columns, destinationTable.Columns); SQLScriptList sqlList = originTable.ToSqlDiff(new List <ISchemaBase>() { originColumn3 }); string sql = sqlList.ToSQL(); Assert.AreEqual("", sql); }