Esempio n. 1
0
        public override DataTable GetTable(string query, int timeout = 3600)
        {
            if (timeout != 3600)
            {
                connString = SetConnectionString(settings, timeout);
            }

            DataTable dt = new DataTable();

            try
            {
                using MySqlConnection connection = new MySqlConnection(connString);
                using (MySqlCommand sqlCom = new MySqlCommand(query, connection))
                {
                    connection.Open();
                    sqlCom.ExecuteNonQuery();
                    using MySqlDataAdapter dataAdapter = new MySqlDataAdapter(sqlCom);
                    dt = new DataTable();
                    dataAdapter.Fill(dt);
                }
                EvntInfoMessage?.Invoke(this, new TextEventArgs($"Получено {dt.Rows.Count} строк(и)"));
            }
            catch (Exception err)
            {
                EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ошибка получения данных {err.Message}"));
            }
            return(dt);
        }
Esempio n. 2
0
        /// <summary>
        /// get only simple query like 'SELECT DISTINCT name_column FROM name_table'
        /// </summary>
        /// <param name="table"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public IModelEntityDB <DBColumnModel> GetFilterList(IDictionary <string, string> columns, string table)
        {
            EvntInfoMessage?.Invoke(this, new TextEventArgs("В таблице: " + table + " " + columns?.Keys?.Count + " колонок "));

            IModelEntityDB <DBColumnModel> _table = new DBTableModel();
            IModelEntityDB <DBFilterModel> result;

            _table.Collection = new List <DBColumnModel>();
            if (CheckUpDBStructure())
            {
                foreach (var column in columns)
                {
                    //SQLiteDBOperations dBOperations
                    using (SqLiteDbWrapper readData = new SqLiteDbWrapper(sqLiteConnectionString, settings.Database))
                    {
                        result = readData.MakeFilterCollection(table, column.Key, column.Value);
                    }

                    EvntInfoMessage?.Invoke(this, new TextEventArgs($"Для фильтра отобрано {result.Collection.Count} строк"));

                    _table.Collection.Add((DBColumnModel)result);
                }
            }
            return(_table);
        }
Esempio n. 3
0
        public void TryMakeLocalDB()
        {
            string strQueryCreateObjectInDb = "CREATE TABLE IF NOT EXISTS 'CarAndOwner' ('Id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Plate TEXT, " +
                                              "Factory TEXT, Model TEXT, ManufactureYear TEXT, BodyNumber TEXT, ChassisNumber TEXT, EngineVolume TEXT, " +
                                              "Type TEXT, DRFO INTEGER, F TEXT, I TEXT, O TEXT, Birthday TEXT, " +
                                              "EDRPOU INTEGER, Name TEXT, City TEXT, District TEXT, Street TEXT, Building TEXT, BuildingBody TEXT, Apartment TEXT, " +
                                              "CodeOperation TEXT, CodeDate TEXT);";

            if (!File.Exists(settings.Database))
            {
                SQLiteConnection.CreateFile(settings.Database);
            }

            using (SqLiteDbWrapper dbWriter = new SqLiteDbWrapper(sqLiteConnectionString, settings.Database))
            {
                dbWriter.Execute("begin");
                dbWriter.Execute(strQueryCreateObjectInDb);
                dbWriter.Execute("end");

                if (CheckUpDBStructure())
                {
                    EvntInfoMessage?.Invoke(this, new TextEventArgs("Таблицы в БД созданы"));
                }
                else
                {
                    EvntInfoMessage?.Invoke(this, new TextEventArgs("Ошибка создания таблиц в БД!"));
                }
            }
        }
        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);
        }
