private IDictionary <string, string> TryToSetColumnDictionary()
        {
            IDictionary <string, string> newDictionary = new Dictionary <string, string>();

            EvntInfoMessage?.Invoke(this, new TextEventArgs($"БД: {settings.Database}"));

            if (SQLiteCheckImportedDB.Check(settings.Database))
            {
                string query = "SELECT ColumnName, ColumnAlias FROM ColumnNameAndAlias;";
                using SqLiteDbWrapper readData = new SqLiteDbWrapper(connString);
                using (DataTable dt = readData?.GetQueryResultAsTable(query))
                {
                    if (dt?.Rows.Count > 0)
                    {
                        foreach (DataRow r in dt?.Rows)
                        {
                            newDictionary[r["ColumnName"]?.ToString()] = r["ColumnAlias"]?.ToString();
                        }
                    }
                }
                EvntInfoMessage?.Invoke(this, new TextEventArgs($"Сгенерирован новый словарь алиасов: {newDictionary?.Count} слов"));
            }

            return(newDictionary);
        }
        public void WriteModelInTable(IList <IModels> models)
        {
            string query = "INSERT OR REPLACE INTO 'MainData' (";
            IDictionary <int, IModel> firstModels = models[0].list;

            foreach (var k in firstModels)
            {
                query += $"{k.Value.Name}, ";
            }
            query  = query.TrimEnd(' ').TrimEnd(',');
            query += ") VALUES (";

            foreach (var k in firstModels)
            {
                query += $"@{k.Value.Name}, ";
            }
            query  = query.TrimEnd(' ').TrimEnd(',');
            query += ");";

            if (SQLiteCheckImportedDB.Check(settings.Database))
            {
                //   if (CheckUpDBStructure("MainData"))
                //{
                using (SqLiteDbWrapper dbWriter = new SqLiteDbWrapper(connString))
                {
                    dbWriter.Execute("begin");

                    foreach (var row in models)
                    {
                        using (SQLiteCommand sqlCommand = new SQLiteCommand(query, dbWriter.sqlConnection))
                        {
                            foreach (var c in row.list)
                            {
                                sqlCommand.Parameters.Add($"@{c.Value.Name}", DbType.String).Value = c.Value.Alias;
                            }

                            dbWriter.Execute(sqlCommand);
                        }
                    }

                    dbWriter.Execute("end");

                    EvntInfoMessage?.Invoke(this, new TextEventArgs("Подготовленные данные импортированы в БД"));
                }
            }
            else
            {
                EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ошибка записи.{Environment.NewLine}Предварительно нужно проверить базу и таблицу 'MainData' в ней!"));
            }

            FileReaderModels.evntWaitHandle.Set();
        }
        public override void SetConnection(ISQLConnectionSettings settings)
        {
            this.settings = settings;
            EvntInfoMessage?.Invoke(this, new TextEventArgs($"Установлено новое подключение{Environment.NewLine}{settings.Database}"));
            connString = SetConnectionString(settings);

            if (SQLiteCheckImportedDB.Check(settings.Database))
            {
                if (!(columnsAndAliases?.Count > 0))
                {
                    MakeNewDictionary();
                }
            }
        }
        public void PrepareTablesForCommonModel(IModels columnsAndAliases)
        {
            //create table MainData
            string query = "CREATE TABLE 'MainData' (";//'Id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            IDictionary <int, IModel> models = columnsAndAliases.list;

            foreach (var k in models)
            {
                query += $"'{k.Value.Name}' TEXT, ";
            }
            query = query.TrimEnd(' ').TrimEnd(',') + ")";  //remove in the end - , "
            DoQuery(query);

            //Create table ColumnNameAndAlias
            query = "CREATE TABLE 'ColumnNameAndAlias' ('ColumnName' TEXT, 'ColumnAlias' TEXT);"; //'Id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            DoQuery(query);

            //fill table ColumnNameAndAlias
            query = "INSERT INTO 'ColumnNameAndAlias' ('ColumnName', 'ColumnAlias') VALUES (@ColumnName, @ColumnAlias);";

            if (SQLiteCheckImportedDB.Check(settings.Database))
            {
                //   if (CheckUpDBStructure("ColumnNameAndAlias"))
                // {
                using (SqLiteDbWrapper dbWriter = new SqLiteDbWrapper(connString))
                {
                    dbWriter.Execute("begin");

                    foreach (var row in models)
                    {
                        using (SQLiteCommand sqlCommand = new SQLiteCommand(query, dbWriter.sqlConnection))
                        {
                            sqlCommand.Parameters.Add("@ColumnName", DbType.String).Value  = row.Value.Name;
                            sqlCommand.Parameters.Add("@ColumnAlias", DbType.String).Value = row.Value.Alias;

                            dbWriter.Execute(sqlCommand);
                        }
                    }

                    dbWriter.Execute("end");

                    EvntInfoMessage?.Invoke(this, new TextEventArgs("Запись имен колонок и к ним алиасов завершена"));
                }
            }
            else
            {
                EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ошибка записи.{Environment.NewLine}Предварительно нужно проверить базу и таблицу 'ColumnNameAndAlias' в ней!"));
            }
        }
        public ModelCommonStringStore GetColumns(string table)
        {
            ModelCommonStringStore models = new ModelCommonStringStore();
            IModel model;

            if (SQLiteCheckImportedDB.Check(settings.Database) && table.Equals("MainData"))
            {
                string query = "SELECT ColumnName, ColumnAlias FROM 'ColumnNameAndAlias';";
                using (DataTable dt = GetTable(query))
                {
                    if (dt?.Rows.Count > 0)
                    {
                        foreach (DataRow r in dt?.Rows)
                        {
                            model = new ModelCommon
                            {
                                Name  = $"{r.Field<string>("ColumnName")}",
                                Alias = $"{r.Field<string>("ColumnAlias")}"
                            };
                            models.Add(model);
                        }
                    }
                }
            }
            else
            {
                DbSchema schemaDB = DbSchema.LoadDB(settings.Database);
                if (schemaDB?.Tables?.Count > 0)
                {
                    foreach (var _table in schemaDB.Tables)
                    {
                        if (_table.Value.TableName.Equals(table))
                        {
                            foreach (var column in _table.Value.Columns)
                            {
                                model = new ModelCommon
                                {
                                    Name  = column.ColumnName,
                                    Alias = column.ColumnName
                                };
                                models.Add(model);
                            }
                        }
                    }
                }
            }

            return(models);
        }
        public override DataTable GetTable(string query, int timeout = 6000)
        {
            DataTable dt = new DataTable();

            if (CheckUpDBStructure(settings.Table))
            {
                //убрать двойные пробелы из запроса
                Regex regex = new Regex(@"\s+", RegexOptions.IgnoreCase);
                query = regex.Replace(query, @" ");

                string newQuery = query;

                if (SQLiteCheckImportedDB.Check(settings.Database))
                {
                    if (!(columnsAndAliases?.Count > 0))
                    {
                        MakeNewDictionary();
                    }

                    if (query.IndexOf(CommonConst.QUERY_COMMON, StringComparison.OrdinalIgnoreCase) != -1)//.ToUpperInvariant().StartsWith(COMMONQUERY)
                    {
                        newQuery = "SELECT ";

                        using SqLiteDbWrapper readData1 = new SqLiteDbWrapper(connString);
                        using (DataTable dt1 = readData1?.GetQueryResultAsTable(CommonConst.QUERY_ALIAS))
                        {
                            if (dt1?.Rows.Count > 0)
                            {
                                foreach (DataRow r in dt1?.Rows)
                                {
                                    newQuery += $"{r.Field<string>("ColumnName")} as '{r.Field<string>("ColumnName")} ({r.Field<string>("ColumnAlias")})', ";
                                }
                            }
                        }

                        newQuery = $"{newQuery.TrimEnd(' ').TrimEnd(',')} FROM MAINDATA ";
                        newQuery = ReplaceCaseInsensitive1(query, CommonConst.QUERY_COMMON, newQuery);

                        EvntInfoMessage?.Invoke(this, new TextEventArgs($"Произведена замена запроса на newQuery:{Environment.NewLine}'{query}'{Environment.NewLine}на: '{newQuery}'"));
                    }
                    else
                    {
                        newQuery = ReplaceColumnByAlias(columnsAndAliases, query); //replace words by new Dictionary
                    }
                }

                EvntInfoMessage?.Invoke(this, new TextEventArgs(
                                            $"{Environment.NewLine}Запрос к БД" +
                                            $"{Environment.NewLine} =>  '{settings.Database}'" +
                                            $"{Environment.NewLine} =>  '{newQuery}'"));

                using SqLiteDbWrapper readData = new SqLiteDbWrapper(connString);
                dt = readData?.GetQueryResultAsTable(newQuery);
            }
            else
            {
                EvntInfoMessage?.Invoke(this, new TextEventArgs($"Со структурой таблицы '{settings.Table}' базы данных '{settings.Database}' проблема!"));
            }

            return(dt);
        }
