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

You never tell us what error you get, how do you expect us to help if you don't say exactly what happened. — Scott Chamberlain
– Scott Chamberlain, Commented Apr 27, 2014 at 7:46
it gives error "Object reference not set to an instance of an object" — prog1011
– prog1011, Commented Apr 27, 2014 at 7:59

Steve · Accepted Answer · 2019-04-02 07:10:12Z

69

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());
    ....
}

edited Apr 2, 2019 at 7:10

answered Apr 27, 2014 at 7:58

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Scott Chamberlain Over a year ago

Instead of a foreach you can also just do cmp.Parameters.AddRange(parameterPassed.ToArray()) this works very well with a params SqlParameter[] parametersPassed parameter in your function.

Steve Over a year ago

Oh yes you are right, just busy fixing something on the parameter list initialization

prog1011 Over a year ago

@Steve It gives error because i want to add SqlParameter with SqlDbType and Value both, your code is allow only ("@ParameterName") and (object value) but i want to mention a SqlDbType also

Kyle Vassella Over a year ago

I'm using kudvenkat's tutorial for logging exceptions to database which only works for one SqlParameter. I'd like to integrate your list solution. In my example, we set the SqlParamter like so: SqlParameter parameter = new SqlParameter("@exceptionMessage", log); cmd.Parameters.Add(parameter); How can I change this SqlParameter format to fit your new SqlParameter() {ParameterName = "@CmpyCode", SqlDbType = SqlDbType.NVarChar, Value= CV.Global.CMPYCODE} format? I'd like to exclude everything but the @exceptionMessage if possible..

Steve Over a year ago

@KyleVassella Sorry but I have no reference to this tutorial. I suggest you to post a new question with the details about your current solution, add what you have tried to integrate this answer and probably you will have more people than me to look at your problem

|

Deathstalker · Accepted Answer · 2022-12-07 21:27:03Z

0

Here is a simplified answer. I use this type of thing for a dynamic SQL query with dynamic parameters. Sometimes you don't need all parameters if you are writing a dynamic sqlquery when determining if a variable has a value.

List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@StartDate", StartDate));
paramList.Add(new SqlParameter("@EndDate", EndDate));

if (TicketID != "" && TicketID != null && TicketID != "undefined")
{
    paramList.Add(new SqlParameter("@TicketID", TicketID));
    SQLQuery = SQLQuery + " AND A.TicketID = @TicketID";
}

var Parameters = paramList.ToArray();
List<Report> ReportList = db.Database.SqlQuery<Report>(SQLQuery, Parameters).ToList();

answered Dec 7, 2022 at 21:27

Deathstalker

1,0101 gold badge13 silver badges10 bronze badges

1 Comment

Deathstalker Over a year ago

Make sure to convert the list to an array.

Collectives™ on Stack Overflow

How to create SqlParameterCollection with multiple parameters?

2 Answers 2

8 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related