Esempio n. 5
0
        public void Uninstall(string serviceName)
        {
            try
            {
                IntPtr schSCManager = OpenSCManager(null, null, SCM_ACCESS.SC_MANAGER_ALL_ACCESS);
                if (schSCManager != IntPtr.Zero)
                {
                    IntPtr schService = OpenService(schSCManager, serviceName, SERVICE_ACCESS.SERVICE_ALL_ACCESS);
                    if (schService != IntPtr.Zero)
                    {
                        if (DeleteService(schService) == false)
                        {
                            EvntInfoMessage?.Invoke(this, new TextEventArgs($"DeleteService failed {Marshal.GetLastWin32Error()}"));

                            //System.Windows.Forms.MessageBox.Show(
                            //    string.Format("DeleteService failed {0}", Marshal.GetLastWin32Error()));
                        }
                    }
                    CloseServiceHandle(schSCManager);
                    // if you don't close this handle, Services control panel
                    // shows the service as "disabled", and you'll get 1072 errors
                    // trying to reuse this service's name
                    CloseServiceHandle(schService);
                }
            }
            catch (System.Exception ex)
            {
                EvntInfoMessage?.Invoke(this, new TextEventArgs(ex.Message));
                //System.Windows.Forms.MessageBox.Show(ex.Message);
            }
        }
Esempio n. 6
0
        public void WriteListInLocalDB(IList <CarAndOwner> list)
        {
            string query =
                "INSERT OR REPLACE INTO 'CarAndOwner' (Plate, Factory, Model, ManufactureYear, BodyNumber, ChassisNumber, EngineVolume, Type, DRFO, F, I, O, Birthday, EDRPOU, " +
                "Name, City, District, Street, Building, BuildingBody, Apartment, CodeOperation, CodeDate) " +
                "VALUES (@Plate, @Factory, @Model, @ManufactureYear, @BodyNumber, @ChassisNumber, @EngineVolume, @Type, @DRFO, @F, @I, @O, @Birthday, @EDRPOU, " +
                "@Name, @City, @District, @Street, @Building, @BuildingBody, @Apartment, @CodeOperation, @CodeDate)";

            if (CheckUpDBStructure())
            {
                using (SqLiteDbWrapper dbWriter = new SqLiteDbWrapper(sqLiteConnectionString, settings.Database))
                {
                    EvntInfoMessage?.Invoke(this, new TextEventArgs($"Запись список в {list.Count} записей в базу список"));

                    dbWriter.Execute("begin");
                    foreach (var row in list)
                    {
                        using (SQLiteCommand SqlQuery = new SQLiteCommand(query, dbWriter.sqlConnection))
                        {
                            SqlQuery.Parameters.Add("@Plate", DbType.String).Value           = row?.Plate;
                            SqlQuery.Parameters.Add("@Factory", DbType.String).Value         = row?.Factory;
                            SqlQuery.Parameters.Add("@Model", DbType.String).Value           = row?.Model;
                            SqlQuery.Parameters.Add("@ManufactureYear", DbType.String).Value = row?.ManufactureYear;
                            SqlQuery.Parameters.Add("@BodyNumber", DbType.String).Value      = row?.BodyNumber;
                            SqlQuery.Parameters.Add("@ChassisNumber", DbType.String).Value   = row?.ChassisNumber;
                            SqlQuery.Parameters.Add("@EngineVolume", DbType.String).Value    = row?.EngineVolume;
                            SqlQuery.Parameters.Add("@Type", DbType.String).Value            = row?.Type;
                            SqlQuery.Parameters.Add("@DRFO", DbType.Int32).Value             = row?.DRFO;
                            SqlQuery.Parameters.Add("@F", DbType.String).Value             = row?.F;
                            SqlQuery.Parameters.Add("@I", DbType.String).Value             = row?.I;
                            SqlQuery.Parameters.Add("@O", DbType.String).Value             = row?.O;
                            SqlQuery.Parameters.Add("@Birthday", DbType.String).Value      = row?.Birthday;
                            SqlQuery.Parameters.Add("@EDRPOU", DbType.Int32).Value         = row?.EDRPOU;
                            SqlQuery.Parameters.Add("@Name", DbType.String).Value          = row?.Name;
                            SqlQuery.Parameters.Add("@City", DbType.String).Value          = row?.City;
                            SqlQuery.Parameters.Add("@District", DbType.String).Value      = row?.District;
                            SqlQuery.Parameters.Add("@Street", DbType.String).Value        = row?.Street;
                            SqlQuery.Parameters.Add("@Building", DbType.String).Value      = row?.Building;
                            SqlQuery.Parameters.Add("@BuildingBody", DbType.String).Value  = row?.BuildingBody;
                            SqlQuery.Parameters.Add("@Apartment", DbType.String).Value     = row?.Apartment;
                            SqlQuery.Parameters.Add("@CodeOperation", DbType.String).Value = row?.CodeOperation;
                            SqlQuery.Parameters.Add("@CodeDate", DbType.String).Value      = row?.CodeDate;

                            dbWriter.ExecuteBulk(SqlQuery);
                        }
                    }

                    dbWriter.Execute("end");

                    EvntInfoMessage?.Invoke(this, new TextEventArgs("Запись списка завершена"));
                }
            }
            else
            {
                EvntInfoMessage?.Invoke(this, new TextEventArgs("Ошибка записи.\r\nПредварительно нужно настроить базу и подключение к базе!"));
            }
        }
