/// <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); }