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