如何使用多个参数创建SqlParameterCollection？

I am trying to create a SqlParameterCollection, but gives error while adding some SqlParameter in sp.Add() method.

我正在尝试创建一个SqlParameterCollection，但在sp.Add（）方法中添加一些SqlParameter时会出错。

Please help me how to add parameter and how to pass it to my another function where I declare a SqlConnection and SqlCommand.

请帮我如何添加参数以及如何将其传递给我声明SqlConnection和SqlCommand的另一个函数。

SqlParameterCollection sp = null;                    
sp.Add(new SqlParameter("@CmpyCode", SqlDbType.NVarChar)).Value = CV.Global.CMPYCODE;
sp.Add(new SqlParameter("@Code", SqlDbType.NVarChar)).Value = codeName;
sp.Add(new SqlParameter("@DisplayCode", SqlDbType.NVarChar)).Value = codeName + "-";
sp.Add(new SqlParameter("@TotalDigit", SqlDbType.Int)).Value = CV.Global.PARAMTOTALDIGIT;
insertData("<Sp Name>", sp);

My another function is insertData(...)

我的另一个函数是insertData（...）

internal static int insertData(string spName, SqlParameterCollection sp)
{
        int retObj = 0;

        using (SqlConnection con = new SqlConnection(CV.Global.CONSTRING))
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(spName, con);
                cmd.CommandType = CommandType.StoredProcedure;

                if (sp.Count > 0)
                {
                    foreach (SqlParameter param in sp)
                        cmd.Parameters.Add(param);
                }

                retObj = cmd.ExecuteNonQuery();
            }
            catch (Exception ev) 
            { 
                Util.Log(ev); 
                throw; 
            }
            finally
            {
                try
                {
                    con.Close();
                }
                catch (Exception ev) { Util.Log(ev); throw; }
            }
        }
        return retObj;
    }

I am trying to create a SqlParameterCollection and passed it to the insertData function. But it throws an error while I am calling sp.Add() method in my first function.

我正在尝试创建一个SqlParameterCollection并将其传递给insertData函数。但是当我在第一个函数中调用sp.Add（）方法时，它会抛出一个错误。

The error is

错误是

Object reference not set to an instance of an object

你调用的对象是空的

1 个解决方案

#1

You cannot use any variable like SqlParameterCollection (a reference object) without a call to its constructor (new), but the SqlParameterCollection is an object that cannot be initialized directly with a new. It has no default constructor and can be retrieved only from an existant SqlCommand.

您不能在不调用其构造函数（new）的情况下使用任何变量，如SqlParameterCollection（引用对象），但SqlParameterCollection是无法使用new直接初始化的对象。它没有默认构造函数，只能从一个存在的SqlCommand中检索。

 SqlCommand cmd = new SqlCommand(commandText, connection);
 SqlParameterCollection sp = cmd.Parameters;

I suggest to change your InsertData method to accept a List<SqlParameter> and let it handle the adding of the parameters to the SqlCommand that executes the command text

我建议更改您的InsertData方法以接受List 并让它处理将参数添加到执行命令文本的SqlCommand

List<SqlParameter> sp = new List<SqlParameter>()
{
    new SqlParameter() {ParameterName = "@CmpyCode", SqlDbType = SqlDbType.NVarChar, Value= CV.Global.CMPYCODE},
    new SqlParameter() {ParameterName = "@Code", SqlDbType = SqlDbType.NVarChar, Value = codeName},
    new SqlParameter() {ParameterName = "@DisplayCode", SqlDbType = SqlDbType.NVarChar, Value = codeName + "-"},
    new SqlParameter() {ParameterName = "@TotalDigit", SqlDbType = SqlDbType.Int, Value = CV.Global.PARAMTOTALDIGIT}
};
insertData(CV.Sps.SP_INSERT_PARAM_TABLE, sp);

and insertData simply receives an optional list of SqlParameter and add them to the internal SqlCommand parameter collection if needed

和insertData只接收一个可选的SqlParameter列表，并在需要时将它们添加到内部SqlCommand参数集合中

internal static int insertData(string spName, List<SqlParameter> sp = null)
{
    ....
    if(sp != null)
        cmd.Parameters.AddRange(sp.ToArray());
    ....
}

#1

 SqlCommand cmd = new SqlCommand(commandText, connection);
 SqlParameterCollection sp = cmd.Parameters;