示例#7
0
        //добавить задачу в конце операции
        // импорт =>сжатие базы (выбор отдельных дополнительных колонок для удаления)=> добавление региона принадлежности
        private async Task CheckTableForPlates(string pathToSqliteDB)
        {
            MakeTextBoxResultShow();
            txtbResultShow.ReadOnly = true;

            if (SQLiteCheckImportedDB.Check(pathToSqliteDB))
            {
                DbSchema       schemaDB   = DbSchema.LoadDB(pathToSqliteDB);
                IList <string> tablesDB   = new List <string>();
                IList <string> columns    = new List <string>();
                string         queryCheck = null;

                AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
                AddLineAtTextBoxResultShow($"- Selected DB: '{pathToSqliteDB}'");
                string columnsLeave  = string.Empty;
                string columnsDelete = string.Empty;

                int totalRows, rowsWithDataInColumn, rowsWithCorrectDataInColumn;

                //check if it is existed 'ColumnPlateDistrict' in the table of the localDB
                bool   isColumnDistrictPlate        = false; //Column 'ColumnPlateDistrict' is not existed in the table 'MainData'
                bool   isExistedColumnDistrictPlate = false; //row 'ColumnPlateDistrict is not existed in the table 'ColumnNameAndAlias'
                string columnWithPlate = string.Empty;

                bool isImportedDB = SQLiteCheckImportedDB.Check(pathToSqliteDB);

                if (isImportedDB)
                {
                    foreach (var table in schemaDB.Tables)
                    {
                        rowsWithDataInColumn = 0;
                        totalRows            = 0;
                        if (table.Value.TableName.Equals("MainData"))
                        {
                            foreach (var column in table.Value.Columns)
                            {
                                if (column.ColumnName == CommonConst.ColumnPlateDistrict)
                                {
                                    isColumnDistrictPlate = true;
                                }

                                rowsWithCorrectDataInColumn = 0;

                                if (totalRows == 0)
                                {
                                    queryCheck = $"SELECT COUNT(*) FROM '{table.Value.TableName}'";
                                    await Task.Run(() => totalRows = CountRowsInTable(queryCheck));
                                }

                                if (rowsWithDataInColumn == 0)
                                {
                                    queryCheck = $"SELECT COUNT(*) FROM {table.Value.TableName} WHERE LENGTH(TRIM({column.ColumnName})) > 0;";
                                    await Task.Run(() => rowsWithDataInColumn = CountRowsInTable(queryCheck));
                                }

                                queryCheck =
                                    $"SELECT COUNT(*) " +
                                    $"FROM {table.Value.TableName} " +
                                    $"WHERE LENGTH(TRIM({column.ColumnName})) > 6 AND  LENGTH(TRIM({column.ColumnName})) < 9 " +
                                    $"AND ( " +
                                    $"{column.ColumnName} LIKE 'А%' OR " +
                                    $"{column.ColumnName} LIKE 'В%' OR " +
                                    $"{column.ColumnName} LIKE 'С%' OR " +
                                    $"{column.ColumnName} LIKE 'І%' OR " +
                                    $"{column.ColumnName} LIKE 'К%' OR " +
                                    $"{column.ColumnName} LIKE 'Н%' OR " +
                                    $"{column.ColumnName} LIKE 'A%' OR " +
                                    $"{column.ColumnName} LIKE 'B%' OR " +
                                    $"{column.ColumnName} LIKE 'C%' OR " +
                                    $"{column.ColumnName} LIKE 'I%' OR " +
                                    $"{column.ColumnName} LIKE 'K%' OR " +
                                    $"{column.ColumnName} LIKE 'H%' " +
                                    $");";


                                //////////////////
                                //do correct in DB
                                //PRAGMA case_sensitive_like = false;
                                //////////////////


                                await Task.Run(() => rowsWithCorrectDataInColumn = CountRowsInTable(queryCheck));

                                bool   isPlateColumn = false;
                                string results       = string.Empty;

                                if (rowsWithCorrectDataInColumn > 0 && rowsWithDataInColumn > 0)
                                {
                                    isPlateColumn =
                                        Math.Round((decimal)rowsWithDataInColumn / (decimal)rowsWithCorrectDataInColumn, 1) > (decimal)0.5 &&
                                        Math.Round((decimal)rowsWithDataInColumn / (decimal)rowsWithCorrectDataInColumn, 1) < (decimal)1.5;
                                }

                                if (isPlateColumn)
                                {
                                    columns.Add(
                                        $"Column '{column.ColumnName}' has auto plates: {isPlateColumn}, {Environment.NewLine}" +
                                        $"Full total rows: {totalRows}, {Environment.NewLine}" +
                                        $"Column total rows: {column.ColumnName} = {rowsWithDataInColumn}, {Environment.NewLine}" +
                                        $"Count right rows: {column.ColumnName} = {rowsWithCorrectDataInColumn}, " +
                                        $"Rows with correct form plates = {(Math.Round((decimal)rowsWithDataInColumn / (decimal)rowsWithCorrectDataInColumn, 1)) * 100}%");

                                    columnWithPlate = column.ColumnName;
                                }
                            }
                        }
                        else if (table.Value.TableName.Equals("ColumnNameAndAlias"))   //SpecialColumn for districtPalte      //ColumnPlateDistrict
                        {
                            queryCheck = $"SELECT ColumnName FROM '{table.Value.TableName}'";
                            using (DataTable dt = GetTable(queryCheck))
                            {
                                foreach (DataRow r in dt.Rows)
                                {
                                    if (r[0].ToString() == CommonConst.ColumnPlateDistrict)
                                    {
                                        isExistedColumnDistrictPlate = true;
                                        break;
                                    }
                                }
                            }
                        }
                    }

                    AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
                    AddLineAtTextBoxResultShow(columns.ToStringNewLine());
                    AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);

                    //update table  'ColumnNameAndAlias'
                    if (!isExistedColumnDistrictPlate)
                    {
                        queryCheck = $"INSERT INTO 'ColumnNameAndAlias' ('ColumnName', 'ColumnAlias') " +
                                     $"VALUES ('{CommonConst.ColumnPlateDistrict}', 'Принадлежность номера региону');";
                        try { dBOperations.DoQuery(queryCheck); } catch (Exception err) { AddLineAtTextBoxResultShow(err.ToString()); }
                    }

                    //ADD COLUMN in the table 'MainData'
                    if (!isColumnDistrictPlate)
                    {
                        queryCheck = $"ALTER TABLE 'MainData' ADD COLUMN '{CommonConst.ColumnPlateDistrict}' TEXT;";
                        try { dBOperations.DoQuery(queryCheck); } catch (Exception err) { AddLineAtTextBoxResultShow(err.ToString()); }
                    }

                    //update table 'MainData'
                    if (columns.Count == 1)
                    {
                        //Start transaction
                        dBOperations.DoQuery("BEGIN TRANSACTION;", false);
                        foreach (var plate in CommonConst.plateDistrict)
                        {
                            queryCheck =
                                $"UPDATE 'MainData' " +
                                $"SET '{CommonConst.ColumnPlateDistrict}' = '" + plate.Value.ToString() + "' " +
                                $" WHERE  {columnWithPlate} LIKE '{plate.Key}%';";
                            try { dBOperations.DoQuery(queryCheck, false); } catch (Exception err) { AddLineAtTextBoxResultShow(err.ToString()); }
                        }

                        dBOperations.DoQuery("COMMIT;", false);
                        //Finished commit transaction
                    }
                    else
                    {
                        AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
                        AddLineAtTextBoxResultShow("Данная база подготовлена в другой программе и не подлежит оптимизации");
                        AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
                    }
                }
            }
            AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
            AddLineAtTextBoxResultShow("Все задачи по обновлению принадлежности номеров а/м регионам завершены");
            AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);

            ShowMessage("Готово!");

            HideTextBoxResultShow();
        }