Esempio n. 7
0
        /// <summary>
        /// show Import Text File Button: -y
        /// </summary>
        public IDictionary <string, string> CheckCommandLineArguments(string[] parameters)
        {
            IDictionary <string, string> dic = new Dictionary <string, string>();

            string[] arguments = null;
            //Get args
            //  string args = ToString(Environment.GetCommandLineArgs());

            string argumentsString = string.Empty;

            if (parameters?.Length > 0)
            {
                for (int i = 1; i < parameters?.Length; i++)
                {
                    argumentsString += parameters[i] + " ";
                }

                char delimiter = FindUsedDelimiter(argumentsString);

                arguments = argumentsString.Split(delimiter);
            }

            EvntInfoMessage?.Invoke(this, new TextEventArgs(argumentsString));

            arguments.Any(x => x.StartsWith("a")); //mode is admin

            arguments.Any(x => x.StartsWith("c")); //mode is configuration db

            //if (args?.Length > 1)
            //{
            //    //remove delimiters
            //    string envParameter = args[1]?.Trim()?.TrimStart('-', '/')?.ToLower();
            //    if (envParameter.StartsWith("y"))
            //    {
            //        administratorMenu.Enabled = true;
            //    }
            //    else if (envParameter.StartsWith("config"))
            //    {
            //        appDbPath = envParameter.Trim('\\', '/', ':', ';', '|', ' ').Replace("config", "");
            //    }
            //    else if (envParameter.StartsWith("n"))
            //    {
            //        administratorMenu.Enabled = false;
            //    }
            //}
            //else
            //{
            //    administratorMenu.Enabled = false;
            //}

            //sqLiteConnectionString = $"Data Source = {appDbPath}; Version=3;";

            return(dic);
        }
        /// <summary>
        /// Check SQLite DB structure
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        private bool CheckUpDBStructure(string TableName)
        {
            bool isGood = false;

            DbSchema schemaDB = null;
            string   errors   = string.Empty;

            try
            {
                schemaDB = DbSchema.LoadDB(settings.Database);

                foreach (var table in schemaDB?.Tables)
                {
                    if (TableName != null && table.Value.TableName.Equals(TableName))
                    {
                        isGood = true;
                        break;
                    }

                    if (!(table.Value.Columns?.Count > 0))
                    {
                        EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ошибка в таблице: {table.Value.TableName} - отсутствуют колонки и структура данных в таблице."));
                    }
                }
            }
            catch (Exception err)
            { EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ошибка БД - {err.Message}:{Environment.NewLine}{err.ToString()}")); }
            finally
            {
                if (!(schemaDB?.Tables?.Count > 0))
                {
                    EvntInfoMessage?.Invoke(this, new TextEventArgs(
                                                $"Подключенная база данных пустая или же в ней отсутствуют какие-либо таблицы с данными!{Environment.NewLine}" +
                                                $"Предварительно создайте базу данных, таблицы и импортируйте/добавьте в них данные..."));
                    isGood = false;
                }

                schemaDB = null;
            }

            if (isGood)
            {
                if (string.IsNullOrWhiteSpace(TableName))
                {
                    EvntInfoMessage?.Invoke(this, new TextEventArgs($"В базе данных {settings.Database} со структурой все в порядке"));
                }
                else
                {
                    EvntInfoMessage?.Invoke(this, new TextEventArgs($"В базе данных {settings.Database} со структурой таблицы '{TableName}' все в порядке"));
                }
            }

            return(isGood);
        }
        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' в ней!"));
            }
        }
