private static T GetMappingSettingValue <T>(string dbSettingName)
{
using (var cInstance = new OleDbConnectionInstance(ConnectionString))
{
var command =
new OleDbCommand(
$"SELECT SettingValue FROM `{GetTableName(SettingsTable.MigrationTableSettings)}` WHERE SettingName = '{dbSettingName}'",
cInstance.GetConnection());
try
{
command.Connection.Open();
var value = command.ExecuteScalar();
if (value == null)
{
return(default(T));
}
else
{
return((T)Convert.ChangeType(value, typeof(T)));
}
}
catch (Exception e)
{
Logger.Instance.Error($"An error occured while updating MappingTable {dbSettingName}. {e.Message}");
}
finally
{
command.Connection.Close();
}
return(default(T));
}
}
private static void SaveMappingSettingValue(string dbSettingName, object value)
{
using (var cInstance = new OleDbConnectionInstance(ConnectionString))
{
var command =
new OleDbCommand(
$"SELECT COUNT(*) FROM `{GetTableName(SettingsTable.MigrationTableSettings)}` WHERE SettingName = '{dbSettingName}'",
cInstance.GetConnection());
try
{
command.Connection.Open();
var rowCount = command.ExecuteScalar();
var rowExists = rowCount != null && Convert.ToInt32(rowCount) > 0;
command.CommandText = rowExists
? $"UPDATE `{GetTableName(SettingsTable.MigrationTableSettings)}` SET SettingValue = '{value}' WHERE SettingName = '{dbSettingName}'"
: $"INSERT INTO `{GetTableName(SettingsTable.MigrationTableSettings)}` (SettingName, SettingValue) VALUES ('{dbSettingName}', '{value}')";
command.ExecuteNonQuery();
}
catch (Exception e)
{
Logger.Instance.Error($"An error occured while updating MappingTable {dbSettingName}. {e.Message}");
}
finally
{
command.Connection.Close();
}
}
}
private static void SaveSettingValue(string dbSettingName, string appSettingName, object value, SettingsTable settingsTable = SettingsTable.Settings)
{
if (string.IsNullOrEmpty(appSettingName))
{
appSettingName = dbSettingName;
}
if (settingsTable == SettingsTable.MigrationTableSettings)
{
SaveMappingSettingValue(dbSettingName, value);
return;
}
using (var cInstance = new OleDbConnectionInstance(ConnectionString))
{
var command = new OleDbCommand($"UPDATE `{GetTableName(settingsTable)}` SET `{dbSettingName}` = @value WHERE `ID` = 1",
cInstance.GetConnection());
command.Parameters.AddWithValue("@value", value);
command.Connection.Open();
try
{
command.ExecuteNonQuery();
var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
config.AppSettings.Settings[appSettingName].Value = value.ToString();
config.Save(ConfigurationSaveMode.Modified);
Logger.Instance.Debug($"{dbSettingName} saved: '{value}'");
}
catch (Exception e)
{
Logger.Instance.Error($"An error occured while updating {dbSettingName}. {e.Message}");
}
finally
{
command.Connection.Close();
cInstance.CloseConnection();
}
}
}
public static bool TryConnect()
{
System.IO.Directory.SetCurrentDirectory(System.AppDomain.CurrentDomain.BaseDirectory);
//var _connection = ConnectionInstance.Instance.GetConnection($"DSN={DsnName}");
using (var cInstance = new OleDbConnectionInstance(ConnectionString))
{
var connection = cInstance.GetConnection();
try
{
connection.Open();
}
catch (OleDbException e)
{
Logger.Instance.Error($"Could not connect to the DSN {ConnectionString}: {e.Message}");
return(false);
}
finally
{
cInstance.CloseConnection();
}
}
return(true);
}
internal static T GetSettingValue <T>(string settingName, SettingsTable settingTableName = SettingsTable.Settings)
{
if (settingTableName == SettingsTable.MigrationTableSettings)
{
return(GetMappingSettingValue <T>(settingName));
}
using (var cInstance = new OleDbConnectionInstance(ConnectionString))
{
var command = new OleDbCommand($"SELECT `{settingName}` FROM `{GetTableName(settingTableName)}` WHERE `Id` = 1", cInstance.GetConnection());
command.Connection.Open();
try
{
var key = command.ExecuteScalar();
if (key == null || key == DBNull.Value)
{
return(default(T));
}
var conv = TypeDescriptor.GetConverter(typeof(T));
return((T)conv.ConvertFrom(key));
}
catch (Exception e)
{
Logger.Instance.Error($"An error occurred while retrieving the {settingName}: {e.Message}");
}
finally
{
command.Connection.Close();
cInstance.CloseConnection();
}
}
return(default(T));
}
