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