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); }
private void CurrentSQLConnectionStore_EvntConfigChanged(object sender, BoolEventArgs args) { ISQLConnectionSettings oldSettings = currentSQLConnectionStore?.GetPrevious(); ISQLConnectionSettings newSettings = currentSQLConnectionStore?.GetCurrent(); if (!(string.IsNullOrWhiteSpace(newSettings?.Database)) && File.Exists(newSettings?.Database)) { try { dBOperations.EvntInfoMessage -= AddLineAtTextBoxResultShow; } catch { } dBOperations = SQLSelector.SetConnector(newSettings); dBOperations.EvntInfoMessage += new SqlAbstractConnector.Message <TextEventArgs>(AddLineAtTextBoxResultShow); } if (oldSettings == null) { tableStore.Set(SQLSelector.GetTables(newSettings)); } else { if (oldSettings.Name != newSettings.Name && oldSettings.Database != newSettings.Database) { tableStore.Set(SQLSelector.GetTables(newSettings)); } } if (!string.IsNullOrWhiteSpace(newSettings?.Database) && !string.IsNullOrWhiteSpace(newSettings?.Table)) { SetAdminStatusLabelText($"Текущая база: {newSettings?.Database}, таблица: {newSettings?.Table}"); } }
private void BtnSave_Click(object sender, EventArgs e) { ISQLConnectionSettings newSettings = currentSQLConnectionStore?.GetCurrent(); if (!string.IsNullOrWhiteSpace(tbName?.Text) && !string.IsNullOrWhiteSpace(tbHost?.Text)) { newSettings.ProviderName = cmbSQLProvider.SelectedItem.ToString().GetSQLProvider(); newSettings.Host = tbHost?.Text; newSettings.Port = int.TryParse(tbPort?.Text, out int port) ? port : 0; newSettings.Username = tbUserName?.Text; newSettings.Password = tbPassword?.Text; newSettings.Database = cmbDataBases?.Items?.Count > 0 ? cmbDataBases?.SelectedItem?.ToString() : null; newSettings.Table = cmbTables?.Items?.Count > 0 ? cmbTables?.SelectedItem?.ToString() : null; currentSQLConnectionStore.Set(newSettings); ActiveForm.Close(); } else { tbResultShow.AppendLine("Check corectness inputed data:"); tbResultShow.AppendLine("Name: " + tbName?.Text); tbResultShow.AppendLine("Host: " + tbHost?.Text); tbResultShow.AppendLine("DataBase: " + cmbDataBases?.SelectedItem?.ToString()); return; } }
private ISQLConnectionSettings CheckSQLiteDB(ISQLConnectionSettings tmpSettings) { ISQLConnectionSettings newSettings = new SQLConnectionSettings(tmpSettings); using (OpenFileDialog ofd = new OpenFileDialog()) { newSettings.Database = ofd.OpenFileDialogReturnPath(); newSettings.Name = "currentDB"; if (!string.IsNullOrWhiteSpace(newSettings.Database)) { newSettings = CheckSchemaSQLite(newSettings); } else { DialogResult dialog = MessageBox.Show("Create new DB?", "DB is empty!", MessageBoxButtons.YesNo); if (dialog == DialogResult.Yes) { newSettings.Database = "MainDB.db"; SQLiteDBOperations connector = new SQLiteDBOperations(newSettings); connector.TryMakeLocalDB(); newSettings = CheckSchemaSQLite(newSettings); } } } return(newSettings); }
/// <summary> /// will return current SQL Connector was connected to MS SQL or My SQL or SQLite DB /// </summary> /// <param name="settings">ISQLConnectionSettings</param> public static SqlAbstractConnector SetConnector(ISQLConnectionSettings settings) { SqlAbstractConnector sqlConnector = null; switch (settings.ProviderName) { case SQLProvider.SQLite: { sqlConnector = new SQLiteModelDBOperations(settings); break; } case SQLProvider.My_SQL: { sqlConnector = new MySQLUtils(settings); break; } case SQLProvider.MS_SQL: { sqlConnector = new MsSqlUtils(settings); break; } } return(sqlConnector); }
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); }
public static string SetConnectionString(ISQLConnectionSettings settings, int timeout = 3600) { string connString = string.Empty; if (string.IsNullOrWhiteSpace(settings?.Host)) { return(null); } else { connString += "Server=" + settings.Host; } if (!string.IsNullOrWhiteSpace(settings?.Database)) { connString += ";Database=" + settings.Database; } if (settings?.Port > 0) { connString += ";Port=" + settings.Port; } ; connString += ";User Id=" + settings.Username + ";Password="******";Default Command Timeout={timeout};charset=utf8"; return(connString); }
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 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_Tables(ISQLConnectionSettings tmpSettings) { string query = $"SHOW TABLES FROM {tmpSettings.Database}"; 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); }
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); }
public static DataTableStore GetDataTableStore(ISQLConnectionSettings tmpSettings, string query) { SqlAbstractConnector sqlConnector = SetConnector(tmpSettings); sqlConnector.EvntInfoMessage += SqlConnector_EvntInfoMessage; DataTableStore dataTableStore = GetDataTableStore(sqlConnector, query); sqlConnector.EvntInfoMessage -= SqlConnector_EvntInfoMessage; return(dataTableStore); }
public void Set(ISQLConnectionSettings settings) { Name = settings?.Name; ProviderName = settings?.ProviderName; Host = settings?.Host; Port = settings?.Port; Username = settings?.Username; Password = settings?.Password; Database = settings?.Database; Table = settings?.Table; }
private void CheckDB() { SQLProvider selectedProvider = cmbSQLProvider.SelectedItem.ToString().GetSQLProvider(); settings.ProviderName = selectedProvider; bool existDB = false; switch (selectedProvider) { case SQLProvider.My_SQL: { if (!string.IsNullOrWhiteSpace(tbHost?.Text)) { settings = CheckMySQLDB(settings); if (cmbDataBases?.Items?.Count > 0) { existDB = true; } } else { tbResultShow.AppendLine("Check Host name: " + tbHost?.Text); return; } break; } case SQLProvider.SQLite: { settings = CheckSQLiteDB(settings); existDB = !string.IsNullOrWhiteSpace(settings?.Database); break; } default: break; } if (existDB) { if (cmbTables?.Items?.Count > 0) { cmbTables.Enabled = cmbTables?.Items?.Count > 1; } btnSave.Enabled = true; } tbName.Text = SetNameConnection(); }
private static string SetConnectionString(ISQLConnectionSettings settings) { SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder { DataSource = settings.Database, PageSize = 4096, UseUTF16Encoding = true, Version = 3 }; // string sqLiteConnectionString = builder.ConnectionString;// $"Data Source = {settings.Database}; Version=3;"; return(builder.ConnectionString); }
public override void SetConnection(ISQLConnectionSettings settings) { this.settings = settings; EvntInfoMessage?.Invoke(this, new TextEventArgs($"Установлено новое подключение{Environment.NewLine}{settings.Database}")); connString = SetConnectionString(settings); if (SQLiteCheckImportedDB.Check(settings.Database)) { if (!(columnsAndAliases?.Count > 0)) { MakeNewDictionary(); } } }
public static SQLConnectionData ToSQLConnectionData(this ISQLConnectionSettings data) { SQLConnectionData newSettings = new SQLConnectionData(); newSettings.Host = data.Host; newSettings.Database = data.Database; newSettings.Name = data.Name; newSettings.Port = data.Port; newSettings.Table = data.Table; newSettings.Username = data.Username; newSettings.Password = data.Password; newSettings.ProviderName = data.ProviderName; return(newSettings); }
private ISQLConnectionSettings CheckSchemaSQLite(ISQLConnectionSettings tmpSettings) { ISQLConnectionSettings newSettings = new SQLConnectionSettings(tmpSettings); DbSchema schemaDB = null; IList <string> tablesDB; DBModel db = new DBModel(); try { schemaDB = DbSchema.LoadDB(tmpSettings.Database); tablesDB = new List <string>(); foreach (var tbl in schemaDB.Tables) { tablesDB.Add(tbl.Value.TableName); } if (tablesDB?.Count > 0) { newSettings.Table = tablesDB[0]; newSettings.ProviderName = SQLProvider.SQLite; cmbDataBases.DataSource = new List <string>() { tmpSettings.Database }; cmbTables.DataSource = tablesDB; tbResultShow.AppendLine("The inputed data are correct."); } } catch (Exception e) { tbResultShow.AppendLine($"Ошибка в БД: {e.Message}"); tbResultShow.AppendLine($"{e.ToString()}"); } finally { if (schemaDB?.Tables?.Count == 0) { tbResultShow.AppendLine("Подключенная база данных пустая или же в ней отсутствуют какие-либо таблицы с данными!"); tbResultShow.AppendLine("Предварительно создайте базу данных, таблицы и импортируйте/добавьте в них данные..."); tbResultShow.AppendLine("Заблокирован функционал по получению данных из таблиц..."); } } return(newSettings); }
public void Set(ISQLConnectionSettings newConnection) { if (newConnection == null) { return; } if (currentConnection != null) { oldConnection = new SQLConnectionSettings(currentConnection); } currentConnection = new SQLConnectionSettings(newConnection); EvntConfigChanged?.Invoke(this, new BoolEventArgs(true)); }
ISQLConnectionSettings MakeFromControls(ISQLConnectionSettings settings, string dbDefault) { string db = cmbDataBases?.SelectedItem?.ToString(); ISQLConnectionSettings newSettings = new SQLConnectionSettings(settings) { Host = tbHost?.Text, Port = int.TryParse(tbPort?.Text, out int port) ? port : 0, Database = string.IsNullOrWhiteSpace(db) ? dbDefault : db, Username = tbUserName?.Text, Password = tbPassword?.Text, ProviderName = cmbSQLProvider.SelectedItem.ToString().GetSQLProvider() }; return(newSettings); }
private void ComboBoxTable_SelectedIndexChanged(object sender, EventArgs e) { ISQLConnectionSettings newSettings = currentSQLConnectionStore?.GetCurrent(); newSettings.Table = comboBoxTable?.SelectedItem?.ToString()?.Split('(')[0]?.Trim(); lstColumn.Clear(); foreach (var col in SQLSelector.GetColumns(newSettings).ToModelList()) { string name = col.Name.Equals(col.Alias) ? col.Name : $"{col.Name} ({col.Alias})"; lstColumn.Add(name); } columnDeleteStore.Reset(); currentSQLConnectionStore.Set(newSettings); }
public void Add(ISQLConnectionSettings newConfig) { if (newConfig?.Name == null) { return; } lock (lockChanging) { if (config == null) { config = new Dictionary <string, object>(); } config[newConfig.Name] = newConfig.DoObjectPropertiesAsObjectDictionary(); } }
private ISQLConnectionSettings CheckMSSQLDB(ISQLConnectionSettings tmpSettings) { ISQLConnectionSettings newSettings = MakeFromControls(tmpSettings, ""); MsSqlUtils msSQL = new MsSqlUtils(newSettings); try { using (DataTable dt = msSQL.GetTable("SELECT Name FROM sys.databases", 5)) { tbResultShow.AppendLine(msSQL.GetConnection().ToString()); tbResultShow.AppendLine("Строка подключения: " + msSQL.connString); if (dt?.Rows?.Count > 0) { IList <string> list = new List <string>(); foreach (DataRow r in dt.Rows) { list.Add(r[0].ToString()); } if (list?.Count > 0) { cmbDataBases.DataSource = list; cmbDataBases.Enabled = true; newSettings.Database = list[0]; tbResultShow.AppendLine("The inputed data are correct."); } } else { tbResultShow.AppendLine("Указаны некорректны данные или отсутствует подключение к серверу!"); } } } catch (Exception excpt) { newSettings = null; tbResultShow.AppendLine(excpt.Message + ":"); tbResultShow.AppendLine(excpt.ToString()); } return(newSettings); }
private void GetNewConnection() { ISQLConnectionSettings tmpConnection = currentSQLConnectionStore?.GetCurrent(); getNewConnectionForm = new SelectDBForm { Owner = this, Icon = Icon.FromHandle(bmpLogo.GetHicon()), Text = "Форма для подключения новой базы", currentSQLConnectionStore = string.IsNullOrWhiteSpace(tmpConnection?.Name) == true ? new SQLConnectionStore() : new SQLConnectionStore(tmpConnection) }; getNewConnectionForm.FormClosing += GetNewConnection_FormClosing; getNewConnectionForm.Show(); }
private ISQLConnectionSettings CheckMySQLDB(ISQLConnectionSettings tmpSettings) { ISQLConnectionSettings newSettings = new SQLConnectionSettings(tmpSettings); newSettings.Host = tbHost?.Text; newSettings.Port = int.TryParse(tbPort?.Text, out int port) ? port : 0; newSettings.Database = cmbDataBases?.SelectedItem?.ToString() ?? selectedDB; newSettings.Username = tbUserName?.Text; newSettings.Password = tbPassword?.Text; MySQLUtils mySQL = new MySQLUtils(newSettings); try { using (DataTable dt = mySQL.GetTable("SHOW DATABASES")) { IList <string> list = new List <string>(); foreach (DataRow r in dt.Rows) { list.Add(r[0].ToString()); } if (list?.Count > 0) { cmbDataBases.DataSource = list; cmbDataBases.Enabled = true; newSettings.Database = list[0]; tbResultShow.AppendLine("The inputed data are correct."); } } } catch (MySqlException excpt) { tbResultShow.AppendLine("My SQL error - " + excpt.Message + ":"); tbResultShow.AppendLine(excpt.ToString()); } catch (Exception excpt) { tbResultShow.AppendLine(excpt.Message + ":"); tbResultShow.AppendLine(excpt.ToString()); } return(newSettings); }
private ISQLConnectionSettings CheckMySQLDB(ISQLConnectionSettings tmpSettings) { ISQLConnectionSettings newSettings = MakeFromControls(tmpSettings, MYSQL); MySQLUtils mySQL = new MySQLUtils(newSettings); try { using DataTable dt = mySQL.GetTable("SHOW DATABASES", 5); tbResultShow.AppendLine(mySQL.GetConnection().ToString()); tbResultShow.AppendLine("Строка подключения: " + mySQL.connString); IList <string> list = (from DataRow r in dt.Rows select r[0]?.ToString()).ToList(); if (list?.Count > 0) { cmbDataBases.DataSource = list; cmbDataBases.Enabled = true; newSettings.Database = list[0]; tbResultShow.AppendLine("The inputed data are correct."); } else { tbResultShow.AppendLine("Указаны некорректны данные или отсутствует подключение к серверу!"); } } catch (MySqlException error) { newSettings = null; tbResultShow.AppendLine("My SQL error - " + error.Message + ":"); tbResultShow.AppendLine(error.ToString()); } catch (Exception error) { newSettings = null; tbResultShow.AppendLine(error.Message + ":"); tbResultShow.AppendLine(error.ToString()); } return(newSettings); }
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); }