/// <summary>
/// Task for executing non-query commands and stored procedures in Oracle. See documentation at https://github.com/CommunityHiQ/Frends.Community.Oracle.ExecuteCommand
/// </summary>
/// <param name="input">The input data for the task</param>
/// <param name="output">The output of the task</param>
/// <param name="options">The options for the task</param>
/// <returns>object { bool Success, string Message, dynamic Result }</returns>
public async static Task <Output> Execute([PropertyTab] Input input, [PropertyTab] OutputProperties output,
[PropertyTab] Options options)
{
try
{
OracleConnection connection = null;
// Get connection from cache, or create a new one
connection = GetLazyConnection(input.ConnectionString);
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
using (OracleCommand command = new OracleCommand(input.CommandOrProcedureName, connection))
{
command.CommandType = (CommandType)input.CommandType;
command.CommandTimeout = input.TimeoutSeconds;
if (input.InputParameters != null)
{
command.Parameters.AddRange(input.InputParameters.Select(x => CreateOracleParam(x))
.ToArray());
}
if (output.OutputParameters != null)
{
command.Parameters.AddRange(output.OutputParameters
.Select(x => CreateOracleParam(x, ParameterDirection.Output)).ToArray());
}
command.BindByName = input.BindParametersByName;
int affectedRows = 0;
// Oracle command executions are not really async https://stackoverflow.com/questions/29016698/can-the-oracle-managed-driver-use-async-wait-properly/29034412#29034412
var runCommand = command.ExecuteNonQueryAsync();
affectedRows = await runCommand;
IEnumerable <OracleParam> outputOracleParams = null;
outputOracleParams = command.Parameters.Cast <OracleParam>()
.Where(p => p.Direction == ParameterDirection.Output);
return(HandleDataset(outputOracleParams, affectedRows, output));
}
}
catch (Exception ex)
{
if (options.ThrowErrorOnFailure)
{
throw ex;
}
return(new Output {
Success = false, Message = ex.Message
});
}
}
private static Output HandleDataset(IEnumerable <OracleParam> outputOracleParams, int affectedRows,
OutputProperties output)
{
if (output.DataReturnType == OracleCommandReturnType.AffectedRows)
{
return(new Output
{
Success = true,
Result = affectedRows
});
}
else if (output.DataReturnType == OracleCommandReturnType.Parameters)
{
return(new Output
{
Success = true,
Result = outputOracleParams.ToList()
});
}
//Builds xml document from Oracle output parameters
var xDoc = new XDocument();
var root = new XElement("Root");
xDoc.Add(root);
outputOracleParams.ToList().ForEach(p => root.Add(ParameterToXElement(p)));
dynamic commandResult;
// Affected rows are handled above!
switch (output.DataReturnType)
{
case OracleCommandReturnType.JSONString:
commandResult = JsonConvert.SerializeObject(outputOracleParams);
break;
case OracleCommandReturnType.XDocument:
commandResult = xDoc;
break;
case OracleCommandReturnType.XmlString:
commandResult = xDoc.ToString();
break;
default:
throw new Exception("Unsupported DataReturnType.");
}
return(new Output
{
Success = true,
Result = commandResult
});
}
