C#在三层中调用存储过程

Question

存储过程:CREATE PROCEDURE dbo.ra_re
@MemberName varchar(50),
@MemberPwd varchar(50),
@MemberRealName varchar(50)
AS
insert into ra_Member(MemberName,MemberPwd,MemberRealName) values (@MemberName,@MemberPwd,@MemberRealName)
GO
model层:namespace Model
{
    public class ra_Member
    {
        private int _memberId;
        private string _memberName ;
        private string _memberPwd ;
        private string _memberRealName;
         
        /// <summary>
        /// 用户ID(自增长)
        /// </summary>
        public int MemberId
        {
            set {this._memberId = value; }
            get { return this._memberId; }
        }
        /// <summary>
        /// 用户名
        /// </summary>
        public string MemberName
        {
            set { this._memberName = value; }
            get { return this._memberName; }
        }
        /// <summary>
        /// 用户密码
        /// </summary>
        public string MemberPwd
        {
            set { this._memberPwd = value; }
            get { return this._memberPwd; }
        }
        /// <summary>
        /// 用户真实姓名
        /// </summary>
        public string MemberRealName
        {
            set { this._memberRealName = value; }
            get { return this._memberRealName; }
        }
            }
}
SQLOperate层:
namespace SQLDAL
{
    
    public class SQLOperate
    {
        //数据库连接
        public static readonly string CONN_STRING = ConfigurationManager.AppSettings["ConnectionString"];
        SqlConnection con = new SqlConnection(CONN_STRING);
#region 检测连接是否打开
        /// <summary>
        /// 检测连接的方法CheckConnection(),若连接是关闭的则打开SqlConnection连接
        /// </summary>
        public void CheckConnection()
        {
            if (this.con.State == ConnectionState.Closed)
            {
                this.con.Open();
            }
        }
        #endregion/// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public bool ExecuteNonQuery(bool IsPro, string strSQL)
        {
            
            CheckConnection();
            try
            {
                SqlCommand com = new SqlCommand(strSQL, con);
                if (IsPro)
                {
                    com.CommandType = CommandType.StoredProcedure;
                }
                else
                {
                    com.CommandType = CommandType.Text;
                }
                com.CommandText = strSQL;
                com.ExecuteNonQuery();
                con.Close();
                return true;             
            }
            catch
            {
                return false;
            }
        }
    }
}IDAL层:namespace IDAL
{
    public class IRegister    
    {
        SQLDAL.SQLOperate db = new SQLDAL.SQLOperate();
                
        public bool IDA_AddRegister(Model.ra_Member register)
        {
                        
            return db.ExecuteNonQuery(true,"ra_re"); //ra_re存储过程名称
        }
    }
}
BLL层:namespace BLL
{
    public class BllRegister
    {
        IDAL.IRegister DAL_register = new IDAL.IRegister();
        
       
        public bool AddRegister(Model.ra_Member m_register)
        {
            return DAL_register.IDA_AddRegister(m_register);
             
        }
        
    }
}WEB层:public partial class user_Re : System.Web.UI.Page
{
    BLL.BllRegister B_register = new BLL.BllRegister();
    Model.ra_Member M_register = new Model.ra_Member();
    
    protected void Page_Load(object sender, EventArgs e)
    {    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlParameter[] parms =
            {
                new SqlParameter("@MemberName", M_register.MemberName),
                new SqlParameter("@MemberPwd", M_register.MemberPwd),
                new SqlParameter("@MemberRealName", M_register.MemberRealName)
            };
        M_register.MemberName = this.txtName.Text;
        M_register.MemberPwd = this.txtPwd.Text;
        M_register.MemberRealName = this.txtRealName.Text;        
        
        if (B_register.AddRegister(M_register))
        {
            Response.Write("<script language=javascript>alert('数据库添加成功!');</script>");
        }
        else
        {
            Response.Write("<script language=javascript>alert('数据库操作有错误!');</script>");
        }
    }
}这样一层一层的调用.
调试的时候catch提示错误: 过程或函数 'ra_re' 需要参数 '@MemberName'，但未提供该参数。
把存储过程'ra_re'中的参数附上默认值,程序是能通过的..我想存储过程'ra_re'写的应该是正确的.
我觉得应该是WEB层中错误.不过不知道怎么写.希望知道的大大帮我看看啊? 谢谢了!!! 100分送上

