private void RunIt() { //0:sp name 1:pars StringBuilder sbPar = new StringBuilder(); Dictionary <string, System.Data.SqlClient.SqlParameter> sqlParameters = new Dictionary <string, System.Data.SqlClient.SqlParameter>(); System.Data.SqlClient.SqlParameter sqlParameter = null; var properties = this._Parameter.GetType().GetProperties(); int totalCount = properties.Count(), nowCount = 0; foreach (var p in properties) { nowCount++; EntityProcedureAttribute CustType = (EntityProcedureAttribute)Attribute.GetCustomAttribute(p, typeof(EntityProcedureAttribute)); object value = p.GetValue(this._Parameter, null); if (CustType != null) { sqlParameter = null; sbPar.Append(this.CreateParameters(value, p, CustType, out sqlParameter)); if (sqlParameter == null) { throw new EntityProcedureAttributeException("找不到SqlParameter,產生預存程序參數時發生錯誤 屬性名稱:" + p.Name); } else { sqlParameters.Add(p.Name, sqlParameter); if (nowCount < totalCount) { sbPar.Append(", "); } } } } var returnCode = new System.Data.SqlClient.SqlParameter(); returnCode.ParameterName = "@Return_value"; returnCode.SqlDbType = System.Data.SqlDbType.Int; returnCode.Direction = System.Data.ParameterDirection.Output; sqlParameters.Add("Return_value", returnCode); string execCommand = string.Format(@"EXEC @Return_value = {0} {1}", this._Parameter.GetType().Name, sbPar.ToString()); var inPar = sqlParameters.Values.ToArray(); var runWork = this._contextModel.Database.SqlQuery <TEntity>(execCommand, inPar).ToList(); sqlParameters.ToList().ForEach(item => { switch (item.Value.Direction) { case System.Data.ParameterDirection.InputOutput: case System.Data.ParameterDirection.Output: case System.Data.ParameterDirection.ReturnValue: foreach (var p in this._Parameter.GetType().GetProperties()) { if (p.Name == item.Key) { var value = item.Value.Value; var t = Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType; var safeValue = (value == null) ? null : (value == DBNull.Value) ? null : Convert.ChangeType(value, t); p.SetValue(this._Parameter, safeValue); } } break; } }); this._returnCode = int.Parse(returnCode == null ? "-1" : returnCode.Value.ToString()); this.ObjectState = true; }
private string CreateParameters(object value, System.Reflection.MemberInfo p, EntityProcedureAttribute custType, out System.Data.SqlClient.SqlParameter sqlParameter) { string resultCmd = string.Empty; string parameterName = string.Empty; #region ActionContent //判斷如果value是NULL預設值時,給Null值 if (custType.DefaultOrNullValue != null && value != null) { if (value.ToString() == custType.DefaultOrNullValue.ToString()) { value = null; } } System.Data.SqlDbType PropertyType = System.Data.SqlDbType.NVarChar; if (custType != null) { int length = 0; if (custType.Size.HasValue) { var hasLen = int.TryParse(custType.Size.Value.ToString(), out length); if (hasLen) { if (value != null) { if (value.ToString().Length > length) { value = value.ToString().Substring(0, length); } } } } //取得 PropertyType = (System.Data.SqlDbType)custType.AttrType; sqlParameter = new System.Data.SqlClient.SqlParameter(); sqlParameter.SqlDbType = PropertyType; if (custType.Size.HasValue) { sqlParameter.Size = custType.Size.Value; } if (( (custType.Direction.HasValue && (custType.Direction == System.Data.ParameterDirection.Input || custType.Direction == System.Data.ParameterDirection.InputOutput ) ) || custType.Direction.HasValue == false )) { if (value == null) { sqlParameter.Value = DBNull.Value; sqlParameter.IsNullable = true; } else { sqlParameter.Value = value; } } if (string.IsNullOrWhiteSpace(custType.ParameterName) == false) { parameterName = sqlParameter.ParameterName = custType.ParameterName.Replace("@", string.Empty); } else { parameterName = sqlParameter.ParameterName = p.Name; } string Cmd = " @{0} = @{0} {1}"; if (custType.Direction.HasValue) { sqlParameter.Direction = custType.Direction.Value; switch (custType.Direction.Value) { case System.Data.ParameterDirection.Output: case System.Data.ParameterDirection.InputOutput: resultCmd = string.Format(Cmd, parameterName, "Output"); break; default: resultCmd = string.Format(Cmd, parameterName, string.Empty); break; } } else { resultCmd = string.Format(Cmd, parameterName, string.Empty); } } else { throw new EntityProcedureAttributeException(p.Name + "屬性找不到EntityProcedureAttribute,請確定是否已定義此設定。"); } #endregion return(resultCmd); }