public StartWindow()
{
InitializeComponent();
SettingsManager.Initialize();
result = new QueryResult();
RadMessageBox.SetThemeName("VisualStudio2012Light");
}
public static string ExecuteInserts(QueryResult querys, RadProgressBar progressBarResult)
{
StringBuilder results = new StringBuilder();
// Obtenemos la configuracion
SettingsModel model = SettingsManager.GetXml(true);
if (model == null)
{
results.AppendLine(GetTime() + " - Settings are not setted");
return results.ToString();
}
// Inicializamos el manager
DatabaseManage manager = new DatabaseManage(model);
int currentNumberRow = 0;
int rowsAffected = 0;
results.AppendLine(string.Format("{0} - Starting migration", GetTime()));
foreach (string query in querys.Querys.ToString().Split('\n'))
{
if (string.IsNullOrWhiteSpace(query)) continue;
try
{
manager.ExecuteCommand(query);
rowsAffected++;
}
catch (Exception ex)
{
results.AppendLine(string.Format("{0} - Migrating error - Current query: {1} - Error: {2}", GetTime(), query, ex.Message));
}
currentNumberRow++;
progressBarResult.BeginInvoke(new Action(() =>
{
int newValue = (int)(((float)currentNumberRow / querys.NumberOfItems) * 100);
if (newValue > 100)
{
newValue = 100;
}
progressBarResult.Value1 = newValue;
}));
}
results.AppendLine(results.Length == 0
? string.Format("{0} - Migrated OK - Rows inserted: {1}", GetTime(), rowsAffected)
: string.Format("{0} - Migrated with Errors - Rows inserted: {1}", GetTime(), rowsAffected));
return results.ToString();
}
public static string ExecuteQuerys(QueryResult result, RadProgressBar progressBarResult)
{
StringBuilder results = new StringBuilder();
// Obtenemos la configuracion
SettingsModel model = SettingsManager.GetXml(true);
if (model == null)
{
results.AppendLine(GetTime() + " - Settings are not setted");
return results.ToString();
}
// Inicializamos el manager
DatabaseManage manager = new DatabaseManage(model);
// Si tenemos que deshabilitar constraints
if (result.DisableConstraints)
{
foreach (string query in result.QuerysDisableConstraints.ToString().Split('\n'))
{
if (string.IsNullOrWhiteSpace(query)) continue;
int startCut = query.IndexOf("EXISTS", StringComparison.Ordinal) + 7;
int lengthCut = query.IndexOf("DISABLE", StringComparison.Ordinal) - startCut - 1;
string tableName = query.Substring(startCut, lengthCut);
try
{
manager.ExecuteCommand(query);
results.AppendLine(string.Format("{0} - Disabling constraints for table {1} - Disabled", GetTime(), tableName));
}
catch (Exception ex)
{
results.AppendLine(string.Format("{0} - Disabling constraints for table {1} - Cannot be disabled - Error: {2}", GetTime(), tableName, ex.Message));
}
}
}
int currentNumberTable = 0;
foreach (string query in result.Querys.ToString().Split('\n'))
{
if (string.IsNullOrWhiteSpace(query)) continue;
int startCut = query.IndexOf("INTO", StringComparison.Ordinal) + 5;
int lengthCut = query.IndexOf("(", StringComparison.Ordinal) - startCut - 1;
string tableName = query.Substring(startCut, lengthCut);
int rowsAffected = 0;
try
{
rowsAffected = manager.ExecuteCommand(query);
results.AppendLine(string.Format("{0} - {1} - Migrated OK - Rows inserted: {2}", GetTime(), tableName, rowsAffected));
}
catch (Exception ex)
{
results.AppendLine(string.Format("{0} - {1} - Migrated Error - Rows inserted: {2} - Error: {3}", GetTime(), tableName, rowsAffected, ex.Message));
}
currentNumberTable++;
int table = currentNumberTable;
progressBarResult.BeginInvoke(new Action(() =>
{
int newValue = (int)(((float)table / result.NumberOfItems) * 100);
if (newValue > 100)
{
newValue = 100;
}
progressBarResult.Value1 = newValue;
}));
}
// Si tenemos que habilitar constraints
if (result.DisableConstraints)
{
foreach (string query in result.QuerysEnableConstraints.ToString().Split('\n'))
{
if (string.IsNullOrWhiteSpace(query)) continue;
int startCut = query.IndexOf("EXISTS", StringComparison.Ordinal) + 7;
int lengthCut = query.IndexOf("ENABLE", StringComparison.Ordinal) - startCut - 1;
string tableName = query.Substring(startCut, lengthCut);
try
{
manager.ExecuteCommand(query);
results.AppendLine(string.Format("{0} - Enabling constraints for table {1} - Enabled", GetTime(), tableName));
}
catch (Exception ex)
{
results.AppendLine(string.Format("{0} - Enabling constraints for table {1} - Cannot be enabled - Error: {2}", GetTime(), tableName, ex.Message));
}
}
}
return results.ToString();
}
public static QueryResult Generate(List<TableModel> orig, List<TableModel> dest, bool disableConstraints)
{
QueryResult tablesResults = new QueryResult {CanBeExecuted = true};
StringBuilder query = new StringBuilder();
StringBuilder result = new StringBuilder();
StringBuilder disableConstraintsQuery = new StringBuilder();
StringBuilder enableConstraintsQuery = new StringBuilder();
if (!orig.Any() || !dest.Any())
{
result.AppendLine(GetTime() + " - Nothing selected");
tablesResults.Querys = query;
tablesResults.Results = result;
tablesResults.CanBeExecuted = false;
return tablesResults;
}
// Obtenemos la configuracion para el origen
SettingsModel modelSettings = SettingsManager.GetXml();
if (modelSettings == null)
{
result.AppendLine(GetTime() + " - Settings not set");
tablesResults.Querys = query;
tablesResults.Results = result;
tablesResults.CanBeExecuted = false;
return tablesResults;
}
// Inicializamos el manager
DatabaseManage manager = new DatabaseManage(modelSettings);
// Recorremos la lista de destino
foreach (TableModel destTable in dest)
{
// Si el nombre de la tabla, no coincide con las de origen, continuamos
if (!orig.Select(s => s.TableName.ToLower()).Contains(destTable.TableName.ToLower()))
{
result.AppendLine(string.Format("{0} - {1} - The table cannot be found in origin selected tables", GetTime(), destTable.TableName));
tablesResults.CanBeExecuted = false;
continue;
}
var tableOrig = orig.First(s => string.Equals(s.TableName, destTable.TableName));
// Comprobamos si los campos de una tabla destino existen en la tabla de origen
// Si uno de ellos no coincide, no continuamos
bool continuar = true;
string columnNotFound = "";
foreach (string column in destTable.Columns.Where(column => !tableOrig.Columns.Contains(column.ToLower())))
{
continuar = false;
columnNotFound = column;
}
if (!continuar)
{
result.AppendLine(string.Format("{0} - {1} - The column {2} cannot be found in the origin selected table", GetTime(), destTable.TableName, columnNotFound));
tablesResults.CanBeExecuted = false;
continue;
}
// Generamos los comandos para des/habilitar las constraints, si asi se requiere
if (disableConstraints)
{
disableConstraintsQuery.AppendLine(string.Format("ALTER TABLE IF EXISTS {0} DISABLE TRIGGER ALL", destTable.TableName));
enableConstraintsQuery.AppendLine(string.Format("ALTER TABLE IF EXISTS {0} ENABLE TRIGGER ALL", destTable.TableName));
}
// Obtenemos todos los tipos de las columnas a seleccionar
IEnumerable<ColumnType> allColumnsAndTypes = manager.GetColumnsAndTypesForTable(destTable.TableName);
// Las recorremos, si estan entre las que estamos buscando, las agregamos
List<string> columnsAndTypes = allColumnsAndTypes.Where(c => destTable.Columns.Contains(c.Column)).OrderBy(o => o.Column).Select(columnType => columnType.ColumnAndType).ToList();
// Generamos la consulta
query.AppendLine(string.Format("INSERT INTO {0} (\"{1}\") SELECT \"{1}\" FROM DBLINK('{2}', 'SELECT \"{1}\" FROM {0}') AS T1 ({3})",
destTable.TableName, string.Join("\", \"", destTable.Columns.OrderBy(o => o)), manager.GetConnectionStringForDbLink(), string.Join(", ", columnsAndTypes)));
result.AppendLine(string.Format("{0} - {1} - Can be migrated", GetTime(), destTable.TableName));
tablesResults.NumberOfItems++;
}
tablesResults.Querys = query;
tablesResults.Results = result;
tablesResults.DisableConstraints = disableConstraints;
tablesResults.QuerysDisableConstraints = disableConstraintsQuery;
tablesResults.QuerysEnableConstraints = enableConstraintsQuery;
return tablesResults;
}
private QueryResult GetSelectedNodes()
{
QueryResult querys = new QueryResult
{
NumberOfItems = 0,
Querys = new StringBuilder(),
Results = new StringBuilder()
};
try
{
foreach (GridViewRowInfo row in gridFields.Rows)
{
var isSelected = (bool)row.Cells[0].Value;
if (isSelected)
{
querys.Querys.Append(row.Cells[3].Value);
querys.NumberOfItems++;
}
}
}
catch (Exception ex)
{
DisplayMessage(ex.Message);
}
return querys;
}
private void btnGenerate_Click(object sender, System.EventArgs e)
{
List<TableModel> tableListOrig = GetSelectedNodes(false);
List<TableModel> tableListDest = GetSelectedNodes(true);
// Send info about disable or not constraints
result = Migrator.Generate(tableListOrig, tableListDest, checkDisableConstraints.IsChecked);
btnExecute.Enabled = result.CanBeExecuted;
btnCopy.Enabled = result.CanBeExecuted;
txtResult.Text = result.Results.ToString();
}