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);
}
//добавить задачу в конце операции
// импорт =>сжатие базы (выбор отдельных дополнительных колонок для удаления)=> добавление региона принадлежности
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();
}
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}");
}
}