.NET ?CORE ?EF 框架调用存储过程

//查 获取信息
string userId =888886868886; ?????//多个参数多表组合值 ???????????SqlParameter[] Param = ???????????????{ ?????????????????new SqlParameter("@UserId", System.Data.SqlDbType.VarChar) ???????????????}; ???????????if (string.IsNullOrEmpty(userId)) ???????????{ Param[0].Value = DBNull.Value; } ???????????else ???????????{ Param[0].Value = userId; } ???????????var userdata = await _context.ExecSpAsync("SP_GetList", Param);

 ??????/// <summary> ???????/// 异步执行带有参数的存储过程方法 获取信息集合以及返回空值处理 ???????/// </summary> ???????/// <param name="db"></param> ???????/// <param name="sql"></param> ???????/// <param name="sqlParams"></param> ???????/// <returns></returns> ???????public async static Task<ArrayList> ExecSpAsync(this DefaultDbContext db, string sql, SqlParameter[] sqlParams) ???????{ ???????????var connection = db.Database.GetDbConnection(); ???????????using (var cmd = connection.CreateCommand()) ???????????{ ???????????????await db.Database.OpenConnectionAsync(); ???????????????cmd.CommandText = sql; ???????????????cmd.CommandType = System.Data.CommandType.StoredProcedure; ???????????????cmd.Parameters.AddRange(sqlParams); ???????????????var dr = await cmd.ExecuteReaderAsync(); ???????????????var columnSchema = dr.GetColumnSchema(); ???????????????var data = new ArrayList(); ???????????????while (await dr.ReadAsync()) ???????????????{ ???????????????????var item = new Dictionary<string, object>(); ???????????????????foreach (var kv in columnSchema) ???????????????????{ ???????????????????????if (kv.ColumnOrdinal.HasValue) ???????????????????????{ ???????????????????????????var itemVal = dr.GetValue(kv.ColumnOrdinal.Value); ???????????????????????????item.Add(kv.ColumnName, itemVal.GetType() != typeof(DBNull) ? itemVal:""); ???????????????????????} ???????????????????} ???????????????????data.Add(item); ???????????????} ???????????????dr.Dispose(); ???????????????return data; ???????????} ???????} ???????/// <summary> ???????/// 异步执行带有参数的存储过程方法 ?增删改操作以及返回带有输出的参数 ???????/// </summary> ???????/// <param name="db"></param> ???????/// <param name="sql"></param> ???????/// <param name="sqlParams"></param> ???????/// <returns></returns> ???????public async static Task<int> ExecuteNonQueryAsync(this DefaultDbContext db, string sql, SqlParameter[] sqlParams) ???????{ ???????????int numint; ???????????var connection = db.Database.GetDbConnection(); ???????????using (var cmd = connection.CreateCommand()) ???????????{ ???????????????await db.Database.OpenConnectionAsync(); ???????????????cmd.CommandText = sql; ???????????????cmd.CommandType = System.Data.CommandType.StoredProcedure; ???????????????cmd.Parameters.AddRange(sqlParams); ???????????????numint = await cmd.ExecuteNonQueryAsync(); ???????????} ??????????????????????????????return numint; ?????????}

//增删改
 SqlParameter[] Param = ???????????????{ ????????????????new SqlParameter("@MobilePhone", System.Data.SqlDbType.VarChar), ?????????????????new SqlParameter("@PayPrice", System.Data.SqlDbType.VarChar), ???????????new SqlParameter("@rt_code", System.Data.SqlDbType.NVarChar, 20), ?????????????????new SqlParameter("@rt_msg", System.Data.SqlDbType.NVarChar, 200), ???//输出一定要定义字符类型长度 以免报错 ?????????????????????????????????}; ???????????if (string.IsNullOrEmpty(strMobilePhone)) ???????????{ Param[0].Value = DBNull.Value; } ???????????else ???????????{ Param[0].Value = strMobilePhone; } ???????????Param[1].Value = strPayPrice; ???????????if (string.IsNullOrEmpty(strParkUserId)) ???????????{ Param[2].Value = DBNull.Value; } ???????????else ???????????{ Param[2].Value = strParkUserId; } ??????????????????????Param[2].Direction = ParameterDirection.Output; ???????????Param[3].Direction = ParameterDirection.Output; ??????????int numdata = await _dbcontext.ExecuteNonQueryAsync("SP_Pay", Param); ???????????string rtcode = Param[2].Value.ToString(); ???????????string rtmessage = Param[3].Value.ToString(); ???????????if (numdata < 0) ???????????{ ???????????????return AsResult.Error(Convert.ToInt32(rtcode), rtmessage); ???????????}