/// <summary>
/// Update Progress Bar By 1 Unit (Option to Update Text)
/// </summary>
/// <param name="PBar">RadProgressBar Control</param>
/// <param name="Text">Optional Display Text</param>
public void UpdateProgress(RadProgressBar PBar, string Text = "")
{
if (PBar == null)
{
return;
}
if (PBar.InvokeRequired) // if currently on a different thread, invoke
{
PBar.BeginInvoke((MethodInvoker) delegate()
{
if (Text != "")
{
PBar.Text = Text;
}
try { PBar.Value1++; }
catch { }
});
}
else
{
if (Text != "")
{
PBar.Text = Text;
}
try { PBar.Value1++; }
catch { }
}
}
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 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();
}
/// <summary>
/// Reset Progress Bar Status to Blank
/// </summary>
/// <param name="PBar">RadProgressBar Control</param>
public void ResetProgress(RadProgressBar PBar)
{
if (PBar == null)
{
return;
}
if (PBar.InvokeRequired) // if currently on a different thread, invoke
{
PBar.BeginInvoke((MethodInvoker) delegate()
{
try { PBar.Value1 = 0; }
catch { }
});
}
else
{
try { PBar.Value1 = 1; }
catch { }
}
}
public static void ExecuteSequences(List <string> querys, RadProgressBar progressBarResult)
{
// Obtenemos la configuracion
SettingsModel model = SettingsManager.GetXml(true);
if (model == null)
{
return;
}
// Inicializamos el manager
DatabaseManage manager = new DatabaseManage(model);
int currentNumberSeq = 0;
int numSequences = querys.Count();
foreach (string query in querys)
{
if (string.IsNullOrWhiteSpace(query))
{
continue;
}
manager.ExecuteCommand(query);
currentNumberSeq++;
progressBarResult.BeginInvoke(new Action(() =>
{
int newValue = (int)(((float)currentNumberSeq / numSequences) * 100);
if (newValue > 100)
{
newValue = 100;
}
progressBarResult.Value1 = newValue;
}));
}
}
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 List<FieldResult> GenerateInserts(string pTableName, List<ColumnData> pValidColumns,
DatabaseManage origManager, DatabaseManage destManager, RadProgressBar progressBarResult)
{
List<FieldResult> results = new List<FieldResult>();
List<RowModel> dataOrig;
List<RowModel> dataDest;
// Obtenemos todos los datos de origen de la tabla
if (pValidColumns.Select(s => s.ColumnName).Contains("id"))
{
dataOrig = origManager.ExecuteQueryList(string.Format("SELECT \"{0}\" FROM {1} ORDER BY id ASC",
string.Join("\", \"", pValidColumns.Select(s => s.ColumnName).OrderBy(o => o)), pTableName), pValidColumns);
// Obtenemos todos los datos de destino de la tabla
dataDest = destManager.ExecuteQueryList(string.Format("SELECT \"{0}\" FROM {1} ORDER BY id ASC",
string.Join("\", \"", pValidColumns.Select(s => s.ColumnName).OrderBy(o => o)), pTableName), pValidColumns);
}
else
{
dataOrig = origManager.ExecuteQueryList(string.Format("SELECT \"{0}\" FROM {1}",
string.Join("\", \"", pValidColumns.Select(s => s.ColumnName).OrderBy(o => o)), pTableName), pValidColumns);
// Obtenemos todos los datos de destino de la tabla
dataDest = destManager.ExecuteQueryList(string.Format("SELECT \"{0}\" FROM {1}",
string.Join("\", \"", pValidColumns.Select(s => s.ColumnName).OrderBy(o => o)), pTableName), pValidColumns);
}
int rowCount = dataOrig.Count();
int rowIndex = 0;
foreach (RowModel row in dataOrig)
{
StringBuilder builderQuery = new StringBuilder();
// Generamos la query
builderQuery.Append(string.Format("INSERT INTO {0} (\"{1}\") VALUES (", pTableName,
string.Join("\", \"", pValidColumns.Select(s => s.ColumnName).OrderBy(o => o))));
int fieldCount = row.Fields.Count;
int idx = 1;
foreach (var fieldValue in row.Fields)
{
if ("null".Equals(fieldValue))
{
builderQuery.Append(idx < fieldCount ? "null, " : "null");
}
else
{
builderQuery.Append(idx < fieldCount ? string.Format("'{0}', ", fieldValue) : string.Format("'{0}'", fieldValue));
}
idx++;
}
builderQuery.AppendLine(")");
// Generamos el identificador a devolver
FieldResult rowResult = new FieldResult
{
Id = row.Id,
Name = row.Name,
Query = builderQuery.ToString(),
Migrate = true
};
// Si ya existe un registro con el mismo identificador, automáticamente lo desmarcamos
if (dataDest.Any(w => w.Id == row.Id) && row.Id > 0)
{
rowResult.Migrate = false;
}
results.Add(rowResult);
rowIndex++;
progressBarResult.BeginInvoke(new Action(() =>
{
int newValue = (int)(((float)rowIndex / rowCount) * 100);
if (newValue > 100)
{
newValue = 100;
}
progressBarResult.Value1 = newValue;
}));
}
return results;
}
public static void ExecuteSequences(List<string> querys, RadProgressBar progressBarResult)
{
// Obtenemos la configuracion
SettingsModel model = SettingsManager.GetXml(true);
if (model == null)
{
return;
}
// Inicializamos el manager
DatabaseManage manager = new DatabaseManage(model);
int currentNumberSeq = 0;
int numSequences = querys.Count();
foreach (string query in querys)
{
if (string.IsNullOrWhiteSpace(query)) continue;
manager.ExecuteCommand(query);
currentNumberSeq++;
progressBarResult.BeginInvoke(new Action(() =>
{
int newValue = (int)(((float)currentNumberSeq / numSequences) * 100);
if (newValue > 100)
{
newValue = 100;
}
progressBarResult.Value1 = newValue;
}));
}
}
public static List <FieldResult> GenerateInserts(string pTableName, List <ColumnData> pValidColumns,
DatabaseManage origManager, DatabaseManage destManager, RadProgressBar progressBarResult)
{
List <FieldResult> results = new List <FieldResult>();
List <RowModel> dataOrig;
List <RowModel> dataDest;
// Obtenemos todos los datos de origen de la tabla
if (pValidColumns.Select(s => s.ColumnName).Contains("id"))
{
dataOrig = origManager.ExecuteQueryList(string.Format("SELECT \"{0}\" FROM {1} ORDER BY id ASC",
string.Join("\", \"", pValidColumns.Select(s => s.ColumnName).OrderBy(o => o)), pTableName), pValidColumns);
// Obtenemos todos los datos de destino de la tabla
dataDest = destManager.ExecuteQueryList(string.Format("SELECT \"{0}\" FROM {1} ORDER BY id ASC",
string.Join("\", \"", pValidColumns.Select(s => s.ColumnName).OrderBy(o => o)), pTableName), pValidColumns);
}
else
{
dataOrig = origManager.ExecuteQueryList(string.Format("SELECT \"{0}\" FROM {1}",
string.Join("\", \"", pValidColumns.Select(s => s.ColumnName).OrderBy(o => o)), pTableName), pValidColumns);
// Obtenemos todos los datos de destino de la tabla
dataDest = destManager.ExecuteQueryList(string.Format("SELECT \"{0}\" FROM {1}",
string.Join("\", \"", pValidColumns.Select(s => s.ColumnName).OrderBy(o => o)), pTableName), pValidColumns);
}
int rowCount = dataOrig.Count();
int rowIndex = 0;
foreach (RowModel row in dataOrig)
{
StringBuilder builderQuery = new StringBuilder();
// Generamos la query
builderQuery.Append(string.Format("INSERT INTO {0} (\"{1}\") VALUES (", pTableName,
string.Join("\", \"", pValidColumns.Select(s => s.ColumnName).OrderBy(o => o))));
int fieldCount = row.Fields.Count;
int idx = 1;
foreach (var fieldValue in row.Fields)
{
if ("null".Equals(fieldValue))
{
builderQuery.Append(idx < fieldCount ? "null, " : "null");
}
else
{
builderQuery.Append(idx < fieldCount ? string.Format("'{0}', ", fieldValue) : string.Format("'{0}'", fieldValue));
}
idx++;
}
builderQuery.AppendLine(")");
// Generamos el identificador a devolver
FieldResult rowResult = new FieldResult
{
Id = row.Id,
Name = row.Name,
Query = builderQuery.ToString(),
Migrate = true
};
// Si ya existe un registro con el mismo identificador, automáticamente lo desmarcamos
if (dataDest.Any(w => w.Id == row.Id) && row.Id > 0)
{
rowResult.Migrate = false;
}
results.Add(rowResult);
rowIndex++;
progressBarResult.BeginInvoke(new Action(() =>
{
int newValue = (int)(((float)rowIndex / rowCount) * 100);
if (newValue > 100)
{
newValue = 100;
}
progressBarResult.Value1 = newValue;
}));
}
return(results);
}
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());
}