/// <summary>
/// Executes the SQL statements in a file given its path, and a connection string to the database.
/// </summary>
/// <param name="scriptFilePath">The path the the SQL script file to execute.</param>
/// <param name="connectionString">The connection string of the database to execute the script against.</param>
/// <param name="log">The event log to log execptions to. May be null for no logging.</param>
/// <returns>True if the script ran, false otherwise.</returns>
public virtual bool ExecuteNonQuerySQLFile(String scriptFilePath, String connectionString, EventLog.EventLog log)
{
string script = File.ReadAllAsText(scriptFilePath);
// Check that the script file was read.
if (!string.IsNullOrWhiteSpace(script))
{
return ExecuteNonQuery(script, connectionString, log);
}
else
{
// The script file was not read.
return false;
}
}
// ----- CONSTRUCTORS -----
/// <summary>
/// Loads a configuration item with the specified name from its file located
/// in a folder at the supplied path.
/// </summary>
/// <param name="folderPath">The path of the folder containing the configuration item.</param>
/// <param name="name">The name of the configuration item.</param>
/// <param name="encrypted">Whether to store the item in an encrypted format.</param>
/// <param name="value">(Optional) The value of the configuration item. (Supplying a null value will retrieve the value stored
/// in the item if it already exists).</param>
/// <param name="log">(Optional) The event log to log exceptions to. May be null for no logging.</param>
/// <param name="readOnly">Whether the configuration item should be opened for reading only. Default: true</param>
public ConfigurationItem(String folderPath, String name, Boolean encrypted, String value = null, EventLog.EventLog log = null, Boolean readOnly = true)
{
if (!String.IsNullOrWhiteSpace(folderPath) && !String.IsNullOrWhiteSpace(name))
{
FolderPath = folderPath;
Name = name;
Encrypted = encrypted;
eventLog = log;
this.readOnly = readOnly;
// If the value is not null, set the value.
if (value != null)
{
// Initialize the value.
Value = value;
}
}
}
// ---------- CONSTRUCTORS ----------
/// <summary>
/// Inititalizes the PowerShellScript environment.
/// </summary>
/// <param name="log">An event log to log exceptions to. May be null if no exception logging is desired.</param>
/// <param name="pool">A runspace pool for the scripting environment to use. May be null if a new Runspace pool is desired.</param>
public PowerShellScript(EventLog.EventLog log, RunspacePool pool)
{
RunspacePool = null;
Log = log;
if (pool != null)
{
RunspacePool = pool;
// Open the Runspace Pool so it's ready for use.
if (RunspacePool.RunspacePoolStateInfo.State != RunspacePoolState.Opened)
{
RunspacePool.Open();
}
}
else
{
InitializeRunspacePool();
}
}
/// <summary>
/// Writes a configuration item's value to a file.
/// </summary>
/// <param name="value">The value to write to the file.</param>
/// <param name="log">The event log to log exceptions to. May be null for no logging.</param>
/// <returns>True if the write was successful, false otherwise.</returns>
protected bool Write(string value, EventLog.EventLog log)
{
// Write the value to the file.
File file = null;
try
{
file = new File(FilePath, false);
return file.WriteLine(value);
}
catch (Exception e)
{
// There was an error opening the file.
LogException(e, log);
return false;
}
finally
{
// Close the file.
if (file != null)
{
file.Close();
}
}
}
/// <summary>
/// Writes an AES 256 encrypted value to its configuration file.
/// </summary>
/// <param name="value">The value to write to the file.</param>
/// <param name="log">The event log to log execptions to. May be null for no logging.</param>
/// <returns>True if the write was successful, false otherwise.</returns>
protected bool SecureWrite(string value, EventLog.EventLog log)
{
// Create a consolidated encrypted string value for storage in the file.
string consolidatedString = AES256.CreateConsolidatedString(value);
// Check that the consolidated string value was created successfully.
if (!string.IsNullOrWhiteSpace(consolidatedString))
{
// The consolidated string value was created successfully.
// Write the consolidated string value containing the encrypted value to a file.
return Write(consolidatedString, log);
}
else
{
// The consolidated string value was not created.
return false;
}
}
/// <summary>
/// Retrieves an AES 256 encrypted value from the configuration file.
/// </summary>
/// <param name="log">The event log to log exceptions to. May be null for no logging.</param>
/// <returns>The value or null if not found or it could not be decrypted.</returns>
protected string SecureGet(EventLog.EventLog log)
{
// Get the encrypted connection string containing the key, initialization vector, value, and key and
// initialization vector lengths from the file.
// Read only the first line of the file, as this is all that is necessary for the encrypted
// format.
StringReader reader = new StringReader(Get(log));
string encryptedValue = reader.ReadLine();
// Check that an encrypted value was retrieved.
if (!string.IsNullOrWhiteSpace(encryptedValue))
{
// The encrypted value was retrieved.
// Decrypt the encrypted value.
return AES256.DecryptConsolidatedString(encryptedValue);
}
else
{
// Could not retrieve the encrypted value.
return null;
}
}
/// <summary>
/// Retrieves the value of a configuration item from its file.
/// </summary>
/// <param name="log">The event log to log exceptions to. May be null for no logging.</param>
/// <returns>The value of the configuration item or null if not found.</returns>
protected string Get(EventLog.EventLog log)
{
File file = null;
try
{
// Get the file.
file = new File(FilePath, false, readOnly);
// Check that the file exists.
if (file.Exists())
{
// Return the value of the item from the file.
return file.ReadAllAsText();
}
else
{
// The file doesn't exist. Return null.
return null;
}
}
catch (Exception e)
{
// There was an error opening or reading from the file.
LogException(e, log);
return null;
}
finally
{
// Close the file.
if (file != null)
{
file.Close();
}
}
}
// ---------- METHODS ----------
/// <summary>
/// Logs an exception to the event log.
/// </summary>
/// <param name="e">The exception to log.</param>
/// <param name="log">The event log to log the execption to.</param>
/// <returns>True if the exception was logged successfully. False otherwise.</returns>
private static bool LogException(Exception e, EventLog.EventLog log)
{
if (log != null)
{
log.Log(new Event(typeof(PowerShell).FullName, DateTime.Now, Event.SeverityLevels.Error, e.GetType().FullName,
"Description:\n" +
e.Message + "\n" +
"Stack Trace:\n" +
e.StackTrace));
return true;
}
else
{
return false;
}
}
/// <summary>
/// Executes the PL/SQL query provided.
/// </summary>
/// <param name="query">The PL/SQL query text to execute.</param>
/// <param name="connectionString">The connection string of the database to execute the script against.</param>
/// <param name="log">The event log to log execptions to. May be null for no logging.</param>
/// <param name="exception">Outputs an exception if there was one during the processing of the query. Null otherwise.</param>
/// <returns>A list of rows of data returned from the query.</returns>
public List<SqlRow> ExecuteQuery(String query, String connectionString, EventLog.EventLog log, out Exception exception)
{
// Check that the query and connection string are supplied.
if (!string.IsNullOrWhiteSpace(query) && !string.IsNullOrWhiteSpace(connectionString))
{
// Get the connection to the database.
using (OracleConnection connection = new OracleConnection(connectionString))
{
try
{
// Open the connection to the server.
connection.Open();
// Create a command that contains the text of the script.
using (OracleCommand queryCommand = new OracleCommand())
{
queryCommand.Connection = connection;
queryCommand.CommandText = query;
queryCommand.CommandType = CommandType.Text;
// Execute the query.
List<SqlRow> results = new List<SqlRow>();
using (OracleDataReader reader = queryCommand.ExecuteReader())
{
if (reader.HasRows)
{
// Add each row's data to the results list.
while (reader.Read())
{
SqlRow row = new SqlRow();
for (int columnNum = 0; columnNum < reader.FieldCount; columnNum++)
{
row.Add(columnNum, reader.GetName(columnNum), reader.GetValue(columnNum));
}
results.Add(row);
}
}
}
exception = null;
return results;
}
}
catch (OracleException e)
{
// There was an error executing the script.
LogException(e, log);
exception = e;
return new List<SqlRow>();
}
catch (Exception e)
{
// There was an error executing the script.
LogException(e, log);
exception = e;
return new List<SqlRow>();
}
}
}
else
{
// The script file was not read.
exception = null;
return new List<SqlRow>();
}
}
/// <summary>
/// Runs a PowerShell command/script asynchronously.
/// </summary>
/// <param name="commandText">The text of the command to run.</param>
/// <param name="pool">An open PowerShell Runspace pool this script will use to invoke its pipeline.</param>
/// <param name="callback">The callback function used to process the results of the asynchronous run.</param>
/// <param name="log">[Optional] The event log to log execptions to. May be null for no logging.</param>
/// <param name="input">[Optional] A collection of strings representing command-line input sent to the script during execution.</param>
/// <param name="stateValues">[Optional] A collection of named state values that should be passed through the invocation to the callback function.</param>
/// <param name="parameterList">An array of key/value objects defining additional parameters to supply to the PowerShell script.</param>
/// <returns>An WaitHandle object that can be used to determine when the scrip has completed execution. Null if an error occurred while processing the command / script.</returns>
public static WaitHandle RunAsynchronously(string commandText, ref RunspacePool pool, ProcessResults callback, EventLog.EventLog log = null, PSDataCollection<string> input = null, Dictionary<String, Object> stateValues = null, params KeyValuePair<String, Object>[] parameterList)
{
try
{
// Create the script object.
PS script = PS.Create();
// Use the runspace pool supplied or create a new one if not supplied.
if (pool == null)
{
pool = RunspaceFactory.CreateRunspacePool(DEFAULT_MIN_RUNSPACES_IN_POOL, DEFAULT_MAX_RUNSPACES_IN_POOL, CreateRunspaceConnectionInfo());
}
// Verify that the pool is open, otherwise open it.
if (pool.RunspacePoolStateInfo.State != RunspacePoolState.Opened)
{
pool.Open();
}
// Add the runspace pool to the script object.
script.RunspacePool = pool;
// Create the PowerShell command object.
Command command = new Command(commandText, true);
// Add parameters to the command.
if (parameterList != null)
{
foreach (KeyValuePair<string, object> param in parameterList)
{
command.Parameters.Add(new CommandParameter(param.Key, param.Value));
}
}
// Add the command to the script object.
script.Commands.AddCommand(command);
// Initialize the script input object if nothing was supplied.
if (input == null)
{
input = new PSDataCollection<string>();
}
// Initialize the state object to maintain data across the invocation.
PowerShellScriptState state = new PowerShellScriptState(script);
// Add the callback function used to process the results of the script invocation.
state.StateVariables.Add(PROCESS_RESULTS_CALLBACK, callback);
// Add any state values passed into the method.
if (stateValues != null)
{
foreach (string key in stateValues.Keys)
{
state.StateVariables.Add(key, stateValues[key]);
}
}
// Invoke the command asyncronously.
return (script.BeginInvoke(input, new PSInvocationSettings(), ProcessAsynchronousResults, state)).AsyncWaitHandle;
}
catch (Exception e)
{
LogException(e, log);
return null;
}
}
/// <summary> /// Executes the SQL query provided. /// </summary> /// <param name="query">The query text to execute.</param> /// <param name="connectionString">The connection string of the database to execute the script against.</param> /// <param name="log">The event log to log execptions to. May be null for no logging.</param> /// <returns>A list of rows of data returned from the query.</returns> public abstract List<SqlRow> ExecuteQuery(String query, String connectionString, EventLog.EventLog log);
/// <summary> /// Executes the non-query SQL command provided. /// </summary> /// <param name="command">The SQL command text to execute.</param> /// <param name="connectionString">The connection string of the database to execute the script against.</param> /// <param name="log">The event log to log execptions to. May be null for no logging.</param> /// <returns>True if the command ran, false otherwise.</returns> public abstract bool ExecuteNonQuery(String command, String connectionString, EventLog.EventLog log);
// ---------- CONSTRUCTORS ----------
// ---------- METHODS ----------
/// <summary>
/// Logs an exception to the event log.
/// </summary>
/// <param name="e">The exception to log.</param>
/// <param name="log">The event log to log the execption to.</param>
/// <returns>True if the exception was logged successfully. False otherwise.</returns>
protected virtual bool LogException(Exception e, EventLog.EventLog log)
{
if (log != null)
{
log.Log(new Event(typeof(SqlUtility).FullName, DateTime.Now, Event.SeverityLevels.Error, e.GetType().FullName,
"Description:\n" +
e.Message + "\n" +
"Stack Trace:\n" +
e.StackTrace));
return true;
}
else
{
return false;
}
}
/// <summary>
/// Runs SQL script files given their path.
/// </summary>
/// <param name="connectionString">The connection string to use when creating the database.</param>
/// <param name="scriptPaths">The paths to the script files on the filesystem.</param>
/// <param name="log">The event log to log execptions to. May be null for no logging.</param>
/// <returns>True if all scripts ran, false otherwise.</returns>
public virtual bool RunScript(string connectionString, string[] scriptPaths, EventLog.EventLog log)
{
// Check that the connection string and script paths were provided.
if (!string.IsNullOrWhiteSpace(connectionString) && scriptPaths != null)
{
// The connection string and script paths were provided.
// Check that the files exist.
foreach (string path in scriptPaths)
{
if (!File.Exists(path))
{
// A file does not exist.
return false;
}
}
// Execute the scripts.
foreach (string path in scriptPaths)
{
if (!ExecuteNonQuerySQLFile(path, connectionString, log))
{
// There was an error running the script.
return false;
}
}
return true;
}
else
{
// The connection string and script paths were not provided.
return false;
}
}
/// <summary>
/// Executes the PL/SQL query provided.
/// </summary>
/// <param name="query">The PL/SQL query text to execute.</param>
/// <param name="connectionString">The connection string of the database to execute the script against.</param>
/// <param name="log">The event log to log execptions to. May be null for no logging.</param>
/// <returns>A list of rows of data returned from the query.</returns>
public override List<SqlRow> ExecuteQuery(String query, String connectionString, EventLog.EventLog log)
{
Exception e = null;
return ExecuteQuery(query, connectionString, log, out e);
}
/// <summary>
/// Runs a PowerShell command/script synchronously.
/// </summary>
/// <param name="commandText">The text of the command to run.</param>
/// <param name="runspace">An open PowerShell Runspace this script will use to invoke its pipeline.</param>
/// <param name="log">[Optional] The event log to log execptions to. May be null for no logging.</param>
/// <param name="input">[Optional] A collection of strings representing command-line input sent to the script during execution.</param>
/// <param name="parameterList">An array of key/value objects defining additional parameters to supply to the PowerShell script.</param>
/// <returns>A collection of PSObjects that are the result of the script/command run. Null if an error occurred while processing the command / script.</returns>
public Collection<PSObject> RunSynchronously(string commandText, ref Runspace runspace, EventLog.EventLog log = null, PSDataCollection<string> input = null, params KeyValuePair<String, Object>[] parameterList)
{
// Run the script synchronously.
return PowerShell.RunSynchronously(commandText, ref runspace, Log, input, parameterList);
}
/// <summary>
/// Runs a PowerShell command/script synchronously.
/// </summary>
/// <param name="commandText">The text of the command to run.</param>
/// <param name="runspace">An open PowerShell Runspace this script will use to invoke its pipeline.</param>
/// <param name="log">[Optional] The event log to log execptions to. May be null for no logging.</param>
/// <param name="input">[Optional] A collection of strings representing command-line input sent to the script during execution.</param>
/// <param name="parameterList">An array of key/value objects defining additional parameters to supply to the PowerShell script.</param>
/// <returns>A collection of PSObjects that are the result of the script/command run. Null if an error occurred while processing the command / script.</returns>
public static Collection<PSObject> RunSynchronously(string commandText, ref Runspace runspace, EventLog.EventLog log = null, PSDataCollection<string> input = null, params KeyValuePair<String, Object>[] parameterList)
{
try
{
// Create the script object.
PS script = PS.Create();
// Use the runspace supplied or create a new one if not supplied.
if (runspace == null)
{
runspace = RunspaceFactory.CreateRunspace(CreateRunspaceConnectionInfo());
}
// Verify that the runspace is open, otherwise open it.
if (runspace.RunspaceStateInfo.State != RunspaceState.Opened)
{
runspace.Open();
}
// Add the runspace to the script object.
script.Runspace = runspace;
// Create the PowerShell command object.
Command command = new Command(commandText, true);
// Add parameters to the command.
if (parameterList != null)
{
foreach (KeyValuePair<string, object> param in parameterList)
{
command.Parameters.Add(new CommandParameter(param.Key, param.Value));
}
}
// Add the command to the script object.
script.Commands.AddCommand(command);
// Initialize the script input object if nothing was supplied.
if (input == null)
{
input = new PSDataCollection<string>();
}
// Invoke the command syncronously.
return (script.Invoke(input));
}
catch (Exception e)
{
LogException(e, log);
return null;
}
}
/// <summary>
/// Executes the non-query PL/SQL command provided.
/// </summary>
/// <param name="command">The PL/SQL command text to execute.</param>
/// <param name="connectionString">The connection string of the database to execute the script against.</param>
/// <param name="log">The event log to log execptions to. May be null for no logging.</param>
/// <returns>True if the command ran, false otherwise.</returns>
public override bool ExecuteNonQuery(String command, String connectionString, EventLog.EventLog log)
{
// Check that the command and connection string are supplied.
if (!string.IsNullOrWhiteSpace(command) && !string.IsNullOrWhiteSpace(connectionString))
{
// Get the connection to the database.
using (OracleConnection connection = new OracleConnection(connectionString))
{
try
{
// Open the connection to the server.
connection.Open();
// Create a command that contains the text of the script.
using (OracleCommand oracleCommand = new OracleCommand())
{
oracleCommand.Connection = connection;
oracleCommand.CommandText = command;
oracleCommand.CommandType = CommandType.Text;
// Execute the command.
oracleCommand.ExecuteNonQuery();
return true;
}
}
catch (OracleException e)
{
// There was an error executing the script.
LogException(e, log);
return false;
}
catch (Exception e)
{
// There was an error executing the script.
LogException(e, log);
return false;
}
}
}
else
{
// The script file was not read.
return false;
}
}