Esempio n. 12
0
 private void Move(string oldFileName, string newFileName)
 {
     if (File.Exists(oldFileName))
     {
         try
         {
             File.Move(oldFileName, newFileName);
             EvntInfoMessage?.Invoke(this, new TextEventArgs($"File '{oldFileName}' was renamed to '{newFileName}' successfully."));
         }
         catch (Exception excpt)
         {
             EvntInfoMessage?.Invoke(this, new TextEventArgs($"File '{oldFileName}' wasn't renamed  to '{newFileName}':{Environment.NewLine}{excpt.Message}"));
         }
     }
 }
Esempio n. 13
0
 private void Delete(string filePath)
 {
     if (File.Exists(filePath))
     {
         try
         {
             File.Delete(filePath);
             EvntInfoMessage?.Invoke(this, new TextEventArgs($"File '{filePath}' was deleted successfully"));
         }
         catch (Exception excpt)
         {
             EvntInfoMessage?.Invoke(this, new TextEventArgs($"File '{filePath}' wasn't deleted:{Environment.NewLine}{excpt.Message}"));
         }
     }
 }
Esempio n. 14
0
 private void CreateDirectory(string backupDirectory)
 {
     if (!Directory.Exists(backupDirectory))
     {
         try
         {
             Directory.CreateDirectory(backupDirectory);
             EvntInfoMessage?.Invoke(this, new TextEventArgs($"Directory '{backupDirectory}' was created successfully."));
         }
         catch (Exception excpt)
         {
             EvntInfoMessage?.Invoke(this, new TextEventArgs($"Directory '{backupDirectory}' wasn't created:{Environment.NewLine}{excpt.Message}"));
         }
     }
 }
        /// <summary>
        /// clear table from columns which don't store any data.
        /// parameters - table which need to clear, columns which need to leave in the result table
        /// </summary>
        /// <param name="columnsDelete">as string - 'column1, column2, column5'</param>
        /// <param name="aliasTable">table where columns' names matches aliases</param>
        /// <param name="NameColumnNames"></param>
        public void ClearDataTable(string aliasTable, string NameColumnNames, IList <string> columnsDelete)
        {
            string[] columnsDeleteInTable = columnsDelete.ToArray();

            DoQuery("BEGIN TRANSACTION;", false);
            foreach (var column in columnsDeleteInTable)
            {
                if (!(string.IsNullOrWhiteSpace(column)))
                {
                    string query = $"DELETE FROM '{aliasTable}' WHERE {NameColumnNames} LIKE '{column.Trim()}'";
                    EvntInfoMessage?.Invoke(this, new TextEventArgs($"{query}"));

                    DoQuery(query, false);
                }
            }
            DoQuery("COMMIT;", false);
        }
Esempio n. 16
0
        private void RecordEntry(string filePath, System.IO.WatcherChangeTypes evntChanging)
        {
            string path      = Assembly.GetExecutingAssembly().Location;
            string pathToLog = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(path), System.IO.Path.GetFileNameWithoutExtension(path) + ".log");

            lock (obj)
            {
                string message = $"{DateTime.Now.ToString("yyyy.MM.dd|hh:mm:ss")}|{evntChanging}|{filePath}";

                EvntInfoMessage?.Invoke(this, new TextEventArgs(message));

                using (System.IO.StreamWriter writer = new System.IO.StreamWriter(pathToLog, true))
                {
                    writer.WriteLine(message);
                    writer.Flush();
                }
            }
        }
