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