/// <summary>
/// Apply all values received in parameters collection to ReturnValue collection
/// </summary>
public override async Task <TaskResult> ExecuteTask(TaskParameters parameters)
{
var result = new TaskResult();
var dateTimeNow = DateTime.Now; // Use single value throughout for consistency in macro replacements
try
{
// Iterate through all keys in incoming parameter collection:
foreach (var key in parameters.GetKeys())
{
// Retrieve specified key value, processing date macros:
var value = parameters.GetString(key, null, dateTimeNow);
// Now process any nested macros in resulting value string - regex will capture argument-named macros,
// to allow keys passed as parameters to this adapter (including the keys of return values output by
// previous adapters in batch) to have their values updated with the value of other keys in collection
// (again including return values output by previous steps). For example, if a previous adapter in this
// batch output a return value of "@FileID"/57, placing the parameter "Command"/"echo <@@FileID>" in
// the collection for THIS adapter will result in value "Command"/"echo 57" being placed in the return
// value collection (and then placed into input parameter collections of subsequent steps):
var valuemacromatches = TaskUtilities.General.REGEX_NESTEDPARM_MACRO
.Matches(value)
.Cast <Match>()
// Flatten match collection into name/value pair and select unique values only:
.Select(match => new { Name = match.Groups["name"].Value, Value = match.Value })
.Distinct();
foreach (var match in valuemacromatches)
{
// Retrieve parameter matching the "name" portion of the macro - processing date/time macros
// again - and replace all instances of the specified macro with the string retrieved:
value = value.Replace(match.Value, parameters.GetString(match.Name, null, dateTimeNow));
}
// Add final value to return value collection:
result.AddReturnValue(key, value);
}
result.Success = true;
}
catch (Exception ex)
{
result.AddException(ex);
}
return(result);
}
/// <summary>
/// Execute the specified SQL stored procedure (deriving parameters automatically, applying inputs from
/// parameters and adding output parameters to return values collection)
/// </summary>
public override async Task <TaskResult> ExecuteTask(TaskParameters parameters)
{
var result = new TaskResult();
var consoleOutput = new StringBuilder();
var dateTimeNow = DateTime.Now; // Use single value throughout for consistency in macro replacements
try
{
#region Retrieve task parameters
// Retrieve and validate required parameters
string connectionString = config.GetConnectionString(parameters.GetString("ConnectionString"));
string storedProcedureName = parameters.GetString("StoredProcedureName");
if (string.IsNullOrEmpty(connectionString) || string.IsNullOrEmpty(storedProcedureName))
{
throw new ArgumentException("Missing or invalid ConnectionString and/or StoredProcedureName");
}
// Retrieve optional parameters
string returnValueRegex = parameters.GetString("ReturnValueRegex");
bool defaultNulls = parameters.GetBool("DefaultNulls");
var dbTimeout = parameters.Get <int>("DBTimeout", int.TryParse);
#endregion
#region Open database connection and execute procedure
await using (var cnn = new SqlConnection(connectionString))
{
await cnn.OpenAsync();
// Capture console messages into StringBuilder:
cnn.InfoMessage += (object obj, SqlInfoMessageEventArgs e) => { consoleOutput.AppendLine(e.Message); };
await using (var cmd = new SqlCommand(storedProcedureName, cnn)
{
CommandType = CommandType.StoredProcedure
})
{
if (dbTimeout > 0)
{
cmd.CommandTimeout = (int)dbTimeout;
}
#region Derive and apply procedure parameters
SqlCommandBuilder.DeriveParameters(cmd); // Note: synchronous (no async version available)
foreach (SqlParameter sqlParameter in cmd.Parameters)
{
if (sqlParameter.Direction.HasFlag(ParameterDirection.Input))
{
// This parameter requires input value - check for corresponding parameter:
if (parameters.ContainsKey(sqlParameter.ParameterName))
{
// Special case - strings are not automatically changed to Guid values, attempt explicit conversion:
if (sqlParameter.SqlDbType == SqlDbType.UniqueIdentifier)
{
sqlParameter.Value = (object)parameters.Get <Guid>(sqlParameter.ParameterName, Guid.TryParse) ?? DBNull.Value;
}
else
{
// Apply string value to parameter (replacing null with DBNull):
sqlParameter.Value = (object)parameters.GetString(sqlParameter.ParameterName, null, dateTimeNow) ?? DBNull.Value;
}
}
// If parameter value was not set above, and either we are set to supply default NULL
// values OR this is also an OUTPUT parameter, set to explicit NULL:
else if (defaultNulls || sqlParameter.Direction.HasFlag(ParameterDirection.Output))
{
sqlParameter.Value = DBNull.Value;
}
// (otherwise, value will be left unspecified; if stored procedure does not provide a default
// value, execution will fail and missing parameter will be indicated by exception string)
}
}
#endregion
await cmd.ExecuteNonQueryAsync();
#region Extract output parameters into return values collection, validate return value
foreach (SqlParameter sqlParameter in cmd.Parameters)
{
if (sqlParameter.Direction.HasFlag(ParameterDirection.Output))
{
result.AddReturnValue(sqlParameter.ParameterName, sqlParameter.Value == DBNull.Value ? null : sqlParameter.Value?.ToString());
}
}
// If return value regex provided, check return value against it:
if (!string.IsNullOrEmpty(returnValueRegex))
{
string returnValue = result.ReturnValues.ContainsKey("@RETURN_VALUE") ? result.ReturnValues["@RETURN_VALUE"] : null;
if (string.IsNullOrEmpty(returnValue))
{
throw new Exception($"Stored procedure did not return a value (or no value retrieved)");
}
else if (!Regex.IsMatch(returnValue, returnValueRegex))
{
throw new Exception($"Invalid stored procedure return value ({returnValue})");
}
}
#endregion
// If this point is reached with no exception raised, operation was successful:
result.Success = true;
}
}
#endregion
}
catch (Exception ex)
{
if (ex is AggregateException ae) // Exception caught from async task; simplify if possible
{
ex = TaskUtilities.General.SimplifyAggregateException(ae);
}
logger.LogError(ex, "Procedure execution failed");
result.AddException(ex);
}
// If console output was captured, log now:
if (consoleOutput.Length > 0)
{
logger.LogDebug(consoleOutput.ToString());
}
return(result);
}