Esempio n. 17
0
        private bool CheckUpDBStructure()
        {
            bool isGood = true;

            DbSchema schemaDB = null;
            string   errors   = string.Empty;

            try
            {
                schemaDB = DbSchema.LoadDB(settings.Database);

                foreach (var table in schemaDB.Tables)
                {
                    if (table.Value.Columns.Count == 0)
                    {
                        EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ошибка в таблице: {table.Value.TableName} - отсутствуют колонки и структура данных в таблице."));
                    }
                }
            }
            catch (Exception e)
            {
                EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ошибка в БД: {e.Message}:\r\n{e.ToString()}"));
                isGood = false;
            }
            finally
            {
                if (schemaDB?.Tables?.Count == 0)
                {
                    EvntInfoMessage?.Invoke(this, new TextEventArgs("Подключенная база данных пустая или же в ней отсутствуют какие-либо таблицы с данными!"));
                    EvntInfoMessage?.Invoke(this, new TextEventArgs("Предварительно создайте базу данных, таблицы и импортируйте/добавьте в них данные..."));
                    isGood = false;
                }
                schemaDB = null;
            }

            if (isGood)
            {
                EvntInfoMessage?.Invoke(this, new TextEventArgs($"В базе данных {settings.Database} со структурой все в порядке"));
            }

            return(isGood);
        }
        string ReplaceColumnByAlias(IDictionary <string, string> dic, string query)
        {
            EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ищу в словаре из {dic?.Count} слов замену для слов в запросе:" +
                                                            $"{Environment.NewLine}'{query}"));
            if (!CheckQueryToReplaceWords(query))
            {
                return(query);
            }

            if (!(dic?.Count > 0))
            {
                EvntInfoMessage?.Invoke(this, new TextEventArgs($"Словарь замен пустой"));
                return(query);
            }

            query = query.Replace($",", $" , ");
            query = query.Replace($"(", $" ( ");
            query = query.Replace($")", $" ) ");

            Regex regex = new Regex(@"\s+", RegexOptions.IgnoreCase);

            query = regex.Replace(query, @" ");

            query = query.Replace($" , ", $", ");
            query = query.Replace($"( ", $"(");
            query = query.Replace($" )", $")");

            foreach (var k in dic)
            {
                EvntInfoMessage?.Invoke(this, new TextEventArgs($"{k.Key} - {query.IndexOf(k.Key)}"));


                query = ReplaceCaseInsensitive(query, $" {k.Key} from", $" {k.Key} as '{k.Key} ({k.Value})' from");
                query = ReplaceCaseInsensitive(query, $" {k.Key},", $" {k.Key} as '{k.Key} ({k.Value})',");
                query = ReplaceCaseInsensitive(query, $" {k.Key})", $" {k.Key} as '{k.Key} ({k.Value})')");
                query = ReplaceCaseInsensitive(query, $"({k.Key}, ", $"({k.Key} as '{k.Key} ({k.Value})', ");
                query = ReplaceCaseInsensitive(query, $"({k.Key}) ", $"({k.Key}) as ('{k.Key} ({k.Value})') ");
            }

            return(query);
        }
Esempio n. 19
0
        public MenuAbstractStore GetTables()
        {
            string            query  = $"SHOW TABLES FROM {settings.Database}";
            MenuAbstractStore models = new MenuItemStore();

            using (DataTable dt = GetTable(query))
            {
                foreach (DataRow r in dt?.Rows)
                {
                    ToolStripMenuItem model = new ToolStripMenuItem
                    {
                        Text = r[0].ToString(),
                        Tag  = r[0].ToString()
                    };
                    models.Add(model);
                }
            }
            EvntInfoMessage?.Invoke(this, new TextEventArgs($"Создана модель содержащая {models?.GetAllItems()?.Count} элемента(ов)"));

            return(models);
        }
        public override void DoQuery(string query, bool isCommit = true)
        {
            if (!File.Exists(settings.Database))
            {
                SQLiteConnection.CreateFile(settings.Database);
            }
            EvntInfoMessage?.Invoke(this, new TextEventArgs($"БД: {settings.Database}{Environment.NewLine}" +
                                                            $"sqLiteConnectionString: {connString}{Environment.NewLine}Выполняю запрос: {query}"));

            using (SqLiteDbWrapper dbWriter = new SqLiteDbWrapper(connString))
            {
                if (isCommit)
                {
                    dbWriter.Execute("begin");
                }
                dbWriter.Execute(query);
                if (isCommit)
                {
                    dbWriter.Execute("end");
                }
            }
        }
