public static ModelCommonStringStore GetDataSample(ISQLConnectionSettings tmpSettings, string column)
{
ModelCommonStringStore models = null;
string query = $"SELECT DISTINCT {column} FROM {tmpSettings.Table} WHERE LENGTH(TRIM({column})) > 0 LIMIT 20;"; //GROUP BY {column}
switch (tmpSettings.ProviderName)
{
case SQLProvider.SQLite:
{
models = GetListForModelStore(new SQLiteModelDBOperations(tmpSettings), query);
break;
}
case SQLProvider.My_SQL:
{
models = GetListForModelStore(new MySQLUtils(tmpSettings), query);
break;
}
case SQLProvider.MS_SQL:
{
query = $"SELECT TOP 20 {column} FROM {tmpSettings.Table} WHERE LEN({column}) > 0 GROUP BY {column};";
models = GetListForModelStore(new MsSqlUtils(tmpSettings), query);
break;
}
default:
break;
}
return(models);
}
public static ModelCommonStringStore GetColumns(ISQLConnectionSettings tmpSettings)
{
ModelCommonStringStore models = null;
switch (tmpSettings.ProviderName)
{
case SQLProvider.SQLite:
{
models = SQLite_Columns(tmpSettings);
break;
}
case SQLProvider.My_SQL:
{
models = My_SQL_Columns(tmpSettings);
break;
}
case SQLProvider.MS_SQL:
{
models = MS_SQL_Columns(tmpSettings);
break;
}
default:
break;
}
return(models);
}
static ModelCommonStringStore SQLite_Tables(ISQLConnectionSettings tmpSettings)
{
string query = $"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY 1";
ModelCommonStringStore models = GetListForModelStore(new SQLiteModelDBOperations(tmpSettings), query);
return(models);
}
static ModelCommonStringStore My_SQL_Tables(ISQLConnectionSettings tmpSettings)
{
string query = $"SHOW TABLES FROM {tmpSettings.Database}";
ModelCommonStringStore models = GetListForModelStore(new MySQLUtils(tmpSettings), query);
return(models);
}
static ModelCommonStringStore MS_SQL_Tables(ISQLConnectionSettings tmpSettings)
{
string query = $"SELECT * FROM sys.objects WHERE type in (N'U')";
ModelCommonStringStore models = GetListForModelStore(new MsSqlUtils(tmpSettings), query);
return(models);
}
static ModelCommonStringStore SQLite_Columns(ISQLConnectionSettings tmpSettings)
{
SqlAbstractConnector connector = new SQLiteModelDBOperations(tmpSettings);
ModelCommonStringStore models = (connector as SQLiteModelDBOperations).GetColumns(tmpSettings.Table);
return(models);
}
static ModelCommonStringStore My_SQL_Columns(ISQLConnectionSettings tmpSettings)
{
string query = $"SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` " +
$"WHERE `TABLE_SCHEMA`= '{tmpSettings.Database}' AND `TABLE_NAME`= '{tmpSettings.Table}';";
ModelCommonStringStore models = GetListForModelStore(new MySQLUtils(tmpSettings), query);
return(models);
}
static ModelCommonStringStore MS_SQL_Columns(ISQLConnectionSettings tmpSettings)
{
string query = $"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS " +
$"WHERE TABLE_NAME= '{tmpSettings.Table}';";
ModelCommonStringStore models = GetListForModelStore(new MsSqlUtils(tmpSettings), query);
return(models);
}
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 void Set(ModelCommonStringStore newStore)
{
lock (locker)
{
if (newStore?.ToModelList()?.Count > 0)
{
ItemDictionary = new Dictionary <string, IModel>();
foreach (var m in newStore.ToModelList())
{
ItemDictionary[m.Name] = m;
}
EvntCollectionChanged?.Invoke(this, new BoolEventArgs(true));
}
}
}
private async Task GetSample(string columnName)
{
ISQLConnectionSettings newSettings = currentSQLConnectionStore?.GetCurrent();
ModelCommonStringStore models = null;
await Task.Run(() => models = SQLSelector.GetDataSample(newSettings, columnName));
if (models?.ToList()?.Count > 0)
{
sampleStore.Set(models);
}
else
{
sampleStore.Reset();
sampleStore.Add(new ModelCommon()
{
Name = $"В таблице данные отсутствует в столбце '{columnName}'"
});
}
}
private void CurrentSQLConnectionStore_EvntConfigChanged(object sender, BoolEventArgs args)
{
ISQLConnectionSettings oldSettings = currentSQLConnectionStore?.GetPrevious();
ISQLConnectionSettings newSettings = currentSQLConnectionStore?.GetCurrent();
if (oldSettings != null)
{
if (oldSettings.Name == newSettings.Name)
{
return;
}
if (oldSettings.Database == newSettings.Database)
{
return;
}
}
ModelCommonStringStore tables = SQLSelector.GetTables(currentSQLConnectionStore?.GetCurrent());
tableStore.Set(tables);
}
static ModelCommonStringStore GetListForModelStore(SqlAbstractConnector sqlConnector, string query)
{
ModelCommonStringStore models = new ModelCommonStringStore();
IModel model;
try
{
using DataTable dt = sqlConnector.GetTable(query);
foreach (DataRow r in dt?.Rows)
{
model = new ModelCommon
{
Name = r[0].ToString(),
Alias = r[0].ToString()
};
models.Add(model);
}
dt?.Dispose();
}
catch { }
return(models);
}
