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);
}
/// <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);
}
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);
}
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);
}
}
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Предварительно нужно настроить базу и подключение к базе!"));
}
}
/// <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' в ней!"));
}
}
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}"));
}
}
}
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}"));
}
}
}
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);
}
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();
}
}
}
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);
}
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");
}
}
}
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);
}
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()}"));
}
}
}
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);
}
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);
}
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);
}
}
