public bool CreateTask(string taskName, string displayName, DateTime startDateTime, int repeatInterval, string externalExecutable, string jobParameters)
{
// var _connection = ConnectionInstance.Instance.GetConnection($"DSN={Settings.DsnName}");
var _connection = new OleDbConnectionInstance(Settings.ConnectionString).GetConnection();
var command = new OleDbCommand($"INSERT INTO Tasks (TaskName, TaskDisplayName, StartDateTime, MinuteRepeatInterval, externalExecutable, JobParameters) VALUES ('{taskName}', '{displayName}', '{startDateTime.ToString()}', {repeatInterval}, '{externalExecutable.Replace("'", "''")}', '{jobParameters.Replace("'", "''")}')")
{
Connection = _connection
};
_connection.Open();
try
{
command.ExecuteNonQuery();
Logger.Instance.Debug($"Task saved: '{taskName}'");
return(true);
}
catch (Exception)
{
Logger.Instance.Error($"An error occured while creating the task {taskName}.");
}
finally
{
_connection.Close();
}
return(false);
}
public bool StartTask(string taskName)
{
var dateNow = DateTime.Now;
var now = dateNow.ToString();
var _connection = new OleDbConnectionInstance(Settings.ConnectionString).GetConnection();
var command = new OleDbCommand($"UPDATE Tasks SET ExecutionStartDateTime = '{now}' WHERE TaskName = '{taskName}'")
{
Connection = _connection
};
_connection.Open();
try
{
_executionStartDateTime = dateNow;
command.ExecuteNonQuery();
return(true);
}
catch (Exception)
{
Logger.Instance.Error($"An error occured while updating the start datetime of task {taskName}.");
}
finally
{
_connection.Close();
}
return(false);
}
public bool DeleteTask(string taskName)
{
var _connection = new OleDbConnectionInstance(Settings.ConnectionString).GetConnection();
var command = new OleDbCommand($"DELETE FROM Tasks WHERE TaskName = '{taskName}'")
{
Connection = _connection
};
_connection.Open();
try
{
command.ExecuteNonQuery();
Logger.Instance.Debug($"Task deleted: '{taskName}'");
return(true);
}
catch (Exception)
{
Logger.Instance.Error($"An error occured while deleting the task {taskName}.");
}
finally
{
_connection.Close();
}
return(false);
}
public bool TaskExists(string task = null)
{
if (!string.IsNullOrEmpty(task) || !string.IsNullOrEmpty(Task))
{
if (string.IsNullOrEmpty(task))
{
task = Task;
}
var _connection = new OleDbConnectionInstance(Settings.ConnectionString).GetConnection();
var command = new OleDbCommand($"SELECT * FROM `Tasks` WHERE TaskName = '{task}'", _connection);
_connection.Open();
var reader = command.ExecuteReader();
try
{
while (reader.Read())
{
return(true);
}
return(false);
}
finally
{
reader.Close();
_connection.Close();
}
}
return(false);
}
public bool SetStatus(string taskName, string status)
{
var now = DateTime.Now.ToString();
var _connection = new OleDbConnectionInstance(Settings.ConnectionString).GetConnection();
var command = new OleDbCommand($"UPDATE Tasks SET Status = '{status}' WHERE TaskName = '{taskName}'")
{
Connection = _connection
};
_connection.Open();
try
{
command.ExecuteNonQuery();
Logger.Instance.Debug($"Task status updated: '{taskName}' {status}");
return(true);
}
catch (Exception)
{
Logger.Instance.Error($"An error occured while updating the status of task {taskName}.");
}
finally
{
_connection.Close();
}
return(false);
}
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));
}
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();
}
}
}
public static Credentials GetDsnCreds(string dsn)
{
// var _connection = ConnectionInstance.Instance.GetConnection($"DSN={DsnName}");
var _connection = new OleDbConnectionInstance(ConnectionString).GetConnection();
var command = new OleDbCommand($"SELECT Username, Password FROM DsnCredentials WHERE DsnName = '{dsn}'", _connection);
_connection.Open();
var reader = command.ExecuteReader();
try
{
string username = null;
string password = null;
while (reader.Read())
{
username = reader[0].ToString();
password = reader[1].ToString();
}
var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
string encryptionKey = config.AppSettings.Settings["EncryptionKey"].Value;
if (!string.IsNullOrEmpty(username) && !string.IsNullOrEmpty(password))
{
return(new Credentials()
{
Username = username,
Password = Encryption.Decrypt(password, encryptionKey)
});
}
}
catch (Exception)
{
Logger.Instance.Error($"An error occured while retrieving the Credentials for DSN {dsn}");
MessageBox.Show($@"An error occured while retrieving the Credentials for DSN {dsn}", @"Emptied Successfully");
}
finally
{
reader.Close();
_connection.Close();
}
return(null);
}
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 bool EndTask(string taskName)
{
var now = DateTime.Now.ToString();
var _connection = new OleDbConnectionInstance(Settings.ConnectionString).GetConnection();
var command = new OleDbCommand()
{
Connection = _connection
};
if (_executionStartDateTime != null)
{
TimeSpan executionDuration = DateTime.Now - (_executionStartDateTime ?? DateTime.Now);
double executionDurationSeconds = executionDuration.TotalSeconds;
command.CommandText = $"UPDATE Tasks SET ExecutionEndDateTime = '{now}', ExecutionDuration = {executionDurationSeconds} WHERE TaskName = '{taskName}'";
}
else
{
command.CommandText = $"UPDATE Tasks SET ExecutionEndDateTime = '{now}' WHERE TaskName = '{taskName}'";
}
_connection.Open();
try
{
command.ExecuteNonQuery();
return(true);
}
catch (Exception)
{
Logger.Instance.Error($"An error occured while updating the end datetime of task {taskName}.");
}
finally
{
_connection.Close();
}
return(false);
}
public static void SaveDsnCreds(string dsn, string user, string pass)
{
var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
string encryptionKey = config.AppSettings.Settings["EncryptionKey"].Value;
pass = Encryption.Encrypt(pass, encryptionKey);
// var _connection = ConnectionInstance.Instance.GetConnection($"DSN={DsnName}");
var _connection = new OleDbConnectionInstance(ConnectionString).GetConnection();
var deleteCommand = new OleDbCommand($"DELETE * FROM DsnCredentials WHERE DsnName = '{dsn}' AND Username = '******'")
{
Connection = _connection
};
var insertCommand = new OleDbCommand($"INSERT INTO DsnCredentials ([DsnName], [Username], [Password]) VALUES (?,?,?)")
{
Connection = _connection
};
insertCommand.Parameters.AddWithValue("Dsn", dsn);
insertCommand.Parameters.AddWithValue("Username", user);
insertCommand.Parameters.AddWithValue("Password", pass);
_connection.Open();
try
{
deleteCommand.ExecuteNonQuery();
insertCommand.ExecuteNonQuery();
}
catch (Exception e)
{
Logger.Instance.Error($"An error occured while saving Dsn Credentials.");
Logger.Instance.Error(e.GetBaseException().Message);
}
finally
{
_connection.Close();
}
}
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);
}
public List <string> GetCommandsByTrigger()
{
var _connection = new OleDbConnectionInstance(Settings.ConnectionString).GetConnection();
var command = new OleDbCommand($"SELECT `Command` FROM `BatchTasks` WHERE (`Trigger` = '{Trigger}' OR `Task` = '{Task}') AND `Priority` <> -1 ORDER BY `Priority` DESC", _connection);
_connection.Open();
var reader = command.ExecuteReader();
try
{
List <string> commands = new List <string>();
while (reader.Read())
{
commands.Add(reader[0].ToString());
}
return(commands);
}
finally
{
reader.Close();
_connection.Close();
}
}
