Prog Prog - 6 months ago 127
SQL Question

How to create SqlParameterCollection with multiple parameters?

I am trying to create a

SqlParameterCollection
, but gives error while adding some
SqlParameter
in
sp.Add()
method.

Please help me how to add parameter and how to pass it to my another function where I declare a
SqlConnection
and
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(...)

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.

The error is


Object reference not set to an instance of an object

Answer

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.

 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

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 takes this list of parameter passed and add them to the internal SqlCommand parameter collection

SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddRange(parameterPasses.ToArray());
Comments