private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
projectNew.ServerName = @"RBM-Shotec";
projectNew.UserName = "******";
projectNew.Password = "******";
projectNew.IsWindowsAuthentication = false;
projectNew.Name = "Migration Script";
projectNew.CreateDate = System.DateTime.Now;
DataType.LoadHashTables(Application.ExecutablePath.Substring(0, Application.ExecutablePath.LastIndexOf("\\")) + "\\" + "DataTypesMapper.xml");
projectNew.DatabaseType = DatabaseTypes.SQLServer;
projectNew.CheckHasData = true;
projectNew.Connect();
projectNew.ExtractorManager.DatabaseReaders.GetDatabases(projectNew);
Database database = projectNew.GetDatabaseByName("PAMSDB");
#region Extrating Table Information
if (database.Tables != null && database.Tables.Count == 0)
{
projectNew.ExtractorManager.DatabaseReaders.GetTables(database);
foreach (Common.Entities.MetaDataSchema.Table tbl in database.Tables)
{
projectNew.ExtractorManager.DatabaseReaders.GetColumns(tbl);
}
}
#endregion
strGen = new StringBuilder();
Generator g = new Generator(CodeType.Sql);
foreach (Table t in database.Tables)
{
if (t.HasData)
{
string columnsScript = "[$$<%$$ TableColumn,Name $$%>,$$]";
strGen.AppendFormat(string.Format("-- Data Migration for Table {0}.{1} --", t.Schema, t.Name));
strGen.AppendFormat(Environment.NewLine);
if (t.HasIdentityColumn)
{
strGen.AppendFormat(Environment.NewLine);
strGen.AppendFormat(string.Format("SET IDENTITY_INSERT [{0}].[{1}] ON;", t.Schema, t.Name));
strGen.AppendFormat(Environment.NewLine);
strGen.AppendFormat(Environment.NewLine);
}
columnsScript = g.GetCode(columnsScript, "Test", "ClassTest", t);
columnsScript = columnsScript.Remove(columnsScript.Length - 1, 1);
strGen.AppendFormat(string.Format(@"insert into [{0}].[{1}] ({2})", t.Schema, t.Name, columnsScript));
strGen.AppendFormat(Environment.NewLine);
strGen.AppendFormat(string.Format(@"select {0} from [{1}].[{2}].[{3}] ", columnsScript, t.ParentDatabase.Name, t.Schema, t.Name));
strGen.AppendFormat(Environment.NewLine);
if (t.HasIdentityColumn)
{
strGen.AppendFormat(Environment.NewLine);
strGen.AppendFormat(string.Format("SET IDENTITY_INSERT [{0}].[{1}] OFF;", t.Schema, t.Name));
strGen.AppendFormat(Environment.NewLine);
strGen.AppendFormat(Environment.NewLine);
}
strGen.AppendFormat("GO");
strGen.AppendFormat(Environment.NewLine);
strGen.AppendFormat(Environment.NewLine);
gridControl1.BeginInvoke(new UpdateDataSourceDelegate(UpdateGridDataSource), new object[] { t });
}
}
}
private void backgroundWorker2_DoWork(object sender, DoWorkEventArgs e)
{
if (projectNew.Databases.Count == 0)
{
projectNew.ServerName = @"RBM-Shotec";
projectNew.UserName = "******";
projectNew.Password = "******";
projectNew.IsWindowsAuthentication = false;
projectNew.Name = "Migration Script";
projectNew.CreateDate = System.DateTime.Now;
DataType.LoadHashTables(Application.ExecutablePath.Substring(0, Application.ExecutablePath.LastIndexOf("\\")) + "\\" + "DataTypesMapper.xml");
projectNew.DatabaseType = DatabaseTypes.SQLServer;
projectNew.CheckHasData = true;
projectNew.Connect();
projectNew.ExtractorManager.DatabaseReaders.GetDatabases(projectNew);
}
Database database = projectNew.GetDatabaseByName("PAMSDB");
Database oldDatabase = projectNew.GetDatabaseByName("ShotecEgypt");
#region Extrating Table Information
if (database.Tables != null && database.Tables.Count == 0)
{
projectNew.ExtractorManager.DatabaseReaders.GetTables(database);
foreach (Common.Entities.MetaDataSchema.Table tbl in database.Tables)
{
projectNew.ExtractorManager.DatabaseReaders.GetTableRelations(tbl);
projectNew.ExtractorManager.DatabaseReaders.GetColumns(tbl);
}
}
if (oldDatabase.Tables != null && oldDatabase.Tables.Count == 0)
{
projectNew.ExtractorManager.DatabaseReaders.GetTables(oldDatabase);
foreach (Common.Entities.MetaDataSchema.Table tbl in oldDatabase.Tables)
{
projectNew.ExtractorManager.DatabaseReaders.GetTableRelations(tbl);
projectNew.ExtractorManager.DatabaseReaders.GetColumns(tbl);
Table newTable = Table.GetByNameandSchema(database.Tables, tbl.Name, tbl.Schema);
if (tbl.HasData)
if (tbl.Name == "sysdiagrams")
continue;
else if (tbl.Name == "Roles" || tbl.Name == "BusinessEntity" || tbl.Name == "Credential" || tbl.Name == "Person" || tbl.Name == "UserRoles" || tbl.Name == "RolePrivileges")
continue;
else
gridControl1.BeginInvoke(new UpdateDataSourceDelegate(UpdateGridDataSource), new object[] { tbl });
}
}
#endregion
strGen = new StringBuilder();
List<Table> tablesOrdered = new List<Table>();
OrderTablesBasedOnDependencies(oldDatabase, ref tablesOrdered);
Generator g = new Generator(CodeType.Sql);
foreach (Table t in tablesOrdered)
{
if (t.Name == "sysdiagrams")
continue;
if (t.HasData)
{
Table newTable = Table.GetByNameandSchema(database.Tables, t.Name, t.Schema);
if (newTable == null)
continue;
if(newTable != null && newTable.HasData)
{
if (newTable.Name != "Roles" && newTable.Name != "BusinessEntity" && newTable.Name != "Credential" && newTable.Name != "Person" && newTable.Name != "UserRoles" && newTable.Name != "RolePrivileges")
continue;
}
string columnsScript = "";
strGen.AppendFormat(string.Format("-- Data Migration for Table {0}.{1} --", t.Schema, t.Name));
strGen.AppendFormat(Environment.NewLine);
if (t.HasIdentityColumn)
{
if (newTable.Name != "Roles" && newTable.Name != "BusinessEntity" && newTable.Name != "Credential" && newTable.Name != "Person" && newTable.Name != "UserRoles" && newTable.Name != "RolePrivileges")
{
strGen.AppendFormat(Environment.NewLine);
strGen.AppendFormat(string.Format("SET IDENTITY_INSERT [{0}].[{1}] ON;", t.Schema, t.Name));
strGen.AppendFormat(Environment.NewLine);
strGen.AppendFormat(Environment.NewLine);
}
}
string oldColumnNames = "";
foreach(Column c in t.Columns)
{
Column newC = Column.GetByName(newTable.Columns, c.Name);
if (c.Name == "EgyptBusinessEntityID" || c.Name == "RPEBusinessEntityID" || c.Name == "QatarBusinessEntityID" || c.Name == "EgyptID" || c.Name == "RPEID" || c.Name == "QatarID")
{
continue;
}
else
{
if (newC == null)
continue;
columnsScript += c.Name + ",";
oldColumnNames += GetColumnString(t, c) + ",";
}
}
foreach (Column c in newTable.Columns)
{
Column oldC = Column.GetByName(t.Columns, c.Name);
if (c.Name == "EgyptBusinessEntityID" || c.Name == "RPEBusinessEntityID" || c.Name == "QatarBusinessEntityID" || c.Name == "EgyptID" || c.Name == "RPEID" || c.Name == "QatarID")
{
if (c.Name == "EgyptBusinessEntityID" || c.Name == "EgyptID")
{
Column pk = Column.GetByPrimaryKey(t.Columns);
if (pk != null)
{
columnsScript += c.Name + ",";
oldColumnNames += pk.Name + ",";
}
else
{
columnsScript += c.Name + ",";
oldColumnNames += "0,";
}
}
}
}
if(columnsScript.Length > 0)
columnsScript = columnsScript.Remove(columnsScript.Length - 1, 1);
if (oldColumnNames.Length > 0)
oldColumnNames = oldColumnNames.Remove(oldColumnNames.Length - 1, 1);
strGen.AppendFormat(string.Format(@"insert into [{0}].[{1}] ({2})", t.Schema, t.Name, columnsScript));
strGen.AppendFormat(Environment.NewLine);
strGen.AppendFormat(string.Format(@"select {0} from [{1}].[{2}].[{3}] ", oldColumnNames, t.ParentDatabase.Name, t.Schema, t.Name));
strGen.AppendFormat(Environment.NewLine);
if (t.HasIdentityColumn)
{
if (newTable.Name != "Roles" && newTable.Name != "BusinessEntity" && newTable.Name != "Credential" && newTable.Name != "Person" && newTable.Name != "UserRoles" && newTable.Name != "RolePrivileges")
{
strGen.AppendFormat(Environment.NewLine);
strGen.AppendFormat(string.Format("SET IDENTITY_INSERT [{0}].[{1}] OFF;", t.Schema, t.Name));
strGen.AppendFormat(Environment.NewLine);
strGen.AppendFormat(Environment.NewLine);
}
}
strGen.AppendFormat("GO");
strGen.AppendFormat(Environment.NewLine);
strGen.AppendFormat(Environment.NewLine);
}
}
}