Esempio n. 21
0
        private ConfigFull <ConfigAbstract> ReadSerializedConfig(string filePath)
        {
            ConfigFull <ConfigAbstract> config = null;

            if (!(File.Exists(filePath)))
            {
                return(config);
            }

            FileInfo fi = new FileInfo(filePath);

            if (fi.Length == 0)
            {
                return(config);
            }


            BinaryFormatter formatter = new BinaryFormatter();
            string          message   = string.Empty;

            try
            {
                message += $"Try to read '{filePath}':{Environment.NewLine}";
                using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate))
                {
                    config   = (ConfigFull <ConfigAbstract>)formatter.Deserialize(fs);
                    message += "Success!";
                }
            }
            catch (Exception excpt)
            {
                message += $"{excpt.Message}:{Environment.NewLine}{excpt.ToString()}";
            }

            EvntInfoMessage?.Invoke(this, new TextEventArgs(message));
            EvntCollectionFull?.Invoke(this, new BoolEventArgs(true));//collection is full

            return(config);
        }
Esempio n. 22
0
        public void Write(string filePath, ConfigFull <ConfigAbstract> config)
        {
            string newFileName = Path.Combine(CommonConst.LocalBackupFolder, $"{DateTime.Now.ToString("yyyy-MM-dd HHmmss")} {Path.GetFileName(filePath)}.bak");

            CreateDirectory(CommonConst.LocalBackupFolder);

            Move(filePath, newFileName);

            EvntInfoMessage?.Invoke(this, new TextEventArgs($"Try to write '{nameof(ConfigFull<ConfigAbstract>)}' in file '{filePath}'"));
            using (FileStream fileStream = new FileStream(filePath, FileMode.OpenOrCreate))
            {
                try
                {
                    BinaryFormatter formatter = new BinaryFormatter();
                    formatter.Serialize(fileStream, config);

                    EvntInfoMessage?.Invoke(this, new TextEventArgs($"{nameof(ConfigFull<ConfigAbstract>)} was written."));
                }
                catch (Exception err)
                {
                    EvntInfoMessage?.Invoke(this, new TextEventArgs($"Writing error:{Environment.NewLine}{err.ToString()}"));
                }
            }
        }
Esempio n. 23
0
        private void ImportExcelFile(string filePath, int maxElementsInDictionary)
        {
            string  nameColumns = null;
            string  currentRow;
            IModels models;

            columnNames      = new ModelCommonStore();
            listCommonModels = new List <IModels>(maxElementsInDictionary);
            var fi = new FileInfo(filePath);

            using (var package = new ExcelPackage(fi))
            {
                var workbook  = package.Workbook;
                var worksheet = workbook.Worksheets.First();

                //get the first worksheet in the workbook
                int colCount = worksheet.Dimension.End.Column;  //get Column Count
                int rowCount = worksheet.Dimension.End.Row;     //get row count

                EvntInfoMessage?.Invoke(this, new TextEventArgs($"File contains: {colCount} columns{Environment.NewLine}and {rowCount} rows"));
                for (int row = 1; row <= rowCount; row++)
                {
                    currentRow = string.Empty;
                    for (int col = 1; col <= colCount; col++)
                    {
                        currentRow += $"{worksheet.Cells[row, col]?.Value?.ToString()?.Trim()}|";
                    }

                    currentRow = currentRow?.TrimEnd('|');

                    if (!currentRow.Contains("|"))
                    {
                        continue;
                    }
                    if (nameColumns == null)
                    {
                        importedRows = 0;

                        parsedModel = new ParserRowModelCommon(currentRow);
                        columnNames = parsedModel.MatchColumnToAlias();
                        nameColumns = parsedModel.ImportedColumnName;

                        EvntHeaderReady?.Invoke(this, new BoolEventArgs(true));//cHeader is ready
                    } //first found not_empty_line will be contained name columns
                    else
                    {
                        parsedModel = new ParserRowModelCommon(currentRow, nameColumns);

                        models = parsedModel?.ConvertRowToModels();

                        if (!(models?.list?.Count > 0))
                        {
                            continue;
                        }
                        importedRows++;
                        listCommonModels.Add(models);

                        if (importedRows <= 0 || importedRows % maxElementsInDictionary != 0)
                        {
                            continue;
                        }
                        EvntCollectionFull?.Invoke(this, new BoolEventArgs(true));//collection is full
                        EvntInfoMessage?.Invoke(this, new TextEventArgs($"lastRow: {currentRow}" +
                                                                        $"{Environment.NewLine}parsed: {models.ToString()}" +
                                                                        $"{Environment.NewLine}Ожидаю пока данные запишутся (до 5 сек.)..."));

                        FileReaderModels.evntWaitHandle.WaitOne(5000);
                        listCommonModels = new List <IModels>(maxElementsInDictionary);
                    }
                }
            }

            if (!(listCommonModels?.Count > 0))
            {
                return;
            }
            EvntCollectionFull?.Invoke(this, new BoolEventArgs(true));//last part of the collection
            EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ожидаю пока запишется последняя часть данных (до 5 сек.)..."));
            FileReaderModels.evntWaitHandle.WaitOne(5000);
        }
