如何在C#中使用不同的参数执行存储过程? [英] How to execute stored procedure in C# with different parameters?

问题描述

 using (SqlConnection conn = new SqlConnection(connection))
 {
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            conn.Open();

            cmd.CommandText = StoredProcedure;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            foreach (var item in reportParams)
            {
                cmd.Parameters.Add(new SqlParameter("@" + item.ParameterName,  item.FieldValue ));
            }

            da.SelectCommand = cmd;
            da.Fill(ds);
            conn.Close();
}

但是，如果我更改此值，将无法传递某些值

But this fails for some value to pass that if I change

 cmd.Parameters.Add(new SqlParameter("@" + item.ParameterName, "'" item.FieldValue"'" ));

那么失败的条件就过去了，一切都没有过去.

Then failing condition passes and things working do no not passes.

执行带有或不带有'"的任何参数值的存储过程的正确方法是什么?

What is the proper method to execute a stored procedure with any parameter value with or without "'"?

只要条件失败，我就会在 da.Fill(ds); 处出现错误

I am getting an error at da.Fill(ds); whenever the condition fails

推荐答案

public void ExecutePRC(字符串userId，引用字符串RetMsg){尝试{

public void ExecutePRC(string userId,ref string RetMsg) { try {

            string RetVal="";
            SqlCommand cmd= new SqlCommand("ProcedureName",ConnectionObject);
            cmd.CommandType=CommandType.StoredProcedure;
            cmd.Parameters.Add("@USER_ID",SqlDbType.Int);

            SqlParameter outParameter = new SqlParameter("@RETVAL",SqlDbType.VarChar,10);
            outParameter.Direction = ParameterDirection.Output; 
            cmd.Parameters.Add(outParameter);   

            outParameter = new SqlParameter("@RETMSG",SqlDbType.VarChar,255);
            outParameter.Direction = ParameterDirection.Output; 
            cmd.Parameters.Add(outParameter);

            // assign Value to parameter userId
            cmd.Parameters["@USER_ID"].Value=userId;

            cmd.ExecuteNonQuery();
            RetVal = cmd.Parameters["@RETVAL"].Value.ToString();
            RetMsg = cmd.Parameters["@RETMSG"].Value.ToString();

        }
        catch(Exception ex)
        {

            //Use Ex.Message Exception
        }

    }

//InputParameter用户ID(Int)//OutputParameter REtVAL，RETMSG(varchar)

// InputParameter UserId (Int) // OutputParameter REtVAL, RETMSG (varchar)

这篇关于如何在C#中使用不同的参数执行存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！