/// <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);
}
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 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("Ошибка создания таблиц в БД!"));
}
}
}
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Предварительно нужно настроить базу и подключение к базе!"));
}
}
public static bool Check(string filePath)
{
bool isImported = false;
DbSchema schemaDB = null;
try { schemaDB = DbSchema.LoadDB(filePath); }
catch { return(false); }
bool isHasImportedTables =
schemaDB.Tables.Values.Any(x => x.TableName.Equals("MainData")) &&
schemaDB.Tables.Values.Any(x => x.TableName.Equals("ColumnNameAndAlias"));
if (!isHasImportedTables)
{
return(false);
}
IList <string> nameColumns = new List <string>();
string query = "SELECT ColumnName FROM 'ColumnNameAndAlias';";
string connString = $"Data Source = {filePath}; Version=3;";
try
{
using SqLiteDbWrapper readData = new SqLiteDbWrapper(connString);
using (DataTable dt = readData?.GetQueryResultAsTable(query))
{
if (dt?.Rows.Count > 0)
{
foreach (DataRow r in dt?.Rows)
{
nameColumns.Add(r[0]?.ToString());
}
}
}
}
catch { return(false); }
if (nameColumns?.Count > 0)
{
var colunmsInMainData = schemaDB.Tables.Values.FirstOrDefault(x => x.TableName.Equals("MainData")).Columns;
foreach (var column in colunmsInMainData)
{
nameColumns.Remove(column?.ColumnName);
}
}
if (nameColumns?.Count == 0 && isHasImportedTables)
{
isImported = true;
}
return(isImported);
}
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 DataTable GetTable(string query)
{
DataTable dt = new DataTable();
if (CheckUpDBStructure())
{
using (SqLiteDbWrapper readData = new SqLiteDbWrapper(sqLiteConnectionString, settings.Database))
{
dt = readData.GetQueryResultAsTable(query);
}
}
return(dt);
}
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 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");
}
}
}
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);
}
