创建应用程序，调用存储过程，并显示用户与角色关系

Question

程序老是报错：过程或函数 'pr' 需要参数 '@RoleName',但未提供该参数   
 /// <summary>
        /// 执行有参SQL语句，并返回sqlDataReader
        /// </summary>
        public static SqlDataReader getReader(string storedProcName,params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(storedProcName, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
     
        }
  
        ///<summary>
        ///构建connection控件
        ///</summary>
        ///<returns></returns>
        private static SqlConnection Con;
        public static SqlConnection Connection
        {
            get
            {
                string connectionstring = "Data Source=.;Initial Catalog=ReportServer;uid=sa;pwd=123456";
                if (Con == null)
                {
                    Con = new SqlConnection(connectionstring);
                    Con.Open();
                }
                else if (Con.State == System.Data.ConnectionState.Closed)
                {
                    Con.Open();
                }
                else if (Con.State == System.Data.ConnectionState.Broken)
                {
                    Con.Close();
                    Con.Open();
                }
                return Con;
            }
        }
        /// <summary>
        /// 根据用户名获取角色名以及权限
        /// </summary>
        /// <returns>角色名及权限</returns>
       public static Roles getReader(string userName)
        {
            try
            {
                SqlDataReader reader = DAL.Role.getReader("pr",new SqlParameter("@userName",userName));
             
                if (reader.Read())
                {
                    Roles roles = new Roles();
                    roles.RoleName = (string)reader["RoleName"];
                    roles.Description = (string)reader["Description"];
                    reader.Close();
                    return roles;
                }
                else
                {
                    reader.Close();
                    return null;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

Answer 1

pr存储过程需要提供名为RoleName的参数值你把pr的参数列出来看看

Answer 2

use ReportServer
go
create proc pr
@userName nvarchar(260), 
@RoleName  nvarchar(260) output,
@Description nvarchar(260) output
as
      select R.RoleName ,R.Description       
      from dbo.Roles as R join dbo.PolicyUserRole as P
      on R.RoleID=P.RoleID join dbo.Users as U
      on U.UserID=P.UserID
      where U.UserName=@userName    
 go 

Answer 3

cmd.Parameters.Add("@RoleName", System.Data.SqlDbType.NVarChar, 10);
            cmd.Parameters["@RoleName"].Direction = System.Data.ParameterDirection.Output;            cmd.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 10);
            cmd.Parameters["@Description"].Direction = System.Data.ParameterDirection.Output;            cmd.ExecuteNonQuery();            Response.Write(cmd.Parameters["@RoleName"].Value);
            Response.Write(cmd.Parameters["@Description"].Value);

Answer 4

添加一个RoleName和Description参数，并设置其方向为output模式