How to create SqlParameterCollection with multiple parameters?

Question

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 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 public constructor and can be retrieved only from the property of 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 receives an optional list of SqlParameter and add them to the internal SqlCommand parameter collection if needed

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