示例#1
0
        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();
        }
        private BindingSource LoadGridTable()
        {
            try
            {
                // Obtenemos la configuracion del destino
                SettingsModel modelDest = SettingsManager.GetXml(true);
                if (modelDest == null)
                    return null;

                // Inicializamos el manager de destino
                DatabaseManage managerDest = new DatabaseManage(modelDest);

                // Obtenemos las secuencias
                IEnumerable<string> sequences = managerDest.GetAllSequences().ToList();

                // Obtenemos las tablas
                List<string> tables = managerDest.GetTables().ToList();

                List<TableSequence> sequenceList = new List<TableSequence>();

                // Comparamos cada secuencia
                foreach (string seq in sequences)
                {
                    int cutIndex = seq.IndexOf("_id_seq", StringComparison.Ordinal);

                    if (cutIndex <= 0)
                    {
                        continue;
                    }

                    string tablename = seq.Substring(0, cutIndex);

                    if (!tables.Contains(tablename))
                    {
                        continue;
                    }

                    sequenceList.Add(new TableSequence
                    {
                        Active = true,
                        Sequence = seq,
                        Table = tablename,
                        MaxId = managerDest.GetMaxIdForTable(tablename)
                    });
                }

                // Asignamos los datos al grid
                var bindingList = new BindingList<TableSequence>(sequenceList);
                var source = new BindingSource(bindingList, null);
                return source;
            }
            catch (Exception ex)
            {
                DisplayMessage(ex.Message);
            }

            return null;
        }
示例#3
0
        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();
        }
示例#4
0
        public static string ToggleContraints(IEnumerable<string> tableList, bool enable = false)
        {
            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);

            foreach (string table in tableList)
            {
                string query = string.Format(enable ?
                    "ALTER TABLE IF EXISTS {0} ENABLE TRIGGER ALL" :
                    "ALTER TABLE IF EXISTS {0} DISABLE TRIGGER ALL", table);

                string mode = enable ? "Enabling" : "Disabling";
                string action = enable ? "Enabled" : "Disabled";

                if (string.IsNullOrWhiteSpace(query)) continue;

                try
                {
                    manager.ExecuteCommand(query);
                    results.AppendLine(string.Format("{0} - {1} constraints for table {2} - {3}", GetTime(), mode, table, action));
                }
                catch (Exception ex)
                {
                    results.AppendLine(string.Format("{0} - {1} constraints for table {2} - Cannot be {3} - Error: {4}", GetTime(), mode, table, action, ex.Message));
                }
            }

            return results.ToString();
        }
示例#5
0
        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;
        }
示例#6
0
        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;
        }
示例#7
0
        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;
                }));
            }
        }
        private BindingSource LoadGridTable(string pTableName)
        {
            try
            {
                // Obtenemos la configuracion del origen
                SettingsModel model = SettingsManager.GetXml();
                if (model == null)
                    return null;

                // Obtenemos la configuracion del destino
                SettingsModel modelDest = SettingsManager.GetXml(true);
                if (modelDest == null)
                    return null;

                // Inicializamos el manager de origen
                DatabaseManage manager = new DatabaseManage(model);

                // Inicializamos el manager de destino
                DatabaseManage managerDest = new DatabaseManage(modelDest);

                List<ColumnData> listColumns = new List<ColumnData>();

                // Obtenemos las columnas de la tabla de origen
                IEnumerable<ColumnData> columns = manager.GetColumnsTypesInfoForTable(pTableName);
                foreach (ColumnData column in columns)
                {
                    // Buscamos la columna en la base de datos de destino,
                    // si no existe, no la guardaremos
                    if (!managerDest.GetColumnWithNameForTable(pTableName, column.ColumnName)) continue;

                    // Agregamos la columna al listado
                    listColumns.Add(column);
                }

                // Enviamos la tabla, y la lista de columnas validas, para que nos genere las querys
                List<FieldResult> listFields = Migrator.GenerateInserts(pTableName, listColumns, manager, managerDest, progressBarResult);

                // Asignamos los datos al grid
                var bindingList = new BindingList<FieldResult>(listFields);
                var source = new BindingSource(bindingList, null);
                return source;
            }
            catch (Exception ex)
            {
                DisplayMessage(ex.Message);
            }

            return null;
        }
        private void LoadComboTables()
        {
            try
            {
                // Obtenemos la configuracion del origen
                SettingsModel model = SettingsManager.GetXml();
                if (model == null)
                    return;

                // Obtenemos la configuracion del destino
                SettingsModel modelDest = SettingsManager.GetXml(true);

                // Inicializamos el manager de origen
                DatabaseManage manager = new DatabaseManage(model);

                // Inicializamos el manager de destino
                DatabaseManage managerDest = new DatabaseManage(modelDest);

                // Obtenemos las tablas
                List<string> tables = manager.GetTables().ToList();

                int tableCount = tables.Count();
                int tableIndex = 0;

                foreach (string table in tables)
                {
                    tableIndex++;

                    progressBarResult.BeginInvoke(new Action(() =>
                    {
                        int newValue = (int) (((float) tableIndex/tableCount)*100);
                        if (newValue > 100)
                        {
                            newValue = 100;
                        }
                        progressBarResult.Value1 = newValue;
                    }));

                    // Buscamos la tabla en la base de datos de destino,
                    // si no existe, no la mostramos
                    if (!managerDest.GetTableWithName(table)) continue;

                    // Buscamos el numero de filas
                    string numRows = manager.GetCurrentRowsForTable(table);

                    // Agregamos la tabla al combo
                    srcTableDropdown.Items.Add(new RadListDataItem
                    {
                        Value = table,
                        Text = string.Format("{0} ({1})", table, numRows)
                    });
                }
            }
            catch (Exception ex)
            {
                DisplayMessage(ex.Message);
            }
        }
示例#10
0
        private void LoadTable(bool isDestination)
        {
            try
            {
                // Obtenemos la configuracion
                SettingsModel model = SettingsManager.GetXml(isDestination);
                if (model == null)
                    return;

                // Limpiamos el arbol
                if (isDestination)
                {
                    treeDest.Nodes.Clear();
                }
                else
                {
                    treeOrig.Nodes.Clear();
                }

                // Inicializamos el manager
                DatabaseManage manager = new DatabaseManage(model);

                // Obtenemos las tablas
                IEnumerable<string> tables = manager.GetTables();
                foreach (string table in tables)
                {
                    // Buscamos el numero de filas
                    string numRows = manager.GetRowsForTable(table);

                    // Agregamos la tabla al arbol
                    RadTreeNode tableNode = isDestination ?
                        treeDest.Nodes.Add(table, string.Format("{0} ({1})", table, numRows), "") :
                        treeOrig.Nodes.Add(table, string.Format("{0} ({1})", table, numRows), "");

                    // Obtenemos las columnas de la tabla
                    IEnumerable<string> columns = manager.GetColumnsForTable(table);
                    foreach (string column in columns)
                    {
                        // Agregamos la columna al nodo de la tabla
                        tableNode.Nodes.Add(column);
                    }
                }
            }
            catch (Exception ex)
            {
                DisplayMessage(ex.Message);
            }
        }