示例#8
0
        private async Task DeleteColumnsInDB(ISQLConnectionSettings settings, IList <string> columns = null)
        {
            if (settings.ProviderName == SQLProvider.SQLite)
            {
                DbSchema schemaDB = DbSchema.LoadDB(settings.Database);

                IList <string> tablesDB   = new List <string>();
                DataTable      dtForStore = null;
                string         queryCheck;

                AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
                AddLineAtTextBoxResultShow($"- Selected DB: '{settings.Database}'");
                IList <string> columnsLeave  = new List <string>();
                IList <string> columnsDelete = new List <string>();

                bool isImportedDB = SQLiteCheckImportedDB.Check(settings.Database);
                if (isImportedDB)
                {
                    DbTable db = schemaDB.Tables.Values.First(x => x.TableName.Equals("MainData"));
                    var     cancellationToken = new System.Threading.CancellationTokenSource(10000).Token;
                    if (columns?.Count > 0)
                    {
                        columnsDelete = columns;
                        columnsLeave  = db.Columns.Where(x => columns.Contains(x.ColumnName) == false).Select(x => x.ColumnName).ToList();
                    }
                    else
                    {
                        foreach (var column in db.Columns)
                        {
                            queryCheck = $"SELECT {column.ColumnName} FROM {db.TableName} WHERE LENGTH(TRIM({column.ColumnName})) > 0  LIMIT 10;";
                            AddLineAtTextBoxResultShow(queryCheck);
                            cancellationToken = new System.Threading.CancellationTokenSource(10000).Token;
                            dtForStore        = (dBOperations as SQLiteModelDBOperations).GetTable(queryCheck);

                            if (dtForStore.Rows.Count > 0)
                            {
                                columnsLeave.Add($"{column.ColumnName}");
                            }
                            else
                            {
                                columnsDelete.Add($"{column.ColumnName}");
                            }
                        }
                    }
                    AddLineAtTextBoxResultShow(Properties.Resources.SymbolsSos);
                    AddLineAtTextBoxResultShow(Properties.Resources.SymbolsSos);

                    (dBOperations as SQLiteModelDBOperations).ClearDataTable("MainData", columnsLeave);                          // delete columns in the table 'MainData' except columns - columnsLeave
                    (dBOperations as SQLiteModelDBOperations).ClearDataTable("ColumnNameAndAlias", "ColumnName", columnsDelete); // delete from 'ColumnNameAndAlias' where ColumnName contained columnsDelete
                }
                else
                {
                    DbTable db = schemaDB.Tables.Values.First(x => x.TableName.Equals(settings.Table));

                    if (columns?.Count > 0)
                    {
                        columnsDelete = columns;
                        columnsLeave  = db.Columns.Where(x => columns.Contains(x.ColumnName) == false).Select(x => x.ColumnName).ToList();
                        (dBOperations as SQLiteModelDBOperations).ClearDataTable(settings.Table, columnsLeave); // delete columns in the table 'MainData' except columns - columnsLeave
                    }
                }

                AddLineAtTextBoxResultShow($"-  The End  -:");
                AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
                AddLineAtTextBoxResultShow();
            }
            else
            {
                AddLineAtTextBoxResultShow($"{Properties.Resources.SymbolsSosSlash}{Properties.Resources.SymbolsSosSlash}");
                AddLineAtTextBoxResultShow($"На данный момент настроена очистка только локальных SQLite БД");
                AddLineAtTextBoxResultShow($"{Properties.Resources.SymbolsSosSlash}{Properties.Resources.SymbolsSosSlash}");
            }
        }