Answer 1

需要给SqlCommand加入参数。下面是MSDN上的例子:private static void UpdateDemographics(Int32 customerID,
    string demoXml, string connectionString)
{
    // Update the demographics for a store, which is stored 
    // in an xml column. 
    string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;        // Use AddWithValue to assign Demographics.
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

Answer 2

/// <summary>
        ///
        /// </summary>
        /// <param name="conn"> </param>
        /// <param name="cmdType"> </param>
        /// <param name="cmdText"> </param>
        /// <param name="cmdParms"> </param>
        /// <returns> </returns>
        public bool ExecuteNonQuery(bool IsPro, string strSQL)
        {
           
            CheckConnection();
            try
            {
                SqlCommand com = new SqlCommand(strSQL, con);
                if (IsPro)
                {
                    com.CommandType = CommandType.StoredProcedure;
                }
                else
                {
                    com.CommandType = CommandType.Text;
                }
                com.CommandText = strSQL;
                com.ExecuteNonQuery();
                con.Close();
                return true;           
            }
            catch
            {
                return false;
            }
        }
    }
}

你执行SQL的时候根本没有给要执行的SQL提供参数
做法可以参考如下代码public bool ExecuteSql(bool isPro,string strSql,SqlParameter[] params)
{
 CheckConnection();
            try
            {
                SqlCommand com = new SqlCommand(strSQL, con);
                if(params !=null){com.Parameters.addArray(params);}                
                if (IsPro)
                {
                    com.CommandType = CommandType.StoredProcedure;
                }
                else
                {
                    com.CommandType = CommandType.Text;
                }
                com.CommandText = strSQL;
                com.ExecuteNonQuery();
                con.Close();
                return true;           
            }
            catch
            {
                return false;
            }
        
}

Answer 3

        public static void ExecPrc(string MemberName, string MemberPwd)
        {
            SqlCommand cmd = new SqlCommand("存储过程名称", cn);
            cmd.CommandType = CommandType.StoredProcedure;            SqlParameter str1 = cmd.Parameters.Add("@MemberName", SqlDbType.VarChar, 50);
            str1.Direction = ParameterDirection.Input;            SqlParameter str2 = cmd.Parameters.Add("@MemberPwd", SqlDbType.VarChar, 50);
            str2.Direction = ParameterDirection.Input;
            
            str1.Value = MemberName;
            str2.Value = MemberPwd;            cmd.ExecuteNonQuery();
        }
上面代码是我随便改的,你参考着做

Answer 4

/// </summary>
        /// <param name="conn"> </param>
        /// <param name="cmdType"> </param>
        /// <param name="cmdText"> </param>
        /// <param name="cmdParms"> </param>
        /// <returns> </returns>
        public bool ExecuteNonQuery(bool IsPro, string strSQL)
        {
           
            CheckConnection();
            try
            {
                SqlCommand com = new SqlCommand(strSQL, con);
                if (IsPro)
                {
                    com.CommandType = CommandType.StoredProcedure;
                }
                else
                {
                    com.CommandType = CommandType.Text;
                }
                com.CommandText = strSQL;
                com.ExecuteNonQuery();
                con.Close();
                return true;           
            }
            catch
            {
                return false;
            }
        } 其实 xml 注释 就能看出来的/// <param name="conn"> </param>
        /// <param name="cmdType"> </param>
        /// <param name="cmdText"> </param>
        /// <param name="cmdParms"> </param><param name="cmdParms"> </param>，在你调用存储过程的时候 你需要传递存储过程的参数可你的方法却没有这个步骤，所以在你没有为存储过程的参数指定默认值时 就会报错了相关代码 可以参考 sqlhelper

Answer 5

写了这么多，唉，还是不知道在哪个层出的错误。既然是三层，那么就model   sql   bll加个web就好了。

Answer 6

    CREATE PROCEDURE dbo.ra_re
@MemberName varchar(50),--建议用nvarchar 更好支持中文
@MemberPwd varchar(50),
@MemberRealName varchar(50)
AS
insert into ra_Member(MemberName,MemberPwd,MemberRealName) values (@MemberName,@MemberPwd,@MemberRealName)
GO
   错误显示是参数有问题，打印出参数的值 比较容易查出问题。BL 方法 建议加   STATIC