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