public async Task <CommandResult> Execute(SpInfo procInfo, DirectSpContext context, IDictionary <string, object> callParams, bool isReadScale)
{
var res = new CommandResult();
//set context param if exists
var contextSpParam = procInfo.Params.FirstOrDefault(x => x.ParamName.Equals("context", StringComparison.InvariantCultureIgnoreCase));
if (contextSpParam != null)
{
callParams.Add(contextSpParam.ParamName, JsonConvert.SerializeObject(context));
}
using (var sqlConnection = new SqlConnection(isReadScale ? ConnectionStringReadOnly : ConnectionStringReadWrite))
using (var sqlCommand = new SqlCommand($"{procInfo.SchemaName}.{procInfo.ProcedureName}", sqlConnection))
{
// create SqlParameters
foreach (var callParam in callParams)
{
var spParam = procInfo.Params.First(x => x.ParamName.Equals(callParam.Key, StringComparison.OrdinalIgnoreCase));
var sqlParam = new SqlParameter($"@{spParam.ParamName}", Enum.Parse <SqlDbType>(spParam.SystemTypeName, true), spParam.Length)
{
Direction = spParam.IsOutput ? ParameterDirection.InputOutput : ParameterDirection.Input
};
if (callParam.Value != Undefined.Value)
{
sqlParam.Value = callParam.Value;
}
if (callParam.Value == Undefined.Value)
{
sqlParam.Direction = ParameterDirection.Output;
}
if (callParam.Key == "returnValue")
{
sqlParam.Direction = ParameterDirection.ReturnValue;
}
sqlCommand.Parameters.Add(sqlParam);
}
sqlCommand.CommandType = CommandType.StoredProcedure;
if (procInfo.ExtendedProps.CommandTimeout != -1)
{
sqlCommand.CommandTimeout = procInfo.ExtendedProps.CommandTimeout;
}
//execute reader
sqlConnection.Open();
try
{
using var dataReader = await sqlCommand.ExecuteReaderAsync();
res.Table = GetResultTable(dataReader);
dataReader.Close();
}
catch (Exception ex)
{
HandleException(ex);
throw;
}
//return out parameter
res.AgentContext = context.AgentContext;
for (var i = 0; i < sqlCommand.Parameters.Count; i++)
{
var sqlParameter = sqlCommand.Parameters[i];
if (sqlParameter.Direction == ParameterDirection.InputOutput || sqlParameter.Direction == ParameterDirection.Output || sqlParameter.Direction == ParameterDirection.ReturnValue)
{
//process context param
if (sqlParameter.ParameterName.Equals("@context", StringComparison.OrdinalIgnoreCase))
{
if (sqlParameter.Value != DBNull.Value)
{
var newContext = JsonConvert.DeserializeObject <DirectSpContext>((string)sqlParameter.Value);
res.AgentContext = newContext.AgentContext;
}
}
//process non context param
else
{
res.OutParams.Add(sqlParameter.ParameterName.Substring(1), sqlParameter.Value != DBNull.Value ? sqlParameter.Value : null);
}
}
}
}
return(res);
}
public async Task <CommandResult> Execute(SpInfo procInfo, DirectSpContext context, IDictionary <string, object> callParams, bool isReadScale)
{
var schema = procInfo.SchemaName;
var procName = procInfo.ProcedureName;
if (schema != _targetType.Name)
{
throw new Exception($"Invalid schema {schema}!");
}
var methodInfo = _targetType.GetMethod(procName);
if (methodInfo == null)
{
throw new Exception($"{procName} was not found!");
}
//call method by name parameters
var parameterInfos = methodInfo.GetParameters();
var parameterValues = new object[parameterInfos.Length];
for (var i = 0; i < parameterInfos.Length; i++)
{
var parameterInfo = parameterInfos[i];
if (callParams.TryGetValue(parameterInfo.Name, out object value) && value != Undefined.Value)
{
var parameterType = parameterInfo.ParameterType.IsByRef ? parameterInfo.ParameterType.GetElementType() : parameterInfo.ParameterType;
parameterType = Nullable.GetUnderlyingType(parameterType) ?? parameterType;
parameterValues[i] = value == null ? null : Convert.ChangeType(value, parameterType);
}
else if (parameterInfo.HasDefaultValue)
{
parameterValues[i] = parameterInfo.DefaultValue;
}
}
//throw error if there is additional params in given parameters
foreach (var callParam in callParams)
{
if (callParam.Key != "returnValue" && parameterInfos.FirstOrDefault(x => x.Name == callParam.Key) == null)
{
throw new Exception($"Unknown parameter! ParameterName: {callParam.Key}");
}
}
var ret = new CommandResult();
try
{
//Set Thread Context
Thread.SetData(Thread.GetNamedDataSlot("DirectSpContext"), context);
//invoke command and set return value
if (methodInfo.ReturnType == typeof(void))
{
methodInfo.Invoke(_targetObject, parameterValues);
}
else
{
var invokeRes = methodInfo.Invoke(_targetObject, parameterValues);
//manage result for Task (void)
if (invokeRes is Task && !invokeRes.GetType().IsGenericType)
{
await(Task) invokeRes;
}
//manage result for Task (generic)
else if (invokeRes is Task)
{
await((Task)invokeRes).ConfigureAwait(false);
ret.OutParams["returnValue"] = ((Task)invokeRes).GetType().GetProperty("Result").GetValue(invokeRes);
}
else
{
ret.OutParams["returnValue"] = invokeRes;
}
}
//set agent context
ret.AgentContext = context.AgentContext;
//set output paramters
for (var i = 0; i < parameterInfos.Length; i++)
{
var parameterInfo = parameterInfos[i];
if (parameterInfo.IsOut || parameterInfo.ParameterType.IsByRef)
{
if (callParams.ContainsKey(parameterInfo.Name))
{
ret.OutParams[parameterInfo.Name] = parameterValues[i];
}
}
}
return(ret);
}
catch (TargetInvocationException ex)
{
throw ex.InnerException;
}
finally
{
Thread.SetData(Thread.GetNamedDataSlot("DirectSpContext"), null);
}
}