Esempio n. 24
0
 public override void SetConnection(ISQLConnectionSettings settings)
 {
     this.settings = settings;
     EvntInfoMessage?.Invoke(this, new TextEventArgs($"Установлено новое подключение{Environment.NewLine}{settings.Database}"));
     connString = SetConnectionString(settings);
 }
        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);
        }
Esempio n. 26
0
        async Task ImportTextFile(string filePath, Encoding encoding, int maxElementsInDictionary)
        {
            const int         DefaultBufferSize = 4096;
            const FileOptions DefaultOptions    = FileOptions.Asynchronous | FileOptions.SequentialScan;
            string            nameColumns       = null;
            string            currentRow;
            IModels           models;

            columnNames      = new ModelCommonStore();
            listCommonModels = new List <IModels>(maxElementsInDictionary);

            using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read, DefaultBufferSize, DefaultOptions))
            {
                using (var reader = new StreamReader(stream, encoding))
                {
                    while ((currentRow = await reader.ReadLineAsync())?.Trim()?.Length > 10)
                    {
                        if (nameColumns == null)
                        {
                            importedRows = 0;

                            parsedModel = new ParserRowModelCommon(currentRow);
                            columnNames = parsedModel.MatchColumnToAlias();
                            nameColumns = parsedModel.ImportedColumnName;

                            EvntHeaderReady?.Invoke(this, new BoolEventArgs(true));//cHeader is ready
                        } //first found not_empty_line containes name columns
                        else
                        {
                            parsedModel = new ParserRowModelCommon(currentRow, nameColumns);

                            models = parsedModel?.ConvertRowToModels();

                            if (models?.list?.Count > 0)
                            {
                                importedRows++;
                                listCommonModels.Add(models);

                                if (importedRows > 0 && importedRows % maxElementsInDictionary == 0)
                                {
                                    EvntCollectionFull?.Invoke(this, new BoolEventArgs(true));//collection is full
                                    EvntInfoMessage?.Invoke(this, new TextEventArgs($"lastRow: {currentRow}" +
                                                                                    $"{Environment.NewLine}parsed: {models.ToString()}" +
                                                                                    $"{Environment.NewLine}Ожидаю пока запишутся данные(до 5 сек.)..."));

                                    FileReaderModels.evntWaitHandle.WaitOne(5000);

                                    listCommonModels = new List <IModels>(maxElementsInDictionary);
                                }
                            }
                        }
                    }
                }
            }

            if (listCommonModels?.Count > 0)
            {
                EvntCollectionFull?.Invoke(this, new BoolEventArgs(true));//last part of the collection
                EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ожидаю пока запишется последняя часть данных(до 2 сек.)..."));
                FileReaderModels.evntWaitHandle.WaitOne(2000);
            }
        }