[Dapper] Get Output Value

 近來專案上遇到，預存程序有Output的情況，踩了一點雷，在這邊紀錄一下。

大致上的流程是要先建立一個DynamicParameters，將預存程序會用到的參數都篩進去，然後是Output的參數要特別處理(direction要給 ParameterDirection.Output),這樣在conn.Query()之後就可以在我們原先建立的DynamicParameters裡取得Output的值了。

ps:請注意在DynamicParameters.Add()的時候，若sql有限制參數大小 如:varchar(10),記得給size，且direction一定要給ParameterDirection.Output，否則可能會有Exception或者取回空值得情況發生。

範例:

1.先建立一個  DynamicParameters 產生器，來產生預存程序會用到的參數

 /// <summary>
    /// DynamicParameters-Builder
    /// </summary>
    public class DynamicParametersBuild
    {
        public DynamicParameters Data { get; set; }
        public DynamicParametersBuild(object data)
        {
            OriginalBuild(data);
        }
        /// <summary>
        ///  Original Build DynamicParameters
        /// </summary>
        /// <param name="data">data</param>
        private void OriginalBuild(object data)
        {
            var parameters = new DynamicParameters();
            var properties = data.GetType().GetProperties();
            foreach (var prop in properties)
            {
                var key = prop.Name;
                var value = prop.GetValue(data);
                parameters.Add(key, value);
            }
            this.Data = parameters;
        }
        /// <summary>
        /// Add a parameter to this dynamic parameter list.
        /// </summary>
        /// <param name="name">The name of the parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <param name="dbType">The type of the parameter.</param>
        /// <param name="direction">The in or out direction of the parameter.</param>
        /// <param name="size">The size of the parameter.</param>
        /// <param name="precision">The precision of the parameter.</param>
        /// <param name="scale">The scale of the parameter.</param>
        public void Add(string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null, byte? precision = null, byte? scale = null)
        {
            Data.Add(name, value, dbType, direction, size, precision, scale);
        }
    }

2.建立一個SqlHelper 來取得預存程序的Output參數(ps:取得的Result會依照outputNames給的順序回傳結果)

 public static class SqlHelper
    {
        private static readonly string _connectString = "your connect string";
        /// <summary>
        /// Get Output
        /// </summary>
        /// <param name="spName">stored procedure's name</param>
        /// <param name="parameters">params</param>
        /// <param name="outputNames">output param Name</param>
        /// <returns></returns>
        public static IEnumerable<object> GetOutputData(string spName, DynamicParameters parameters, params string[] outputNames)
        {
            using (var conn = new SqlConnection(_connectString))
            {
                conn.Query(spName, parameters, commandType: CommandType.StoredProcedure);
                for (var i = 0; i < outputNames.Length; i++)
                {
                    var name = outputNames[i];
                    yield return parameters.Get<object>(name);
                }
            }
        }

    }

3.測試code

  var paramsData = new { A = "A", B = "B" };
            //建立動態參數
            var parameters = new DynamicParametersBuild(paramsData);
            //新增Output 參數(ps:若sql有限制參數大小 如:varchar(10),記得給size)
            parameters.Add("OutputA", dbType: DbType.String, size: 10, direction: ParameterDirection.Output);
            parameters.Add("OutputB", dbType: DbType.DateTime, direction: ParameterDirection.Output);
            //取得「OutputA」,「OutputB」的object(取得的Result會依照outputNames給的順序回傳結果)
            var result = SqlHelper.GetOutputData("MyStoredProcedure", parameters.Data, "OutputA", "OutputB").ToArray();
            //再將Result轉型即可
            var outputA = (string)result[0];
            var outputB = (string)result[1];