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