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